sql server - How to get all errors of all SSIS packages in a solution

Question

In Visual Studio 2015 I have a solution with 3 dozens dtsx in the SSIS Packages Folder. I re-build the solution and I get success. Only when I open single dtsx one after the other I notice that some of them (not all), actually, have several problems.

Is there a way to get a list of these problems in the Error List or do I need to open all dtsx one by one?

Answer 1

Unfortunately, there is no way to achieve this from your integration services solution (in visual studio) without opening the packages or maybe executing them using DTExec Utility. But you can do some workaround and check get errors programmatically:

Workaround

1. I created a winforms application using visual studio (using Vb.Net)
2. I added Microsoft.SqlServer.DTSPipelineWrap and Microsoft.SQLServer.ManagedDTS as references

3. I used the following code to loop over packages in a specific directory, validate, and get errors into a log file:

   Dim strPackagesDirectory As String = "C:UsersAdminDesktopNew folder"
   Dim strOutputLogFile As String = "D:1.txt"
   
   For Each strFile As String In IO.Directory.GetFiles(strPackagesDirectory, "*.dtsx", IO.SearchOption.TopDirectoryOnly)
   
       Dim pckg As New Microsoft.SqlServer.Dts.Runtime.Package
       Dim app As New Microsoft.SqlServer.Dts.Runtime.Application
   
       pckg = app.LoadPackage(strFile, Nothing)
       Dim obj = pckg.Validate(Nothing, Nothing, Nothing, Nothing)
   
       If pckg.Errors.Count > 0 Then
   
           Using sr As New IO.StreamWriter(strOutputLogFile, True)
               sr.WriteLine("")
               sr.WriteLine(strFile)
               sr.WriteLine("--------------")
               For Each err As Object In pckg.Errors
   
   
                   sr.WriteLine(err.Description)
   
               Next
   
               sr.WriteLine("==========")
               sr.Close()
           End Using
   
       End If
   Next
   

References

• https://msdn.microsoft.com/en-us/library/ms136090.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1
• https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.package.aspx
• https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.dtscontainer.validate.aspx