Last post May 29, 2015 09:35 AM by hong_ma
May 27, 2015 09:56 PM|hong_ma|LINK
May 27, 2015 11:27 PM|hong_ma|LINK
May 28, 2015 12:12 PM|limno|LINK
You can call stored procedure in your data flow task.
May 28, 2015 01:32 PM|hong_ma|LINK
<p>my code like below</p>
<p>DECLARE @TESTS AS TABLE(RefID [INT], MaterialNumber[varchar](18), N[FLOAT])<br>
INSERT INTO @TESTS<br>
SELECT A.RefID, A.MaterialNumber, CAST(COUNT(*) AS FLOAT) N<br>
FROM TESTS A<br>
INNER JOIN TBLTemp1 B ON A.RefID = B.RefID <br>
INNER JOIN ( <br>
SELECT BNum, A.MNum<br>
FROM @TESTS A<br>
INNER JOIN TBLTemp1 C ON A.RefID = C.RefID<br>
)B on A.BNum = B.BNum</p>
May 28, 2015 02:21 PM|limno|LINK
What is the issue do you have? I can see only one table variable @TEST1 but not the definition of @TESTS. Where are you defining the @TIME_STAMP variable?
You should be able to run this query as T-SQL statement without using stored procedure if they are runnable without errors.
May 28, 2015 03:04 PM|hong_ma|LINK
before the query , there is another temp table @ TEST1 and define @TIME_STAMP variable. this query can run without stored procedure,
the solution I have is change the query to one query without temp tables and can not pass variable to subquery inside this query. it's very hard, so
I looking for another way.
May 28, 2015 03:17 PM|limno|LINK
I would wrap the logic inside a stored procedure with SET-based approach. At the same time, you can pass variables to it and use it in your data flow task.
May 28, 2015 11:10 PM|hong_ma|LINK
May 29, 2015 04:07 AM|Edwin Guru Singh|LINK
then connect to destation source to insert data to another table.
not sure stored procedures in sql task will be set to return object variable or not , will try tomorrow
Click here to know more about Stored Procedure Return Values and Output Parameters in SSIS via sql task
Click here to refer the idea from similar thread about SSIS with Execute SQL task via Stored Procedure output parameter
May 29, 2015 09:35 AM|hong_ma|LINK
Thanks the info, it works