Is there a way to do a common package based on parameters

Question

Is there a way to do a common package based on parameters

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
October 5, 2016 at 7:11 am

#400836

I have a question to clear.
We are trying to implement a common package(Generic one) doing everything based on parameters (Which database to go and which source to go etc..) For the package we tweak it for other stuff as well if we need.It will pick up appropriate connection string.
For testing purpose we created one table with 13 to 14 connection strings in it. We have everything in this table related to configurations'. I mean list of connection strings, All the packages will load it from here..
Need Ideas please is it possible to do all in one package?
The main concept is we have to pull 15 to 16 country's data into our system. All are in different connection strings..

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

tim.ffitch 25252 SSCrazy Points: 2055 More actions · Answer 1

Use an Execute SQL Task to run the SQL to return the connection string records. Map the result to an object variable in SSIS. Then use a Foreach loop using the Variable as the control for the loop.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Exactly what Tim said. Just be aware that the tables' structure should be the same on all connections or you'll be prone to errors.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eric M Russell SSC Guru Points: 125561 More actions · Answer 3

In most of my SSIS packages, I have one project parameter called @Environment which indicates: 'UNITTEST', 'DEV', 'QA', or 'PROD', and I have (4) parameters for each Connection Manager respectively. I then evaluate the ConnectionString property of each ConnectionManager as an Expression that references the appropriate connection string parameter depending on the value of @Environment.

For example, let's assume there is a ConnectionManager called [StoreOpsDW]. The expression for it's ConnectionString property would be:

(

@[$Project::Environment] == "UNITTEST" ? @[$Project::UNITTEST_StoreOpsDW]

:

@[$Project::Environment] == "DEV" ? @[$Project::DEV_StoreOpsDW]

:

@[$Project::Environment] == "QA" ? @[$Project::QA_StoreOpsDW]

:

@[$Project::Environment] == "PROD" ? @[$Project::PROD_StoreOpsDW]

:

"(Invalid $Project::Environment)"

)

Also, when building SSIS packages that do stuff like dynamically building SQL or referencing alternate objects, familiarize yourself with the following:

- Evaluating a variable as an Expression.

- Setting the data access mode of a source to: "SQL Command from variable".

- Setting EvaluateExternalMetadata = FALSE

- Setting DelayValidation = FALSE

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Eric M Russell SSC Guru Points: 125561 More actions · Answer 4

In my previous post, I meant to say "setting DelayValidation = TRUE".

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 5

mcfarlandparkway (10/5/2016)
I have a question to clear.
We are trying to implement a common package(Generic one) doing everything based on parameters (Which database to go and which source to go etc..) For the package we tweak it for other stuff as well if we need.It will pick up appropriate connection string.
For testing purpose we created one table with 13 to 14 connection strings in it. We have everything in this table related to configurations'. I mean list of connection strings, All the packages will load it from here..
Need Ideas please is it possible to do all in one package?
The main concept is we have to pull 15 to 16 country's data into our system. All are in different connection strings..

Normally I would suggest using two configuration parameters, one for the log which is initialised before the package is executed and one for the configuration tables (I usually have 8-10 tables) for the variable configurations.

😎