manipulating excel using ssis - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

manipulating excel using ssis

Author

Topic

sarahmfr
Posting Yak Master

214 Posts

Posted - 2011-11-17 : 00:01:01

How to run an excel macro through ssis
If I have an excel spreadsheet book1.xls
I want to split it to various sheets
column1 column2 in sheet1
column1 column3 in sheet2
column1 column4 in sheet 3
etc.
Is there a way to do it in ssis having xls as a source and outpupt exel as a destination

sarah

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-17 : 01:00:53

you dont have to use macro for splitting. you can do it while generating excel inside ssis itself

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sarahmfr
Posting Yak Master

214 Posts

Posted - 2011-11-18 : 00:46:17

How can I do that?
----------------
Is there a way to know how many columns with data exist in an excel spreadsheet?
i.e.
If I have an excel spreadsheet first column is student number and the following columns are quizzes
the number and name of quizzes can vary from one section to another.
Thanks

sarah

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-18 : 03:13:18

quote:
Originally posted by sarahmfr

How can I do that?
----------------
Is there a way to know how many columns with data exist in an excel spreadsheet?
i.e.
If I have an excel spreadsheet first column is student number and the following columns are quizzes
the number and name of quizzes can vary from one section to another.
Thanks

sarah

do you mean your excel columns are not static?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sarahmfr
Posting Yak Master

214 Posts

Posted - 2011-11-18 : 18:06:24

yes
in other words what i am trying to achieve is
i have an excel spreadsheet that has studentid, and a list of columns each represents the student's grade in a quiz
each section provide us with such a sheet and every section has different number and names of quizzes
i would like to use ssis to import from those excel sheets to a table
studentid quizname sectionid score

I tried to use unpivoting in ssis and then import the result to a table but the issue with pivoting that it depends on the column names and number
appreciate your help

sarah

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-19 : 02:11:24

i think in your scenario it might be best to pull the rows from excel to a table by using OPENROWSET and then apply dynamic UNPIVOT to get the columns onto rows and then getting it onto your table. so ssis package will look like

Execute SQL Task -> check if intermediate tables (unpivotted and other) exists and if yes drop it
Execute SQL Task-> SELECT * INTO intermediatetable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=excelpathhere', [Sheetname$])
if you want you can pass excel path and sheetname values through variables in which case you need to use sql source type as variable
Add a string variable to package to store the column list of intermediate table and use query on INFORMATION_SCHEMA.COLUMNS to populate it with all column names except studentid
Execute SQL Task-> unpivot query based on column list generated in above variable. you can use sourcetype as variable and create another variable to hold query with column list appended using already created column list variable and put it in new unpivotted intermediate table
Data flow task-> simple one to one transfer from unpivotted intermediate table to your final table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sarahmfr
Posting Yak Master

214 Posts

Posted - 2011-11-20 : 14:11:52

Thanks so much.
You are the best.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-20 : 23:39:54

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources