October 6, 2010 at 7:58 am
Hi,
I have not configured a SSIS file before and have decided that perhaps the best option for me is to configure my packages within SQL Server.
I have 2 questions regarding the configuration wizard:
1. Is the 'Configuration Filter' an identifier for a specific package?
2. When I select 'Finish' I get the following message displayed:
"Cannot retrieve parameter length of the ConfigurationValue column in the configuration table."
What does this mean? Do I need to specify a parameter? If so, how?
Thanks in advance,
Neal
October 6, 2010 at 8:50 am
1. Is the 'Configuration Filter' an identifier for a specific package?
Not necessarily, a single configuration filter can be shared across packages.
2. When I select 'Finish' I get the following message displayed:
"Cannot retrieve parameter length of the ConfigurationValue column in the configuration table."
What does this mean? Do I need to specify a parameter? If so, how?
I guess you have selected wrong property of the variable during the configuration. Ensure that you have selected Variable-->Value while configuring.
October 6, 2010 at 12:55 pm
We run a few dozen daily production SSIS jobs. I have my all of my package configs stored in SQL server in a single database. We have implemented a standard naming scheme for the Configuration Filter field. In my case, each package has its own configuration filter name.
I find it clearer to have a one to one relation between the package config and the package. That way if I need to modify a parameter for a rerun or a one-off run, I won't inadvertantly affect other packages, which could happen if you are sharing a config across packages.
May 20, 2011 at 2:09 pm
Ed Zann (10/6/2010)
We run a few dozen daily production SSIS jobs. I have my all of my package configs stored in SQL server in a single database. We have implemented a standard naming scheme for the Configuration Filter field. In my case, each package has its own configuration filter name.I find it clearer to have a one to one relation between the package config and the package. That way if I need to modify a parameter for a rerun or a one-off run, I won't inadvertantly affect other packages, which could happen if you are sharing a config across packages.
I agree that you can't go wrong having a one to one relation between the package config and the package. The duplication of config data is well worth the flex it affords you when doing a re-run.
Assuming you do not develop in PROD directly how do you manage configuration data when moving a brand new package from DEV to STAGING to PRODUCTION?
Also, how do you manage the properties for the connection that gets you to your configuration data table?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 22, 2011 at 11:39 pm
opc.three (5/20/2011)
Assuming you do not develop in PROD directly how do you manage configuration data when moving a brand new package from DEV to STAGING to PRODUCTION?Also, how do you manage the properties for the connection that gets you to your configuration data table?
You have 3 configuration tables: one for each environment, and each table resides in it's dedicated database on the corresponding server.
You can point to that config table either by an environment variable (my favorite choice) or by an XML config file (just make sure the absolute path to that file is the same on each server). You can look this up on MSDN under "indirect configuration".
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 23, 2011 at 9:54 am
Koen Verbeeck (5/22/2011)
opc.three (5/20/2011)
Assuming you do not develop in PROD directly how do you manage configuration data when moving a brand new package from DEV to STAGING to PRODUCTION?Also, how do you manage the properties for the connection that gets you to your configuration data table?
You have 3 configuration tables: one for each environment, and each table resides in it's dedicated database on the corresponding server.
You can point to that config table either by an environment variable (my favorite choice) or by an XML config file (just make sure the absolute path to that file is the same on each server). You can look this up on MSDN under "indirect configuration".
Thanks Koen. I figured 3 databases, one for each environment, would be the way to go. We use environment-specific logins for our sql agent jobs across environments to create an artificial wall between DEV, INT and PROD. How would you propose getting the data moved across environments? I was hoping to hear of some export tool that could dump config variable data to a SQL script or something homegrown to that effect. I can write something in pretty short order I guess but was hoping there was something in existence people were using.
Re: the indirect config. Have you ever had trouble with using environment variables when it comes to having to reboot to get those setup or changed? If I did not want to use an environment var I guess having one path synced across environments using the config file approach is doable but also could be a pain I guess.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 23, 2011 at 11:39 am
Euhm, about moving config data from one place to another:
I usually just script out the data from one environment using SSMS and then modify it slightly to put it on a new environment. 😀
A bit of tedious manual work combined with SSMS scripting, but it gets the job done.
In SQL Server Denali, the new version, most of these issues will thankfully be in the past.
Regarding the indirect configuration:
if the administrator doesn't allow environment variables, you are practically stuck with XML config files. And as SSIS doesn't understand relative paths, you must fiddle around with full paths. An issue that happens with environment variables is when you have multiple SQL Server instances on one machine. You can't use a single environment variable to configure them all.
But hey, what's the alternative? Using a registry key? I have never met a sane person who did that 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply