Sql Connection in SSIS Package through Webservices

Question

Hi I have SSISpackage which is developed in VS2008/SQL2008(Target Database),My source data is SQL2005. This package is execute with in the .Net framework of webservice. while executing am getting error with SOURCE Connection (with provider=SQLNCLI10 in connection string)

getting error,the description as follows. Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0 " Hresult: 0x80004005 Description: "Login timeout expired". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0 " Hresult: 0x80004005 Description: "A network-related or instance-specific err or has occurred while establishing a connection to SQL Server. Server is not fou nd or not accessible. Check if instance name is correct and if SQL Server is con figured to allow remote connections. For more information see SQL Server Books O nline.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0 " Hresult: 0x80004005 Description: "Named Pipes Provider: Could not open a con nection to SQL Server [2]. ".

please let me know how to resolve this issue. Thanks

Answer 1

That error message generally pops up if you have improper credentials.

Ensure that you can connect to the server via the same credentials used in the SSIS package via Management Studio. The SQL 10.0 driver is fully compatible and able to connect to a SQL 2005 box.

Most often, however, is that you're running this as the SQL Server Agent account, which is a SQL Server account. And you're connecting to a remote server. If this is the case, you need to create a Credential in SQL Server. Then, you have to create a Proxy in SQL Server Agent, and allow it to run SSIS packages. Then, assign that Proxy as the owner of the Job Step that kicks off the package.