How to create excel destination dynamically in SSIS 2008?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Business Intelligence » How to create excel destination dynamically...

How to create excel destination dynamically in SSIS 2008?

Rate Topic

Display Mode

Topic Options

Author

Message

only4mithunc

Posted Friday, September 07, 2012 12:25 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 9:40 AM
Points: 358, Visits: 2,775

Hi friends,

I’m facing a problem with my SSIS package, I want to export the data into an excel 2003 using SSIS 2008.

Here the output columns are not fixed i.e., the no. of output columns will vary and so we need to create excel dynamically in run time and insert the data.

Option tried:

1. Add Microsoft.Office.Interop.Excel DLL in the script task.
2. Create the excel object from that and insert the data.

Issue I’m getting is:

I am able to add the DLL in the script task but when I close and reopen the script the reference is getting deleted.

I’m using Visual Studio 2008, Windows XP SP 3

Can some one provide any solution to solve this?

Thanks & Regards,
MC

Post #1355773

Brandie Tarvin

Posted Friday, September 07, 2012 4:34 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:57 AM
Points: 6,724, Visits: 5,796

The only way to do this (and it's not a good way) is to create an expression that populates a T-SQL task that creates the Excel spreadsheet on the fly.

The T-SQL would look something like this:

CREATE TABLE `MyReport`(
`MyId` LongText,
`Name` LongText, 
`BeginDate` LongText, 
`EndDate` LongText, 
`OrderNumber` LongText)
GO

I don't have any samples on my current stuff that includes mapping to integars, etc., but I image you'd use Number instead of LongText.

As far as how to make the connection manager dynamic, you'd use an Excel connection manager with an expression for the file location and name. Make sure to turn Delay Validation to True on all of these.

Brandie Tarvin, MCITP Database Administrator, MCDBA, MCSA

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Post #1355885

only4mithunc

Posted Friday, September 07, 2012 5:54 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 9:40 AM
Points: 358, Visits: 2,775

Thanks Brandie,

But here in our case we are not sure of the out put column names. Based on the parameters what we pass to the SP, the out put columns will be different. So in that case how do we approach this?

For example; I have 3 parameters say

@p1 -- this can have values Month or Qtr or Year
@p2 -- this can have values Income

So based on the parameters we pass to the SP ( which is used in the OLEDB Source ) the output columns can be either Income_Month OR Income_Qtr OR Income_Year based on the value we passed for @p1

Thanks & Regards,
MC

Post #1355927

Brandie Tarvin

Posted Friday, September 07, 2012 6:01 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:57 AM
Points: 6,724, Visits: 5,796

You can use an expression to calculate based on a value of a variable. The variable will be what you alter based on what's coming in.

Because I don't know anything about your specific business model, rules, file formats, etc, I can't give you more details. Do some research on expressions and variables, and check some of the blogs here on the forums (http://www.sqlservercentral.com/blogs/) to see if anyone has done something close to what you have in mind. Then adapt and experiment.

If that doesn't work, you may have to hire a consultant. Pragmatic Works (based in Jacksonville, FL but willing to travel) includes a stable of some fantastically talented BI people. If they can't accomplish a task like this, I doubt anyone can.

Brandie Tarvin, MCITP Database Administrator, MCDBA, MCSA

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Post #1355933

opc.three

Posted Thursday, September 20, 2012 4:07 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:24 PM
Points: 6,826, Visits: 11,950

only4mithunc (9/7/2012)

Thanks Brandie,

But here in our case we are not sure of the out put column names. Based on the parameters what we pass to the SP, the out put columns will be different.

I can smell the stored procedure from here Smile

Ideally a stored procedure should have a defined output interface that does not depend on the input parameters. That is neither here nor there is you do not control the code in that stored procedure and just need to work with it as is. Just know that it is less than ideal and is something to avoid in your own new development.

So in that case how do we approach this?

How much data are we talking about? You need a way to derive the metadata so you can build your Excel Worksheets. There are likely many ways to do this but if there is not more data than you can comfortably hold in memory you could capture the results of the stored procedure into a variable of type Object, i.e. a DataSet, then inspect the DataSet in a ScriptTask to figure the columns names and data types. From that you can uset he Excel object to build your Workbook and write your data.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1362367

sumit.joshij

Posted Monday, September 24, 2012 12:04 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 09, 2013 6:06 AM
Points: 47, Visits: 266

Hope this article will help you.
http://msbi2012.blogspot.in/2012/09/expression.html

Post #1363664

« Prev Topic | Next Topic »

Permissions