Is SSIS the solution to our problem?

Hi All,

A small part of our project needs to be customizable for each of our clients. Each of our clients have the concept of a "JOB"; The details of which are held in their own database. When our product is installed it must gather Job information from their database and match the appropriate fields to our generic Jobs table. Once this has been done, we can generate a stored procedure that will periodically poll the clients database for new/updated jobs and transfer the data to our database.

What we really need is to come up with something that has a generic UI that will allow us to connect to any data source, enumerate the databases and their tables so it will allow someone to perform the operations above.

My question is can SSIS assist us in achieving this goal?

Any help on this would be greatly appreciated.

Paul.

From a quick reading, it seems like your solution might not require the full extraction, transformation, and loading power of SSIS. Sounds more like an ADO.NET application that would use the appropriate data provider to connect to the selected source, query for schema information and then for Job information, use its internal logic to map fields, then generate the stored procedure.

-Doug

|||Cheers Doug, Very much appreciated...|||

Hi again,

Carrying on from what you said about using ADO.NET for our solution (which i have done) I just wanted to see if there was anybody out there who knows about the following. When you are setting up a database connection in any connection manager, for example the one in visual studio or the one in SQL server management studio. whenever you select a specific provider, a connection string property page seems to be presented for that particular provider. Now i assume that the connection manager somehow gets the connection properties page by calling a method or accessing some property in the provider assembly, buit i just can't find it. I would rather use the providers connection property page rather than create my own if i don't have to, so does anyone know how to access this page?

Any help on this, as always, is greatly appreciated.

Paul.

|||

From classic ADO, it used to be possible to invoke the Data Link Properties dialog and get the connection string back for any [native] OLE DB provider by referencing MSDASC.DLL, the Microsoft Data Access Service Components. Of course with the appropriate managed wrapper you could still do so, but I don't think there's a managed equivalent for .NET Framework Data Providers. However I don't write as much data access code as I used to...if someone corrects me and lets us know that there are managed components available for building connection strings, all the better! I see there's the ConnectionStringEditor class new in the 2.0 Framework that's a UITypeEditor, meaning you could invoke it for a connection string property from a property grid. The MSDN help topic includes a code sample.

-Doug