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 synchronization issue: Connection string conflict?

I was NOT using data source till now. I was directly using Ole DB Connection. Everything works as expected.

Today, I created a new Data Source based on the existing Ole Db connection.

I changed my tasks to use this Data Source.

Now, everytime I open the dtsx file, a screen pops-up with title "Synchronize Connection Strings". It reads: "This package contains at least one connection which is based on a data source. The connection strings for the connection and the data source listed below are not identical. Connection string of the connection will be updated to reflect those on the data source.

Connection : XYZ

Data Source: XYZ

Old Connection String: machine_name\instance_name; User ID=sa; Initial Catalog = MyDB; Provider=SQLOLEDB.1

New Connection String: Provider=SQLOLEDB.1; Data Source = machine_name\instance_name; Persist Security Info = true; Password=; User ID=; Initial Catalog = MyDB

I press the OK button, but the screen repeats if I close the dtsx file and re-open it. The underlying error is:

Error 1 Validation error. Data Flow Task - MyTaskName: OLE DB Destination - mytable [214]: The AcquireConnection method call to the connection manager "machine_name\instance_name.MyDB.sa" failed with error code 0xC0202009. mypackage.dtsx 0 0
Any idea?looks like a bug... can you open one, please?

thanks|||Sorry, I did not look at this post and therefore your reply till today (when I thought about using Data Sources again).

How do I submit this bug?

Data Source file on Reporting Services server question

In the Reporting Services Manager, when the user selects the folder where the
reports reside there is a file that contains the connection string
information along with a user name and password (Data Source).
Is there a way to keep the file from being selected by the user ?
Thanks.There is a setting in the properties of the data source to "Hide in List
View". Unfortunately the users can just click on the "Show Details"
option and see the datasource. Depending on the permissions you gave
them they will only be able to view the information.
I sent an question out some time ago seeing if there was away to remove
the "Show Details" for some users but no one replied.
Mike wrote:
> In the Reporting Services Manager, when the user selects the folder where the
> reports reside there is a file that contains the connection string
> information along with a user name and password (Data Source).
> Is there a way to keep the file from being selected by the user ?
>
> Thanks.|||You can use a datasource in another location. It's a faff because you
have to change it manually for each deployed report affected. Also
everytime you deploy the report it will create the datasources in the
deployment folder if it can, so you have to remove it each time.
Having said that, this would essentially cure your problem.
Chris
Bon733 wrote:
> There is a setting in the properties of the data source to "Hide in
> List View". Unfortunately the users can just click on the "Show
> Details" option and see the datasource. Depending on the permissions
> you gave them they will only be able to view the information.
> I sent an question out some time ago seeing if there was away to
> remove the "Show Details" for some users but no one replied.
> Mike wrote:
> > In the Reporting Services Manager, when the user selects the folder
> > where the reports reside there is a file that contains the
> > connection string information along with a user name and password
> > (Data Source).
> >
> > Is there a way to keep the file from being selected by the user ?
> >
> >
> > Thanks.

Data Source Connection String

Is there any way to find out what the data souce connection string is at
runtime?
I have a report that is sent out to various sites, each with a common data
schema, but various connection strings. The report uses custom code in the
report rdl, so each time I deploy the report to a new site, I have to reset
the custom code connection string to match the individual site I am deploying
it to.
I would like to be able to have it dynamically query the connection string
so I can just pass it in to my code as a variable. Any ideas?
MichaelI think the report only knows about the data source name (if using shared ds)
What we have is a bunch of reports using the same data source.
We then deploy to different folders, as long as you are not "overwriting
data source" then you can just configure the different data sources in report
manager.
So 1 report gets deployed to 2 or more folders, each folder having its own
datasource. (the config manager is not intuitive)
We find this easier to manage 2 different regions using the same reports on
2 different databases like this.
We even have it so different sub regions have different sub folders using
linked reports with their sub region 'hardcoded' as a linked report param -
they are not even aware of the other sub regions' reports existing due to
security setup
You could always used linked reports to point back at the different folders
if the users complain.
"Michael C" wrote:
> Is there any way to find out what the data souce connection string is at
> runtime?
> I have a report that is sent out to various sites, each with a common data
> schema, but various connection strings. The report uses custom code in the
> report rdl, so each time I deploy the report to a new site, I have to reset
> the custom code connection string to match the individual site I am deploying
> it to.
> I would like to be able to have it dynamically query the connection string
> so I can just pass it in to my code as a variable. Any ideas?
> Michael|||Adolf,
I too am trying to deploy my solution like you described, one folder for
each region with it's own datasource. My problem is how do you get the
reports to use the correct datasource? For example, my VS2005 solution has a
shared data source which I use to build my reports. When the reports are
deployed, the reports went to Region1 directory and the datasource went to
Data Sources directory. I manually added a datasource in the Region1
directory with the same name as the one in the Data Sources directory, but
the reports aren't using it. How do I get them to use that one instead. I
can't find anything that explains how to do this. Thank you.
"adolf garlic" wrote:
> I think the report only knows about the data source name (if using shared ds)
> What we have is a bunch of reports using the same data source.
> We then deploy to different folders, as long as you are not "overwriting
> data source" then you can just configure the different data sources in report
> manager.
> So 1 report gets deployed to 2 or more folders, each folder having its own
> datasource. (the config manager is not intuitive)
> We find this easier to manage 2 different regions using the same reports on
> 2 different databases like this.
> We even have it so different sub regions have different sub folders using
> linked reports with their sub region 'hardcoded' as a linked report param -
> they are not even aware of the other sub regions' reports existing due to
> security setup
> You could always used linked reports to point back at the different folders
> if the users complain.
> "Michael C" wrote:
> > Is there any way to find out what the data souce connection string is at
> > runtime?
> >
> > I have a report that is sent out to various sites, each with a common data
> > schema, but various connection strings. The report uses custom code in the
> > report rdl, so each time I deploy the report to a new site, I have to reset
> > the custom code connection string to match the individual site I am deploying
> > it to.
> >
> > I would like to be able to have it dynamically query the connection string
> > so I can just pass it in to my code as a variable. Any ideas?
> >
> > Michaelsql

data source connection error...

Guess this was already posted somewhere...
My connection string to connect to sybase: DRIVER={Sybase ASE ODBC
Driver};SRVR=<server_name>;DB=<db_name>
Data source type : ODBC
Works on the Designer but not on the server.
I get a system error 126 - specified driver could not be loaded.
?If its of interest to anybody, this problem was resolved once the MDAC
components were upgraded to 2.8.
"NU" <vidhuvaradan@.rediffmail.com> wrote in message
news:eW$MlA9ZEHA.3596@.tk2msftngp13.phx.gbl...
> Guess this was already posted somewhere...
> My connection string to connect to sybase: DRIVER={Sybase ASE ODBC
> Driver};SRVR=<server_name>;DB=<db_name>
> Data source type : ODBC
> Works on the Designer but not on the server.
> I get a system error 126 - specified driver could not be loaded.
> ?
>
>

Data script task - how to generate multiple rows from one row ? (string splitter)

Hi

in input we have a set of rows, each one with a column containing a string (eg: "AAOOOOAAAOOA").

We'd like to split this string (using a vb.net data script task) into tokens (eg: "AA", then "OOOO","AAA","OO","A"), and to output one line per token.

How can we achieve that with a vb.net data script task ? (Or anything else ?)

best regards

ThibautJust take your source and throw it into a derived column transformation where you'll perform your string split to create new fields for each "token." Then you'll go into an unpivot transformation where you'll take the new columns and turn them into rows.

Why use a script task when you can use the optimized data flow tools as-is?|||

You need to create an asynchrnous transform. So add the transform, select and also setup columns as required. Ensure the SynchronousInputID of the output is set to 0, making it async.

Then read rows and add them to the output as required. Sum dummy code -

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)

' Read the rows in...

While Row.NextRow() ' This happens once for each input row

' Do something here, and as required, add rows to the output, use a loop or whatever

For i As Int = i < 10

With OutputBuffer

.AddRow() ' Adding a new output row and setting values. Can do this as many times as we like, 0 or more times in the context of this input row loop interation

.Asset = Row.InputColumn

.Product = i

Next

End While

If Row.EndOfRowset Then

OutputBuffer.SetEndOfRowset()

End If

End Sub

|||Thanks a lot Darren ! That's really perfect (and thanks for the detailed sample, I really appreciate).

regards,

Thibaut|||Just be careful because some have tested the script task and it performs slower than the other, native dataflow tasks.|||Phil, I agree in principal, the native stock components should be faster as a rule, but have you got any references?|||

DarrenSQLIS wrote:

Phil, I agree in principal, the native stock components should be faster as a rule, but have you got any references?

Yep, no problem... http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=857796&SiteID=1|||Hi Phil

thanks for your input as well. Like Darren, I agree that a good rule of thumb is that a pipeline component will work generally faster than a corresponding script component.

A script component is generally one-shot code, which generally doesn't get the same level of testing and optimization. In the article you point to, the script task benchmark shows an average CPU usage of 5% (which is often a sign of synchronization issues, contention etc). Here (like suggested by a commenter), a modification of the implementation (like handling sets of rows instead of one row at a time) would most likely boost the performance a lot.

So I wouldn't draw conclusions based on a single script example, given that each implementation is likely to vary a lot in terms of performance and memory consumption - just like any kind of code!

But anyway - it seems that I have two solutions for my problem now. I don't hesitate to use script task when they prove useful, but I first try to stick to the pipeline components.

I'll keep you posted!

thanks again for the input

Thibaut Barrère|||Hi

I've finished the job using a script task (I will report back the details later).

I'm curious about how it would have been possible to implement this using a derived column transformation (at first sight I couldn't find out).

Phil could you give a bit more details ? I could not find any real string splitting functions in SSIS - were you thinking of using a FIND function recursively (or any other feature I've missed ?)

cheers

Thibaut

Data Proc Ext - Dynamic Connect String

I am trying to create a data processing extension that will allow me to
dynamically set the connect string. I have been able to successfully
register a sample data processing extension (from Teo Lachev's book), but I'm
not sure if I need to implement all of the classes included - can I just
implement the IDbConnection, IDbConnectionExtension, and IExtension much like
DsConnectionWrapper, but then use OleDbCommand rather than writing DsCommand,
etc.?
I really just need the ability to override the connection string based on
the user that authenticates to the report server. I have a security
extension in place for Forms authentication. I need to be able to switch
dev/test/prod connect strings in addition to changing databases based on who
connected.
BTW, I am using OLEDB and the OLAP provider to connect to an Analysis
Services database so I'm not sure I can just dynamically change my MDX like
some have suggested changing SQL.This is coming in SQL 2005 but I'm not sure it is possible in SQL2K. The
only thing I could think of is to pass the user ID and the query in on the
command and then connect and query at the same time.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"danb" <danb@.discussions.microsoft.com> wrote in message
news:9E9697FD-86A2-45E4-9652-62CE1BCFCFC1@.microsoft.com...
>I am trying to create a data processing extension that will allow me to
> dynamically set the connect string. I have been able to successfully
> register a sample data processing extension (from Teo Lachev's book), but
> I'm
> not sure if I need to implement all of the classes included - can I just
> implement the IDbConnection, IDbConnectionExtension, and IExtension much
> like
> DsConnectionWrapper, but then use OleDbCommand rather than writing
> DsCommand,
> etc.?
> I really just need the ability to override the connection string based on
> the user that authenticates to the report server. I have a security
> extension in place for Forms authentication. I need to be able to switch
> dev/test/prod connect strings in addition to changing databases based on
> who
> connected.
> BTW, I am using OLEDB and the OLAP provider to connect to an Analysis
> Services database so I'm not sure I can just dynamically change my MDX
> like
> some have suggested changing SQL.