如何从C＃.net代码执行SSIS包。 [英] How to execute a SSIS package from C#.net code.
问题描述
我需要使用SSIS从C＃.net代码将excel文件加载到数据库。
我已经创建了一个SSIS包，并且能够从SQL Server代理成功地将Excel数据加载到数据库。但是当从C＃.net代码执行相同的包时，它会给我以下错误：
错误描述：运行时连接管理器无法找到ID{CE20AECF-AA9A-4B20-AA98-8BE230DE4327}。验证连接管理器集合是否具有具有该ID的连接管理器。 HPDNAContainerPOC目标验证失败，返回错误代码0xC020801B。一个或多个组件验证失败。任务验证期间出现错误
说没有找到具有上述id的运行时连接管理器并建议检查。 />
我检查过ID：CE20AECF-AA9A-4B20-AA98-8BE230DE4327，可在SSIS DB连接XML中找到。以下是连接XML代码 -
<< 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：密码
DTS：名称= 密码
敏感= 1 > AQAAANCMnd8BFdERjHoAwE / CL + sBAAAAC39bMYpMd02FtpwNDd + TQwAAAAACAAAAAAADZgAAwAAAABAAAAB1vxvTpzwwAheAS7067BPmAAAAAASAAACgAAAAEAAAAK + h1rgGmfbPCvkHYQigPy54AAAAzUPWXOuxOhShcWao47ZNHPv3lM8DY + P6QO1iGHa4saIb80RIth5LFm7m41B5SKqPib89 / adZPeZJKf / NTbj7KWH / zbRVBXpMp3Mjq / N8rgOadsQXRl4NY / lTgqfPnPJKIV / y7etyJv12qxGRmx30iBtlNLXRnoRkFAAAAJf pzffaZJl1Yk2t0O0UfXMbJqei< / DTS：密码>
< / DTS：ConnectionManager>
< / DTS：ObjectData>
< / DTS： ConnectionManager
>
以下是C＃.net代码
---- --------------------
protected void ExecuteSSISPackage（）
{
尝试
{
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.SqlServe r.Dts.Runtime.DTSExecResult.Failure）
{
string err =;
foreach（Microsoft.SqlServer.Dts.Runtime pkg.Errors中的.DtsError local_DtsError）
{
string error = local_DtsError.Description.ToString（）;
err = err + error;
}
Response.Write（错误）;
}
if（pkgResults == Microsoft.SqlServer。 Dts.Runtime.DTSExecResult.Success）
{
string message =包执行成功....;
Response.Write（消息）;
}
}
catch（Exception exP）
{
string exp = exP.Message.ToString（）;
}
}
有人可以帮我解决这个错误和任何参考代码吗？
感谢您的快速回复。
问候，Surya
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
推荐答案
你好 -
问题与您的包裹保护有关。连接字符串需要密码，包保护设置为EncryptSensitiveWithUserKey - 默认设置。唯一可以解密的人在运行时获取连接是你/谁创建了包。
您需要将其更改为EncryptSensitiveWithPassword或EncryptAllWithPassword并为其设置密码包。说MypkgPassword
参考：包保护级别 [ ^ ]
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[^]
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();
...
...
设置密码后，保存，关闭并重新打开密码。编辑使用密码的连接并提供密码和测试连接。然后保存并使用它。
Excel驱动程序在63位环境下不起作用，需要在SQL Setver Agent中将其设置为32位
参考： http ：//sqlblog.com/blogs/john_paul_cook/archive/2010/03/24/running-32-bit-ssis-in-a-64-bit-environment.aspx [ ^ ]
它是否与Windows身份验证有任何其他连接？
如果是，请创建代理（您需要创建凭据）第一个）这个代理应该用来执行包。
Ref： http://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-u sing-a-proxy-account / [ ^ ]
谢谢，
Kuthuparakkal
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
这篇关于如何从C＃.net代码执行SSIS包。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
