Multi Table Source

I am wondering how I can create an OLE DB Source component that can store a multi-table DataSet object. Is this something that is possible or do I need some custom object to do this? I'm sure I can create a multi-table destination object and create sources for each data table needed however, I need to get the data for 5 tables and do this about 30K times. I'm thinking this approach will perform better.

Here is what I've been trying to get working. (Note there is only one parameter that all the queries use - @.keyName)

SELECT * FROM Table1
WHERE (Key = ?)

SELECT * FROM Table2
WHERE (Key = ?)

SELECT * FROM Table3
WHERE (Key = ?)

SELECT * FROM Table4
WHERE (Key = ?)

SELECT * FROM Table5
WHERE (Key = ?)

TIA

Ian

You can have more than one OLE DB source on a given data flow. From there you can merge/union records as required.|||

Does that mean I should use a separate source for each table then merge them into one DataSet Destination? (Sorry, I'm new to SSIS)

A single procedure/statement returning multiple tables sounds more efficient, is this not possible?

|||

enizin wrote:

Does that mean I should use a separate source for each table then merge them into one DataSet Destination? (Sorry, I'm new to SSIS)

A single procedure/statement returning multiple tables sounds more efficient, is this not possible?

A SQL statement doesn't return a table. It returns a result set. Either write a SQL statement that selects from all of your tables and does the necessary joins or unions and then use that statement in an OLE DB source, or you can use an OLE DB source for each table -- which will have to be merged together to get one "result set."|||

Sorry, I'm used to referring to data tables within ADO.NET DataSets...

In the Management Studio, if I run this set of statements against the AdventureWorks database I can get a "dataset" containing each result set - all of which have different columns.

SELECT * FROM HumanResources.Employee WHERE EmployeeId = ?

SELECT * FROM HumanResources.EmployeeAddress WHERE EmployeeId = ?

SELECT * FROM HumanResources.EmployeeDepartmentHistory WHERE EmployeeId = ?

SELECT * FROM HumanResources.EmployeePayHistory WHERE EmployeeId = ?

It sounds like this wouldn't work in SSIS because one source cannot contain multiple result sets without performing a union as it can only contain one set of columns.

The reason for needing the data like this is I need to add/update/delete rows to/from each of these tables then save them to my destination database. For my purposes it sounds like using the multiple source option will be the best route.

Thanks for your help.

MTS Threads

I am wondering how to determine the the number of threads an MTS object can handle, and where you can view this information in the mts explorer.

Thanks

Mike

You should be posting the MTS group not SQL.

http://support.microsoft.com/kb/282490/

MSXML3: Reference to undeclared namespace prefix: 'sql'.

I have a vb com+ object that has a function that is supposed to return some XML from the database. It gets the XML via an ADO stream object. However, when I run the VB from its test harness in the project group it gives the following error;
MSXML3: Reference to undeclared namespace prefix: 'sql'.
My project references has;
MS ActiveX Data Objects 2.6 Library
MS XML v4.0
If I debug print the XML out it before writing it to the stream it gives ;
<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:header><sql:param name='ID'>
1
</sql:param></sql:header><sql:query>
exec sp100PaAddress_Select @.ID
</sql:query></ROOT>
"Richard P" <anonymous@.discussions.microsoft.com> wrote in message
news:3D95465D-CAD3-41CF-894E-B4077DF9B75C@.microsoft.com...
> I have a vb com+ object that has a function that is supposed to return
some XML from the database. It gets the XML via an ADO stream object.
However, when I run the VB from its test harness in the project group it
gives the following error;
> MSXML3: Reference to undeclared namespace prefix: 'sql'.
Try posting the code that is causing the error...
Bryant