November 29, 2011 at 4:59 am
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
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
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy