March 23, 2015 at 8:26 am
I am creating an SSIS Package which has a for each loop container. Inside the container are 2 tasks a data flow task and an execute sql task.
The data source for the data flow is a stored procedure (called LoadCorrtran) which has an output parameter called @feedkey nvarchar(10). The value of @feedkey is set as follows:
SELECT @feedkey =
CASE WHEN LEN(MONTH(GETDATE())) = 1 THEN '0' + CAST(MONTH(GETDATE()) AS NVARCHAR) ELSE CAST(MONTH(GETDATE()) AS NVARCHAR) END
+ CASE WHEN LEN(DAY(GETDATE())) = 1 THEN '0' + CAST(DAY(GETDATE()) AS NVARCHAR) ELSE CAST(DAY(GETDATE()) AS NVARCHAR) END
+ CASE WHEN LEN(DATEPART(hh, GETDATE())) = 1 THEN '0' + CAST(DATEPART(hh, GETDATE()) AS NVARCHAR) ELSE CAST(DATEPART(hh, GETDATE()) AS NVARCHAR) END
+ CASE WHEN LEN(DATEPART(mi, GETDATE())) = 1 THEN '0' + CAST(DATEPART(mi, GETDATE()) AS NVARCHAR) ELSE CAST(DATEPART(mi, GETDATE()) AS NVARCHAR) END
+ CASE WHEN LEN(DATEPART(ss, GETDATE())) = 1 THEN '0' + CAST(DATEPART(ss, GETDATE()) AS NVARCHAR) ELSE CAST(DATEPART(ss, GETDATE()) AS NVARCHAR) END
@feedkey becomes one of the columns in the destination table ( called Corrtran) CTFeedKey
@feedkey is mapped to the ssis variable User::parfeedkey as an inputoutput parameter
When the dataflow tasks completes the execute SQL Task begins. The execute SQL task is another stored procedure called CorrFeed. This procedure takes as one of its parameters an input parameter called @feedkey and is also mapped to the ssis variable User::parfeedkey.
The procedure finds the rows just inserted in the dataflow tasks by querying the Corrtran table with the where clause CTFeedkey = @feedkey.
The problem is the value of the variable in the dataflow task is not getting passed to the Execute SQL task correctly....User::parFeedKey is blank when I run the execute sql task...
What am I doing wrong. I tried creating the variable directly as an expression in SSIS then the problem is the value changes from one task to the next
March 23, 2015 at 8:39 am
How exactly did you do this?
> @feedkey is mapped to the ssis variable User::parfeedkey as an inputoutput parameter
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 23, 2015 at 8:49 am
I created a string variable in ssis called parfeedkey. In the dataflow I set the data source to SQL Command "exec LoadCorrtran ?,?" . for Parameters The first comes from the for each loop, the other is the @feedkey which is mapped to the variable user::parfeedkey and the param direction is set to inputoutput.
March 23, 2015 at 8:56 am
paulze2000 (3/23/2015)
I created a string variable in ssis called parfeedkey. In the dataflow I set the data source to SQL Command "exec LoadCorrtran ?,?" . for Parameters The first comes from the for each loop, the other is the @feedkey which is mapped to the variable user::parfeedkey and the param direction is set to inputoutput.
According to this thread, the output parameters doesn't really work:
To be honest, stored procedures and OLE DB source don't really work well together.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 23, 2015 at 8:57 am
mmmm do I specifically need to say exec LoadCorrtran ?,? OUTPUT ?
March 23, 2015 at 9:17 am
Adding the word OUTPUT seems have fixed the problem... I noticed the article refers to ssis 2005. Im using ssis 2012....Perhaps that issue was address in subsequent versions
March 23, 2015 at 9:18 am
paulze2000 (3/23/2015)
Adding the word OUTPUT seems have fixed the problem... I noticed the article refers to ssis 2005. Im using ssis 2012....Perhaps that issue was address in subsequent versions
That's good news!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply