The first thing I want to record is something I ALWAYS forget and have to look up every time - how to assign parameter values from a SQL Script task within SSIS. Obviously, it differs depending on an OLEDB connection or an ADO.NET connection.
With the OLEDB connection, you will first add an Execute SQL Task onto the Control Flow of your SSIS package. (This is assuming that you have already created both the OLEDB database connection, and the variable that you will be using).
Step 1: Select your OLE DB connection:
Step 2: Click the ellipse next to the SQLStatement property, and enter your statement. Please note how, in this specific example, I have given the field name a pseudonym (BeginDate), that I will also be using as a pseudonym for my variable. These two can not be the same, or the system will get into knots.
Step 3: Change your ResultSet property to Single Row. This tells the system that a single row is going to be returned from the query:
Step 4: Click on the Result Set link on the left of the pane, and press the Add button at the bottom. This is where you will be assigning that pseudonym (BeginDate) to your package variable (in this case, StartDate):
Step 5: Press OK, and run your package:
Now, that's all fine and well, but what if you've kept with the times, and want to use the ADO connections you are using throughout the rest of your package without having to add an OLEDB connection only to assign a value to a variable?
For step 1, you will do the same, except you will choose the ADO.NET connection under the ConnectionType property:
Follow Steps 2, 3 and 4 above, with one difference: When you get to Step 4, instead of giving the variable your pseudonym name, you MUST give it a 0. If you don't, this step will fail, and you will spend hours trying to figure out why:
And there you have it - a ridiculously small difference, that can waste hours of productive time.
Enjoy :-)
No comments:
Post a Comment