July 18, 2007 at 10:05 am
Hi,
I am relatively new to SSIS, so I am a little confused about what some of the "best practices" might be. I am trying to simplify the configuration of my packages and make it seamless to move from environment to environment.
I have multiple SSIS 2005 packages that pull data from the same data sources (SQL Server catalogs). In order to consolidate the configuration settings, I created a Data Source (& Data Source View) for each catalog from which I am pulling data. In my packages, I created configuration managers that point to my data sources ("New Configuration Manager From Data Source").
The connection manager appears to make a copy of the settings (connection string, etc) from my Data Source. This seems to mean that if I redirect one of my Data Sources to point to a catalog on a different server (moving from test to production environment, for example), the package won't pick up the change unless I re-open (and consequently re-deploy) the package in BIDS. I also want to avoid having to edit the Data Sources using BIDS each time I develop/deploy in a different environment. I looked at the Data Source file, and it seems like a relatively straightforward XML file, but I can't have environment-specific Data Sources without changing each package to point to different Data Sources every time I change environments.
Ideally, the configuration would look something like this: Package (points to) Data Source (pulls connection information from) Environment-specific XML config file (one for each environment). Only the XML config file would ever have to change.
I looked into package configurations, but I'm not sure if they are intended to be used in addition to or instead of Data Sources. The package configuration is (duh) configuration stored at the package level. So I'm not sure if it's appropriate to use a package configuration AND a Data Source, because it seems like the config and the DS would be competing to tell the package how to connect to the data source. At least, that's how I interpret the behavior I saw when I created a package configuration.
Am I on the right path? Do people even use Data Sources?
July 18, 2007 at 2:40 pm
I found this thread on the Microsoft SSIS forum that seems to answer all my questions: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1748534&SiteID=1
Bottom line: Data Sources can be considered nothing more than a convenience provided by Visual Studio Designer (this is my summary of the poster's comments), not a true way to make packages portable across multiple environments. Package configurations are the preferred way to make packages configurable. One can't expect package configurations to play nicely with Data Sources.
July 20, 2007 at 6:54 am
Exactly that - Data Sources are templates to create connection managers. I never use them as I see no real gain.
You best be is to use configuration files. (Or SQL configuration to some extent)
With them, you can specify everything in the connection manager such as user, password etc etc.
You can either use an environment variable to store the path to the file (preferred) or have the file in a default location.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply