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.
Hadi
  • 36,233
  • 13
  • 65
  • 124
AutomatedChaos
  • 7,267
  • 2
  • 27
  • 47
  • 2
    Have you tried to deploy packages on SSIS Catalog and validate it on the SSIS Server? I use this approach for DB-Package match test; among the advantages - you can use environments to specify Connection Managers. – Ferdipux Apr 18 '17 at 07:18
  • 1
    Look at my answer here. maybe it is different to what you have tried http://stackoverflow.com/questions/42577741/how-to-get-all-errors-of-all-ssis-packages-in-a-solution – Hadi Apr 18 '17 at 21:23
  • 1
    Thank you both for the replies. I am still in the investigation phase and will take these options certainly into account! I'll post my final solution when I've found one. – AutomatedChaos Apr 18 '17 at 21:26

0 Answers0