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:
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.User contributions licensed under CC BY-SA 3.0