April 7, 2009 at 2:09 pm
So I might have a chart filled out by an end user like so:
ID.....1/10/2008...1/11/2008...1/12/2008
1......10..............1...............0
55.....10008.........3...............15
Only you might see 500 ID's (rows) and normally about 30 dates (a month). Those dots are just there to preserve formatting. These are different cells in Excel.
I'm transforming it like so:
Date.........ID............Count
1/10/2008..1.............10
1/10/2008..55...........10008
etc.
Problem 1.
SSIS won't allow a date to be a column header. I have to add brackets around every date by hand so that SSIS will accept the date as part of the header. Might be able to overcome this by using formulas in Excel, but still it's really annoying and repeditive.
Problem 2.
Unpivot Transformation seems to require the hardcoded column value to transpose. This means if I'm importing 5,000 sheets each with 30 dates on it, you need to create 5,000 Unpivots and check all the boxes even though logically it's the same task.
Solution
I'm looking for a way I can set up SSIS that I can just change the Import Excel Source and run the import without having to reconfigure all the column headers. Maybe even looping through tabs in Excel (I know how to loop through files in a directory)
I've been using SSIS for a few days. I can do normal imports fine and I even have this UnPivot import working statically (for a specific tab). It imports, unpivots, takes the brackets off the date, converts the data types and then exports to the SQL Server.
So if there is just a good resource that I can use (online or book) to learn the sort of techniques I'm looking for, then I'm fine with that or if anyone has specific advice.
Thanks very much in advance!
April 7, 2009 at 9:02 pm
So is ID the PK in your destination table?
It is possible to do the UNPIVOT in T-SQL - but you would have to do the import of the data 'as is' to a staging table first. This would save the hardcoding issues that you are facing at the moment.
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
April 7, 2009 at 10:17 pm
I guess you could say that. Technically it's an ID that references another table. Like a PartId or EmployeeId.
TSQL I'm fine with. But I have to exhaust my SSIS resources first -- company push to SSIS over TSQL for efficiency. I'm still learning SSIS so I'm holding my opinion while I'm trying to find all of it's limitations and most importantly where it will help save time/effort.
So doing the Unpivot in TSQL is fine with me, just select *, unpivot and remove brackets, then pass the staging table over to finish the import.
Manually linking every column from a table by hand every time you want to run it does not seem like less time/effort in this odd case.
A MS employee mentioned on their forums the option of writing an application that will create the XML for the SSIS package by reading the source. This seems like it would be worth the effort in some cases, but not mine.
SSIS Unpivot seems like it would be useful for importing from a table with static column names, but not something changing every import.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply