March 26, 2007 at 8:32 am
Hello,
I am working for the first time with SSIS.
I have an excel source that has the following structure:-
Col1 Col2 Col3 Col4 -> any number of these columns
Desc1 99.9 99.9 99.9
Desc2 99.9 99.9 99.9
Desc3 99.9 99.9 99.9
Desc4 99.9 99.9 99.9
Desc5 99.9 99.9 99.9
Desc6 99.9 99.9 99.9
Desc7 99.9 99.9 99.9
Desc8 99.9 99.9 99.9
Desc9 99.9 99.9 99.9
etc...
There can be any number of columns, so what I would like to do is just have the one column and then have the values as:-
Col1 Col2
Desc1 99.9
Desc1 99.9
Desc1 99.9
Desc1 99.9
Desc2 99.9
Desc2 99.9
Desc2 99.9
Desc2 99.9
Desc3 99.9
Desc3 99.9
Desc3 99.9
Desc3 99.9
Desc4 99.9
Desc4 99.9
Desc4 99.9
Desc4 99.9
Anybody any idea how I can do this?
Thanks in advance,
Rich
March 26, 2007 at 10:34 am
You are describing a normalising operation which can be achieved either through the SSIS Unpivot transform or if already imported into your SQL table, you can use the UNPIVOT clause in your SQL.
Cheers
Kindest Regards,
Frank Bazan
March 26, 2007 at 4:27 pm
I think the fact that Richard is trying to normalize a flexible number of columns into a single column makes the Unpivot transform unsuitable.
The problem with Unpivot is that it is a transformation component and has a hard-wired input. Once Unpivot is set up to normalize a particular set of columns, it will work only with this set of columns. If the columns change, you need to go back and manually update the metadata of the Unpivot transform. Unfortunately, changing the pipeline metadata in runtime is not an option either, so automated solution is out of the question unless you want to dynamically construct the entire SSIS package via scripting for every new Excel worksheet you come across.
Said that, I think that a tool like DataDefractor will be more helpful.
Best regards,
Vassil Kovatchev
March 27, 2007 at 6:33 am
Hi,
Add an import object of type excel datasource in SQL.
Pass the import to a process which starts at the first line and works with each row.
1. loop through the recordsets in an internal loop x= 1 to 3 (for each data col), within the loop working ending on End of File.
2 Set a variable in the first line which will store the value of Col1 before you enter 1 above
3 execute an insert three times, with the data being :
Col 1, Col2
Col 1, Col 3
Col 1, Col 4
Exit the loop in step 1.
4. move to the next row in the excel data source and process until EOF
5. your target data table will look like your desired results.
6. Expect bad data - Set up on error process and write to an error table the same way, using the field location (record number), date time, and a field consisting of Col1-4 as one string, if the process had a problem I would expect one of the Col1-4 values are null or different than you expected.
Lookup Extract Transform and Load, as this is what you are trying to do.
If you do not get the answer you want, keep asking. Do not give up!
jam
Joseph A. Montione-SQLServerCentral
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply