February 19, 2008 at 4:24 am
My first ever package copies a base excel file to a new version that will be populated by the next 2 packages in the project.
At present each of the excel connections has the path hard coded into the package. As there will be different version in Dev, Test and Live, and I do not want to amend the package each time it is moved between environments, I have set up variables for the paths.
What totally confuses me is how to manage updating these variables in an environment dependent way. After some research, I have elected for an xml configuration file and an environment variable that says where this source is.
Then I realised I could have a version of the same package in all 3 environments, so which environment should my environment variable point to?
This whole issue seems immensely complicated to me. I would be perfectly happy for the package to automatically find the xml file in the same folder as itself, but this doesn't seem possible.
Can anyone give me some pointers as to how they manage these issues in their envornment? Is there a good web site that addresses this issue?
TIA
February 19, 2008 at 6:38 am
If you are a member of SQLServer Magazine, here is a great article by Kirk Haselden from November 2005, that has worked very well for us. It explains how to use Indirect configurations by storing the location of the config file in a windows Environment variable:
http://www.sqlmag.com/Articles/ArticleID/47688/47688.html?Ad=1
Blog blogs.conchango.com also has a great article on best practices for SSIS.
February 19, 2008 at 7:16 am
Hi Gordon
Thanks for the pointers. The conchango blog didn't cover my question but was packed with useful tips so I've saved the link for later.
I don't have access to the SQL mag. I'm not sure that using an environment variable is going to help however. This is a rough example of my problem:
My dev packages and files are in C:\DEV or sub folders, my test packages and files are in C:\TEST and my UAT packages are in C:\UAT
So I have XML files giving the path names of any non db source or destination files. Now I need an environment variable - I called it CONFIGFILEPATH - and while I am developing my package it points to C:\DEV. Not a problem at the moment, but what happens if a package is in test or UAT and I need to work on the next release of it at the same time? The environment variable can't contain 2 paths simultaneously
Sorry if this a really dumb question, but I'm very new to SQL Server and have no reference system to copy. I haven't been able to find anything online either
Thanks again
February 19, 2008 at 7:35 am
No - it's not a dumb question at all. You are correct that the environment variable would not work if all 3 enviornments are on the same machine. That solution was geared more towards moving packages from one server environment to another.
Since you have 3 environments on the same machine, you are probably using 3 instances of SQL Server?
If so, you can store the data path in a SQL table. I've set up a settings table to handle this. Put the path of each environment in the appropriate sql server instance.
When you run the SSIS package, have an initial step to read this path value and store it in a package variable using an Execute SQL task. This variable should store the path and the actual file name.
Override the connectionString for the approprate connection as follows:
1. Right click the connection and select properties.
2. Select the Elipsis of the Expression property.
3. Select property "ConnectionString" and click the Elipsis of the Expression
4. Select the package variable you created and drop it into the Expression box.
5. Select OK.
When you run this package it will override this connection to the right location based on the value in your instance table.
Hope this helps and wasn't too confusing!!
February 19, 2008 at 7:49 am
Cheers, Gordon, that did help and I'll keep a note of it. Actually, I'm so new I haven't got out of dev yet - I'm just trying to establish some good foundations.
The prod environment will be on a different machine, but the rest will be on the 'dev' machine, so I guess I need to create new instances of the server when I get to test and uat.
I hadn't thought this far because I am migrating a reporting system from Access. This uses linked tables which we only read, running queries and producing mainly excel output. The linked tables are always production data and we've just copied the entire access .mdb when we needed to make changes.
SQL Server is a big step change from Access, hence the need to start off on the right foot.
Thanks very much for the help
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply