Dynamic Oledb Destination

Hello,

I am a beginner for the SSIS and would like to know how to modify the OLDEB Destination connectionString property at run time like using "for each loop container".

My requirement is that I have a single source which would be Sql Server 2005 and my destination is in MS-Access database residing in 100 places. I do not want to manually design in the data flow to these 100 destinations.

I have all the destinations stored in a table and would like to pick these destinations from the table and loop through the same at run time by modifying destination connection string.

I have planned using dts but the for each loop container does not work through as it works with flat file connection manager , but does not go well with OLDEB connection.

Highly appreciate any help in this regard.

Regards

Sameer

Sameer,

The OLE DB Destination does not have a connection string property however the OLE DB Connection Manager that it uses DOES. You can change this connection string so that you are pointing at (e.g.) a different server or a different .mdb file.

What you cannot do however is change the name of the table in the OLE DB Destination that you are inserting into. Well actually you can but the metadata (i.e. column names, types) of the table into which you are inserting must be the same as that selected at design-time.

If you are inserting into 100 identical MS Access databases then you can do this by modifying the connection string property of the OLE DB Connection Manager and looping using the For Each Loop container. If you want to insert to 100 different tables then you need 100 different data-flows.

-Jamie

|||

Jamie,

Thanks for the reply. Yes you are right it is the OLEDB Connection manger.

My requirement is that the these are 100 identical MS access tables which needs to be loaded with production data after every 1 hour. Would like to know the details of looping the For Each loop container using my Configuration table, which holds information about MDB file locations. How do I configure the For each loop container to point to the Configuration table. I have now started feeling that connection manager goes well only with the flat file and does not go well with OLEDB.

Can you provide me a small example which loops the files. Also these access files are spread across the network, and many time the network connectiion goes down, hence I have made an extra column in my table which updates that the data has reached/Unreached to final destination. How do i capture the same in SSIS. In old traditional way of DTS I remember I used to have privledge of doing the

Set oPkg = DTSGlobalVariables.Parent through which I could find out the execution result like this, which is oPkg.steps(i).ExecutionResult. How do I achieve the same in SSIS.

Again if you could provide me an hint or working example that would be great. I do have working example for flat file connection manager , but I am unable to replicate the same using OLEDB destination.

Thanks in advance for the suggestions

Regards

|||

Greetings Sameer,

I am working on a similar issue. have you found any resolutions for dymanic configuration of an oledb connection?

my task is smiliar. I plan to dymanically change (table drive) oledb connection "servernames" and query each of these servername, which will in turn write to a single datasource. I am stuck on the dynamic configuration of these oledb sources.

Any feedback you can provide would be greatly appreciated.

Thanks

|||

bowmandba wrote:

Greetings Sameer,

I am working on a similar issue. have you found any resolutions for dymanic configuration of an oledb connection?

my task is smiliar. I plan to dymanically change (table drive) oledb connection "servernames" and query each of these servername, which will in turn write to a single datasource. I am stuck on the dynamic configuration of these oledb sources.

Any feedback you can provide would be greatly appreciated.

Thanks

You need to use expressions.

SSIS>>Expressions

(http://blogs.conchango.com/jamiethomson/archive/tags/SSIS/Expressions/default.aspx)

Any questions, reply here.

-Jamie

Dynamic Oledb Destination

Hello,

I am a beginner for the SSIS and would like to know how to modify the OLDEB Destination connectionString property at run time like using "for each loop container".

My requirement is that I have a single source which would be Sql Server 2005 and my destination is in MS-Access database residing in 100 places. I do not want to manually design in the data flow to these 100 destinations.

I have all the destinations stored in a table and would like to pick these destinations from the table and loop through the same at run time by modifying destination connection string.

I have planned using dts but the for each loop container does not work through as it works with flat file connection manager , but does not go well with OLDEB connection.

Highly appreciate any help in this regard.

Regards

Sameer

Sameer,

The OLE DB Destination does not have a connection string property however the OLE DB Connection Manager that it uses DOES. You can change this connection string so that you are pointing at (e.g.) a different server or a different .mdb file.

What you cannot do however is change the name of the table in the OLE DB Destination that you are inserting into. Well actually you can but the metadata (i.e. column names, types) of the table into which you are inserting must be the same as that selected at design-time.

If you are inserting into 100 identical MS Access databases then you can do this by modifying the connection string property of the OLE DB Connection Manager and looping using the For Each Loop container. If you want to insert to 100 different tables then you need 100 different data-flows.

-Jamie

|||

Jamie,

Thanks for the reply. Yes you are right it is the OLEDB Connection manger.

My requirement is that the these are 100 identical MS access tables which needs to be loaded with production data after every 1 hour. Would like to know the details of looping the For Each loop container using my Configuration table, which holds information about MDB file locations. How do I configure the For each loop container to point to the Configuration table. I have now started feeling that connection manager goes well only with the flat file and does not go well with OLEDB.

Can you provide me a small example which loops the files. Also these access files are spread across the network, and many time the network connectiion goes down, hence I have made an extra column in my table which updates that the data has reached/Unreached to final destination. How do i capture the same in SSIS. In old traditional way of DTS I remember I used to have privledge of doing the

Set oPkg = DTSGlobalVariables.Parent through which I could find out the execution result like this, which is oPkg.steps(i).ExecutionResult. How do I achieve the same in SSIS.

Again if you could provide me an hint or working example that would be great. I do have working example for flat file connection manager , but I am unable to replicate the same using OLEDB destination.

Thanks in advance for the suggestions

Regards

|||

Greetings Sameer,

I am working on a similar issue. have you found any resolutions for dymanic configuration of an oledb connection?

my task is smiliar. I plan to dymanically change (table drive) oledb connection "servernames" and query each of these servername, which will in turn write to a single datasource. I am stuck on the dynamic configuration of these oledb sources.

Any feedback you can provide would be greatly appreciated.

Thanks

|||

bowmandba wrote:

Greetings Sameer,

I am working on a similar issue. have you found any resolutions for dymanic configuration of an oledb connection?

my task is smiliar. I plan to dymanically change (table drive) oledb connection "servernames" and query each of these servername, which will in turn write to a single datasource. I am stuck on the dynamic configuration of these oledb sources.

Any feedback you can provide would be greatly appreciated.

Thanks

You need to use expressions.

SSIS>>Expressions

(http://blogs.conchango.com/jamiethomson/archive/tags/SSIS/Expressions/default.aspx)

Any questions, reply here.

-Jamie

Dynamic Connectionstring and Email delivery

Hi,
Im new to reporting services.
My requirement is to develop only one set of reports and to pass the
connectionstring dynamically based on the user logs in.I used the
setdatasourcecontents API as it overwrites the connectionstring and Im
passing it just before the retrieval of the reports.Can anyone suggest me
whether it is the best way or is there any other way to acheive this.
Also the user will have an option of subscribing to a report and he can
specify a scheduled time for that.As the report runs just before the delivery
and Im Overwriting the datasource the mail will be sent with wrong report
data.So,please help me in solving this.
Im using standard edition.
Thanks..What are you trying to accomplish with this? Is it a matter of several
databases on the same server and based on the user you are allowing them to
see data from that database?
What you are doing is very problematic (as you have seen) but there might be
some other solution for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GK" <GK@.discussions.microsoft.com> wrote in message
news:45299590-B2F5-4FEA-9544-9F92EE2EF028@.microsoft.com...
> Hi,
> Im new to reporting services.
> My requirement is to develop only one set of reports and to pass the
> connectionstring dynamically based on the user logs in.I used the
> setdatasourcecontents API as it overwrites the connectionstring and Im
> passing it just before the retrieval of the reports.Can anyone suggest me
> whether it is the best way or is there any other way to acheive this.
> Also the user will have an option of subscribing to a report and he can
> specify a scheduled time for that.As the report runs just before the
> delivery
> and Im Overwriting the datasource the mail will be sent with wrong report
> data.So,please help me in solving this.
>
> Im using standard edition.
> Thanks..
>|||Hello Bruce,
Thanks for your relply,I have seen some articles and custom data extension
might be a solution for this,and I don't know how to implement this and
whether it solves our email issue.
Definitely "Setdatasourcecontents" is not a solution as it applies changes
globally.
Our client have different companies wherein the database structure is
same.So I can create the reports for one company and pass the connection
string based on the employee login,so that he can view the reports specific
to his company.
Also when an employee subscribes for a report,how to pass the connection
string at the time of delivery of the report so that he receives his
company's report data.
"Bruce L-C [MVP]" wrote:
> What are you trying to accomplish with this? Is it a matter of several
> databases on the same server and based on the user you are allowing them to
> see data from that database?
> What you are doing is very problematic (as you have seen) but there might be
> some other solution for you.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "GK" <GK@.discussions.microsoft.com> wrote in message
> news:45299590-B2F5-4FEA-9544-9F92EE2EF028@.microsoft.com...
> > Hi,
> > Im new to reporting services.
> > My requirement is to develop only one set of reports and to pass the
> > connectionstring dynamically based on the user logs in.I used the
> > setdatasourcecontents API as it overwrites the connectionstring and Im
> > passing it just before the retrieval of the reports.Can anyone suggest me
> > whether it is the best way or is there any other way to acheive this.
> > Also the user will have an option of subscribing to a report and he can
> > specify a scheduled time for that.As the report runs just before the
> > delivery
> > and Im Overwriting the datasource the mail will be sent with wrong report
> > data.So,please help me in solving this.
> >
> >
> > Im using standard edition.
> >
> > Thanks..
> >
> >
>
>|||There are several solutions for this with one exception. The problem is with
subscriptions. There is the User!userid global variable which will work for
everything but a subscription. The subscription will not know who to run as.
I have trouble seeing how to get around that even for a custom data
extension.
Leaving aside subscriptions you can solve the problem without a custom data
extension. Have a table that maps userid to database. Could also use dynamic
sql in Reporting Services but it would be cleaner with Stored Procedures.
Pass the User!Userid to the SP. Look up the database to use and then
assemble your SQL string to use it and execute it. This is clean and cannot
be modified by the user. It does not, however deal with subscriptions. I
don't have a good solution for you for that.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GK" <GK@.discussions.microsoft.com> wrote in message
news:F76DFED5-42A9-4351-9284-E82370257FCB@.microsoft.com...
> Hello Bruce,
> Thanks for your relply,I have seen some articles and custom data extension
> might be a solution for this,and I don't know how to implement this and
> whether it solves our email issue.
> Definitely "Setdatasourcecontents" is not a solution as it applies changes
> globally.
> Our client have different companies wherein the database structure is
> same.So I can create the reports for one company and pass the connection
> string based on the employee login,so that he can view the reports
specific
> to his company.
> Also when an employee subscribes for a report,how to pass the connection
> string at the time of delivery of the report so that he receives his
> company's report data.
>
> "Bruce L-C [MVP]" wrote:
> > What are you trying to accomplish with this? Is it a matter of several
> > databases on the same server and based on the user you are allowing them
to
> > see data from that database?
> >
> > What you are doing is very problematic (as you have seen) but there
might be
> > some other solution for you.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> >
> > "GK" <GK@.discussions.microsoft.com> wrote in message
> > news:45299590-B2F5-4FEA-9544-9F92EE2EF028@.microsoft.com...
> > > Hi,
> > > Im new to reporting services.
> > > My requirement is to develop only one set of reports and to pass the
> > > connectionstring dynamically based on the user logs in.I used the
> > > setdatasourcecontents API as it overwrites the connectionstring and Im
> > > passing it just before the retrieval of the reports.Can anyone suggest
me
> > > whether it is the best way or is there any other way to acheive this.
> > > Also the user will have an option of subscribing to a report and he
can
> > > specify a scheduled time for that.As the report runs just before the
> > > delivery
> > > and Im Overwriting the datasource the mail will be sent with wrong
report
> > > data.So,please help me in solving this.
> > >
> > >
> > > Im using standard edition.
> > >
> > > Thanks..
> > >
> > >
> >
> >
> >