August 16, 2012 at 9:40 am
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
August 16, 2012 at 10:09 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 16, 2012 at 10:35 am
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,
August 16, 2012 at 12:29 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 16, 2012 at 2:03 pm
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
August 16, 2012 at 2:35 pm
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?
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 17, 2012 at 1:55 pm
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
August 20, 2012 at 1:30 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 20, 2012 at 1:36 pm
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 ...
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply