November 23, 2012 at 11:27 am
Hello,
I am using SSIS in SQL Server 2008 R2 and have been creating packages for a few years, but have not done so where the packages will be going from a development, to a QA and finally to a production environment. The database name will be the same in each environment, but the server name will differ. Also, the SSIS packages will reside on the same server as the database. I will eventually be applying Microsoft Foundation Services as the source control.
My best guess for keeping everything the same while applying a different server name is to add the Connection String to Expressions for the OLE DB and use the default system variable MachineName in place of the hardcoded value. This would need to be applied twice in the connection string where the server name is defined.
(e.g. "Data Source = " + @[System::MachineName] + ";Initiall...."
Is this the best approach or is there a mechanism in Microsoft Foundation Services that will handle this automatically?
Thanks
November 25, 2012 at 5:06 am
grantbanjo (11/23/2012)
Hello,I am using SSIS in SQL Server 2008 R2 and have been creating packages for a few years, but have not done so where the packages will be going from a development, to a QA and finally to a production environment. The database name will be the same in each environment, but the server name will differ. Also, the SSIS packages will reside on the same server as the database. I will eventually be applying Microsoft Foundation Services as the source control.
My best guess for keeping everything the same while applying a different server name is to add the Connection String to Expressions for the OLE DB and use the default system variable MachineName in place of the hardcoded value. This would need to be applied twice in the connection string where the server name is defined.
(e.g. "Data Source = " + @[System::MachineName] + ";Initiall...."
Is this the best approach or is there a mechanism in Microsoft Foundation Services that will handle this automatically?
Thanks
I'm not sure what you mean by "Microsoft Foundation Services" - do you mean "Team Foundation Server" perhaps? If so, it will not look after your configuration requirements.
The standard way to do this is by using 'connections' in your SSIS package for your various sources and destinations.
The connection string properties for these connections can easily be overriden at runtime (to deal with exactly the situation you mention) through the use of Package Configurations (see here).
While your requirement can probably be accommodated the way you mention, anything more sophisticated is likely to cause a headache. It will take a little longer to set up, but having a separate config database per environment, which includes all your config settings for all your packages, keeps things visible and allows you to tweak almost any package property you can think of.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 26, 2012 at 5:37 pm
Yes, I meant to say "Team Foundation Server."
I was able to resolve this issue by applying "LocalHost" in place of the server name. Since the only thing that will differ between one server and the next is the server name (basic configurations and database name will remain constant), "LocalHost" works great and I'm able to avoid the headache of modifying the database connection manager's connection string in an expression by replacing the server name with the MachineName system variable.
Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply