January 31, 2014 at 8:41 am
I've developed a lengthy SSIS package to migrate data from an old database to a new SQL Server 2008 database. My workstation is on the same domain as our dev SQL server. The SSIS package has several connection managers. When I'm connecting to the databases in the dev environment, everything works fine.
As a final stage in testing the package, I'm trying to change the connection managers of the source databases to our production environment. Production is in a different, untrusted domain. I'm using SQL Server authentication. When I change a connection manager from the dev server to the production server, I click the Test Connection button and see "Test connection succeeded." I go to an OLE DB source object that uses this connection manager. I click the "Preview..." button and it successfully displays sample data. But when I try to execute the step that contains this OLE DB source object, I get these error messages and the box turns red:
Error: There were errors during task validation.
[SSIS.Pipeline] Error: One or more component failed validation.
[SSIS.Pipeline] Error: component [OLE DB source object name] (329) failed validation and returned error code 0xC020801C.
[PRIMS Commands [329]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager [Connection Manager name] failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
For the provider on the connection manager, I've tried SQL Server Native Client 10.0, SQL Server Native Client 11.0, and Microsoft OLE DB Provider for SQL Server. All of them give me the same failure.
I'd really appreciate any suggestions. Thanks.
January 31, 2014 at 10:37 am
Addendum. In my troubleshooting, I've turned off Run64BitRuntime, and the connections work. Why would 64-bit runtime work when connecting to the dev server but not the production server?
This isn't really a good workaround for my problem because the package uses a fuzzy lookup transformation, which only runs in 64 bit mode. I might be able to separate that out into another package, but that would be inconvenient at best.
January 31, 2014 at 1:44 pm
That's a really strange set of errors and troubleshooting results. I'm half posting just so you have a response on this and don't think you're being ignored. Personally, I'm stumped, and I work with SSIS a lot.
The real change between switching between 64 bit and 32 bit is which drivers it'll use. Since that's a local concern, I would try sending your package (as 64 bit) to another machine, and testing from there. Determine if it's a local issue. If it is you'll have to troubleshoot/reinstall your local.
The other possibility is the connections on the foreign server are having trouble. Testing the connection doesn't do much, just confirms the listener isn't going to reject the login, really. Getting the metadata (clicking on columns) requires it to run something through the connection, so those should be sound. Having it only fail at runtime is very strange.
Also, I hate to say it because I don't like going here first for troubleshooting SSIS, but sometimes you have to scrap it and rebuild it. Something glitches under the hood (this was MUCH more common in 2k5 than 2k8) and you can't recover from it. To this nature though I would recommend taking a single component into a new package shell (one that's failing in the original), build it back up from scratch, and see if you can run it there. If you can, destroy the objects in the original shell and copy/paste them over (and re-adjust your data source connections). Retest.
I guess my best recommendation is don't troubleshoot the security or the connection... troubleshoot SSIS.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply