Post reply

Execute Task In SSIS

  • November 24, 2009 at 8:38 pm

    #156439

    Hi Friends

    I used Execute SQL Task to call the Stored Procedure.

    Can anybody helps me that how to get the results from stored procedure in excel format.

    thanks

  • November 24, 2009 at 9:05 pm

    #1083790

    I am not aware of the straight line answer.

    This is only a work around / suggestion to you.

    Can you use the stored procedures to generate the data and store in a staging tables and then in Dataflow task use those tables (OLE DB Source) and destination as excel file destinations.

    Hope someone will give another solution which would be more easier and better one.

    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • November 24, 2009 at 9:11 pm

    #1083792

    Thanks ,it gives me some ideas.

  • November 24, 2009 at 9:18 pm

    #1083793

    anitha.cherukuri (11/24/2009)

    Thanks ,it gives me some ideas.

    Start to work on it and lets hope someone would come up with their brilliant idea. 🙂

    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • January 26, 2010 at 1:51 am

    #1108503

    why can't you use the SP as the SQL of your data flow source and use an Excel destination?

  • January 26, 2010 at 4:47 am

    #1108561

    Hey Anitha,

    you can achieve this by giving the exec sp query in the oledb source.

    Now the question is, are you passing any parameters ?

    If yes, the parameters should be defined as user variable with scope at the package level.

    Let me make it very clear...

    Go to the DataFlow task- select oledb source from the toolbox - In the oledb connection manager select the desired connection string - in the data access mode select sql command- in the sql command text give ur exec sp query.

    Now on the right hand side you can see a tab as Parameters, click on it and map the variable to parameters in the sql statement.

    Remember that the parameter name should be same as the actual parameter name in the sp.

    Click on OK.

    Now select a excel destination from the toolbox. Give the connection string and connect the oledb output to the excel destination. Map the columns in the excel destination to the oledb output columns.

    I hope this solution works fine for you.......:-)

    With Regards,
    Anu..;-):hehe:

  • January 27, 2010 at 11:28 pm

    #1109758

    Thanks for your time,i did as you expanined..it worked well........

    Regards,

    Anitha

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

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