I have an SSIS process that is scheduled to run each night. It's been working fine for months. The process exports data from an iSeries database and caches the data in a SQL Server 2005 database. This weekend the managers of the iSeries system upgraded there server. As far as my application is concerned, all I had to do to fix my export project was change a few ODBC drivers, as well as a few table references in a few SQL statements in my package. When I test project in Visual Studio 2005 before publishing it to the server, the exportation process works perfectly. When I publish the package to the server and schedule an immediate test run of the package, the job fails within seconds of it's execution. When I initially setup this nightly process I had to read up on configuring security settings on SQL Server. I already have proxy setup for this job which runs the package as an administrator and I've verified that the proxy account is still available. In the past, I've had to update this package and republish it to SQL Server numerous times. Everything has worked perfectly. I've pasted the extensive error message from the SQL Jobs history file (replacing sensitive information with explanations.) Please, can anyone help? I am well aware of how SSIS security works. Almost every solution that I have found deals with fixing security issues-- all of which I was aware of and I have double-checked. Thank you. ----- Error Message --- Date 4/20/2011 9:55:00 AM Log Job History ([Package Name]) Step ID 1 Server [The SQL Server] Job Name [Package Name] Step Name Execute Package Duration 00:00:03 Sql Severity 0 Sql Message ID 0 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0 Message Executed as user: [This is the admin account]. ...e Utility Version 9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 9:55:00 AM Error: 2011-04-20 09:55:03.52 Code: 0xC020838F Source: [iSeries table] [SSIS Dataflow step that fails] [1] Description: An error occurred executing the provided SQL command. End Error Error: 2011-04-20 09:55:03.52 Code: 0xC004706B Source: [iSeries table] DTS.Pipeline Description: "component "[SSIS Dataflow step that fails]" (1)" failed validation and returned validation status "VS_ISBROKEN". End Error Error: 2011-04-20 09:55:03.52 Code: 0xC004700C Source: BBLINV DTS.Pipeline Description: One or more component failed validation. End Error Error: 2011-04-20 09:55:03.52 Code: 0xC0024107 Source: [iSeries table] Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:55:00 AM Finished: 9:55:03 AM Elapsed: 2.953 seconds. The package execution failed. The step failed.

Update the iSeries drivers on your server, too.Arthur My Blog

They have been updated and triple checked.

Does the package run interactively (outside SQL Agent)?Arthur My Blog

Nope. I logged into the SSIS server through Management Studio, expanded Stored Packaged->MSDB->Maintenance Plans and right-click & selected "Run Package". After attempting to execute the package I get another error screen, it's a tree view but after browsing the data, it's stating the same (albeit vague) problem as the problem that I copied from the Job History log. (Note, when I attempted to execute the job this way, I did recall to select the correct config file, which caches iSeries credentials. This is the same config file that I use from within VS and the config file is stored locally on the SQL Server-- not my PC.)

By doing this you are running this package on your machine rather than on the server. Thus, I am starting to suspect the package did not get updated/refreshed properly. So, I suggest you go DELETE instead this package from the store and then re-add it again (anew). Let us know if then it worked. But it is better to run it say using DTExecUI when you are right on the box which executed this package.Arthur My Blog

I'm trying to add another Maintenance Plan of the package and I will report it's sucess/failure soon. I forgot to mention in the last post-- I was using Management Studio while remoting into the SQL Server machine, not from my PC.

Are there any updates on the schema of the destination tables? VS_BROKEN is something related to the metadta in the dataflow task.Please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Deepak.

Technically, this wasn't the answer but I wanted to give you the points for helping me out. I managed to fix this problem by importing the project as a new SSIS package (with a different name) and, subsequently, creating new SQL Server Agent job. After the re-importation and scheduling of the new job, everything is working perfectly. Would you (or anyone) happen to have any clue as to what was going on? All of the new SQL objects that I've created have the exact same properties as the originals, which I double checked before executing the new job. IMHO, this behavior seems like a SSIS bug...

I believe in your case not the whole package became refreshed, thus by just removing and re-adding the package back to the store you supposed to fix this issue. I can see it does happen sometimes, and even happened to me last week when a disabled part of a package was not propagated to the package store after a package update after I re-enabled it in BIDS.Arthur My Blog