May 2, 2012 at 4:19 pm
Hello Folks,
I have an excel file placed in a shared folder. The Columns of this file are variable(may be 2 or 3 or 5). I need an idea how to develop an SSIS package to write the data to a table. Thx
May 2, 2012 at 4:21 pm
You've got variable meta-data. You can't do this directly in SSIS, it wants hardened metadata that doesn't change between iterations. There's some third party connections that can help with this however.
There are workarounds to the idea (dumping them in as a single column and then separating yourself in procs based on metadata), but they're all workarounds.
Keyword search google for variable metadata, changing number of columns, and same file different datatypes. That should get you started in finding your workarounds and you can decide which one is the best application to your environment.
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
May 2, 2012 at 4:27 pm
Exactly!! That's what I've done for Flat File and CSV File, putting all columns data under one column and split using a script component. However, I do not have an idea as to how to apply the same logic to Excel File.
May 2, 2012 at 5:02 pm
sqlbi.vvamsi (5/2/2012)
Exactly!! That's what I've done for Flat File and CSV File, putting all columns data under one column and split using a script component. However, I do not have an idea as to how to apply the same logic to Excel File.
Best way I've found? ExcelVBA to SaveAs the file as a CSV. Fighting with excel odbc components is a hazard to my sobriety at best.
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
May 2, 2012 at 8:09 pm
Evil Kraig F (5/2/2012)
sqlbi.vvamsi (5/2/2012)
Exactly!! That's what I've done for Flat File and CSV File, putting all columns data under one column and split using a script component. However, I do not have an idea as to how to apply the same logic to Excel File.Best way I've found? ExcelVBA to SaveAs the file as a CSV. Fighting with excel odbc components is a hazard to my sobriety at best.
Would that count as Office Automation? I am sure you know where I am going with this one :Whistling:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 2, 2012 at 11:09 pm
Well, I cannot save it into other file formats. I know, if I have fixed column names(Headers) in excel file then that would work. My file has no columns(Header). That's the challenge 🙂
May 2, 2012 at 11:25 pm
With that limitation, writing an .exe or CLR procedure that will use Excel Interop seems to be the only really flexible solution.
I do NOT like Excel Interop, but within the last five years I was forced to use it several times. Until there is a decent SQLS interface (such as, "import this worksheet as a temp table and let me iterate through columns"), we are stuck with what is available.
May 3, 2012 at 6:16 am
Using the Excel Object Model is what I would do. I suspect that's what Craig (mentioned VBA, don't think he meant true Office Automation) and Revenant (Excel interop) are saying as well. It goes by too many names. If you're in SSIS you can do this work in a Script Component within a Data Flow and just add rows to the SSIS pipeline manually as you iterate through the Worksheet and read the rows and cells manually.
The solution requires you install Excel and the Primary Interop Assemblies on your server running SSIS though, which is forbidden in many corporate environments.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 3, 2012 at 4:05 pm
opc.three (5/3/2012)
Using the Excel Object Model is what I would do. I suspect that's what Craig (mentioned VBA, don't think he meant true Office Automation) and Revenant (Excel interop) are saying as well. It goes by too many names. If you're in SSIS you can do this work in a Script Component within a Data Flow and just add rows to the SSIS pipeline manually as you iterate through the Worksheet and read the rows and cells manually.The solution requires you install Excel and the Primary Interop Assemblies on your server running SSIS though, which is forbidden in many corporate environments.
Pretty much. I used to code using the Excel Object Model so that's what I default to. I'd personally actually setup some Excel code to simply open the excel file, re-save it as a CSV, and then load the converted file, ignoring the fact that it was a csv, into a staging table that I could then perform metadata tricks against.
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
May 4, 2012 at 3:44 am
if you can change the spreadsheet, add a worksheet with 5 columns and formulae in them to look up the other sheet and fill in the other columns with dummy data
or if you can't, create a sister spreadseheet to do that job
May 4, 2012 at 12:00 pm
Another option that hasn't been mentioned is using PowerShell: you can use PS to effectively treat Excel sheets as tables and either shred them in to a delimited file for consumption in your SSIS package, or bulk load the data into staging tables. Once there (by either method), you can handle your data within the data flow.
May 11, 2012 at 9:29 am
Thanks folks. I used Excel Interop dll reference and read each excel cell to get this done.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply