How to execute a SSIS package from C#.net code.

Question

5.00/5 (1 vote)

See more:

Hi,

I have a requirement to load an excel file to database from C#.net code using SSIS.

I have already created a SSIS package and able to load the excel data to database successfully from SQL Server Agent. But when tring to execute the same package from C#.net code it is giving me below error :

Error Description : The runtime connection manager with the ID "{CE20AECF-AA9A-4B20-AA98-8BE230DE4327}" cannot be found. Verify that the connection manager collection has a connection manager with that ID. HPDNAContainerPOC Destination failed validation and returned error code 0xC020801B. One or more component failed validation. There were errors during task validation

It is saying the runtime connection manager with above id is not found and advise to check.

I have checked the ID : CE20AECF-AA9A-4B20-AA98-8BE230DE4327 and found available in the SSIS DB connection XML. Below is the connection XML code -
<<pre lang="xml">?xml version="1.0"?>
<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts"
DTS:ObjectName="DNA"
DTS:DTSID="{CE20AECF-AA9A-4B20-AA98-8BE230DE4327}"
DTS:CreationName="OLEDB">
<DTS:ObjectData>
<DTS:ConnectionManager
DTS:ConnectionString="Data Source=SQL2012;User ID=damcohpdna;Initial Catalog=TEST;Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=False;">
<DTS:Password
DTS:Name="Password"
Sensitive="1">AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAC39bMYpMd02FtpwNDd+TQwAAAAACAAAAAAADZgAAwAAAABAAAAB1vxvTpzwwAheAS7067BPmAAAAAASAAACgAAAAEAAAAK+h1rgGmfbPCvkHYQigPy54AAAAzUPWXOuxOhShcWao47ZNHPv3lM8DY+P6QO1iGHa4saIb80RIth5LFm7m41B5SKqPib89/adZPeZJKf/NTbj7KWH/zbRVBXpMp3Mjq/N8rgOadsQXRl4NY/lTgqfPnPJKIV/y7etyJv12qxGRmx30iBtlNLXRnoRkFAAAAJfpzffaZJl1Yk2t0O0UfXMbJqei</DTS:Password>
</DTS:ConnectionManager>
</DTS:ObjectData>
</DTS:ConnectionManager
>

Below is the C#.net Code
------------------------
protected void ExecuteSSISPackage()
{
try
{
string pkgLocation;
Microsoft.SqlServer.Dts.Runtime.Package pkg;
Microsoft.SqlServer.Dts.Runtime.Application app;
Microsoft.SqlServer.Dts.Runtime.DTSExecResult pkgResults;

pkgLocation = @"D:\Upload\Package.dtsx";
app = new Application();
pkg = app.LoadPackage(pkgLocation, null);
pkgResults = pkg.Execute();

if (pkgResults == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure)
{
string err = "";
foreach (Microsoft.SqlServer.Dts.Runtime.DtsError local_DtsError in pkg.Errors)
{
string error = local_DtsError.Description.ToString();
err = err + error;
}
Response.Write(err);
}
if (pkgResults == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success)
{
string message = "Package Executed Successfully....";
Response.Write(message);
}
}
catch(Exception exP)
{
string exp = exP.Message.ToString();
}
}

Can someone please help me to come out of this error and any reference code ?

Appreciate your quick response.

Regards, Surya

Posted 16-Jun-15 0:26am

Surya(10980329)

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kuthuparakkal · Answer 1 · 2015-06-17T14:19:00

Hello -

The problem is related to your package protection. The connection string requires a password and the package protection is set to "EncryptSensitiveWithUserKey" - the default setting. The only person who can decrypt & acquire the connection at run-time is you/whoever created the package.

You need to change it to "EncryptSensitiveWithPassword" OR "EncryptAllWithPassword" and set a password for the package. Say "MypkgPassword"
Ref: Package Protection Level[^]

C#

Microsoft.SqlServer.Dts.Runtime.Application app;
Microsoft.SqlServer.Dts.Runtime.Package pkg;
app = new Microsoft.SqlServer.Dts.Runtime.Application();
app.PackagePassword = "MypkgPassword";
Package pkg = app.LoadPackage(pkgLocation, null);
pkgResults = pkg.Execute();
...
...

Afteryou set the password, save it, close and reopen it. Edit the connection that uses password and supply password and test connection. Then save and use it.

Excel driver does not work in 63bit environment, you need to set it to 32 bit in SQL Setver Agent
Ref: http://sqlblog.com/blogs/john_paul_cook/archive/2010/03/24/running-32-bit-ssis-in-a-64-bit-environment.aspx[^]

Does it have anyother connection with Windows Authentication ?
If yes Create a Proxy (you need to create credentials first) and this proxy should be used to execute the package.
Ref: http://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/[^]

Thanks,

Kuthuparakkal