Validate dtsx packages outside Visual Studio

2

Background: Our application is split into a Database project and a SSIS project. Right now, it can occur that a change is pushed on the DB project (like a column name change) that will go unnoticed until runtime when a package is failing that tries to use that column. As part of our integration build, I want to validate the packages in the SSIS project against the database, so we can detect these kind of errors earlier.

Question: How can I validate our dtsx packages so that I can detect errors in the design during an integration build?

Preferred solution: I want to have a list of errors for all packages that are mismatching to database objects like I see when I open the package in design view:

Error   4   Validation error. 
My_Package_Name [40]: SSIS Error Code DTS_E_OLEDBERROR.  
An OLE DB error has occurred. Error code: 0x80040E14.  
An OLE DB record is available.  
Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80040E14  
Description: "Statement(s) could not be prepared.".  An OLE DB record is available.  
Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80040E14  
Description: "Invalid column name 'MyRenamedColumn'.".      
My_Package_Name.dtsx    0   0   

Ideally: running the "Design Warnings Scanner" from the command line and capturing the errors and warnings.

Already tried:

  1. Running DTEXEC.exe from commandline. But because we are using Connection Managers instead of in-package variables for the database connections, DTEXEC does not run without errors.
  2. Validating packages programmatically with the Validate() method on the package as Microsoft.SqlServer.Dts.Runtime.Package object. This will always succeed (even on packages that use faulty columns). Adding the sqlserver connection manually through the Connections collection does not help.
sql-server
visual-studio-2012
ssis
sql-server-2012
asked on Stack Overflow Apr 18, 2017 by AutomatedChaos • edited Apr 18, 2017 by Hadi

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0