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.

multi step OLE DB error

Hi All,

I have a MS Access DB that I have successfully u/graded to SQL Express 2005. I run my code using a recordset as normal, and all connections to the database work fine, but I receive the multi step OLE DB error when it gets to a line trying to populate an address field which has a datatype of nvarchar(max), null. (Field was Memo in Access version before).

This field in SQL2005 has allow nulls.

I've tried to add an empty string " " to the variable before being saved, but this still doesn't work.

Any ideas?

Hi,

could you please provide the code you are using as well as the exact error message ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

The exact error message received is:

-2147217887 - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

**************************************************************************

The code that is used, (some cut to save space) is below. All is fine, but breaks on the Physical Address line highlighted.

GetDSN gets the DSN in the DSN settings using ODBC. As I said, this worked in Access, but now in SQL, its not working.

**************************************************************************

Public Function mStaffUpdate(StaffID As Long, Title As String, FirstName As String, LastName As String, _
JobTitle As String, Department As String, Phone As String, _
Fax As String, PhoneExt As String, Mobile As String, _
HomePhone As String, Email As String, Website As String, _
CompanyLink As Long, PhysicalAddress As String, _
PhysicalCity As String, PhysicalState As String, PhysicalZipCode As String, _
PhysicalCountry As String, PostalAddress As String, _
PostalCity As String, PostalState As String, _
PostalZipCode As String, PostalCountry As String, _
UserName As String, Password As String, SecurityLevel As String)

' Open a Connection Object to the database
Dim cnnData As ADODB.Connection
Set cnnData = New Connection

cnnData.Open GetDSN

' Open a Recordset Object
Dim rsData As ADODB.Recordset
Set rsData = New ADODB.Recordset
Dim strSQL As String
strSQL = "SELECT * FROM tblStaff WHERE StaffID= " & StaffID
rsData.Open strSQL, cnnData, adOpenStatic, adLockPessimistic

'cut some out for less to see here...
rsData.Fields("HomePhone").Value = HomePhone
rsData.Fields("Email").Value = Email
rsData.Fields("Website").Value = Website
rsData.Fields("PhysicalAddress").Value = PhysicalAddress & " "
rsData.Fields("PhysicalCity").Value = PhysicalCity & " "
rsData.Fields("PhysicalState").Value = PhysicalState & " "

rsData.Update

' Close cnn and recordset
rsData.Close
Set rsData = Nothing
cnnData.Close
Set cnnData = Nothing

End Function

|||any ideas?|||Do you exceed the max possible characters eventually ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

No. Actually the address field doesn't need to be that large, it will only have 3-4 lines of an address. But I can't work out why this error is happenning?

|||any suggestions|||

Hi All,

Is there a different group that might be better to help with this question above?

I'm really keen to know what's the cause of this error and how to correct.

|||Moved to Data Access :-)|||can anyone help?|||any ideas?|||

What OLEDB provider are you using?

- Waseem

|||

i have this same problem,

program written in visual basic run properly on access database, but on sql server 2k sp 4 causes errors with

multi step error.

i change provider from mdac to sql native client and now program is running great

and this is only one opportunity to get your program running (i think - updating mdac doesn't help me)

best

|||

mrowek wrote:

i change provider from mdac to sql native client and now program is running great

and this is only one opportunity to get your program running (i think - updating mdac doesn't help me)

yeah, i just check code in vb with SQLOLEDB.1 provider

when it's saves strings to db it generate error

i put trim(cstr(blah blah)) and now it's write correctly to db without multi step error

[; check code and put this same

it should help

|||

My program occurs this error at the line below:

rs.delete <- occurs "multi-step multi step OLE DB error ......."

I spent two days for finding the resolve way....

then.....I insert "rs.AbsolutePosition = rs.AbsolutePosition" before "rs.delete", like:

.....

rs.AbsolutePosition = rs.AbsolutePosition

rs.delete

rs.update

......

and the error is gone............(maybe this is the most strange thing I have ever seen....@.@.)

multi step OLE DB error

Hi All,

I have a MS Access DB that I have successfully u/graded to SQL Express 2005. I run my code using a recordset as normal, and all connections to the database work fine, but I receive the multi step OLE DB error when it gets to a line trying to populate an address field which has a datatype of nvarchar(max), null. (Field was Memo in Access version before).

This field in SQL2005 has allow nulls.

I've tried to add an empty string " " to the variable before being saved, but this still doesn't work.

Any ideas?

Hi,

could you please provide the code you are using as well as the exact error message ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

The exact error message received is:

-2147217887 - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

**************************************************************************

The code that is used, (some cut to save space) is below. All is fine, but breaks on the Physical Address line highlighted.

GetDSN gets the DSN in the DSN settings using ODBC. As I said, this worked in Access, but now in SQL, its not working.

**************************************************************************

Public Function mStaffUpdate(StaffID As Long, Title As String, FirstName As String, LastName As String, _
JobTitle As String, Department As String, Phone As String, _
Fax As String, PhoneExt As String, Mobile As String, _
HomePhone As String, Email As String, Website As String, _
CompanyLink As Long, PhysicalAddress As String, _
PhysicalCity As String, PhysicalState As String, PhysicalZipCode As String, _
PhysicalCountry As String, PostalAddress As String, _
PostalCity As String, PostalState As String, _
PostalZipCode As String, PostalCountry As String, _
UserName As String, Password As String, SecurityLevel As String)

' Open a Connection Object to the database
Dim cnnData As ADODB.Connection
Set cnnData = New Connection

cnnData.Open GetDSN

' Open a Recordset Object
Dim rsData As ADODB.Recordset
Set rsData = New ADODB.Recordset
Dim strSQL As String
strSQL = "SELECT * FROM tblStaff WHERE StaffID= " & StaffID
rsData.Open strSQL, cnnData, adOpenStatic, adLockPessimistic

'cut some out for less to see here...
rsData.Fields("HomePhone").Value = HomePhone
rsData.Fields("Email").Value = Email
rsData.Fields("Website").Value = Website
rsData.Fields("PhysicalAddress").Value = PhysicalAddress & " "
rsData.Fields("PhysicalCity").Value = PhysicalCity & " "
rsData.Fields("PhysicalState").Value = PhysicalState & " "

rsData.Update

' Close cnn and recordset
rsData.Close
Set rsData = Nothing
cnnData.Close
Set cnnData = Nothing

End Function

|||any ideas?|||Do you exceed the max possible characters eventually ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

No. Actually the address field doesn't need to be that large, it will only have 3-4 lines of an address. But I can't work out why this error is happenning?

|||any suggestions|||

Hi All,

Is there a different group that might be better to help with this question above?

I'm really keen to know what's the cause of this error and how to correct.

|||Moved to Data Access :-)|||can anyone help?|||any ideas?|||

What OLEDB provider are you using?

- Waseem

|||

i have this same problem,

program written in visual basic run properly on access database, but on sql server 2k sp 4 causes errors with

multi step error.

i change provider from mdac to sql native client and now program is running great

and this is only one opportunity to get your program running (i think - updating mdac doesn't help me)

best

|||

mrowek wrote:

i change provider from mdac to sql native client and now program is running great

and this is only one opportunity to get your program running (i think - updating mdac doesn't help me)

yeah, i just check code in vb with SQLOLEDB.1 provider

when it's saves strings to db it generate error

i put trim(cstr(blah blah)) and now it's write correctly to db without multi step error

[; check code and put this same

it should help

|||

My program occurs this error at the line below:

rs.delete <- occurs "multi-step multi step OLE DB error ......."

I spent two days for finding the resolve way....

then.....I insert "rs.AbsolutePosition = rs.AbsolutePosition" before "rs.delete", like:

.....

rs.AbsolutePosition = rs.AbsolutePosition

rs.delete

rs.update

......

and the error is gone............(maybe this is the most strange thing I have ever seen....@.@.)