Dynamic source and destination in a SSIS package

Question

Dynamic source and destination in a SSIS package

DamianC

SSCrazy Eights

Points: 8023
More actions
September 2, 2015 at 4:28 am

#302702

Hi
I'm looking to create a package that truncates and populates a few tables
I want the source and destination server/database to be dynamic, so I can deploy the package on multiple servers (develop/test/live type scenario)
Then, once deployed, call using changeable parameters for the source and destination in SSISDB
What would be the best approach here? Does my method make sense?
Could somebody point me in the direction of a step-by-step method
Much appreciated
Damian.
- Damian

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Damian

For moving through from dev to test to live, use Package Configurations. I find it works best with a configuration file that you specify in your job. There's numerous resources on the web describing how to do this - here's an example.

For changing source and destination after deployment, set up package variables called @Source and @Destination (for example), and retrieve values for those variables at run time by querying your SSISDB database - you can do this with an Execute SQL task containing a query with an output parameter. You can then use the values of the variables to set the properties of the connection managers.

John

DamianC SSCrazy Eights Points: 8023 More actions · Answer 2

Thanks John

I've also just found this

It looks like quite a good approach

What do you think?

Thanks

- Damian

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Yes, certainly if you need to connect to multiple sources and destinations every time your package runs. My advice was based on my assumption that you only needed to connect to one source and destination, which would change every time the package runs.

John

Sir Hossfly Say Hey Kid Points: 663 More actions · Answer 4

I basically use this for my packages. Something to consider is still use package configurations to denote the various environments...assuming you have a SQL Server running SSIS in DEV, QA, and PROD.

I configure an environment variable (named the same) on each server that contains the data source of the server. This requires a package configuration to be created in the package that looks at the environment variable. This way when you deploy to QA, the environment variable loads when the package runs and points to the SQL Server that contains the other QA connection strings and other config items.

This is a rough overview but do you see where it's going?

DamianC SSCrazy Eights Points: 8023 More actions · Answer 5

Thanks

I've used the post deployment source and destination method

That works well

Thanks

Damian.

- Damian

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 6

John Mitchell-245523 (9/2/2015)
Damian
For moving through from dev to test to live, use Package Configurations. I find it works best with a configuration file that you specify in your job. There's numerous resources on the web describing how to do this - here's an example.

John, package configurations are sooooo 2008 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP