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...

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...

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...

Data Mining Wizard (Not Responding)

I am trying to create a new mining structure, but when launching the wizard the "Data Mining Wizard" stops responding where the dropdownlist with the algorithms should appear.

The bizarre thing is that I can access the cube and the datawarehouse just fine using the same setup, it's just the mining wizard that doesn't work.

I am using SQL 2005 standard & Visual Studio 2005 Team Suite (and yes I have reinstalled SQL 2005) - no change.

Are there any logs that might shed some light on this strange behavior, or is this a known issue?

The main difference with the DM wizard is that, unlike other wizards, it actually connects to the server to determine the list of available algorithms. However, it is supposed to provide a default list of algorithms if it cannot connect.

You might try looking at the project settings to make sure that the deployment server is set up correctly.

Have you succesfully deployed any AS projects to an AS server?

|||

Hi Chris

The behavior of the algorithm selection wizard page is to retrieve the list of algorithm from the deployment server. If the query times out, it reverts to a list of cached algorithms. The value of the timeout depends on whether:

1. The server is a nonexistent machine (in this case, the timeout is equal to the time it takes to determine from DNS that the machine name is nonexistent and is usually fast)

2. The server is offline. In this case, the timeout is longer (usually 30 seconds, but can be configured under network settings).

3. The server is online, but Analysis Service is not started. This timeout is the TCP timeout, usually 30 seconds.

The first step is to determine your deployment server. For that, right click on the project select properties and look under Configuration Properties -> Deployment. The server name is available in the property grid. Next, please ensure that the server is reachable and is running Analysis Services. If it's an offline server, that you should get a list of default algorithms after 30 seconds or the timeout configured for your machine

Let me know if you have any further questions

Thanks

Shuvro

|||

I have the exact same issue, I have deleted and removed every thing and reinstalled SQl 2005 standard and Visual Studio 2005 and every time I get an unresposive screen when trying to pick a mining model.

I had created a mining model before and worked fine but know just stops responding.

|||

If possible, please work with tech support - we haven't seen these issues outside of what we've already commented on, and it's difficult to debug through the forum.

Thanks

Data Mining Wizard (Not Responding)

I am trying to create a new mining structure, but when launching the wizard the "Data Mining Wizard" stops responding where the dropdownlist with the algorithms should appear.

The bizarre thing is that I can access the cube and the datawarehouse just fine using the same setup, it's just the mining wizard that doesn't work.

I am using SQL 2005 standard & Visual Studio 2005 Team Suite (and yes I have reinstalled SQL 2005) - no change.

Are there any logs that might shed some light on this strange behavior, or is this a known issue?

The main difference with the DM wizard is that, unlike other wizards, it actually connects to the server to determine the list of available algorithms. However, it is supposed to provide a default list of algorithms if it cannot connect.

You might try looking at the project settings to make sure that the deployment server is set up correctly.

Have you succesfully deployed any AS projects to an AS server?

|||

Hi Chris

The behavior of the algorithm selection wizard page is to retrieve the list of algorithm from the deployment server. If the query times out, it reverts to a list of cached algorithms. The value of the timeout depends on whether:

1. The server is a nonexistent machine (in this case, the timeout is equal to the time it takes to determine from DNS that the machine name is nonexistent and is usually fast)

2. The server is offline. In this case, the timeout is longer (usually 30 seconds, but can be configured under network settings).

3. The server is online, but Analysis Service is not started. This timeout is the TCP timeout, usually 30 seconds.

The first step is to determine your deployment server. For that, right click on the project select properties and look under Configuration Properties -> Deployment. The server name is available in the property grid. Next, please ensure that the server is reachable and is running Analysis Services. If it's an offline server, that you should get a list of default algorithms after 30 seconds or the timeout configured for your machine

Let me know if you have any further questions

Thanks

Shuvro

|||

I have the exact same issue, I have deleted and removed every thing and reinstalled SQl 2005 standard and Visual Studio 2005 and every time I get an unresposive screen when trying to pick a mining model.

I had created a mining model before and worked fine but know just stops responding.

|||

If possible, please work with tech support - we haven't seen these issues outside of what we've already commented on, and it's difficult to debug through the forum.

Thanks