Wednesday, March 28, 2012

SSIS package to export to a new Excel file

Problem

How to export a large number of columns into non existing excel file.

Developing an SSIS package to export to an existing Excel file is easy when the Excel file exists with column headings on the excel sheet. If you have an export that will be scheduled on a daily basis of 50+ columns to a new excel file, then you have some careful typing/copy-pasting to do to create an excel template file. Instead.

Solution

What I often do is to generate an SSIS package with the Sql Server Management Studio export wizard. The interesting thing about this wizard is that it can generate excel files on the fly.After saving the package thus generated by SSMS, you can open the SSIS package in Visual Studio to inspect what it is doing.

image

The package has a dataflow task, but it is preceded by a SQL task. The trick lies in the SQL statement that generates the columns for the Excel sheet. When we edit the Preparation SQL Task 1, then we see a SQL statement that creates a table called ‘Sheet’.

CREATE TABLE `Query` (
`PlantKey` VarChar(8),


‘OrderValue’ Decimal(18,5)
)
GO



image


Conclusion


I can use the package generated by SSMS to adapt to my needs, for instance to generate a series of excel filenames in a For Each Loop Container or in a For Loop container, store theEexcel filename in a variable and put the Preparation SQL task and the Dataflow task in the container. I will still need the Excel file generated by the SSMS package to further design my Dataflow task. But once the package is deployed it will use the Excel variable name in the Excel connection.


Remark


This solution does not take into consideration the problem of Numeric values getting converted to text in the excel sheet.

6 comments:

Unknown said...

How to add a new sheet to already generated excel file through SSIS?

Unknown said...
This comment has been removed by the author.
Anonymous said...

It's ɡood for a laugh if you find it now (or to reѕell online).
These gears are manufactured with ɗifferent material and are absolսtely
durable. Youu ɑlso must take precautions when hiкiong up
and down a hill.

Feel free to surf to my site; Hiking boots For kids

Unknown said...

louis vuitton outlet, sac longchamp, ralph lauren pas cher, replica watches, louboutin outlet, louboutin shoes, christian louboutin outlet, prada outlet, ugg boots, longchamp pas cher, tiffany and co, louis vuitton, louboutin, air jordan pas cher, tory burch outlet, cheap oakley sunglasses, nike outlet, polo ralph lauren outlet, ray ban sunglasses, polo ralph lauren outlet, louis vuitton outlet, nike air max, replica watches, louis vuitton, air max, longchamp outlet, michael kors, oakley sunglasses, chanel handbags, nike free, nike roshe run, oakley sunglasses, burberry, tiffany jewelry, ray ban sunglasses, kate spade outlet, prada handbags, oakley sunglasses, nike air max, louis vuitton, louboutin pas cher, uggs on sale, ray ban sunglasses, oakley sunglasses, longchamp, jordan shoes, gucci outlet, ugg boots, nike free, longchamp outlet

Unknown said...

lancel, celine handbags, jimmy choo shoes, bottega veneta, nike roshe, asics running shoes, gucci, giuseppe zanotti, nike huarache, mcm handbags, herve leger, hollister, hollister, new balance, ray ban, p90x workout, soccer shoes, babyliss, longchamp, mac cosmetics, nike trainers, chi flat iron, mont blanc, vans, vans shoes, ghd, iphone cases, nike air max, hollister, ferragamo shoes, ralph lauren, louboutin, nike air max, beats by dre, valentino shoes, converse outlet, lululemon, north face outlet, instyler, soccer jerseys, birkin bag, insanity workout, baseball bats, north face outlet, abercrombie and fitch, timberland boots, reebok shoes, nfl jerseys, oakley, wedding dresses

Unknown said...

hollister, louis vuitton, canada goose outlet, moncler, pandora jewelry, swarovski, louis vuitton, bottes ugg, coach outlet, moncler, supra shoes, montre pas cher, moncler, ugg,uggs,uggs canada, ugg,ugg australia,ugg italia, canada goose, links of london, pandora jewelry, karen millen, doudoune canada goose, juicy couture outlet, moncler, pandora charms, marc jacobs, swarovski crystal, moncler, ugg pas cher, thomas sabo, louis vuitton, moncler outlet, moncler, canada goose, canada goose uk, canada goose outlet, ugg boots uk, juicy couture outlet, wedding dresses, moncler, canada goose, toms shoes, louis vuitton, replica watches, sac louis vuitton pas cher, pandora charms, canada goose