Data Source and Connection Manager

Hi all,

I don't really understand the difference between the "data source" and the "connection manager" in a SSIS solution.

I tried to create a data source (if I understood correctly, I will ba able to share that connection between all packages in the solution), then I create a new connection manager inside a package. But when I make a modification in the data source, nothing change in the connection manager ?!!?

What I think is that when I'm making a new connection manager, what's really happen is a copy of the data source, is that correct ? If it is I dont't understand the avantage of making a new data source ....

Thanks for your response

In my personal opinion, there is no advantage of data sources. They were created by the Analysis Services team and the Integration Services thought tthey looked like something nice they could use. Rmemeber that they were designed for SSAS though.

In my opinion - don't use them.

-Jamie

|||

jh0483 wrote:

Hi all,

What I think is that when I'm making a new connection manager, what's really happen is a copy of the data source, is that correct ?

That's correct.

I don't use them either. You can run into additional issues when the properties either from the connection manager or from the data source changes; you will start receiving annoying notifications/warnings that your data source and connection manager are not sync every time you open the package.

|||

I think of it like this. You will always a have a connection, but you can attribute the connection as being linked to the data source. See the DataSourceID of a connection. This property maintains the link between the physical package related structure and the ephemeral design-time only data source.

Much like the others I never found it offered much. The design-time management of connections can be achieved through the same methods as you would use for the deployed run-time stuff, i.e. configurations.

|||But for me there is a big advantage : if you have many packages sharing the same connections and you have to change the connection, then you just have to change it in one place. If you use only connection manager, then you have to check all your packages.|||

jh0483 wrote:

But for me there is a big advantage : if you have many packages sharing the same connections and you have to change the connection, then you just have to change it in one place.

Have you ever used or at least looked into configurations? They allow you to manage connections through a single point, across multiple packlages and even solutions. They also have the huge benefit of working at runtime, and are a very valuable tool when it comes to deploying your packages.

Use both if you want, but without configurations you will have to make changes as packages get deployed between environments. Data sources do not solve that for you in any effecitive manner. Configurations do, and for my money work just as well when devloping packages.

|||Of course I use configurations ...

But still in your configuration file, if you have 5 packages with 5 connection manager you will have to configure 5 time the connection, which is not the case with the Data source.|||

Ok, good so you hopefully see the benefit of configurations. Your point seemed to be stating that there was a benefit of data sources that was not available with configurations, that was what I disagreed with.

If you have 5 packages and 5 connections, that are the same, presumably linked to the same Data Source to give you that change once, update all effect. This is not solely a benefit of data sources. You can get the same effect by using the same configuration for all five connections. If the connection string changes you update the one configuration store. Whenever the 5 packages are now opened the configuration applies the change, and without the annoying dialog you get with a data source warning you or the discrepancy between the updated data source and soon to be updated connection.

The benefit for me of configurations over data sources is that I can use that same mechanism to change connection strings simply as packages move between development, testing and production for example. Using data sources would require you to open the package in the designer and actually modify the package, albeit with minimal user input since the data source does the hard work, but still having to change packages as part of a deployment process is just wrong in my opinion.

Is your point that that each time you add a distinct connection to a package you need to set that connection as being controlled by a configuration? Data sources will avoid that I agree. Still for me I will always use configurations to solve the deployment issue, so I will incur that cost anyway. It is minimal since there is virtually no reason to have more than one connection object per connection string as SSIS will by default create multiple instances of that connection. This is of course a change from the DTS world if you used that previously, where it was actually good practice to duplicate connections.

Do you see any other benefits of data sources over configurations for connection management?

|||

I am using data connections ( in each package) which is referencing to one of the datasource.... i used this becuase i thought there would be one object i.e. Data Source (for creating connection with SQL sever or ne other database) and that object reference would be used in each package via Connection manager, and that makes it memory efficient because u would not have more objects (connection objects) in memory as connection manager is just a reference to that Data Source Object... connection string can be set up dynamicaly to point that connection object to a database and if connection string changes at run time even then there would be only one object (just pointing to differet database)

can ne one tell me isnt that right? as i hv read on this thread that it copies connection from data source to connection manager .... can ne one tell me ne article in that issue?

ne comments?

regards,

Anas

|||

Zadoras wrote:

I am using data connections ( in each package) which is referencing to one of the datasource.... i used this becuase i thought there would be one object i.e. Data Source (for creating connection with SQL sever or ne other database) and that object reference would be used in each package via Connection manager, and that makes it memory efficient because u would not have more objects (connection objects) in memory as connection manager is just a reference to that Data Source Object... connection string can be set up dynamicaly to point that connection object to a database and if connection string changes at run time even then there would be only one object (just pointing to differet database)

can ne one tell me isnt that right? as i hv read on this thread that it copies connection from data source to connection manager .... can ne one tell me ne article in that issue?

ne comments?

regards,

Anas

ne comments please...