c# call SSIS package

lubber

developed SSIS package in sql2008 with saved connection and running from there ok,

now using vs2012 to call the package(saved file .dtsx) on the same machine, result error, any idea?


"SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.\
r\nAn OLE DB record is available.  Source: \"Microsoft SQL Server Native Client 10.0\"  
Hresult: 0x80040E4D  Description: \"Login failed for user 'sa'.\".\r\nFailed to acquire
connection \"dso-www-pri.REX_BASE.sa\". Connection may not be configured correctly
or you may not have the right permissions on this connection.\r\nSSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred. Error code: 0x80040E4D.\r\nAn OLE DB record is available.  
Source: \"Microsoft SQL Server Native Client 10.0\"  Hresult: 0x80040E4D  Description:
\"Login failed for user 'sa'.\".\r\nFailed to acquire connection \"dso-www-pri.REX_BASE.sa\".
Connection may not be configured correctly or you may not have the right permissions on this connection.\r\n"

回头望

配置有问题。

lubber

回头望 发表于 2013-12-13 11:43
配置有问题。

detail?

回头望

lubber 发表于 2013-12-13 12:26
detail?

错误信息里面是 sa 用户登录不进去。

lubber

回头望 发表于 2013-12-13 14:01
错误信息里面是 sa 用户登录不进去。

same package run in vs2008 design type ok, then save as package with option "EncryptSensitiveWithPassword". so I believe sa password saved in the package file, but when call that package in VS window form, it is fail with that error

5.5

Never use sa in the first place!

huaxianz

lubber 发表于 2013-12-13 14:01
same package run in vs2008 design type ok, then save as package with option "EncryptSensitiveWithP ...

Naah... "EncryptSensitiveWithPassword" means the package is encrypted by a password at design time. So at execution time the password has to be provided to decrypt the content. e.g. if run by DTEXEC, parameter /Decrypt is required. It's nothing to do with the 'sa' account.

lubber

huaxianz 发表于 2013-12-14 20:47
Naah... "EncryptSensitiveWithPassword" means the package is encrypted by a password at design time ...

so better use the something like package xml for the connection string?

huaxianz

lubber 发表于 2013-12-17 15:10
so better use the something like package xml for the connection string?

我不太确定你说的package xml指的是什么。我觉得你可能混淆了概念。

EncryptSensitiveWithPassword 和connection string没有关系。它的意思是说你的那个package里的所有敏感信息，比如你的那个sa帐户的密码不是以clear text存在的。这样的目的是别人即使拿到你的package，sa密码也不会泄露。同理，C#也看不见。你需要pass in这个密码才能decrypt出sa的密码是什么，然后才能建立db connection.这个package密码是在VS里design的时候设置的。这个就是你的那个error message的意思。

解决方法有很多，看你的要求：
1。在SSDT里直接选择不加密；
2。保持package不变，创建Package object时要初始化PackagePassword。这是一个只写property.然后再Execute()。这个应该最接近你现在做的。
3。如果有运行DTEXEC的权限，直接使用 /Decrypt命令行参数。
。。。