Dynamically change the servername

Hi all,

I've created 1 solution and added all my packages in different projects (like DIMENSIONS, SOURCES_SAP, ...).

For each project I have a Data Source that connects to the server. The problem is that when I want to deploy a package to the server that I always need to change the Data Source before deployment.

Before SQL Server 2005 we used a connection file (which was located as well on the server as on the development pc's in the same locations) within our DTS packages. This way we didn't had to change the connections when deploying to the server.

My intention was to use the current configuration from the configuration manager(development / production) to select the servername. Unfortunately, I didn't succeed to retrieve it's value from a variable script.

I need to have a solution that dynamically changes the datasources for multiple packages depending on a specific action.

How can I achieve this the easiest way ?

Thanks in advance !

Geert

You need to take a look at creating "package configurations". Try doing a search in Books Online for this. It helps you to do exactly what you are looking for.

dynamic sql w/sp_executesql - servername parameter issue

I know that we can't pass serername/tablename as a parameter to sp_executesq
l
as follow...
But I have to work around sql injection vulnerability too...
I want to use some type of paratertize way but...can't find a solution yet.
Please help!!
create proc SaferDynamicSQL(@.serverName nvarchar(25))
as
declare @.sql nvarchar(255)
set @.sql = 'select @.p_serverName+'..'+'action from action'
exec sp_executesql @.sql,
N'@.p_serverName nvarchar(25)',
@.p_serverName = @.serverName
goIf you want to avoid injection then why do this dynamically in TSQL? You
could create views that reference the linked server(s) and then reference
the view by name. You could parameterize the connection string in your
client application. You could use UDL files to abstract the server name.
David Portas
SQL Server MVP
--