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

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.aspxArthur My Blog

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

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 recordsArthur My Blog

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.

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

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/

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

This link may help you http://sqlserverrider.wordpress.com/2011/10/27/in-memory-data-processing-ssis/Thanks Ayyappan Thangaraj, http://SQLServerRider.wordpress.com

This link may help you http://sqlserverrider.wordpress.com/2011/10/27/in-memory-data-processing-ssis/Thanks Ayyappan Thangaraj, http://SQLServerRider.wordpress.com

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