Post reply

How to create excel destination dynamically in SSIS 2008?

  • September 7, 2012 at 12:25 am

    #263831

    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

  • September 7, 2012 at 4:34 am

    #1534980

    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/[/url]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.

  • September 7, 2012 at 5:54 am

    #1535015

    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

  • September 7, 2012 at 6:01 am

    #1535021

    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/[/url]) 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/[/url]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.

  • September 20, 2012 at 4:07 pm

    #1540428

    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

  • September 24, 2012 at 12:04 pm

    #1541554

    Hope this article will help you.

    http://msbi2012.blogspot.in/2012/09/expression.html

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply