Changing Connections in DTS Packages

I have 4-5 DTS packages with more than 100 connections. If I want to change the connections of the source (Oracle Database) from Server 1 to Server 2, how do I do it dynamically...? I know that I can go to Disconnected Edit and change the connections manually , but it is a very tedious process as there are more than 100 connections.

Thanks.I'm moving this thread to the Microsoft SQL Server Forum where it will get attention from users with more Microsoft experience.

Do you actually have more than 100 connections (the Oracle Connection icon is the gold twin-disk-drives)? I have not EVER seen that happen, and I've seen a lot of Oracle to SQL Server DTS packages!

-PatP|||Pat

Thanks for the reply. If I add up all the connections in 3-4 packages I will have 100 connections.

Let me know if you know any solution.
Thanks.sql

Changing Connection string in multiple packages.

Scenario:

130 dtsx packages using 4 matching connections.
3 of those connections are stored in an SSIS Configuration table in an Operational database.
The last connection is in a shared data source and points to the Operational database so the packages can grab the others.

Problem:

It's time for deployment and all of those connections must change to production servers.
The 3 are no issue, just change the ConfiguredValue in the SSIS Configuration table on the production box to point to the other production servers.
However, the fourth one... I had made an assumption that when you changed a shared data source it filtered down throughout all the packages. We all know what assumptions do to you.... So. I need a way to change all 130 connections (and be able to change ALL packages quickly and simply for other projects in the future)

Solution:

It has been suggested that we use another package to run though all of the packages and change the connection with a script task. I can live with this (and more importantly so can our DBS's who have to deploy).

I have one snippet of code to ADD a connection using a variable holding the connection string, but we dont' want to add one, just change an existing one.

Has anyone else done this? Or had a similar problem and way to fix?

We are likely to have many projects in which the connections MUST change at deployment, and the idea of going into every package to make the change is sad at best... We would be more likely to move back to a competitor's product that has a connection repository, than continue with SSIS.

Ches Weldishofer
ETL Developer
Clear Channel Communications

Ches Weldishofer wrote:

We are likely to have many projects in which the connections MUST change at deployment, and the idea of going into every package to make the change is sad at best... We would be more likely to move back to a competitor's product that has a connection repository, than continue with SSIS.

Alternatively you could use SSIS's built-in mechanism for doing this. i.e. Configurations.

You can share an XML configuration file between many packages. If your connection managers are identically named in each package then there won't be a problem.

-Jamie

|||

Yes we've discussed using an XML Configuration, however... it causes it's own issues. Our production servers are clustered so anything having to point to a file would have to be set up on many many servers.

And the same path would have to exist on all development servers, all developers workstations and all production servers. Maintenance nightmare.

It may be the way we have to go, but... not really a path we want to go down.

|||

Interesting. Are you clustering your servers that run the ETL jobs? Why can you not run all jobs on a single server? That's not rhetoric, I'm interested to know.

-Jamie

|||

Yes they are :)

Both the actual data servers and the SSIS server are clustered (and seperate). The plan is to have all packages run from one cluster so they're easier to find (compared to spread out over 40-50 servers as our dts packages are now) and easier to manage.

We've discussed using a cluster file share, but again we would have to be able to make that work on so many different servers, every developer every development server... oi.

In theory this is all great, but in practice we were hit with the changing of connections.

|||

Right. Any reason why your ETL machine can't be non-clustered but still keep your data servers as clustered? What's the advantage of clustering your ETL server?

Forgive me if I ask daft questions - I don't know much about clustering.

-Jamie

|||Failover, if the SSIS server goes down, then the second one takes over and continues to run the jobs as scheduled.|||

OK gotcha.

I assume you've read this post on clustering: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/07/06/16015.aspx

You know that packages are not dependant on the SSIS service in order to run right?

-Jamie

|||

Nope I wouldn't have read it. :) That's an ops thing. But I'll pass it on to our DBA's. I have to code to the environment I,m given.

One thing about the failover is it works even if the entire server is down, not just the Service. Say they want to put SP1 on the ssis box, this way the put failover to the second box, upgrade the first, it then falls back on the first box and they can update the second (including reboots) and we never lose a step.

|||

Which all leads us back to my original problem.

What I'm looking for is the code/syntax to change a connection in an existing package through another package to use as a deployment utility for chaning dev connections to production ones.

Any pointers as to where to look for the syntax would be greatly appreciated.

|||

This might help: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/12/31/17731.aspx

-Jamie

|||

Ches,

I have some script tasks in various packages that change connection manager databases on the fly from within a package. Here is the code:

Dim oCon As ConnectionManager
Dim oProp As DtsProperty
oCon = Dts.Connections("ConnectionManagerX")
oProp = oCon.Properties("InitialCatalog")
oProp.SetValue(oCon, Dts.Variables("DatabaseNameVariable").Value.ToString)
Dts.TaskResult = Dts.Results.Success

Obviously, this runs from within the target package and is pulling the database name from a package variable, which isn't quite what you're looking for.

I think you'd be going for something like this:

Const c_sTargetPkg As String = "This is my package"
Const c_sTargetServer As String = "Server1"
Const c_sTargetConnection As String = "Connection A"
Dim oApp As Application = New Application()
Dim oPkg As Package
Dim oCon As ConnectionManager
Dim oProp As DtsProperty

'Get administrative values from package variables
Dim sConnectionName As String = Dts.Variables("TargetConnection").Value.ToString
Dim sNewServerName As String = Dts.Variables("NewServer").Value.ToString
Dim sNewDatabaseName As String = Dts.Variables("NewDatabase").Value.ToString

'Get a handle on the target package and connection manager
oPkg = oApp.LoadFromSqlServer(c_sTargetPkg, c_sTargetServer, "", "", Nothing)
oCon = oPkg.Connections(sConnectionName)

'Change the server name
oProp = oCon.Properties("ServerName")
oProp.SetValue(oCon, sNewServerName)

'Change the database name
oProp = oCon.Properties("InitialCatalog")
oProp.SetValue(oCon, sNewDatabaseName)

'Save the package
oApp.SaveToSqlServer(oPkg, Nothing, c_sTargetServer, Nothing, Nothing)

It uses some constants and some package variables to load up a target package, adjust a particular connection manager's properties and then saves the package back to the target server. I'm sure you could add all kinds of looping or target package lists to this code to have it handle all packages within one run.

Mike Ogilvie
Pendulum, Inc.
http://www.PendulumSolutions.com

|||

Thank you Mike that's much closer to what I'm looking for, I'll start playing with that and repost when I get a final answer. (or more questions which seems likely with SSIS :)

|||I wrote my own vb app to maintain my connection strings in a data table in sql. I have all of my packages querying it and loading the connection strings to variables in memory and Iset them as an expression for the connection strings that I need.|||

Here's the code I finally used, it's inside a dataset enumerated loop to change which ever packages exist in the msdb root by name supplied in a variable (or % for all).

Worked nicely, the dba is much happier with me now that he doesnt' have to change 130 connections.

Dim c_sTargetPkg As String = Dts.Variables("DTSXName").Value.ToString
Const c_sTargetServer As String = "servername"
Const c_sTargetConnection As String = "ConnectionName"
Dim oApp As Application = New Application()
Dim oPKG As Package
Dim oCon As ConnectionManager
Dim oProp As DtsProperty

'Get administratvive values from package variables
Dim sConnectionName As String = Dts.Variables("ConnectionName").Value.ToString
Dim sNewServerName As String = Dts.Variables("NewServerName").Value.ToString
Dim sNewConnectionString As String = Dts.Variables("NewConString").Value.ToString

'Get package and connection manager
oPKG = oApp.LoadFromSqlServer(c_sTargetPkg, c_sTargetServer, "login", "password", Nothing)
oCon = oPKG.Connections(sConnectionName)

'Change Server name
oProp = oCon.Properties("ServerName")
oProp.SetValue(oCon, sNewServerName)
oProp = oCon.Properties("ConnectionString")
oProp.SetValue(oCon, sNewConnectionString)

'Save the packages
oApp.SaveToSqlServer(oPKG, Nothing, c_sTargetServer, "login", "password")

Dts.TaskResult = Dts.Results.Success

sql

Changing config table location on the fly

Hi everyone,

I am trying to create a proof of concept to show that we can have packages deployed on 3 different servers and all we need to do is tell the package, upon execution/scheduled task, where to go fetch it's configurations from.

The configurations are using SQL Server as the package configuration. This makes it easier for DBA's to maintain since DBA's are responsible for package executions and job scheduling.

For example, the configuration database and all package configurations would be found on server1, server2 and server3 The difference in values is that on Server1, the configuration for the source data and the destination data point to Server1\DatabaseSource and Server1\DatabaseDestination and on server2, the configs point the source to Server2\DatabaseSource and Server2\DatabaseDestination and for server 3, the same thing but pointing to server3.

There is also a connection for the SSIS_Config database we're getting the configurations from. In theory, if we specify a different server where this SSIS_Config database is found, it should override the settings in the package. No?

When I schedule OR execute the package, it's always getting it's configs from the config specified in the package independent of wether I specify it in the Connection Managers of the Execute Package Utility when I manually execute it OR in the data sources tab when I configure the package to be run as a job in SQL Server 2005.

Am I missing something here?

Thanks,

Rob

You use an XML file to set the "config database" location dynamically. You can't use a SQL Server based config table to control where the "config database" resides because that is what you're trying to change.

So, use an XML file, place it on all of the servers, each pointing to the correct "config database" for that environment.|||

Rob,

it sounds like you want to decide, at execution-time, which set of configurations it should use. Right?

So, in essence you need a way of "parameterising" your package so it needs which set of configurations to use. Right?

The best way to do this is to use the /SET option of dtexec.exe

Regards

Jamie

|||

Thats the one!

Thank you Jamie

|||

Jamie,

I've tried using the /SET option but I can't get it to change the set of configurations to use.

This is the command line options I've used, did I miss something?

/FILE "D:\Documents and Settings\forestr\Desktop\Deployment Test\Deployment Test\bin\Deployment\Package.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI /SET "\Package.Connections[b-ncrsql1.SSIS_CONFIG].Properties[ConnectionString]";"Data Source=b-ncrsql1\test;Initial Catalog=SSIS_CONFIG;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"

|||

Jamie,

I've tried using the /SET option but I can't get it to change the set of configurations to use.

This is the command line options I've used, did I miss something?

/FILE "D:\Documents and Settings\forestr\Desktop\Deployment Test\Deployment Test\bin\Deployment\Package.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI /SET "\Package.Connections[b-ncrsql1.SSIS_CONFIG].Properties[ConnectionString]";"Data Source=b-ncrsql1\test;Initial Catalog=SSIS_CONFIG;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"

|||

Hmmm, I wonder if the order in which things get applied is a problem? Davide Mauri talks a little avout this here: http://weblogs.sqlteam.com/dmauri/archive/2006/04/02/9489.aspx but I don't think it'll help in your case.

Sorry. I'm a bit stumped. Phil...any ideas?

-Jamie

|||

I've tried a different approach.

Storing the source and destination servers in my SSIS_Config tables on all three servers and storing the source server name for the package configurations in an xml file.

After altering between server1, server2 and server3 in the xml config file, the package always goes directly to the initial location set up in the package: server1.

So I'm confused, no matter what method I use, the configuration I tell the package to use is overridden with the initial configuration in the package.

If I edit the package and set the configuration table to point to server 2, that works but now I can't get it pointing to server 1 or server 3.

There's got to be something I am missing... but what?!

|||

r4enterprises wrote:

I've tried a different approach.

Storing the source and destination servers in my SSIS_Config tables on all three servers and storing the source server name for the package configurations in an xml file.

After altering between server1, server2 and server3 in the xml config file, the package always goes directly to the initial location set up in the package: server1.

So I'm confused, no matter what method I use, the configuration I tell the package to use is overridden with the initial configuration in the package.

If I edit the package and set the configuration table to point to server 2, that works but now I can't get it pointing to server 1 or server 3.

There's got to be something I am missing... but what?!

Make sure that you have that config file listed under "Package Configurations" and that you have package configurations enabled. It works, trust me! I use it all of the time. If it doesn't work for you, then something is wrong.

Know that you must use an absolute file path when pointing to the XML config file.|||If you're going to change the connection string of a connection, I'd use the /CONN switch.|||

Phil,

thanks for your insight. the absolute path might be the problem as when I would build the package, I'd go change the config settings of the xml file that is found in the /bin/Deployment directory and not in the project's folder. I never thought that it would reference an absolute path like that... kinda dumb since that would make packages non-movable between locations.

Please note that when you execute a package with the /conn, that didn't seem to work either. its still pointing to its original location.

I'll try a combination of things when I get to that and keep you guys posted.

Thanks for the help,

Robin

|||

r4enterprises wrote:

I never thought that it would reference an absolute path like that... kinda dumb since that would make packages non-movable between locations.

What if the package is stored in SQL Server? What is a "relative path" and what should it point to? This is why absolute paths are required.sql

Changing a Connection in DTS Local packages

Many of our Local packages in Data Transformation Services has the job of
copying data from one database on one server to another database on another
server. The package typically has 2 Connections defined.
Now we want to move the "destination" database to a new server. Is there any
way this can be done without editing each package in the "Design Package"
GUI
Thanks in advance
Henrik.You can use UDL files for the connection; you can change the properties
programmatically; you can use the Dynamic Properties task to change settings
at runtime.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Henrik Hjøllund Hansen" <hh@.dlf.dk> wrote in message
news:bicqd4$2hmp$1@.news.cybercity.dk...
> Many of our Local packages in Data Transformation Services has the job of
> copying data from one database on one server to another database on
another
> server. The package typically has 2 Connections defined.
> Now we want to move the "destination" database to a new server. Is there
any
> way this can be done without editing each package in the "Design Package"
> GUI
>
> Thanks in advance
>
> Henrik.
>
>

change ownership of objects

A co-worker recently quit the company, and all of his stored
procedures and DTS packages are apparently under his name. Is there
any way to change his account to another (ie administrator or SA)'
I am afraid to delete his account in fear all the stored procedures
will be lost. (we have backups, but ... ugh)
(running mssql server 2000)Hello,
Fiirst of all change the password for his account to a one with special
charecters, so as no one can access. After that you could use
sp_changeobjectowner system proc to change the owner of all object from that
user to DBO. take
alook into sp_changeobjectowner in books online..Once you completed the
object owner to DBO you could drop the user and
login associated.
Thanks
Hari
"berwiki" <steveberwick@.gmail.com> wrote in message
news:1171656219.472511.150980@.m58g2000cwm.googlegroups.com...
>A co-worker recently quit the company, and all of his stored
> procedures and DTS packages are apparently under his name. Is there
> any way to change his account to another (ie administrator or SA)'
> I am afraid to delete his account in fear all the stored procedures
> will be lost. (we have backups, but ... ugh)
>
> (running mssql server 2000)
>|||To add on to my previous post to change DTS owner use:-
use msdb
go
sp_reassign_dtspackageowner [@.name =] 'name',[@.id =] 'id',[@.newl
oginname =]
'newloginname'
Thanks
Hari
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:...
> Hello,
> Fiirst of all change the password for his account to a one with special
> charecters, so as no one can access. After that you could use
> sp_changeobjectowner system proc to change the owner of all object from
> that user to DBO. take
> alook into sp_changeobjectowner in books online..Once you completed the
> object owner to DBO you could drop the user and
> login associated.
> Thanks
> Hari
> "berwiki" <steveberwick@.gmail.com> wrote in message
> news:1171656219.472511.150980@.m58g2000cwm.googlegroups.com...
>|||To add on Hari's reply
Run the output in the QA
SELECT 'EXEC sp_changeobjectowner '''+ROUTINE_NAME+''',''dbo'''
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+
'.'+ROUTINE_NAME),
'IsMsShipped')=0
AND ROUTINE_SCHEMA != 'dbo'
"berwiki" <steveberwick@.gmail.com> wrote in message
news:1171656219.472511.150980@.m58g2000cwm.googlegroups.com...
>A co-worker recently quit the company, and all of his stored
> procedures and DTS packages are apparently under his name. Is there
> any way to change his account to another (ie administrator or SA)'
> I am afraid to delete his account in fear all the stored procedures
> will be lost. (we have backups, but ... ugh)
>
> (running mssql server 2000)
>

Change ownership of DTS package

Howdo I change the ownership of DTS packages?
ThanksIn the msdb database there is stored procedure named
sp_reassign_dtspackageowner that will help you change it.
Here is a sample syntax:
msdb..sp_reassign_dtspackageowner
@.name = 'Package Name',
@.id = '-7DED-11D4-BF04-00B0D057DBE4',
@.newloginname = 'new user'
You can get the package ID (@.id) from the msdb..sysdtspackages table (ID
column).
"Mecn" wrote:
> Howdo I change the ownership of DTS packages?
> Thanks
>
>

Change Object Ownership

A co-worker recently quit the company, and all of his stored
procedures and DTS packages are apparently under his name. Is there
any way to change his account to another (ie administrator or SA)??

I am afraid to delete his account in fear all the stored procedures
will be lost. (we have backups, but ... ugh)berwiki (steveberwick@.gmail.com) writes:

Quote:

Originally Posted by

A co-worker recently quit the company, and all of his stored
procedures and DTS packages are apparently under his name. Is there
any way to change his account to another (ie administrator or SA)??
>
I am afraid to delete his account in fear all the stored procedures
will be lost. (we have backups, but ... ugh)

For the stored procedures do:

SELECT 'EXEC sp_changeobjectowner ''' + name + ''', '''dbo''''
FROM sysobjects
WHERE uid = USER_ID('co-worker')

copy, paste and run result set. This will take care of stored procedures,
tables etc. Note that any references to these procedures from elsewhere will
need to be updated.

I have never worked with DYS, so I cannot answer that part.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Change Management for Jobs and DTS packages

We have a solution for change management for our databases, but how do others
handle updates/modifications to jobs or DTS packages. Does anyone know of a
tool to help us do this or is this one of those things we might have to grow
our own solution by checking out the tables in msdb?
Any suggestions would be appreciated.
Thanks,
Linda
When saving DTS packages on the SQL Server or as structured storage files
(*.dts), previous versions of the package are automatically saved. You can
go back to any previous version you wish to.
As for change management, I would suggest setting a user password so the
package can be executed by users, but not viewed or modified. Then the
package can only be altered with an owner password.
But if you want more details on changes, save your DTS packages as
structured storage files and add them to VSS (or whatever versioning
software you're using). Then you are able to capture check out and check in
details as well, and can reference change request id's etc.
Simon Worth
"lslmustang" <lslmustang@.discussions.microsoft.com> wrote in message
news:6F69B8E7-DAFA-4755-AECE-952B7A5F8142@.microsoft.com...
> We have a solution for change management for our databases, but how do
others
> handle updates/modifications to jobs or DTS packages. Does anyone know of
a
> tool to help us do this or is this one of those things we might have to
grow
> our own solution by checking out the tables in msdb?
> Any suggestions would be appreciated.
> Thanks,
> Linda
|||That's an idea we may use. What about Jobs though?
"Simon Worth" wrote:

> When saving DTS packages on the SQL Server or as structured storage files
> (*.dts), previous versions of the package are automatically saved. You can
> go back to any previous version you wish to.
> As for change management, I would suggest setting a user password so the
> package can be executed by users, but not viewed or modified. Then the
> package can only be altered with an owner password.
> But if you want more details on changes, save your DTS packages as
> structured storage files and add them to VSS (or whatever versioning
> software you're using). Then you are able to capture check out and check in
> details as well, and can reference change request id's etc.
> --
> Simon Worth
>
> "lslmustang" <lslmustang@.discussions.microsoft.com> wrote in message
> news:6F69B8E7-DAFA-4755-AECE-952B7A5F8142@.microsoft.com...
> others
> a
> grow
>
>
|||Simply script the job and keep the source script under source control
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"lslmustang" <lslmustang@.discussions.microsoft.com> wrote in message
news:1ABF1581-CF71-48A7-87AD-D047AD1BF4A1@.microsoft.com...[vbcol=seagreen]
> That's an idea we may use. What about Jobs though?
> "Simon Worth" wrote: