May 4, 2009 at 3:04 am
Hi,
I'm trying to import an excel sheet data to Sql Server 2005 using SSIS. In excel sheet, there are multiple values in 1 single cell like 3 values in 1 cell and I have to create 3 rows in the database for each corresponding value. For example the Operating system column have 3-4 values in 1 cell, "Windows Vista Business - 32 bit
Windows Vista Ultimate - 32 bit
Windows Vista Business - 64 bit
Windows Vista Ultimate - 64 bit"
i have to create 4 rows in the database for each value. Should I use conditional split here? Any help will be appreciated.
Thanks
May 4, 2009 at 5:29 am
That sounds a bit nasty - I think you might end up having to do that work in a Script Component, where you can certainly generate multiple output rows for a single input row...
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 4, 2009 at 6:25 am
check for if the multiple values are seperated by space or carraige retrun(Enter). If they are seperated by space then its a hard time, may be use lookup values to create new rows. If they are seperated by carraige return(new line) then its bit easy with some script task to split and use to the output.
May 4, 2009 at 11:23 pm
I have to import the excel sheet data into multiple tables in the database. For example the column name Region goes in the Region table and the Region Id for that entry goes in different table. I have to check each and every excel cell value to make sure the value does not exist in the region table, If the region value already exists in the Region table then I have to look for Region ID. So to loop around each and every single row which Data flow task should I use?
May 4, 2009 at 11:46 pm
So the regions on your spreadsheet may or may not match existing regions in your region table.
If they do, pick up the region ID
If they don't, create the region and then pick up the region ID
Is that correct?
Two ways I can think of doing this:
1) Get a list of all the regions in the Excel file and then use this in a preliminary dataflow task to add the missing regions to the region table. Then use a lookup transformation to access the ID field in the next dataflow task that actually does the main import.
2) Do the import, putting region into a temp field in the database. Then run SQL to INSERT missing regions to the regions table (as 1) and then run a query to populate region ID, in the knowledge that all your regions now have IDs.
IMO, (2) is faster and more efficient, but then again, I almost always use T-SQL if I have the option.
Phil
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 5, 2009 at 11:22 pm
How should I write the code in the script component? I mean how can i refer to the columns in the code?
May 6, 2009 at 12:05 am
There are loads of examples on the Internet. This one is nice and detailed and will help you get started:
http://www.codebeach.com/tutorials/sql-server-integration-services-your-first-script-component.asp
This one generates multiple output rows from a single input:
Phil
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply