Data sources and deployment of packages

I notice that when deploying a package that uses data sources behind its connection managers, the deployed package has the connection string built within the package that gets deployed.

From a previous post from Kirk - "What I'd suggest is that you create a Data source per machine and have all the connections reference it. You'll still need to create a connection manager in each package, but they will all reference the same data source. Moving to different machines is no problem because you can have a "machine resident" data source file that properly points to your "ETL Server"."

How are data sources defined for a machine? Is this simply via one of the configuaration options (i.e. an .xml file) that are loaded upon runtime by a package that is built to reference that config, which then trumps the connection string defined within the package?

ThanksDatasources are a design time feature. The connection manager references the DS and caches the connection string so that even if the datasource is no longer available, the connection will still be viable.

For cases where you need to deploy to a location where you will not be opening the package in the designer, and you need the connection string to change, you'll need to configure the connection manager in the package. The data source will only modify the connection in the designer.

The point of the earlier post was that if you're designing multiple packages and you wish to have all the connections in those packages pointing to the same server, you can create datasource and reference it by the DataSourceID in all the packages. If the server changes, you can modify one data source and it will modify all the connections for you. Next time you open the packages, they will pick up the new connection string. Moving the package to a different machine is also no problem so long as there is a data source available on the target machine with the same DataSourceID.

Configurations are your first line of defense for making packages location independent for easier deployment and your best option for packages that won't be opened in the designer. The DataSource approach is just a one off thing you can do in some cases.

K|||

>> Moving the package to a different machine is also no problem so long as there is a data source available on the target machine with the same DataSourceID.
>> Configurations are your first line of defense for making packages location independent for easier deployment and your best option for packages that won't be opened in the designer.

When I'm talking about deployment, I'm thinking test/prod etc where I'll never need to open it in Designer. That's what the dev env is for. Isn't it?

Given that, package configurations look like the "only option" and not just a "best option", and "only line of defence", not "first line of defense". Isn't that true? Basically, if I don't plan on opening my packages in Designer, then I have to go through the painful process of configuring each package separately and entering values for each package at deployment time. Is there an alternative?

Thanks,

RV.

|||

We are required to do a silent install in production at client sites, ie. no DBA or designer access. In this case, we develop in our environment and may would have to deploy each package with its connection strings. This may impact our design to minimize the number of packages.

Any thoughts?

|||

Use configurations. For each site you will need to adjust the configuration data store to hold the correct values for that site, and how you that depends on the store you use. You could write a wizard where the person running the install simply supplies the values and these are written to the correct location. Your packages will already be configured to read their information from configurations so everything should be sweet. Indirect configurations may be easier, and the values set by the install wizard.

Some links-

http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2342.aspx

http://blogs.conchango.com/jamiethomson/archive/2005/10/31/2336.aspx

http://msdn2.microsoft.com/en-us/library/ms141682.aspx

http://www.sqljunkies.com/WebLog/knight_reign/archive/2004/12/07/5445.aspx

|||

I went through all this frustration myself and eventually settled on a solution that works very well. In a nutshell, I store my connection strings for all my connection managers in the database.

1) All my connection managers are of type OLEDB

2) Every package that has a connection string has package configurations enabled with a Configuration entry of type SQL Server that points to a Configuration table in the database. This entry is the way the connection manager gets the connection string at runtime. In the Configuration entry, it is the Configuration filter setting which will differentiate the different entries in the database pertaining to different connection strings.

3) My deployment scripts insert the correct database connection info into the configuration database for each connection. The connection strings are dependent on the environment I'm deploying to. The connection managers in the solution will at runtime grab the value from the database via the package configurations on the package the connection managers live on.

4) I deploy using dtsutil and make it silent. No need to not have it silent.

Data sources and deployment of packages

I notice that when deploying a package that uses data sources behind its connection managers, the deployed package has the connection string built within the package that gets deployed.

From a previous post from Kirk - "What I'd suggest is that you create a Data source per machine and have all the connections reference it. You'll still need to create a connection manager in each package, but they will all reference the same data source. Moving to different machines is no problem because you can have a "machine resident" data source file that properly points to your "ETL Server"."

How are data sources defined for a machine? Is this simply via one of the configuaration options (i.e. an .xml file) that are loaded upon runtime by a package that is built to reference that config, which then trumps the connection string defined within the package?

ThanksDatasources are a design time feature. The connection manager references the DS and caches the connection string so that even if the datasource is no longer available, the connection will still be viable.

For cases where you need to deploy to a location where you will not be opening the package in the designer, and you need the connection string to change, you'll need to configure the connection manager in the package. The data source will only modify the connection in the designer.

The point of the earlier post was that if you're designing multiple packages and you wish to have all the connections in those packages pointing to the same server, you can create datasource and reference it by the DataSourceID in all the packages. If the server changes, you can modify one data source and it will modify all the connections for you. Next time you open the packages, they will pick up the new connection string. Moving the package to a different machine is also no problem so long as there is a data source available on the target machine with the same DataSourceID.

Configurations are your first line of defense for making packages location independent for easier deployment and your best option for packages that won't be opened in the designer. The DataSource approach is just a one off thing you can do in some cases.

K|||

>> Moving the package to a different machine is also no problem so long as there is a data source available on the target machine with the same DataSourceID.
>> Configurations are your first line of defense for making packages location independent for easier deployment and your best option for packages that won't be opened in the designer.

When I'm talking about deployment, I'm thinking test/prod etc where I'll never need to open it in Designer. That's what the dev env is for. Isn't it?

Given that, package configurations look like the "only option" and not just a "best option", and "only line of defence", not "first line of defense". Isn't that true? Basically, if I don't plan on opening my packages in Designer, then I have to go through the painful process of configuring each package separately and entering values for each package at deployment time. Is there an alternative?

Thanks,

RV.

|||

We are required to do a silent install in production at client sites, ie. no DBA or designer access. In this case, we develop in our environment and may would have to deploy each package with its connection strings. This may impact our design to minimize the number of packages.

Any thoughts?

|||

Use configurations. For each site you will need to adjust the configuration data store to hold the correct values for that site, and how you that depends on the store you use. You could write a wizard where the person running the install simply supplies the values and these are written to the correct location. Your packages will already be configured to read their information from configurations so everything should be sweet. Indirect configurations may be easier, and the values set by the install wizard.

Some links-

http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2342.aspx

http://blogs.conchango.com/jamiethomson/archive/2005/10/31/2336.aspx

http://msdn2.microsoft.com/en-us/library/ms141682.aspx

http://www.sqljunkies.com/WebLog/knight_reign/archive/2004/12/07/5445.aspx

|||

I went through all this frustration myself and eventually settled on a solution that works very well. In a nutshell, I store my connection strings for all my connection managers in the database.

1) All my connection managers are of type OLEDB

2) Every package that has a connection string has package configurations enabled with a Configuration entry of type SQL Server that points to a Configuration table in the database. This entry is the way the connection manager gets the connection string at runtime. In the Configuration entry, it is the Configuration filter setting which will differentiate the different entries in the database pertaining to different connection strings.

3) My deployment scripts insert the correct database connection info into the configuration database for each connection. The connection strings are dependent on the environment I'm deploying to. The connection managers in the solution will at runtime grab the value from the database via the package configurations on the package the connection managers live on.

4) I deploy using dtsutil and make it silent. No need to not have it silent.

Data source problem. Help! - remote report server

Hi guys,

I have project A with the data source name called 'dsyellow', which is a shared data source. The reports in this project were deployed and working fine.I created a new project(project B) and used the same dataset name (i.e.dsyellow), however this data source points to a different database. Accidentaly when I deploy project B, I overwrote the data source of Project B and I am unable to run the reports in project A. I am getting the following error message. I tried to re-deploy the the first datasource by overwriting the latest data source, however, when I run the report, I am getting the same problem. Please this is urgent. Let me know if you have any idea.

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'dsyellow'. (rsErrorOpeningConnection)

For more information about this error navigate to the report server on the local server machine, or enable remote errors

From Report Manager you can create a second data source that will work for Project B. Then go to the properties page for the report and select the Data Source tab and point it to this new Data source. For the Project A report, also make sure that on the Data Source tab you are pointing to the dsYellow shared data source, then go to the properties tab for the dsYellow shared datasource and make sure that the connection string and user information are what you expect.

I hope this helps.

|||

I think what you said make sense. However, I am having one more issue that prevent me to do that. The issue is that I am not seeing the Data Source folder from the content page of the Report Manager. I think it seems that it is hidden. I hope I will be able to solve the problem if I am able to see the Data Source folder first. Do you have any idea about this?

Appreciate it.

|||

hey! I figured out how to get back the hidden data source folder. I think I can do it now.

Thanks for your help.

|||Items are only hidden in the list view, if you click on the Detail View button, all items are visible. Is this what you found?|||

