I'm storing the connection string in sql table (ssis_config) and the Provider=SQLNCLI10.1; but when it executes from sql agent I get the message that: "Description: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR. The requested OLE DB provider SQLNCLI.1 is not registered -- perhaps no 64-bit provider is available. " What gives?

If it runs outside the SQL Agent job, will the package succeed?Arthur My Blog

Yes, it indicates it is "attempting to configure from SQL server using the configuration string" and works fine. I can check the 'data sources' tab in the job step and everything looks good there too (Provider=SQLNCLI10.1).

Then the culprit lies somewhere in the way the SQL Agent job is configured and I can tell you specifically it would be the account the Agent Job is running under. This account cannot access the database driver (SQLNCLI10.1) to connect to the table containing the config setting.Arthur My Blog

ok, I know how to find the account the agent is running under but how do I verify what has access to the database driver?

One common way of figuring out is to create a test UDL connection. Creating and Configuring Universal Data Link (.udl) Files: http://msdn.microsoft.com/en-us/library/e38h511e%28v=vs.71%29.aspxArthur My Blog

When you said it "runs fine outside of Agent", please let us know EXACTLY how you intiated package execution - on what machine, and with what tool. Second, please describe your server environment. Do you have a 32-bit or 64-bit OS? Is the SQL install 32-bit or 64-bit? Talk to me now on

Environment for the sql server is is Windows Server Enterprise service pack 1, SQL server 2008 service pack 1, both 64 bit. I am able to run the package successfully from the 'run package' option in integration services from my personal machine.

Running a package on your personal machine does not equal running it on the server. Todd (I think) and I suspect you do not have the driver even installed. Arthur My Blog

I think you're on to something, when I run it from integration services on the sql server I get: "Error: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_ERROR. The requested OLE DB provider SQLNCLI.1 is not registered...".

Strange that in this error message it is not Provider=SQLNCLI10.1, but SQLNCLI.1 What should it be? But in any case it is driver related, thinking may be you tinkered with the conn. string? Arthur My Blog

no, ran it directly from the server from SSMS integration services. That is the message I get when it tries to read from the ssis_config table. Since it's a sql 2008 instance shouldn't it be SQLNCLI10.1?

Yes, but make sure there is no typo anywhere in the conn string, this is what I meant. Arthur My Blog

That means you do not have the SQL Server Native Client provider ("SQLNCLI") installed on the client computer. Install the SQL Server Native Client tools or you can try to change the provider to "sqloledb" to use OLE DB and see if that works. http://uk.linkedin.com/in/ramjaddu

Arthur, thank you. The real issue turned out to be a leftover attribute from an earlier attempt in one of the connection strings. Thanks everybody for your help.

I am having the same problem - could you tell me what leftover attribute was causing your problem?

Thanks to all posters who have helped me solve this issue. I had a SSIS from a SQL 2005 server running on a 2008R2 server with this error. It would run fine in BIMS but not from the SQL agent. I tried NTFS permissions, 32-bit runtime, SQL agent user, provider string - all to no avail. Simply creating a new connection string in the SSIS job made all the pain disappear. Must have been a parameter incorrectly set but I'm happy now!