Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to create excel destination dynamically in SSIS 2008?


How to create excel destination dynamically in SSIS 2008?

Author
Message
only4mithunc
only4mithunc
Old Hand
Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)

Group: General Forum Members
Points: 396 Visits: 2803
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
Brandie Tarvin
Brandie Tarvin
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7742 Visits: 8718
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
only4mithunc
only4mithunc
Old Hand
Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)

Group: General Forum Members
Points: 396 Visits: 2803
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
Brandie Tarvin
Brandie Tarvin
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7742 Visits: 8718
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8209 Visits: 14368
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 :-) 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
sumit.joshij
sumit.joshij
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 269
Hope this article will help you.
http://msbi2012.blogspot.in/2012/09/expression.html
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search