Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS: DTS Connections and SSIS Connection Managers

A post appeared on the SSIS forum today in which the poster was getting slightly confused about the use of SSIS Connection Managers and how they differ from DTS Connections so I thought it would be a good idea to illustrate the differences here to hopefully save anyone else from having the same headaches.

A problem with DTS Connections was that if all your tasks used the same Connection object then everything happened agains the same physical connection. Not a good situation as operations against the connection object effectively get queued up. This problem is alluded to at the bottom of this article.

This is not the case with SSIS Connection Managers. Instead, Connection Managers maintain a pool of connections. Each task that utilises the Connection Manager will get its own connection to the data source.

Note that if you want to you can make SSIS Connection Managers behave like DTS Connections by setting RetainSameConnection=TRUE. There is actually a use for this as exhibited here: http://blogs.conchango.com/jamiethomson/archive/2005/08/20/2048.aspx

-Jamie

 

Comments

 

Professional Association for SQL Server (PASS) SIG said:

April 3, 2006 00:09
 

Amir Saberi said:

WE have an application that uses DTS.  We tried running it in Vista.  And DTS is opening a second connection when it starts the data pump step!

How can I keep it from doing this?

March 28, 2008 07:33
 

pritesh2205 said:

Hi Jamie,

        I have an issue with Data Reader source. The thing is i have to connect to Informix to fetch data from. I  use third party ODBC driver (Open Link) to make a connection. I have one data flow, within which i have 7 source and 7 destination components (to fetch data from 7 tables).All of the 7 sources use the same connection manager.

The problem is that my package blocks all the connections that can be made to Informix and doesnt allow anyone else to connect and hangs. It hangs of infinite time and this happens randomly.

I have set the engine thread property for the data flow task to 2, so  at any given time only 2 flows are running in parallel. I want to know at what point it the connection made to Informix. If i separate out the 7 flows into separate dataflows, will it make less number of connections parallely ?

Is there a way to make the sources use only one open connection , instead of opening a separate connection each?

I appreciate any help on this.

- Pritesh

August 6, 2008 11:55
 

jamie.thomson said:

Pritesh,

As it says above, set RetainSameCOnnection=TRUE.

-Jamie

August 6, 2008 12:38
 

pritesh2205 said:

Agreed,

       But the issue with that is that the first connection expects an open data reader connection to use which is not available. Hence, we have an error.

- Pritesh

August 7, 2008 07:06
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems