Link to home
Start Free TrialLog in
Avatar of Jim Youmans
Jim YoumansFlag for United States of America

asked on

Set Up SSIS data source to use a system environment variable

SQL Server 2012 and VS 2013 for SSIS.

I want to set up a connection in my packages to point to a configuration table for the packages other connections (OLE DB, Flat File, and Excel).  I have read you can use a system environment variable to do that so that when you promote the packages to different servers, it will pick up the environment variable (named the same) and point the package to the correct configuration table on that server (for dev, test, and prod).

I have a system environment variable set up (SSIS_Config) with the connection string for the SSIS Configuration table in it on the server and a data source (dsSSISConfig) in each package to connect to the SSIS Configuration table.  There are a couple of other data sources in the packages that I want to config from the SSIS Configuration table and have it set up for them.

So how do I make the dsSSISConfig data source use the system environment variable SSIS_Config?  Can I do that?  I can't seem to find a good example of how to set it up.  Any help would be greatly appreciated.

Thank you!

Jim
Avatar of Jim Youmans
Jim Youmans
Flag of United States of America image

ASKER

After a few hours of trial and error and searching the internet I figured it out.   You have to use the environment variable to set the connection string property of the data source and then use the data source to connect to the SSIS configuration table to get the values for the rest of the package.
ASKER CERTIFIED SOLUTION
Avatar of Jim Youmans
Jim Youmans
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial