Warning: This is SQL Server 2008. I need to add a package variable/parm - just a char value that will get passed into the package at execution time. The first task in the package will need to retrieve the variable which wil be used later in a Select statement. Which option should I choose below? If I choose SQL Server does it get stored into a SQL Table? (SSIS parameters came out in 2012 and made it easier. This is 2008). Thanks
I am still confused as to why you even need to create packages for 2008 using package configurations. You already have an integration services catalog available where you can deploy - so why are you still deploying to a 2008 instance?
What is the source of this value - and how will it be passed into the package? You state that the first thing the package needs to do is retrieve this value - so how is that value determined in the first place? If the package is going to retrieve it from somewhere - then you don't need it defined in the configuration file.
If the value is a fixed value - that will only be changed by which system the package runs on (e.g. prod vs test), then all you would do is add that variable to the package configuration file. Then you can have a different configuration file where that variable is defined as TEST on the test server and PROD on the production server.
With all of that said - deploying this to your catalog and all of this can be defined using environment variables.
1 Like
All valid questions, @jeffw8713. I may have mislead the discussion by using the term "package configuration".
Package configurations are useful for storing connection strings, user/passwords things like that.
But I need to pass a parameter (same as you would pass to a stored proc) to my (2008) package at execution time. That value will be a company code "ABC" when I run the package on mondays but will be "DEF" when the package gets executed on Tuesdays, for example. To meet business needs, we need the flexibility of processing only 1 company code at a time.
I can store the company code in a table then retrieve the value within the package but I want a more dynamic technique, not one where I have to change the table row every day. So I need to be able to execute the package from a job step and pass the company code to the package, somehow.
If this can be done in SSIS 2008, I'm not grasping the solution.
Thanks.
You still have not answered why you need to do this in a 2008 package - when you already have access to a catalog.
The reason I bring that up - you can set the variables in the command line or through t-sql code for packages stored in the catalog. It is a much easier to implement and manage. With that said - the command line option for 2008 DTEXEC would be /SET. Something like:
/SET "\Package.Variables[YourVariable].Value";""your variable value;""
I think you will find that doing this is going to take a lot of time and effort - and eliminates the ability to utilize SQL Agent jobs. Whereas using the stored procedures in the SSISDB database to execute your packages with the project parameters set will be much easier.
1 Like