February 8, 2010 at 5:41 am
i've used dts and SSIS for a while now but never used it for anything more than simply moving data from one place to another.
We're setting up a Datawarehouse (quite a large scale) and this may incorporate 100+ ssis packages. Now, i know i can amend package config to store connection strings but is this the best way to make the environment transferable? For example, if i do it this way, i will have to update the connections/config table with the relevant connection hosts in order to pull information from the various environemnts i'd have (dev/integration/test/live) but i was wondering if there is a more savvy way of doing it? For example using an ADO variable to store @@Servername or the likes?
Also, if i were to use this method, is there a way of creating a package template so whenever i create a new package it automatically uses the package configuration i've setup as opposed to having to add it everytime?
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
March 12, 2010 at 9:36 pm
I'm using SQL 2005 but should work the same.
Once our configs are setup there is no need to alter anything to move from one environment to another.
Basically there is a config that sets the connection string of a connection manager that is then used to apply the SQL Configs. The connection string is retrieved from an XML file and the location of that file is provided by an environment variable that is setup on each server or workstation.
To Implement:
To start just do this locally on a single empty package, this package can become the SSIS VS template. (explained at the end)
First each package is setup with an OLE DB connection manager. I call it SSIS_CONFIG. It must always have the same name. This connection manager will be used as the data source for all your SQL Server configurations.
Next Create an XML configuration and specify the connection string property of SSIS_CONFIG. Create it anywhere, on your desktop or wherever. Your going to make copies of this XML, one for each environment that a package is run, or developed in.
You need to put each copy in its own folder since they must all have the same name. I call mine SSIS_MasterConfig.dtsconfig and I put them in folders using the server name as the folder name, for the (local) environment I create a folder named LocalDatabase. Initially I had stored them all in one central location and gave my proxy accounts access to them. This was changed due to disaster recovery concerns, we now keep a folder which contains the folders for all environments on each server. This can be a maint headache if you have a lot of servers so a central location that can be easily restored in a DR situation may be better.
Next on each server or laptop / desktop that a package is run, or developed, you need to create an environment variable. Name it something like SSIS_Master_Config_Location and give it a file path to the XML file in the folder for that environment. This variable also needs the same name everywhere.
Now, modify each XML file by changing the connection string so that data source = ServerName and Initial Catalog = Your SSIS config database. I have a database on each server called SSIS. This database is for storing all package configurations. If you have different named databases for different environments you will need to specify that database name for the initial catalog in the appropriate files.
Now you should have a connection manager, XML config files, and an environment variable setup.
In your package go to the configs open the XML config you created and switch it to get the value from the environment variable. Now when the package loads the connection string for SSIS_CONFIG will come from the XML setup for that environment.
Now you can specify the other SQL Configs you need in that package and use SSIS_CONFIG as the connection manager. Remember, the configs are applied from top to bottom so the XML config for the SSIS_CONFIG must be the first in the list. When the package loads it sets that connection string first then sets the following SQL configs based on that connection.
You do need to maintain multiple sets of configs, one for each environment. Also I have a table called Shared Configs that contains anything I think will be relevant for all or groups of packages such as email settings, logging connections, etc.... Every package we create has a set of variables and connection managers that is shared among all packages. One thing to remember when sharing configs is that all items setup in the config must exist in every package using that config.
Next the template. Once you have this package built with all the configs and whatnots you want, save it in C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems
You may need to adjust the path for VS2008.
Use this from Visual Studio by going to the file menu: Project / Add New Item / then pick the template name.
Once the template is loaded into your project you must change the ID property (Guid) of the new DTSX package. Do this by right clicking on the control flow workspace and selecting Properties. Click the dropdown by the ID property and select <Generate New ID>. If you don't do this then all your packages will have the same GUID.
Hope that all made sense - tom
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply