Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Recordset Destination to SQL table? Expand / Collapse
Author
Message
Posted Thursday, August 16, 2012 9:40 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 25, 2014 9:58 AM
Points: 92, Visits: 679
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



Post #1346073
Posted Thursday, August 16, 2012 10:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:23 AM
Points: 5,074, Visits: 11,852
david.ostrander (8/16/2012)
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



Never used it. But why not just multicast from your Excel source.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1346100
Posted Thursday, August 16, 2012 10:35 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 25, 2014 9:58 AM
Points: 92, Visits: 679
Never used it. But why not just multicast from your Excel source.


That was my first approcah but then when writing the SQL command in my OLE DB Destination I didn't know what to put the FROM

SELECT DISTINCT Value1, Value 2, Value 3, Value 4
FROM ???

Regards,
Post #1346115
Posted Thursday, August 16, 2012 12:29 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 6,257, Visits: 7,444
david.ostrander (8/16/2012)

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.

As Phil mentioned, a multi-cast is your best bet here. However, I'm not sure I understand your followup statement. Your OLEDB destination should be aimed at a table (or updateable view), not a SQL command. Then you map your inbound columns to where you want them to go, for both of them.

As to using the Recordset Destination, that's an in memory object you would need to use a for-each loop for in the control flow. It's best avoided unless you're looking to internally loop on a series of items internally in SSIS, which happens occassionally, but it does mean 1 by 1 inserts to the targets.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1346180
Posted Thursday, August 16, 2012 2:03 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 25, 2014 9:58 AM
Points: 92, Visits: 679
I would agree and would like to use the Multicast. I attached a image of what my Data Flow looks like at the moment.

How can I upload my excel data to a temp table from with SSIS? Or do I even have to do that?

Regards,
D



  Post Attachments 
8-16-2012 SSIS.jpg (12 views, 28.86 KB)
Post #1346232
Posted Thursday, August 16, 2012 2:35 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 6,257, Visits: 7,444
david.ostrander (8/16/2012)
I would agree and would like to use the Multicast. I attached a image of what my Data Flow looks like at the moment.

How can I upload my excel data to a temp table from with SSIS? Or do I even have to do that?

Regards,
D



Well, the structure looks right, but those OLEDB objects are complaining about something. You shouldn't need a temp table of any kind here, really. If you do need to do other things with the data afterwards, what you'll want is a staging table (a hard table dedicated to temporary storage for only this process) and not a #tmp, just so we're both speaking apples to apples.

If this is just a straight load though, no data manipulation and/or updating of previous information, you should be able to directly insert into both target tables, however. Can you give us more of a description of your end to end process?



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1346243
Posted Friday, August 17, 2012 1:55 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 25, 2014 9:58 AM
Points: 92, Visits: 679
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 one excel file and split (or break) that out and insert the needed data into either Table A or Table B.

Regards,
D
Post #1346738
Posted Monday, August 20, 2012 1:30 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 6,257, Visits: 7,444
That shouldn't be too hard, actually, you merely only map the columns you need in the OLEDB destination to the table(s) in question. However, of a more critical issue, how do you determine which rows associate to each other between the tables, and does that key come from Excel? If it's a SQL generated surrogate key, this is going to require a lot more work.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1347437
Posted Monday, August 20, 2012 1:36 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:23 AM
Points: 5,074, Visits: 11,852
Evil Kraig F (8/20/2012)
That shouldn't be too hard, actually, you merely only map the columns you need in the OLEDB destination to the table(s) in question. However, of a more critical issue, how do you determine which rows associate to each other between the tables, and does that key come from Excel? If it's a SQL generated surrogate key, this is going to require a lot more work.


Sounds like a multicast to two tables should do the job.

As for the association bit, I agree. To give better advice, we need to understand how the tables are linked. It may be wise to add a surrogate key to the spreadsheet and use that as an aid ...



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1347441
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse