November 4, 2009 at 9:25 am
Hello,
I have a bunch of SSIS Packages which have variables set for the Destination Connection and Source Connection. The variables are set within the packages themselves. Our DBA want us to make it so the variables for these connections are actually set within the agent job that calls the SSIS package so that when the DBA deployes our packages from UAT to production they only have to update the variables in the agent job to point to the production paths as opposed to having to update the SSIS package manually. Waht is the best way to accomplish this? I'm unsure how to make my package call a variable set by an agent job.
November 4, 2009 at 9:40 am
Your confusion is understandable, it is not clearly documented or very clear at all.
I think I want to clear up a misunderstanding first. The variables are ALWAYS in the package itself, there is no calling them from the job. What I mean by this is that the job sets the variables at run-time and then the package just runs with them, the package does't have to do anything special to use the variables set by the job.
Also, I would recommend that you use package scoped variables for this, it makes it a little easier to explain.
Now, to set a variable for the package you go to the "Set Values" tab of the step, to set a variable called: Test1 you would set the property path to:
\Package.Variables[Test1].Value
And the value to whatever value you want it to be.
When the job starts it will set these variables to the specified values and then the package will use them. Nothing special about them as far as the package is concerned, just regular variables, that happen to have been set at the start.
If you go to the "Command Line" tab yuo can see how it actually builds up the command line with your variable substitution values..
Clear?
CEWII
November 4, 2009 at 9:51 am
Thanks, Elliott! This helps big time. In the example you provided :
\Package.Variables[Test1].Value
Lets say I wanted to use the above to set my source connection. In my SSIS package do I create a variable for the Source connection, lable it 'Test1' and leave the value blank?
Thanks again for the help.
-Code
November 4, 2009 at 9:56 am
For connections it is even easier. There is a tab for connections. Go there and check the checkbox next to the connection you want to change and then change the connection string, done!
The only thing I would caution there is chained (parent-child) packages that you want to use the same connection string. But novice users don't usually create scenarios like that. Still I wanted to warn you that such cases require additional care.
CEWII
November 4, 2009 at 9:59 am
Ah, ok. Nice. How about Excel Destination connections? How should those be handled?
November 4, 2009 at 10:10 am
Since they are defined as connections they should be the same, their "connection string" looks a little wierd but is fairly self explanatory..
That does beg another question though, are you using a 32 or 64-bit version of SQL?
If it is 64 then we have additional work to do..
CEWII
November 4, 2009 at 10:46 am
Yes, 64 Bit. Currently I have the Agent Job step configured to run the package in a command line with the following for it uses the correct jet engine: "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /SQL "\SSIS PACKAGE NAME" /SERVER "SQL679\PRODDATABASE" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E
November 4, 2009 at 11:13 am
Great, you are on the right track.. The 32/64 thing bites more people than I can count. I decided to add a suggestion for it:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=508253
Well I like to use SQL Agent to build the command line in these cases and then use the built command line in the step calling the 32-bit version..
Good job. I think you are in good shape..
CEWII
November 4, 2009 at 12:35 pm
Excellent. Thanks again for your help, I really appreciate it. 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply