Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

What is the best approach for defining server name in OLE DB connection for SSIS package running in different environments? Expand / Collapse
Author
Message
Posted Friday, November 23, 2012 11:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 4:13 PM
Points: 27, Visits: 141
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
Post #1388220
Posted Sunday, November 25, 2012 5:06 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:48 AM
Points: 4,977, Visits: 11,668
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.





Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1388384
Posted Monday, November 26, 2012 5:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 4:13 PM
Points: 27, Visits: 141
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.
Post #1388904
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse