June 17, 2008 at 7:07 am
Having not set up a package previously to use configuration files I'm finding it a bit of a struggle at the moment! I think I ought to start with a description of what I'm trying to achieve...
I've written a package to populate a number of sql server 2005 tables from a number of text files. Depending on when the package is due to run though, I only want to transfer a proportion of the text files across i.e. the overnight version should transfer all files but a mid-afternoon run only a subsection of the files. I've set up a number of user variables to state whether the transfer occurs for that file. I thought that I could then set up a configuration file that would allow me to set up separate Sql agent jobs to run using different values for these variables, in my example the overnight job would have all the variables set to Yes, and the mid-afternoon one with the subset of variables set to yes. Sounds good in theory, but struggling to get it to work.
I thought the best way from my perspective was to set up a SQL Server Configuration Type configuration file. I thought I would be able to add new executable configurations in the sql configuration table once the package was deployed. I'm not now convinced of this though. What I would prefer to avoid is having to add numerous configurations in the package configurations organizer every time and then have to redeploy the package. If I have to do it that way then so be it. Is this how it works?
Anyway, once setting up a configuration file and deploying the package to the server I was going to run the package from, I then went in to SQL Server Agent to set up the jobs. In the new step I then created to run this package I thought I'd see if I could change the variable content contained in the configuration file via the configurations tab. No such luck! It seems this only looks for xml type files, no sign of a reference to my Sql table, and there doesn't seem to be a means of viewing, checking, or amending the configuration setup in there. Am I looking in the wrong place?
I'm a bit at sea with this. What's the best way to setup what I'm trying to do here please?
If I'm to use the SQL server configuration type, should I have a first step in my sql agent job to update the values in my sql configuration table with the required values prior to running the package? Seems a bit over the top that really. Any help would be greatly appreciated.
June 17, 2008 at 11:07 am
Don't seem to be getting much response on this one. Having trawled the net on package configurations this seems to be the norm. Does everyone use the xml versions only?
June 18, 2008 at 1:15 am
Hey Martin,
Quite a long posting, didnt read through most of it, but thought you would like some feedback on configurations ....
I use SQL configurations quite successfully, and port across environments without any hassles.
Simply put
- Create an environment variable. ONE per environment (i.e. on your local machine, have one, on preprod have one etc....). This variable should contain the connection string to your SSIS Configurations database (if you choose to have one), or wherever the database is that your configurations is stored. Special note, if you running under windows authentication, your SQL server agent account will need access to these configurations, if its SQL server authentication, make sure you specify the userid and password as part of your connection string. This will tell SSIS where to get the package configurations. It is a once off, so if you get it right on one package, it works for all.
- In your connection manager, create a connection to the SSIS configurations database.
- In your SSIS configurations, add an environment variable configuration. Make this point to the SSIS configurations connectionstring. You will notice that the configuration type is Environment variable, and that the target object will be SSIS Configurations
- Now, from here you can specify any configurations that you want to make portable. Note, it is good practice to specify a configuration per single entity (for example, dont specify connection string along with variable value, rather do them seperately)
Let me know if this helps
~PD
June 18, 2008 at 3:23 am
Thanks for your response there PD. The connection string to the database that contains my SSIS Configurations table would remain the same for all environments I will be using as I'm developing the package on my local pc connecting to SQL on the server it will be running on once it gone live.
I'm a bit confused though with what you're saying here sorry. I thought the concept with SQL server configuration type is that it connects to the table containing our configurations using the configuration filter i.e. it selects all records that match the configuration filter for the configuration set in the package and uses that. Are you saying to set up a number of configuration filters in the SSIS Configurations sql table and use the new environment variable to assign the filter used by the subsequent SQL configuration setup?
June 18, 2008 at 3:41 am
I am saying that the environment variable will tell SSIS where the configurations reside.
The configuration filter will tell SQL what to load (i.e. configuration filter "MartinsDBConnectionString" which points to "MartinsDB" will tell SSIS what the connectionstring is to load)
Hope this makes a little more sense
~PD
June 18, 2008 at 3:59 am
Mmm. If the environment variable will tell SSIS where the configuration will reside, do you mean the table name and location as this will be the same wherever the program is running from?
If I had VARX as the variable I want the configuration to change for different run types (RunType1 = overnight run, RunType2 = lunchtime run) of the package, the package can from what I see only run one configuration filter. Lets say my table was called SSISConfigurations and configuration filter MartinDB1 contained the value property of this variable, set to 0 initially so as not to import the file (RunType2). I wanted the other run type (RunType1) to set this value as -1 (boolean data type field). I'd have thought the way this would work is that I'd have another configuration filter called MartinDB2 with it set to -1 and set in the sql agent job to use the MartinDB2 configuration filter instead of the defaulted MartinDB1. Doesn't look like it works this way.
Would I need another step in the sql agent job to run a sql statement to update the value of VARX in MartinDB1 to the other value prior to running another step to run the package?
June 18, 2008 at 4:22 am
Yes, the environment variable will dictate to SSIS where the actual configuration table lies, plus any connection information required (i.e. userid and password).
hmmmm, suppose we have the following:
a) varRunType. varRunType can be either "OverNight" or "Midday"
b) 1 variable called "MiddayConnectionString" and 1 variable called "OverNightConnectionString"
c) 1 Scripting task which reads varRunType. If varRunType = OverNight, it sets the connectionstring on the connectionmanager equal to OverNightConnectionString, if varRunType = Midday it sets the connectionstring on the connectionmanager equal to MiddayConnectionString
TADA, multiple connectionstrings using package configurations.
NOTE: specify 3 configurations
NOW, the trick will be to change the configuration to Midday or OverNight. You can do this as part of your agent job or whatever.
Alternatively, you can set the configuration at runtime with the agent.
Choice is basically up to your design
~PD
June 18, 2008 at 5:09 am
Ok, thanks for your input here. I'll give that a go.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply