Custom task database connection

I'm developing a custom error task for SSIS. I've got a couple of existing OLE DB connections that exist in the Connection Manager. I want a drop down to be displayed in the Custom Task dialogue properties where the user can select one of the databases from the Connection manager.

2 questions:
1. How can i get the list of OLE DB databases in the properties dialogue?
2. Does anyone have a code sample on accessing a database like that within the custom component?Try this post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=122993&SiteID=1

I don't think you can use an OLE-DB connection directly in managed code. You may (I haven't tried) be able to read off the connection string and use that to create your own ADO.net connection. I have been using ADO.Net connections myself, normally SQL, which gives you a SqlClient.SqlConnection object.

|||Hi Darren,

I don't understand how the IDtsTaskUI.Initialize one ties in here? Also, do you have to tie in the connections within the Acquire Connection method?|||If you have a custom UI for your task then you will have implemented IDtsTaskUI.Initialize in the class that implements IDtsTaskUI.

You call AcquireConnection on the connection you want to get the real connection object. For a file connection that is just a string of the filename, for an ADO.NET connection type SQL, that is a SqlConnection. You would call this in your UI, and also at run-time in the Execute method.

Custom DataSets

Is there any way to add a dataset to RS without it being a SP or Query? I
have .Net code that handles all of the database connections and SP calls and
returns datasets that I would like to plug into RS for my repotsOn Dec 20, 4:37 pm, Adam <A...@.discussions.microsoft.com> wrote:
> Is there any way to add a dataset to RS without it being a SP or Query? I
> have .Net code that handles all of the database connections and SP calls and
> returns datasets that I would like to plug into RS for my repots
You could try using the Code section (via: Layout view >> Report drop-
down >> Report Properties... >> Code tab); however, I'm not certain
that it will work. That said, its best for performance for you to use
the built in dataset options available (preferably stored procedure-
based datasets). Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks I will keep that in mind.. the only issue I have with the SP route is
moving the code across environments(DEV, TEST to PRD). Each of these
environments are on different servers with different connection strings.
"EMartinez" wrote:
> On Dec 20, 4:37 pm, Adam <A...@.discussions.microsoft.com> wrote:
> > Is there any way to add a dataset to RS without it being a SP or Query? I
> > have .Net code that handles all of the database connections and SP calls and
> > returns datasets that I would like to plug into RS for my repots
>
> You could try using the Code section (via: Layout view >> Report drop-
> down >> Report Properties... >> Code tab); however, I'm not certain
> that it will work. That said, its best for performance for you to use
> the built in dataset options available (preferably stored procedure-
> based datasets). Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||You can do data processing extension but it is non-trivial and I really
recommend against it.
As far as moving across Dev/Test/Production. This is no problem whatsoever.
While reports can have their own per report data source I strongly suggest
not doing that. Instead you use shared data sources. The first time you
deploy a report based on a shared datasource the datasource gets deployed.
By default thereafter the data source does not get deployed. After the first
deployment of the datasource you set up the connection strings and
credentials for that data source. For each place (Dev/test/production) you
would have different connections/credentials.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Adam" <Adam@.discussions.microsoft.com> wrote in message
news:2E856BD3-2D8F-430F-8CDD-C67A3160BC08@.microsoft.com...
> Thanks I will keep that in mind.. the only issue I have with the SP route
> is
> moving the code across environments(DEV, TEST to PRD). Each of these
> environments are on different servers with different connection strings.
> "EMartinez" wrote:
>> On Dec 20, 4:37 pm, Adam <A...@.discussions.microsoft.com> wrote:
>> > Is there any way to add a dataset to RS without it being a SP or Query?
>> > I
>> > have .Net code that handles all of the database connections and SP
>> > calls and
>> > returns datasets that I would like to plug into RS for my repots
>>
>> You could try using the Code section (via: Layout view >> Report drop-
>> down >> Report Properties... >> Code tab); however, I'm not certain
>> that it will work. That said, its best for performance for you to use
>> the built in dataset options available (preferably stored procedure-
>> based datasets). Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant|||On Dec 21, 8:50 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> You can do data processing extension but it is non-trivial and I really
> recommend against it.
> As far as moving across Dev/Test/Production. This is no problem whatsoever.
> While reports can have their own per report data source I strongly suggest
> not doing that. Instead you use shared data sources. The first time you
> deploy a report based on a shared datasource the datasource gets deployed.
> By default thereafter the data source does not get deployed. After the first
> deployment of the datasource you set up the connection strings and
> credentials for that data source. For each place (Dev/test/production) you
> would have different connections/credentials.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Adam" <A...@.discussions.microsoft.com> wrote in message
> news:2E856BD3-2D8F-430F-8CDD-C67A3160BC08@.microsoft.com...
> > Thanks I will keep that in mind.. the only issue I have with the SP route
> > is
> > moving the code across environments(DEV, TEST to PRD). Each of these
> > environments are on different servers with different connection strings.
> > "EMartinez" wrote:
> >> On Dec 20, 4:37 pm, Adam <A...@.discussions.microsoft.com> wrote:
> >> > Is there any way to add a dataset to RS without it being a SP or Query?
> >> > I
> >> > have .Net code that handles all of the database connections and SP
> >> > calls and
> >> > returns datasets that I would like to plug into RS for my repots
> >> You could try using the Code section (via: Layout view >> Report drop-
> >> down >> Report Properties... >> Code tab); however, I'm not certain
> >> that it will work. That said, its best for performance for you to use
> >> the built in dataset options available (preferably stored procedure-
> >> based datasets). Hope this helps.
> >> Regards,
> >> Enrique Martinez
> >> Sr. Software Consultant
You're welcome. Also, you could have a table that exists in every
environment that basically tells what the environment is and the
locations of the remote environments and uses linked servers to
execute the local stored procedure of the desired environment. Hope
this helps further.
Regards,
Enrique Martinez
Sr. Software Consultant