October 20, 2011 at 9:55 am
Don't forget to flip the flag -- EvaluateAsExpression to True!!!! Otherwise it will just ignore that beautiful expression you just composed and go about its merry way using the date you picked originally and your package will run with the wrong values and screw up all your (hopefully test) data.
You would think that Microsoft in all its infinite wisdom to make everything user friendly would've popped up one of their message boxes (you know the ones that pop up every millisecond when you've done something wrong and pounding on the enter key relieves your frustrations but still can't make them all go away) when you close the expression editor to remind you that your expression will be ignored unless you flip the flag to tell your variable to use it. It should say something really useful like "Hey you, thanks for composing an expression, but we've got a flag that will ignore it unless you turn it on because we really couldn't figure out that just because you added an expression that you REALLY wanted to use it"
+________________+ +__________________________________________________________________+
|Use Your Expression| |Ignore the past 10 minutes of work and use that lame value you chose originally|
+-------------------+ +---------------------------------------------------------------------------+
Those are buttons by the way, and I apologize for my obvious (hopefully) sarcasm.
October 20, 2011 at 10:39 am
Yes that is a good point. I was assuming we were using a sql task and a single result set to assign the value to the variable up higher in the control flow. But yes that is a great point. I have been burnt by not setting that value to True when assgined a value to a variable using that methodology.:-)
December 18, 2011 at 6:11 pm
I do think you are missing a + sign
"exec [dbo].[spGetContractForCMS_ImportEDW]" + "'" + @[User::RunDate_EDW] + "'"
February 22, 2012 at 1:46 pm
Actually you can do it quite simple - for example you are using a stored procedure to load data from SQL Server or other RDBMS - LoadData_sp.
And this procedure has 2 input parameters @date_start and @date_end.
You are going to use SSIS package variables as input parameters for your stored procedure:
Step 1: Create - USER::DateStart and USER::DateEnd variables.
Step 2: Set their data types to datetime.
Step 3: Use any appropriate expression to assign dates to your variables.
Step 4: Put into SQL Command text box:
exec LoadData_sp ?,?
Step 5: Only after you put ?,? instead of input parameters you can click buttom 'Parameters' and see a dialog window to assign them to variables.
By default their names like Parameter1 and Parameter2.
You need to use exact names of your stored procedure parameters instead of Default names - @date_start and @date_end.
Step 6: After you enter a proper SP input parameters names on the left - you can select your variables from drop down boxes on your righ.
Finally you have a mapping table like:
SP parameter=SSIS variable_name
That is it.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply