Advantage of using a Data Source in a project

I am new to SSIS.

I came across the following article:

http://technet.microsoft.com/en-us/library/ms139848(SQL.90).aspx

It states that:

"A data source can be defined one time and then referenced by connection managers in multiple packages. You use a data source object in a package by adding a connection manager that references the data source object to the package. There is no dependency between a data source and the connection managers that reference it."

I have created a data source (DS1) and set it to point to a database say DB1. In the connection managers area, I create a connection manager CM1 using the datasource DS1.Now I edit DS1 to point to a different database DB2. When I open CM1 however it’s still pointing to DS1.

I guess this is because it’s said that there is no dependency between the connection manager and data source. My question is what exactly is the advantage of using a data source?

Priya

AFAIK, it is used only as a template of sorts for a data source connection. Other than that, I don't really know why one would use it. I have never used them.

Maybe someone else can shed some light onto why you should use it.|||

Hi Priya,

I went through the article and what i understood is:

"A datasource is created outside the reference of a package" , means you can share it across many packages by pointing your connection manger to the datasource you just created.Consider this scenario where you have 10 packages pointing to same source then you may use a Datasource so that all connection managers point ot same datasource.Hence at later point of time if you want to change the source you just need to update you datasource rather than updating each connection manager independently.

Note: Please correct me if I am wrong.

|||

When I started with SSIS , I also wanted to use Data source, since it seemd reasonable to reuse the Data source in various packages. Until you want to use package configuration files to store your connectionstrings. Then it is no longer possible to use datasources. From now on I do not use Data sources any longer. I am using package configuration files that contain the connctions strings of my connections, and in my packages I use the appropriate configuration file. I also use environment variables to proint to my package configuration files. This allows for a somewhat easier deployment of packages on developer and production servers.

|||This is the correct way of handling environment differences with SSIS.
As said, data sources are for design time.

A nice little "feature" of data sources: Delete it and it deletes all your connections which are derived from it.|||Hi all,

Thanks for ur replies.
I'v used configuration file to configure connection manager before and you can't configure data source with it.

Unni, as you'v said that:
"Hence at later point of time if you want to change the source you just need to update you datasource rather than updating each connection manager independently."

Did you try out updating the datasource to point to a different source? Does it get reflected in the connection managers? As i've mentioned in the 1st post the connection manager was still pointing to the older db in my case.
Priya

|||

Let me clarify why I am using configuration files and not datas sources:

- datasources are fine if you always edit your package with visual studio as a developer, no matter where your packages are deployed.

- I have to design a package on a development server where I have full access. but my package will be deployed in a production environment where I will have very little access. The sysadmin will now how to make the enviroment variable en how to edit the configuration file with notepad, those are all the tools he will need. The ssis packages will be restored on the production server via backup restore.

So as far I am concerned this way of working is SOX compliant, and therefore I do not use datasources.

|||In general, I think the consensus among most of the regular posters on the forums is that using data sources is more trouble than it is worth. If updating a data source actually updated all the connection managers derived from it, there might be a benefit to using them. But each connection manager maintains its own copy of the connection string, so there is not much value added by using the data source. Plus, the messages to Synchronize Connection Strings are really annoying |||

Hi,

"Hence at later point of time if you want to change the source you just need to update you datasource rather than updating each connection manager independently."

I have not tried this, I am using configuration files in my package.

|||Yes, if you open each and every package in the project, it will prompt you to change the connect string. It doesn't automatically propagate the change. Configurations are a much easier way of getting the same result.