Yea, that was what I found. But the issue is still there. Here is the thing,I created a new project with some reports in it. My data source is from a remote server. The report is also deployed on the same remote server. I am able to run the report from my development machine. However, the error comes after deploying it on the remote server and try to run it from the report server. I checked that my report is pointing to the right server name and database name. But no luck. Ohhhhhh it makes me crazy.

|||Can you run the report on the actual report server so you can get the full error?|||

I did. This is the error message:

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'dsyellow'. (rsErrorOpeningConnection)

For more information about this error navigate to the report server on the local server machine, or enable remote errors

|||The error is stating that you are not local so therefore it is not showing the full error stack. Can you look in the RS log files to get the actual error?|||Do you know which table shows the errors?|||

Daniel is suggesting to look in the RS log files. They are located here:

{Drive}:\Program Files\Microsoft SQL Server\MSSQL.{Instance_of_RS}\Reporting Services\LogFiles

Jarret

|||

Hey guys,

It may sounds crazy but I am having some previllage issue to access the report server machine to get the log file. Anyhow, let me put the problem this way. I have three servers, assume ServerA,ServerB, and ServerC. ServerA is where the database(datasource) is located. ServerB is used as a report server and Server C is my local machine where I design reports. So obviously I deployed the reports from my local machine(server C) to the remote report server(server B). Deployement was successful but I got the following error when I run the report from the report server. You might think that the report is not pointing to the right data source, I checked that several times and it is pointing to the right data source.

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'dsCallB'. (rsErrorOpeningConnection)

For more information about this error navigate to the report server on the local server machine, or enable remote errors|||

Going along with Daniel's suggestion earlier, you should open up Internet Explorer from ServerB's desktop (the RS server), and go to http://localhost/ReportServer. Then navigate to your report and see if you get more information about your error.

Another thing to check... Make sure your report's data source is setup correctly by doing this:

Open http://ServerB/Reports and navigate to your report.
Click on the 'Properties' tab, then select 'Data Source' from the left side.
Double check your data source and initial catalog in your connection string.
Then, select 'Credentials stored securely in the report server' in the 'Connect using...' section.
Type in a domain account with sysadmin privileges (for this test) like DOMAIN\USERNAME, with its respective password.
Check the box for 'Use as Windows credentials when connecting to the data source'.
Click 'Apply' at the bottom, and then click the 'View' tab to run the report.

Hope this helps.

Jarret

|||I did that so many times, but no luck.|||

Hmmmm...

Open up Reporting Services Configuration Manager and connect to ServerB. Click 'Database Setup' from the left pane. Make sure that the Server Name, Database Name, Credentials Type, Account Name, and Password are all correct.

Log into ServerB directly and browse to http://localhost/Reports and paste the message you get when you try to run this report. It should give more information about your error.

Do any other reports work from ServerB? If so, can you create another report pointing to the same database as dsCallB, with "select top 1 Name from sys.objects". Deploy it and see if it returns any data.

Jarret

sql

Data source problem. Help!

Hi guys,

I have project A with the data source name called 'dsyellow', which is a shared data source. The reports in this project were deployed and working fine.I created a new project(project B) and used the same dataset name (i.e.dsyellow), however this data source points to a different database. Accidentaly when I deploy project B, I overwrote the data source of Project B and I am unable to run the reports in project A. I am getting the following error message. I tried to re-deploy the the first datasource by overwriting the latest data source, however, when I run the report, I am getting the same problem. Please this is urgent. Let me know if you have any idea.

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'dsyellow'. (rsErrorOpeningConnection)

For more information about this error navigate to the report server on the local server machine, or enable remote errors

From Report Manager you can create a second data source that will work for Project B. Then go to the properties page for the report and select the Data Source tab and point it to this new Data source. For the Project A report, also make sure that on the Data Source tab you are pointing to the dsYellow shared data source, then go to the properties tab for the dsYellow shared datasource and make sure that the connection string and user information are what you expect.

I hope this helps.

|||

I think what you said make sense. However, I am having one more issue that prevent me to do that. The issue is that I am not seeing the Data Source folder from the content page of the Report Manager. I think it seems that it is hidden. I hope I will be able to solve the problem if I am able to see the Data Source folder first. Do you have any idea about this?

Appreciate it.

|||

hey! I figured out how to get back the hidden data source folder. I think I can do it now.

Thanks for your help.

|||Items are only hidden in the list view, if you click on the Detail View button, all items are visible. Is this what you found?|||

Yea, that was what I found. But the issue is still there. Here is the thing,I created a new project with some reports in it. My data source is from a remote server. The report is also deployed on the same remote server. I am able to run the report from my development machine. However, the error comes after deploying it on the remote server and try to run it from the report server. I checked that my report is pointing to the right server name and database name. But no luck. Ohhhhhh it makes me crazy.

|||Can you run the report on the actual report server so you can get the full error?|||

I did. This is the error message:

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'dsyellow'. (rsErrorOpeningConnection)

For more information about this error navigate to the report server on the local server machine, or enable remote errors

|||The error is stating that you are not local so therefore it is not showing the full error stack. Can you look in the RS log files to get the actual error?|||Do you know which table shows the errors?|||

Daniel is suggesting to look in the RS log files. They are located here:

{Drive}:\Program Files\Microsoft SQL Server\MSSQL.{Instance_of_RS}\Reporting Services\LogFiles

Jarret

|||

Hey guys,

It may sounds crazy but I am having some previllage issue to access the report server machine to get the log file. Anyhow, let me put the problem this way. I have three servers, assume ServerA,ServerB, and ServerC. ServerA is where the database(datasource) is located. ServerB is used as a report server and Server C is my local machine where I design reports. So obviously I deployed the reports from my local machine(server C) to the remote report server(server B). Deployement was successful but I got the following error when I run the report from the report server. You might think that the report is not pointing to the right data source, I checked that several times and it is pointing to the right data source.

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'dsCallB'. (rsErrorOpeningConnection)

For more information about this error navigate to the report server on the local server machine, or enable remote errors|||

Going along with Daniel's suggestion earlier, you should open up Internet Explorer from ServerB's desktop (the RS server), and go to http://localhost/ReportServer. Then navigate to your report and see if you get more information about your error.

Another thing to check... Make sure your report's data source is setup correctly by doing this:

Open http://ServerB/Reports and navigate to your report.
Click on the 'Properties' tab, then select 'Data Source' from the left side.
Double check your data source and initial catalog in your connection string.
Then, select 'Credentials stored securely in the report server' in the 'Connect using...' section.
Type in a domain account with sysadmin privileges (for this test) like DOMAIN\USERNAME, with its respective password.
Check the box for 'Use as Windows credentials when connecting to the data source'.
Click 'Apply' at the bottom, and then click the 'View' tab to run the report.

Hope this helps.

Jarret

|||I did that so many times, but no luck.|||

Hmmmm...

Open up Reporting Services Configuration Manager and connect to ServerB. Click 'Database Setup' from the left pane. Make sure that the Server Name, Database Name, Credentials Type, Account Name, and Password are all correct.

Log into ServerB directly and browse to http://localhost/Reports and paste the message you get when you try to run this report. It should give more information about your error.

Do any other reports work from ServerB? If so, can you create another report pointing to the same database as dsCallB, with "select top 1 Name from sys.objects". Deploy it and see if it returns any data.

Jarret

Data source problem. Help!

Hi guys,

I have project A with the data source name called 'dsyellow', which is a shared data source. The reports in this project were deployed and working fine.I created a new project(project B) and used the same dataset name (i.e.dsyellow), however this data source points to a different database. Accidentaly when I deploy project B, I overwrote the data source of Project B and I am unable to run the reports in project A. I am getting the following error message. I tried to re-deploy the the first datasource by overwriting the latest data source, however, when I run the report, I am getting the same problem. Please this is urgent. Let me know if you have any idea.

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'dsyellow'. (rsErrorOpeningConnection)

For more information about this error navigate to the report server on the local server machine, or enable remote errors

From Report Manager you can create a second data source that will work for Project B. Then go to the properties page for the report and select the Data Source tab and point it to this new Data source. For the Project A report, also make sure that on the Data Source tab you are pointing to the dsYellow shared data source, then go to the properties tab for the dsYellow shared datasource and make sure that the connection string and user information are what you expect.

I hope this helps.

|||

I think what you said make sense. However, I am having one more issue that prevent me to do that. The issue is that I am not seeing the Data Source folder from the content page of the Report Manager. I think it seems that it is hidden. I hope I will be able to solve the problem if I am able to see the Data Source folder first. Do you have any idea about this?

Appreciate it.

|||

hey! I figured out how to get back the hidden data source folder. I think I can do it now.

Thanks for your help.

|||Items are only hidden in the list view, if you click on the Detail View button, all items are visible. Is this what you found?|||

Yea, that was what I found. But the issue is still there. Here is the thing,I created a new project with some reports in it. My data source is from a remote server. The report is also deployed on the same remote server. I am able to run the report from my development machine. However, the error comes after deploying it on the remote server and try to run it from the report server. I checked that my report is pointing to the right server name and database name. But no luck. Ohhhhhh it makes me crazy.

|||Can you run the report on the actual report server so you can get the full error?|||

I did. This is the error message:

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'dsyellow'. (rsErrorOpeningConnection)

For more information about this error navigate to the report server on the local server machine, or enable remote errors

|||The error is stating that you are not local so therefore it is not showing the full error stack. Can you look in the RS log files to get the actual error?|||Do you know which table shows the errors?|||

Daniel is suggesting to look in the RS log files. They are located here:

{Drive}:\Program Files\Microsoft SQL Server\MSSQL.{Instance_of_RS}\Reporting Services\LogFiles

Jarret

|||

Hey guys,

It may sounds crazy but I am having some previllage issue to access the report server machine to get the log file. Anyhow, let me put the problem this way. I have three servers, assume ServerA,ServerB, and ServerC. ServerA is where the database(datasource) is located. ServerB is used as a report server and Server C is my local machine where I design reports. So obviously I deployed the reports from my local machine(server C) to the remote report server(server B). Deployement was successful but I got the following error when I run the report from the report server. You might think that the report is not pointing to the right data source, I checked that several times and it is pointing to the right data source.

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'dsCallB'. (rsErrorOpeningConnection)

For more information about this error navigate to the report server on the local server machine, or enable remote errors|||

Going along with Daniel's suggestion earlier, you should open up Internet Explorer from ServerB's desktop (the RS server), and go to http://localhost/ReportServer. Then navigate to your report and see if you get more information about your error.

Another thing to check... Make sure your report's data source is setup correctly by doing this:

Open http://ServerB/Reports and navigate to your report.
Click on the 'Properties' tab, then select 'Data Source' from the left side.
Double check your data source and initial catalog in your connection string.
Then, select 'Credentials stored securely in the report server' in the 'Connect using...' section.
Type in a domain account with sysadmin privileges (for this test) like DOMAIN\USERNAME, with its respective password.
Check the box for 'Use as Windows credentials when connecting to the data source'.
Click 'Apply' at the bottom, and then click the 'View' tab to run the report.

Hope this helps.

Jarret

|||I did that so many times, but no luck.|||

Hmmmm...

Open up Reporting Services Configuration Manager and connect to ServerB. Click 'Database Setup' from the left pane. Make sure that the Server Name, Database Name, Credentials Type, Account Name, and Password are all correct.

Log into ServerB directly and browse to http://localhost/Reports and paste the message you get when you try to run this report. It should give more information about your error.

Do any other reports work from ServerB? If so, can you create another report pointing to the same database as dsCallB, with "select top 1 Name from sys.objects". Deploy it and see if it returns any data.

Jarret

Data processing extensions on SQL Server Express Edition?

I developed nice reports using custom data processing extensions. When I deployed the reports on my report server (I am using the express edition of SQL Server 2005) I was surprised to see that my reports were not rendering successfully.

After searching the web, I found this page listing the supported/unsupported features of SQL Server 2005 Express Edition: http://msdn2.microsoft.com/en-us/library/ms365166.aspx

On this page it clearly says “The Reporting Services API extensible platform for delivery, data processing, rendering, and security is not supported.”

Is there a way to get my reports to work on the express edition?

If not, which minimal version of SQL Server should a buy to get it to work (workgroup, standard or enterprise)?

Thanks for your help.

You will need Standard or Enterprise edition:

http://msdn2.microsoft.com/en-us/library/ms143761.aspx

Data Mining slicing / filtering

Is there a way to dynamically slice / filter data when running a data mining query?

(Dynamically means - not deployed as part of the structure)

For example - I’d like find associations to products sold in different geographic locations.

Does the answer differ if the structure is based on a cube or on a rational DB?

The answer is no - the counts of product associations are the counts for the products with which the model was changed. You would have to create specific structure for each subset of the data you would want to model.

You may be able to use many-many dimensions in OLAP and then provide the appropriate slice to see association counts by region, but you won't have a mining model that can perform predictions, and you will only see pairwise counts.

Data Mining slicing / filtering

Is there a way to dynamically slice / filter data when running a data mining query?

(Dynamically means - not deployed as part of the structure)

For example - I’d like find associations to products sold in different geographic locations.

Does the answer differ if the structure is based on a cube or on a rational DB?

The answer is no - the counts of product associations are the counts for the products with which the model was changed. You would have to create specific structure for each subset of the data you would want to model.

You may be able to use many-many dimensions in OLAP and then provide the appropriate slice to see association counts by region, but you won't have a mining model that can perform predictions, and you will only see pairwise counts.