Post reply

Dot Net application which will actually trigger the SSIS package to transform the data from sql server to excel.

  • November 29, 2011 at 4:59 am

    #263755

    I want to add a functionality in one of our Dot Net application which will actually trigger the SSIS package to transform

    the data from sql server to excel.

    The records are few hundred thousands; so we need to process the data in chunks of say 70k in each worksheet of the excel file.

    Is there a way to call the the ssis package from the dot net application.

    Please let me know, if any other info is required ....

    Many Thanks

  • November 29, 2011 at 8:10 am

    #1414108

    Directly from the .NET application?

    I usually do it by having the DTEXEC command run through a stored proc so I can keep all SQL-related stuff in the database, but this website has an example of executing the thing directly in .NET. The relevant code is:

    using Microsoft.SqlServer.Dts.Runtime;

    Application app = new Application();

    Package package = null;

    //Load DTSX

    package = app.LoadPackage([Path], null);

    //Execute DTSX.

    Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = package.Execute();

    And the reference required is: Microsoft.SqlServer.ManagedDTS. NOTE: This only will work if SSIS is installed on the *same machine* that is running SSIS. See this thread for more info.

    You're probably better off using the stored proc approach. Make a stored proc, and use the following code:

    DECLARE @DTExec VARCHAR(500)

    SET @DTExec = 'dtexec /FILE "[Path]" /DECRYPT [Password] /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E'

    EXEC xp_cmdshell @DTExec

    This requires the person running to have a pretty high level of access though. There's other ways, I just haven't been able to figure out how to get them to work yet 😛

  • November 29, 2011 at 8:38 am

    #1414138

    thanks for the info... ...

    I hope the second example with dtexec and stored proc works fine.

Viewing 3 posts - 1 through 3 (of 3 total)

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