Recordset Destination to SQL table?

Question

Hello -

I currently have an SSIS package being
built where I want to upload rows from an excel document into a Recordset
Destination in SSIS. What I'm having trouble understanding is once the excel
data is uploaded into the Recordset Destination how do I get that out and into
two separate tables in my database with the data I want to query.

If
anyone can please provide me some direction I can research more from there. I'm
a visual person if that helps :-)

Thank you in adavance -

David

Answer 1

Hello David,

You need to SHRED the recodset. Then you may use a conditional split coming out of it, and/or a Multicast component.

See how we shred it here: http://www.sqlis.com/post/Shredding-a-Recordset.aspx

---

Arthur My Blog

Answer 2

How can I do that when my source connection is to an Excel document not a table in SQL?

Regards,

David

Answer 3

You said David that you can populate the recodset/object variable with the records from the Excel file, so here you go, shred it to further distribute the records

---

Arthur My Blog

Answer 4

I believe you understand that the RecordSet destination component is not a persisted destination for the data you ar trying to process from the Excel source. It is an In-Memory object that is available only while the SSIS package is still running. It may be easier for you to Stage the excel data into a truncate-and-load intermediate physical table. That way, you can essentially query it as a SQL Table source and process the data in it based on the business rules you're trying to implement. This will also avoid excess usage of your server memory if that data in excel is too large. Also, you won;t need to shred the "Object" that stores this data in memory.

To do so, all you need to do is create a table in the database with similar schema as the excel (not necessarily one-to-one) and use a OLEDB Destination instead of a recordset destination.

If you really want to use the recordset destination only, share some more details around what you're trying to do once you read the data from the excel source and you'll get some specific help around that :).

Hope this helps!

Cheers!

Muqadder.

Answer 5

Muqadder -

I think I will try the Staging way. I was able to create that and get my data loaded. Below is a snap shot from SSIS. You can see that I'm loading that staging table from Excel.

We have one Excel file with data that needs to be uploaded every month. In my database there are two separate tables (Table A and TAble B) that I need to split that data from that one Excel file and load into the correct table in SQL.

For Example:

From Excel my columons are:
Version
Project_Code
Project_Name
Contract_Type
Hosting_CV,
OOP_Budget,
Non_Media_Pass,
Media_Pass
SEM_Budget
Business_Unit
Level
Billable_Hours
Non_Billable_Hours
Employee_Hours
Hourly_Rate,
Cost_Rate,
Rack_Rate


But I only need the data from the coloumns below to be inserted into Table A
Version
Project_Code
Project_Name
Contract_Type
Hosting_CV,
OOP_Budget,
Non_Media_Pass,
Media_Pass
SEM_Budget

And these need to go into Table B

             Business_Unit
Level
Billable_Hours
Non_Billable_Hours
Employee_Hours
Hourly_Rate,
Cost_Rate,
Rack_Rate

One of the things I'm having a time working out is how to I get the data from that staging table and split (or break) that out and insert the needed data into either Table A  or Table B?

Regards,

D

Answer 6

As you have already extracted data from Excel and loaded into Staging table, Now drag second data flow.

USe the OLE DB Source , write Select statement or Select Staging table from drop down.

Then use the conditional split if you want to split the records into two tables depending upon some value in column.

If you want to load all of the records into both tables then use multicast and it will provide you multiple outputs with same number of records.Finally connect destination/destinations.

please explain more details about your requirement so you can get better help.

Thank you

---

http://sqlage.blogspot.com/

Answer 7

Hi Aamir

With the conditional split idea do you have any links or documentation that will show me how to config this?

I am so new to using this area of SSIS I need the guidance of how to config it and then I catch on to what is happening.

Regards,

D

Answer 8

This link may help you

http://sqlserverrider.wordpress.com/2011/10/27/in-memory-data-processing-ssis/

---

Thanks Ayyappan Thangaraj, http://SQLServerRider.wordpress.com

Answer 9

Thank you to everyone all this information helped me get up and running right.

Regards,

David