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 connections (Does't work with "Script Task"...)

Hi,

I've a simple package which basically copies a few record from a Source connection to a Destination connection.
This package must run ten times, every time with a different Source and Destination.

I dont want to duplicate the tasks inside the package and set the right connections at design time. I'm looking for a way to parametrize the connections.

In DTS2000 I could use an "Activex Script" to access the Package's Connections and change them at design time. The new "Script Task" does not allow access to others task, so it can't be done anymore. (There's is still the "Activex Script" task on my toolbox, but i get errors trying to place it on the design surface).

Package Configurations also does not seems the right way: to use multiple configurations I should invoke the package from the command line multiple times with different command line parameters, while I wuold like to have everything in one place (ie 1 package file instead of 1 batch file, 1 package file and 10 package configurations files).

Regards,
CorradoYou are no longer allowed to access task and package properties at runtime and change things. Part of the rational is that it makes for potentially unstable and unpredicatble packages. So you know have a structured way of doing this through configurations and expressions.

Expressions can be set on connection, task and some component properties. Most UI's expose connections, or they are also available in the VS properties grid.

For example you could use an Exec SQL Task to get a recordset of connection information. You could then use the For Each Loop container to crack this, and for each iteration assign the value(s) to a variable. You can then use the variable(s) within an expression on the connection property(s).

Think expressions instead of ActiveX Script Tasks for glue code.

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

Changes to table not reflected in report

Hi!

I usa a SQL server for my windows application project. I have created a Crystal report, using .NET dataset connections. I made changes to one of the tables used in the report, and I updated the dataset. I then opened the report and used the "Verify database" feature, but the changes are not reflected. This worked before in the same report with changes to the same table. It seems like it does not work in any of my reports (using different datasets).

I have spent a good hour searching knowledge bases and forums, but found nothing. Anyone here that can help me? (I'm a bit pressed for time, close to deadline and all)

Thanks in advanceHave you checked EnableSaveDataWithReport and made sure its set off (either thru the menu option) or thru your code? Hope this helps|||Also Open the report and Do verify database|||Hi!

Thanks for the answers. The problem is solved for the moment. It seems that the "Verify database" functionality always works after I have run the applikation, a build is not enough. But If I start the app and then try to do the changes it seems to work....

So I consider it a bug that its not enough to changes and save the dependant DS... but as long as I get it to work I keep on. =)

Change sql server connections from 32767 to 60000 or more

Is that possible ?
BOL states that max user connections is 32767. Is that hard coded with SQL ?
Or can I increase that to 60000 and have SQL open 60000 connections.
Yes you may ask, why do I need and i understand that it all depends on
capacity.
But my question is fairly simple and assuming load,etc are all taken account
of, i want to open say 60,000 connections and if i specify 60,000 as max
user connections using sp_configure, will it work ?The largest SQL Server systems in the world do not use that many connections
and I am sure you won't either. That does not mean you can't have more than
32K users connected. The key is to use connection pooling. With proper use
of connection pooling you can service many more users than you have actual
connections since rarely are they all actually busy at the same time.
Andrew J. Kelly SQL MVP
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:eBpYCy96FHA.4036@.TK2MSFTNGP11.phx.gbl...
> Is that possible ?
> BOL states that max user connections is 32767. Is that hard coded with SQL
> ? Or can I increase that to 60000 and have SQL open 60000 connections.
> Yes you may ask, why do I need and i understand that it all depends on
> capacity.
> But my question is fairly simple and assuming load,etc are all taken
> account of, i want to open say 60,000 connections and if i specify 60,000
> as max user connections using sp_configure, will it work ?
>|||I understand all of that Andrew and was only curious to know if I can have
more than 32767 connections and if so, can i increase it using sp_configure
?
Also, where can i read more about connection pooling
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uwFzKf$6FHA.3588@.TK2MSFTNGP15.phx.gbl...
> The largest SQL Server systems in the world do not use that many
> connections and I am sure you won't either. That does not mean you can't
> have more than 32K users connected. The key is to use connection pooling.
> With proper use of connection pooling you can service many more users than
> you have actual connections since rarely are they all actually busy at the
> same time.
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <hassanboy@.hotmail.com> wrote in message
> news:eBpYCy96FHA.4036@.TK2MSFTNGP11.phx.gbl...
>|||In general when the specifications in BooksOnLine states that something is
the Maximum you can be pretty sure you can not exceed that<g>. As for
Connection Pooling I would do a Google search and be sure to specify what
type of drivers you are using to connect.
Andrew J. Kelly SQL MVP
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:eLDIS0A7FHA.2176@.TK2MSFTNGP14.phx.gbl...
>I understand all of that Andrew and was only curious to know if I can have
>more than 32767 connections and if so, can i increase it using sp_configure
>?
> Also, where can i read more about connection pooling
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uwFzKf$6FHA.3588@.TK2MSFTNGP15.phx.gbl...
>

Change sql server connections from 32767 to 60000 or more

Is that possible ?
BOL states that max user connections is 32767. Is that hard coded with SQL ?
Or can I increase that to 60000 and have SQL open 60000 connections.
Yes you may ask, why do I need and i understand that it all depends on
capacity.
But my question is fairly simple and assuming load,etc are all taken account
of, i want to open say 60,000 connections and if i specify 60,000 as max
user connections using sp_configure, will it work ?
The largest SQL Server systems in the world do not use that many connections
and I am sure you won't either. That does not mean you can't have more than
32K users connected. The key is to use connection pooling. With proper use
of connection pooling you can service many more users than you have actual
connections since rarely are they all actually busy at the same time.
Andrew J. Kelly SQL MVP
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:eBpYCy96FHA.4036@.TK2MSFTNGP11.phx.gbl...
> Is that possible ?
> BOL states that max user connections is 32767. Is that hard coded with SQL
> ? Or can I increase that to 60000 and have SQL open 60000 connections.
> Yes you may ask, why do I need and i understand that it all depends on
> capacity.
> But my question is fairly simple and assuming load,etc are all taken
> account of, i want to open say 60,000 connections and if i specify 60,000
> as max user connections using sp_configure, will it work ?
>
|||I understand all of that Andrew and was only curious to know if I can have
more than 32767 connections and if so, can i increase it using sp_configure
?
Also, where can i read more about connection pooling
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uwFzKf$6FHA.3588@.TK2MSFTNGP15.phx.gbl...
> The largest SQL Server systems in the world do not use that many
> connections and I am sure you won't either. That does not mean you can't
> have more than 32K users connected. The key is to use connection pooling.
> With proper use of connection pooling you can service many more users than
> you have actual connections since rarely are they all actually busy at the
> same time.
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <hassanboy@.hotmail.com> wrote in message
> news:eBpYCy96FHA.4036@.TK2MSFTNGP11.phx.gbl...
>
|||In general when the specifications in BooksOnLine states that something is
the Maximum you can be pretty sure you can not exceed that<g>. As for
Connection Pooling I would do a Google search and be sure to specify what
type of drivers you are using to connect.
Andrew J. Kelly SQL MVP
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:eLDIS0A7FHA.2176@.TK2MSFTNGP14.phx.gbl...
>I understand all of that Andrew and was only curious to know if I can have
>more than 32767 connections and if so, can i increase it using sp_configure
>?
> Also, where can i read more about connection pooling
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uwFzKf$6FHA.3588@.TK2MSFTNGP15.phx.gbl...
>

Change sql server connections from 32767 to 60000 or more

Is that possible ?
BOL states that max user connections is 32767. Is that hard coded with SQL ?
Or can I increase that to 60000 and have SQL open 60000 connections.
Yes you may ask, why do I need and i understand that it all depends on
capacity.
But my question is fairly simple and assuming load,etc are all taken account
of, i want to open say 60,000 connections and if i specify 60,000 as max
user connections using sp_configure, will it work ?The largest SQL Server systems in the world do not use that many connections
and I am sure you won't either. That does not mean you can't have more than
32K users connected. The key is to use connection pooling. With proper use
of connection pooling you can service many more users than you have actual
connections since rarely are they all actually busy at the same time.
--
Andrew J. Kelly SQL MVP
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:eBpYCy96FHA.4036@.TK2MSFTNGP11.phx.gbl...
> Is that possible ?
> BOL states that max user connections is 32767. Is that hard coded with SQL
> ? Or can I increase that to 60000 and have SQL open 60000 connections.
> Yes you may ask, why do I need and i understand that it all depends on
> capacity.
> But my question is fairly simple and assuming load,etc are all taken
> account of, i want to open say 60,000 connections and if i specify 60,000
> as max user connections using sp_configure, will it work ?
>|||I understand all of that Andrew and was only curious to know if I can have
more than 32767 connections and if so, can i increase it using sp_configure
?
Also, where can i read more about connection pooling
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uwFzKf$6FHA.3588@.TK2MSFTNGP15.phx.gbl...
> The largest SQL Server systems in the world do not use that many
> connections and I am sure you won't either. That does not mean you can't
> have more than 32K users connected. The key is to use connection pooling.
> With proper use of connection pooling you can service many more users than
> you have actual connections since rarely are they all actually busy at the
> same time.
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <hassanboy@.hotmail.com> wrote in message
> news:eBpYCy96FHA.4036@.TK2MSFTNGP11.phx.gbl...
>> Is that possible ?
>> BOL states that max user connections is 32767. Is that hard coded with
>> SQL ? Or can I increase that to 60000 and have SQL open 60000
>> connections.
>> Yes you may ask, why do I need and i understand that it all depends on
>> capacity.
>> But my question is fairly simple and assuming load,etc are all taken
>> account of, i want to open say 60,000 connections and if i specify 60,000
>> as max user connections using sp_configure, will it work ?
>>
>|||In general when the specifications in BooksOnLine states that something is
the Maximum you can be pretty sure you can not exceed that<g>. As for
Connection Pooling I would do a Google search and be sure to specify what
type of drivers you are using to connect.
Andrew J. Kelly SQL MVP
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:eLDIS0A7FHA.2176@.TK2MSFTNGP14.phx.gbl...
>I understand all of that Andrew and was only curious to know if I can have
>more than 32767 connections and if so, can i increase it using sp_configure
>?
> Also, where can i read more about connection pooling
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uwFzKf$6FHA.3588@.TK2MSFTNGP15.phx.gbl...
>> The largest SQL Server systems in the world do not use that many
>> connections and I am sure you won't either. That does not mean you can't
>> have more than 32K users connected. The key is to use connection
>> pooling. With proper use of connection pooling you can service many more
>> users than you have actual connections since rarely are they all actually
>> busy at the same time.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Hassan" <hassanboy@.hotmail.com> wrote in message
>> news:eBpYCy96FHA.4036@.TK2MSFTNGP11.phx.gbl...
>> Is that possible ?
>> BOL states that max user connections is 32767. Is that hard coded with
>> SQL ? Or can I increase that to 60000 and have SQL open 60000
>> connections.
>> Yes you may ask, why do I need and i understand that it all depends on
>> capacity.
>> But my question is fairly simple and assuming load,etc are all taken
>> account of, i want to open say 60,000 connections and if i specify
>> 60,000 as max user connections using sp_configure, will it work ?
>>
>>
>