SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Recordset Destination to SQL table?


Recordset Destination to SQL table?

Author
Message
david.ostrander
david.ostrander
Old Hand
Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)

Group: General Forum Members
Points: 390 Visits: 692
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
Phil Parkin
Phil Parkin
SSC-Forever
SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)

Group: General Forum Members
Points: 49679 Visits: 21150
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
david.ostrander
david.ostrander
Old Hand
Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)

Group: General Forum Members
Points: 390 Visits: 692
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,
Evil Kraig F
Evil Kraig F
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19821 Visits: 7660
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
david.ostrander
david.ostrander
Old Hand
Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)

Group: General Forum Members
Points: 390 Visits: 692
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
Attachments
8-16-2012 SSIS.jpg (32 views, 28.00 KB)
Evil Kraig F
Evil Kraig F
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19821 Visits: 7660
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
david.ostrander
david.ostrander
Old Hand
Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)

Group: General Forum Members
Points: 390 Visits: 692
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
Evil Kraig F
Evil Kraig F
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19821 Visits: 7660
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
Phil Parkin
Phil Parkin
SSC-Forever
SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)

Group: General Forum Members
Points: 49679 Visits: 21150
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search