August 26, 2009 at 11:52 am
I've created an SSIS package in Visual Studio 2005, and imported it into my SQL 2005 Server, Integration Services. Here's the rub. If I run the package, through Visual Studio, it runs fine. If I run the package through Integration Services, using the DTEXECUI utility, it runs fine WHILE connected to the SQL Server from my workstation. If I run the package, using DTEXECUI, from the actual SQL Server, I receive the following error messages. The package is simply importing records from an AS400 file to an SQL table.
Message: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E00.
Message: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Select ACT_BLKDTL" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
Message: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
Message: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
Message: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
Message: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the error.
The connected user is different in each case, and I suspect that might be the issue, but I'm not sure how I can correct that. I've seen some things about a proxy account, but I'm not sure how it works or how to set it up.
I'm new to SQL Server, so any suggestions will be greatly appreciated.
August 26, 2009 at 4:07 pm
Jeff,
I suspect that your problem is the Package Protection Level setting. By default this is set to SaveSensitiveWithUserKey, which works great until you deploy the package or try to automate it in any way. In the package properties window (press F4 while in the Control Flow), set the Package Protection Level to "Save Sensitive with Password", and specify a package password. Since you're connecting to an AS400, you've probably had to specify a password to connect, and moving the package to production or running under a different user context causes this password to be removed.
For more information on the different options available, search Books Online or the web for SSIS package protection levels.
hth,
Tim
Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices
August 27, 2009 at 7:23 am
Tim,
Thanks for the reply. I will take a look at this setting, HOWEVER, my package uses an ingenious setup (not mine, but I like it). I hope I describe this correcting...
Package configurations are enabled within my package, with the first configuration being a connection to an XML file, which defines a database table on my SQL Server. This database table contains the information for the remaining two connections, and so the next two configurations instruct the package to use the database table to define the connections. When the connections were initially exported to the table, the password for the AS400 connection was omitted, by design. However, I'm able to edit the table and add the required text for including the password.
Not sure if that changes your response, but I will try your suggestion.
Jeff
August 27, 2009 at 8:50 am
Tim,
I changed the protection level, saved my package, and deployed it to the server. When I run the package, from the server, it asks for the password (so I know the new protection level is working), BUT I get the same error message as before and no records are imported to the database.
There must be an obvious difference between running it from the server (as user \XYZ) and running it from my workstation (as user \ABC). Yes, the users are different, but I don't know what to do with user /XYZ, so this will run. Or if that's even the answer.
Jeff
September 3, 2009 at 10:54 pm
Is your environment such that you can try to run the package with the same account you used in testing? That should tell you if the problem is with the user account rather than some other package issue. I'll admit that I haven't done much with AS/400 integration in SSIS, so I don't know if there are any other gotchas in connecting to one of those old boxes.
Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices
September 4, 2009 at 8:20 am
Tim,
This select statement 'Select ACT_BLKDTL'. Is this the 'special' table that contains your passwords or is this the select from the data tables?
Josef Richberg
2009 Exceptional DBA
http://www.josef-richberg.squarespace.com
http://twitter.com/sqlrunner
September 4, 2009 at 8:40 am
Tim and sqlrunner,
I was just going to put up another post. I've resolved my issue. It wasn't an account issue at all. The part that threw me, was being able to run it successfully from my workstation, but not from the server. So I started checking version levels for everything involved. It turns out the IBM iSeries ODBC driver, I was using to connect to the AS400, was a slightly older version on the server. Once I upgraded the driver to the same version I had on my workstation, it worked like a charm.
I sincerely appreciate the suggestions, and hope that maybe this will help someone else in the future.
Thanks,
Jeff
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply