I have a Data Flow Task in an SSIS package that transfers data from a local table to a table on a remote server. The remote database is SQL Server 2000 and the local one is SQL Server 2005. The package in the designer runs great. But, when I setup the job for the server agent it fails with the connection. I get the following error:
The AcquireConnection method call to the connection manager "server.name.here" failed with error code 0xC0202009.
This is on validation from what the error logs say. It is probably just a simple configuration problem or something I have overlooked.
=============================================================
I also ran a seperate import task on the remote server (the same one as above) database table (Tasks > Import Data) and saved it as an SSIS package as the last option it gives you in the wizard. I am copying from a local table to a remote table. The import was successful as expected. But, when I run that same SSIS package in a SQL Server Agent Job, that package fails. I have not made any changes to the package what so ever. I never even opened the package to look it in the editor. I just ran it as is.
I am thinking it is a security problem of some sort. Like the Agent does not have right privilidges. I am running the packages under my local system account.
Thanks in advance!
This does sound like a security problem. If you're executing from SQL SAerver Agent then the packages will run as the startup acocunt for SQL Server Agent. Does this account have permissions on the source database?
There is a way that you can see what connection string is being used at runtime as I have explained here: http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx
This will cause the connection string to be output to your log provider (assuming you are using one and if you aren't - you should be).
-Jamie
|||
Thanks for your response.
I am running the local database from a Windows XP development machine. This is where the SSIS package resides because the hosting provider I am using for the remote database does not support SSIS. So I have to run the Server Agent locally and then copy the data over as the last step of the package.
So to answer your question, NO, my local account that is running Server Agent is not on the remote server. I am using the same username/password on the remote machine as I am for the ASP.NET pages on the application on that remote machine. The remote account works. I am using windows security locally on my development for the database server login.
As I mentioned earlier, I can run the SSIS package in the designer without a problem but it does not work in the Server Agent.
I do have a log provider setup to output to a table on the local database. Below is what the log output is (I replaced the actual server name for security reasons to "server.name.here"):
__
| 988 | OnInformation | RETHINK1 | NT AUTHORITY\SYSTEM | Export Listings Data | 13d18a9e-932d-4bcc-9b14-d55c139c0bec | b3ae2538-1821-4999-9c2d-b29cc3511ca6 | 12/18/2005 4:33:52 PM | 12/18/2005 4:33:52 PM | 1074016266 | <Binary data> | Validation phase is beginning. |
| 989 | OnProgress | RETHINK1 | NT AUTHORITY\SYSTEM | Export Listings Data | 13d18a9e-932d-4bcc-9b14-d55c139c0bec | b3ae2538-1821-4999-9c2d-b29cc3511ca6 | 12/18/2005 4:33:52 PM | 12/18/2005 4:33:52 PM | 0 | <Binary data> | Validating |
| 990 | OnProgress | RETHINK1 | NT AUTHORITY\SYSTEM | Export Listings Data | 13d18a9e-932d-4bcc-9b14-d55c139c0bec | b3ae2538-1821-4999-9c2d-b29cc3511ca6 | 12/18/2005 4:33:52 PM | 12/18/2005 4:33:52 PM | 50 | <Binary data> | Validating |
| 991 | OnError | RETHINK1 | NT AUTHORITY\SYSTEM | Export Listings Data | 13d18a9e-932d-4bcc-9b14-d55c139c0bec | b3ae2538-1821-4999-9c2d-b29cc3511ca6 | 12/18/2005 4:33:52 PM | 12/18/2005 4:33:52 PM | -1071611876 | <Binary data> | The AcquireConnection method call to the connection manager "server.name.here" failed with error code 0xC0202009. |
| 992 | OnError | RETHINK1 | NT AUTHORITY\SYSTEM | Export Listings Data | 13d18a9e-932d-4bcc-9b14-d55c139c0bec | b3ae2538-1821-4999-9c2d-b29cc3511ca6 | 12/18/2005 4:33:52 PM | 12/18/2005 4:33:52 PM | -1073450985 | <Binary data> | component "Destination - Listings" (187) failed validation and returned error code 0xC020801C. |
| 993 | OnProgress | RETHINK1 | NT AUTHORITY\SYSTEM | Export Listings Data | 13d18a9e-932d-4bcc-9b14-d55c139c0bec | b3ae2538-1821-4999-9c2d-b29cc3511ca6 | 12/18/2005 4:33:52 PM | 12/18/2005 4:33:52 PM | 100 | <Binary data> | Validating |
| 994 | OnError | RETHINK1 | NT AUTHORITY\SYSTEM | Export Listings Data | 13d18a9e-932d-4bcc-9b14-d55c139c0bec | b3ae2538-1821-4999-9c2d-b29cc3511ca6 | 12/18/2005 4:33:52 PM | 12/18/2005 4:33:52 PM | -1073450996 | <Binary data> | One or more component failed validation. |
| 995 | OnError | RETHINK1 | NT AUTHORITY\SYSTEM | Export Listings Data | 13d18a9e-932d-4bcc-9b14-d55c139c0bec | b3ae2538-1821-4999-9c2d-b29cc3511ca6 | 12/18/2005 4:33:52 PM | 12/18/2005 4:33:52 PM | -1073594105 | <Binary data> | There were errors during task validation. |
| 996 | OnPostValidate | RETHINK1 | NT AUTHORITY\SYSTEM | Export Listings Data | 13d18a9e-932d-4bcc-9b14-d55c139c0bec | b3ae2538-1821-4999-9c2d-b29cc3511ca6 | 12/18/2005 4:33:52 PM | 12/18/2005 4:33:52 PM | 0 | <Binary data> |
After my last post I changed the timeout on the remote database connection to 60 seconds as you suggested in your blog. I would have never thought that it would have been that simple but it fixed the problem. I thought that the timeouts set at 0 seconds meant there WAS NO TIMEOUT. BAH!!
Thanks for your help! I was about ready to pull my hair out.
No comments:
Post a Comment