How to store a sqldatareader returned from from script code to database table?

Question

How to store a sqldatareader returned from from script code to database table?

MissyDaisy

Ten Centuries

Points: 1164
More actions
April 7, 2012 at 8:49 pm

#146724

In Database1 on server A, I have a tableA with CustomerIDs. null name, null address
In Database2 on server B, I have a tableX with CustomerID, name, address etc…
There is no linked server between them.
I need to get a result set using the list of CustomerIds from database1 as input to query database2 to fill in the name, address etc.. and store that result set in database1.
Sample input:
In database1:
TableA CustomerID
1111
2222
3333
4444
In database 2:
Table X CustomerID, Name, Address
1111 John Doe 123 NW 17
1234 Ginger Smith 432 Park Blvd
2222 Sue Parker 5400 Arapaho Rd
9876 Jess Burns 1 Ti St
Desired output on TableA in database1
1111 John Doe 123 NW 17
2222 Sue Parker 5400 Arapaho Rd
3333 null null
4444 null null
Note that the input customer list is a selected result set that can be anywhere from a few hundreds to hundred thousands.
Here is my current SSIS implementation
-A User variable of type Object called User::ObjCustomerList
- Data Flow task ( OLE DB source connected to database 1 and a recordset destination that gives me an in-memory table and is mapped to User::ObjCustomerList )
-A script component that takes User::ObjCustomerList as readonly variable, connects to database2 to execute the sproc on database2 taking in User::ObjCustomerList as a table value parameter.
The script does an ExecuteReader and the result set is a sqldatareader.
Up to this point, it works.
BUT I don’t know what to do next.I would like to just store that sqldatareader result set in a work table call WorkTableA in database1, then do an execute sql task to do a join update on TableA and WorkTableA .
Is that possible or do I have to process one row at a time?
Thank you for your help in advance.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

MissyDaisy Ten Centuries Points: 1164 More actions · Answer 1

Update - I was able with script to convert the sqldatareader to a datatable and save that datatable into a user variable of type object.

Thanks!

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 2

What you must understand here is that this is not SSIS. Instead you could have chosen tasks such as Merge Join etc

Raunak J

MissyDaisy Ten Centuries Points: 1164 More actions · Answer 3

I totally agreed with your point. I'd love to do the same thing with SSIS tasks but do not know how to pass a table value parameter in SSIS so I went with script code.

Any suggestions for this scenario with SSIS tasks only will be greatly appreciated.

Thanks

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 4

I am thinking of a data flow that goes like this:

1. OLE DB Source from ServerA.Database1 selects customerIDs where name/address is null

2. OLE DB Destination to ServerB.tempdb table with customerID

3. OLE DB Source ServerB.tempdb joined to ServerB.Database2 on customerID to return customerID, name, address, etc.

4. OLE DB Destination to ServerA.Database1 staging table for later use in updating ServerA.Database1

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

MissyDaisy Ten Centuries Points: 1164 More actions · Answer 5

Your suggestion will work optimally. Unfortunately, I can't implement it as the service account does not have write permissions on Database2 tempdb and never will.

So I have to do it the inefficient way: pass a table value parm, get back and result set and do something with that result set.

Thanks!

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 6

MissyDaisy (4/12/2012)
Your suggestion will work optimally. Unfortunately, I can't implement it as the service account does not have write permissions on Database2 tempdb and never will.
So I have to do it the inefficient way: pass a table value parm, get back and result set and do something with that result set.
Thanks!

Explicit permissions are needed to create permanent tables in tempdb, but it seems unlikely that anyone would block a login from creating a temporary table (i.e. a hash-table like #someTable) as that can have unexpected adverse effects on the logins ability to do standard query operations.

In SSIS you can force multiple tasks to use the same ole db connection by setting the RetainSameConnection property on the OLE DB Connection. Theoretically you could create a temp table in an Execute SQL Task and then leverage that same temp table in a Data Flow Task. I tried this out in SSIS 2012 against SQL 2012 and but was having trouble because SSIS wants to retrieve the table's metadata to build the pipeline...but seeing as the #temp table will not exist at design time it complains. There may be a way to defer the metadata resolution but I don't have time to dig into that at the moment.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato