Sharing Configuration Info Among Packages Via Variables
Mar 26, 2007
It is pretty easy to create the package configuration to the shared, never-changing path to XML file. But to get this to work, I have to add a bunch of package variables which are driven from the configuration. I then use expressions to compute actual paths to source files and packages.
This business of copying a block of variables from one package to another is extremely tedious, and at least to me, something of a maintenance issue. I have found if I get ahead of myself and forget to create the variables first, then Visual Studio is NOT very forgiving, and will complain repeatedly just prior to crashing without the offer to save anything. If I add a new variable, it is like an easter egg hunt. This does NOT feel very single point of maintenance when variables must be used in this way to really effectively apply configuration, and it is so tedious and error prone, and unforgiving. I seem to spend a great percentage of time copying the names and datatypes and values of variables ONE AT A TIME. What is that all about?
I hear people telling the praises of configurations, but I must be missing something.
With DTS I used a system where global scope settings were "injected" into packages as they ran, and I never had to worry about anything, and it was damn hard to goof it.
Please someone, evangelize me!! Are people bypassing VS and using NOTEPAD on the packages or something like that to save time?
Ok, I understand it is possible, but I still can't quite get the mechanics of it to work.
I create a new BIDS project, and add a package to it, and add 3 connection managers, and set the server, instance, DB, etc. for SQL connections. I change the name of each connection manager so the server name is not there, but use something more like a generic name of what the database is. Fine. I right click in the Control Flow area and go to configurations. I enable configurations, and save to a common place on the C drive. Save everything, exit, VS, fine, and I export all connection managers properties.
Then I start Visual Studio again. Create another new BIDS project, add a package to it, and add 3 connection managers. I don't actually connect them, but use those same generic names from the first iteration. I enable configurations for the package. I am somewhat expecting to see the connections change from the same-o-default to the data I used in the first go around.
The scenario is an ETL that takes flat file feeds via FTP to move data into varous production SQL server databases nightly.
There are a number of packages involved, and this depends upon the type of data being sent.
There are a set number of servers and databases to receive the transformed data. I would like to be able to define say 3 servers, and maybe a couple of databases in each one time in the configuration. For simplicity lets say 6 databases total. I would like a single point of maintenance for these 6 locations. I would like all connection managers in all packages in all solutions to share these 6 settings in all connection managers. Is this possible? From my initial attempts, it would appear each package gets its own independent list of connection managers and which must be configured separately. I don't see how to share settings, which is really where the power of SSIS configurations would be.
Similarly, I would like to be able to locate flat files at given paths. The package may know the name of the file it is looking for, or the file it will create, and the folder path needs to be computed from a configured folder root, and for the package connection manager to store only the name of the file, which never changes.
I can not see how to set something like this up with connection managers and configurations. Perhaps I still need a highly customized solution to achieve this, as we did for DTS, where we would only execute packages using our own executor, which would load the package, search out all environment specific settings and modify them on the fly prior to executing the package.
Thanks for any direction you can give here. The books I've read seem to imply I might be able to do what I need here, but I can't seem to find the mechanics of making it happen.
I'm using an XML file to configure my dataBase connection string. I try to deploy my package on a new server and it works perfectly.
Then I made a second package which also need a configuration for the dataBase connection string. (I made the connection with the connection manager inside packages). The configuration is the same that for my first package, so I thought to use the same configuration file.
I can use the same configuration file but the problem is when I try to generate a deployment for my solution. I got an error which tell me that the xxx.dtsConfig file already exist and can not be copy again.
When I made the configuration in the second package I said that I want to reuse the file ... and I thought that for the deployment SSIS would know that it has to include that file only once ...
This seems like a no brainer, but it's driving me nuts. I want one XML file for the entire solution. There are multiple packages in the solution which have different Connections in the conntion Managers. The packages share some connection names, and some are unique to the package.
Example:
LoadData.dtsx would have a source database connection named (SourceDB_OLEDB) and a oledb connection (DataWarehouse_OLEDB).
LoadDataMart.dtsx would use the same name for the (DataWarehouse_OLEDB) connection and have another oledb connection (DataMart_OLEDB)
I want one XML config file that has all the connection strings, but the problem is that the LoadDataMart.dtsx will throw an error:
Error 1 Error loading LoadDataMart.dtsx: The connection "SourceDB_OLEDB" is not found. This error is thrown by Connections collection when the specific connection element is not found. c:ssisLoadDataMart.dtsx 1 1
I want to log package info like when the package starts and ends, and write info to a sql server table. there are of course many ways to do this. I just want some opinions from you if you have some clever ways to do this.
€™m just starting out with SSIS and am trying to establish architecture standards that are sound and workable. We will be developing BI solutions for customers who use our banking and finance package that runs on an IBM System i. Modularity is important as all our BI solutions will have a combination of re-used functionality along with varying degrees of unique functionality for each customer. My current area of interest is in externalizing all environmental factors and ensuring such things as passwords are protected.
I understand how configuration files work and how to use environment variables to provide a layer of indirection to them. At this stage we want to avoid encryption of packages and plan to use file system based deployment. We plan to use windows security for SQL server so I have no issue in exposing connection info here but for our DB2 connections we need to include user id and passwords. As I understand it, configuration files can not be encrypted so how do I store this externally while preserving its security. Nothing I have read so far has given me exactly what I need.
I'm new to SSIS. I'm trying to build a simple package, run it on dev and migrate it to our stg and tst environments. It works on my dev environment great! It simply loads data from a flat file into a table. The trouble i'm having is exporting the connection string info to an xml file and runnign it on other environments. I am not storing the package on the db. I am saving to a file and running through SQL Server BI Development Studio.
I go to "Configuration File" and export InitialCatalog, ServerName, User, Password to the file. I can manually change the xml to the new environment but don't know how to set the password. It's not in the xml and i am constantly getting "Login failed for 'sa'" errors. Anyone know how to do this. I CAN get this whole process to work if i use Windows authentication security but this isn't an option. I need to use the db security.
How to access "Package Configuration" variables values inside the VSA.
For Ex: When I create the Package, at the Package Configuration Level I have created 2 variables. One is for MDB file Location and another is for SQL Server configuration details.
Now, When I move from Dev. server to Prod Server, the environment would change therefore I want to access these variables inside the design script (VSA).
I need some information on the package configuration files. i.e how to set the configuration parameters from outside the packages. Pls provide me some detailed information along with examples. I want to make my packages to read paramaters from the configuration files as we have in .NET(web.config)////
Secondly I want to know how to play well with variables.. i.e setting a variable.. reading a variable.. passing a variable in the SSIS package dataflows and datatasks.. respectively. Please provide some infomation and a good exam[ple on it I need
We can set the connection parameters using Variables as well as thru Package Configuration. My question is, if we use both to pass value for a connection string, which one will take precedence or in other words, which value will be used?
I would just like to give my feedback on the configuration packages implementation in SSIS:
THIS STUFF SHOULD BE A NO BRAINER AS IT IS A COMMON REQUIREMENT ON ETL PROJECTS.
I have trolled through these forums and found that I am not alone on this issue. Note for SSIS Team: Please bring out a HOWTO on this subject that details all the little work arounds because intuitively used it does not work.
Consider this:
I develop in BIDS on my client and everything runs. I create a config package and I choose only the Name and ConnectionString Properties for each Connection Manager. (Thanks to Jamie for that tip)
I build the app and a deployment utility is created. I navigate to the deployment directory, and deploy. I point to a mapped share on the production server, change all the properties for production and continue until finished.
On the production server I run a script containing:
Still: Cannot find the configuration file: source.dtsConfig
I have to MANUALLY edit the dtsx file to put the path in from the the name of the config file and THEN it works.
Thinking that it might not like a mapped share from desktop to deploy to, I copIED the contents of the depLoyMENT directory on my desktop to a folder on the production server and deploy from there. It should pick up the proper path from there. NOT!!!!
So it was back to editing the dtsx file.
So there you have it. I await all comments with great interest.
I hope this is all fixed in SP2, Because I don't want to waste anymore time on what should be a simple task.
I have deployed to production a number of nested packages (parent packages that call child packages) to the SQL msdb via the Save As option rather than building a deployment utility. These packages reference configuration files in a static location off of the c: drive on the production server. In the development environment, when connection changes are made and I run the Reload with Upgrade option the connection manager takes on the new server and user id settings. However, out on the production side I get the following error from the SQL job log:
Cannot load the XML configuration file. The XML configuration file may be malformed or not valid.
As a result the SQL job uses the default connection information which references the development database rather than the production database. I did research the error but found no good solutions. Is there a way to ensure the configuration files are formed correctly and that the packages are correctly referencing the configuration files? We are trying to run the ETL updates via a SQL job.
I'm working on a solution in Visual Studio that has 3 SSIS packages, and now I want to add a 4th that needs access to variables defined in one of the other packages. How can I do that?
hi, Is there any way to export a set of variables from each other package?? Issues comes when you have to built five packages which share the same variables. It's very tedious and hard to re-write them every time... Thanks for your time,
I have a simple task that I would like to manage through a SSIS package but do not know how to accomplish it. I need to perform the following tasks:
1. update a sql server table (ecwcust) and set the ftpstatus column to 'P' when the column's value is 'E'. update ecwcust set ftpstatus = 'P' where ftpstatus = 'E' 2. declare a variable and set the variable to the number of rows that have a ftpstatus equal to 'P' declare @newcustomercount int select @newcustomercount = count(*) from ecwcust where ftpstatus = 'P' 3. if @newcustomercount > 0, copy all of the rows from ecwcust where ftpstatus = 'P' to an excel spreadsheet. 4. send an email to an email recipient with the excel spreadsheet as an attachment. 5. delete the excel spreadsheet.
I do not know how to create the variable @newcustomercount so that I can determine whether or not to create the excel spreadsheet and copy the records to it. I have read all the online help and stuff that MS has but I can't understand the instructions. Please point me to some instructions that are understandable.
I have three variables that are stored in an SQL Server configuration table. When I execute the package, they do not seem to be getting updated. At what point do the configurations get loaded?
I have two packages share same configuration file. There are 4 connections in the first package and 2 connections in the second. Configuration file contains properties for 4 connections. First package opens in VS designer with no error. The problem with second package. I get error that connection exist in the configuration but not in the package and "Could not copy file " Master_Configuration.dtsConfig" to the deployment utility output directory. Master_Configuration.dtsConfig' already exists." Any help?
Basically it seems that whilst you can indeed share a config file, it doesn't aggregate sets of say different connection managers, into a superset - you choose to reuse, but it actually overwrites. Any ideas, anyone??
When executing a package from Business Intelligence Studio variables are well passed between packages but when same is done using dtexec utility variables don't get passed between packages. Does anyone have any idea why this could be happening and what would be possible solutions? Thanks in advance!
I need some clarification. I am trying to utilize the XML Direct Configuration in my SSIS packages to utilize database connections in the package. I am wanting to utilize this the same way you could use UDLs in the SQL Server 2000 DTS packages.
Currently, I am creating the dtsConfig file and saving it to my desktop. I then modify it with notepad and add the following configuration where it looks like this:
Once this is created, I am trying to "re-use" this in SSIS packages created moving forward, where they all point to this configuration for the same database connection.
What I don't understand is when "Enabling Package Configuration" and then pointing to this dtsConfig file doesn't create a connection in "Connection Manager" NOR does it provide a way to create a blank connection and point to this configuration.
I feel like I am missing something thats so "great" about XML configuration files. Any help would be appreciated.
We have been conducting some testing regarding package deployment and SQL Server based configuration. It seems there is a problem that was documented in an MS Feedback entry (
I searched for a viable answer, work-around, or hotfix that would address this issue but found none.
The jist is that a package that uses SQL Server based configuration must have an xml-based configuration entry to "re-point" the SQL Server based configuration connection manager to the deployment target server. We cannot use environment variable or registry as this is against internal policy. The problem is that even though the xml based config is specified first in the list of package configurations it does not get applied first at run-time. So, when the package runs from a SQL Server Agent job the package's connection manager for the configuration entries is not updated.
The package runs correctly through BIDS. You can change the connection string in the .dtsConfig file and the SQL Server based package configuration is obtained from the correct source.
Environment is SQL Server Enterprise Edition 64-bit w/ SP1, Windows Server 2003 Enterprise Ed. 64-bit.
Does anyone have any experience with this issue? Know any hotfixes, other work-arounds?
I currently have multiple (parent and child) packages using the same config file. The config file has entries for connections to a number of systems. All of them are not used from the child packages. Hence, my child package throws an error when it tries to configure using the same config file because it can't find the extra connections in my connection collection.
Does anyone have any ideas on the best way to go about resolving this? Is multiple config files (one for each connection) the only way?
We're just in the middle of performing our first release of SSIS packages through various environments.
The way we are set up currently is the developer will check the package(s) and related config files out of source control, develop on their own machine and check everything in again. Then we deploy the packages consecutively to the Dev, Tst and Prd servers.
We are going down the path of using one environment variable for every config file. some packages share config files (e.g. we've only one config file for each database or ftp connection etc.) and some config files are package specific (error log file connections and success/failure e-mail sources etc.).
What we want ideally is a script that we can check into source control that will create the environment variables on a server at deployment. The "set" command at the command line can be used to change the value of an environment variable, or to create a session-specific variable, but not to create environment variables.
So far the only method that we're using is manually typing in the environment variable names via control panel and copying and pasting the paths into the value fields. Given that we're deploying potentially hundreds of config files, it's obvious that this new-fangled GUI point-and-click and copy-and-paste method of deployment is absolutely foolproof and totaly not prone to any error whatsoever.
Please tell me there's a way to create and set environment variables without going through control panel. running a script or something to do it automatically will: Ensure that each environment is set up accurately and identically, eliminating human error. Ensure that when a developer checks out a package to their local drive, although they may have to change the variable values, he can at least create the relevant variables without having to type them in. Enable efficient migration to another new server (for example during Disaster Recovery).
Can anyone point me to some example scripts at all?
We have an existing OLD System in SQL Server 2000 DTS Packages. The Whole application runs on DTS. There are several Packages which are called from a Master Package. Each Child packages have their own Global Variables. Most of them are the File Location variables to have the Source Location of the Input Data, mainly from the Excel Files. Now, even if the Global Variables are there to change whenever they want to change the Locations of the Files, they have to goto each child package and change the variable themselves. To resolve this issue, they want a configuration File (INI) / Table which would store those Variables. My thought is to read from that File/Table and Update all the packages' global variables through an ActiveX Script as the First Step of the Master package. That would eliminate the need of changing anything in the existing System. But the Problem is the management (PM / DBAs / Team members) have different views to store the Configuration data. 1. Some wants it into a Different Database, having one table for this application so in future they can also add another table for some other application. 2. Some wants to store it in a Table in the Same Database of this Application. 3. Some wants to save it as a INI file.
As i'm the one who's going to really implement it, they have asked me to research for a best solution out there.
so I request to help me to decide which is a good solution and why.
I am currently migrating from Oracle to MS SQL Server 2005 using SSIS. Since the new schema being used on the SQL Server is very different, we have created separate packages for each target table.
We have several different sets of data stored as separate Oracle schemas, and I would now like to change to a different schema for the OLE DB Source objects in my packages to use (Table/column names to remain the same). While I can go through all packages and search through the drop-down list to select the table of the same name from other Schema, doing so is an extremely slow process.
Is it possible to use configuration files to set a schema to be used for a number of OLE DB Sources over a number of packages?
At the moment I am doing a find and replace over the .dtsx files, but can't believe I have to resort to this for something that I imagine many people would want to do with SSIS.
I've found this problem that when I change settings in my configuration file it does not automatically apply to all child packages which uses the same configuration file if run from a job in SQL Server Agent. I need to open the package and save it again from BIDS. I use one "load group" package to execute all other packages.
Is there a way from the job configuration to set a setting so the package allways will have the newest configuration?
Hi everybody, I moved my packages to the new machine, and have problem with parent variables. When child package tries to get parent variable value I get an error:
Information: 0x40016042 at LoopPackage: The package is attempting to configure from the parent variable "MAIN_SesId".
Warning: 0x80012028 at LoopPackage: Process configuration failed to set the destination at the package path of "Package.Variables[User::MAIN_SesId].Properties[Value]". This occurs when attempting to set the destination property or variable fails. Check the destination property or variable.
