BIDS 2008 SSIS Package Data Flow Task fails validation and returns “VS_ISBROKEN” Error

0
votes

A BIDS 2008 SSIS package that I am working is failing validation when it reaches any Data Flow Task in the package. I am able to run the SSIS package within BIDS 2008 on my computer locally. When I run the SSIS package through an SQL Agent Job, the package fails with the error message below. I have researched error code 0x80004005 and error code 0xC004706B through Google searches, but have not found anything that is of use.

The SSIS package in question is one of several SSIS packages that run together in the same SQL Agent Job. The other SSIS packages in the original SQL Agent Job all are able to run successfully. This leads me to believe that the SQL Agent Service Account and Proxy Account are configured with the appropriate permissions. A DBA was able to confirm that the Service Account and Proxy Account do have the appropriate permissions.

The SQL Agent Job runs the SSIS package using the Operating System (CmdExec) type and the SSIS package has Run64BitRuntime set to FALSE so the SSIS Package is running under the 32-bit runtime.

For debugging purposes, I recreated the Data Flow Task, Database Connections, and related source and destination tasks in a new SSIS Package from the original SSIS package. The debugging SSIS package is able to run locally on my computer, but fails when I try to run using a new SQL Agent Job setup exactly like the original SQL Agent Job.

I have also use different Database Connections such as Native OLE DB\SQL Server Native Client 10.0, 10.1, 11.1, and Native OLE DB\Microsoft OLE DB Provider for SQL Server. All of which have failed when I test the debugging SSIS package

I have verified that the metadata for the OLE DB Source and Destinations match the datatypes and lengths in the SQL Server Database tables. I have verified that the stored procedure used by the OLE DB Source task in the Data Flow Task work when ran on SQL Server Management Studio and the Service Account has permissions to run the stored procedure.

I think that is every bit of information that I can recall from working on troubleshooting this issue.

Has anyone else come across this error before? Any possible solutions to attempt?

Any help is welcomed!

Error Message:

Executed as user: domain\SQLAgentServiceAccount. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 64-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.

Started: 1:37:09 PM

Error: 2014-08-14 13:37:09.94 Code: 0xC0202009 Source: Data Flow Task - OLEDB Source [1]
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.

Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005
Description: "Syntax error or access violation".
End Error

Error: 2014-08-14 13:37:09.94 Code: 0xC004706B Source: Data Flow Task SSIS.Pipeline
Description: "component "OLEDB Source" (1)" failed validation and returned validation status "VS_ISBROKEN".
End Error

Error: 2014-08-14 13:37:09.94 Code: 0xC004700C Source: Data Flow Task SSIS.Pipeline
Description: One or more component failed validation.
End Error

Error: 2014-08-14 13:37:09.94 Code: 0xC0024107 Source: Data Flow task
Description: There were errors during task validation.
End Error

DTExec: The package execution returned DTSER_FAILURE (1).

Started: 1:37:09 PM Finished: 1:37:09 PM Elapsed: 0.219 seconds. Process Exit Code 1.

The step failed.

3
sql-serverssissql-server-2008-r2bidssql-agent-job
When you migrate a package from one machine to another and have a validation error, it is most likely caused by the configuration of your connection manager or differences in the database that it is actually connecting to. You would get a login failed message if it was a problem with your service account. However, you have "Syntax error or access violation." Is the command in the OLEDB source an expression in a variable? Is this SSIS 2008r2 or under or ssis 2012 and up? Are you using package configs or params to set you connection string? – Mark Wojciechowicz
The command in the OLEDB source is an embedded stored procedure that takes in two parameters and returns a dataset to move to a table in another database. The SSIS package was developed in BIDS 2008 and being ran through a SQL Job on SQL Server 2008 R2. Using a config file to get the SQL Server name. – Kenny Houser
When you test this locally, are you connecting to the same server as when you run it through SQL agent? If not, is the proc the same in both places? – Mark Wojciechowicz
Yes, I am connecting to the same server that I am run the package through SQL agent. – Kenny Houser
Are the two parameters set the same way in both cases? Also, you mentioned 64 bit vs 32, is this important for what you are connecting to? You are executing under 64 bit according to the error message that you posted. – Mark Wojciechowicz

3 Answers

1
votes

I found the this solution :

Ensure that the connection string in Connection Manager has "Provider=SQLNCLI11.1; Persist Security Info=True; Auto Translate=False;"

I did it and it worked for me on Hyperscale / SQL Server.

0
votes

This is kind of strange error that I got a while ago and I used the following to resolve the problem:

Run command prompt as Administrator Type the following command and hit Enter: regsvr32 jscript.dll Type the following command and hit Enter: regsvr32 vbscript.dll

0
votes

The solution to the problem was that the service account that is running the SQL Agent job did not have execute permissions on the stored procedure being executed through the Data Flow Task. Gave the service account execute permissions and the SSIS package was able to successfully execute.