list SqlStatementSource of all “Execute SQL Task”

Question

I would like to list SqlStatementSource of all "Execute SQL Task" in SSIS package. By referencing relevant, it shows the ssis package of SQLPSX may be helpful to resolve this task. However, while I am trying to do the following procedure:

import-module SSIS
$package = Get-ISPackage -path "xxx.dtsx"

My powershell returns an error message of:

"Failed to remove package protection with error 0xC0014037 "The package is encrypted with a password. The password was not specified, or is not correct.". This occurs in the CPackage::LoadFromXML method."

it shows I should import password to decrypt the package to retrieve data, but where should I put the password? Or is there any other convenient solution for me to solve the issue.

Best, David

Answer 1

you usually use DTEXEC to run an encrypted package like this:

DTExec.exe /FILE "C:\Package1.dtsx" /DECRYPT password@1.

/FILE means the package is on the file system. You would use /SQL to a package on the SQl Server database or /DT if it is on the file store

Also if you open the package on BIDS, you should be prompted for a password

Answer 2

After searching online information about how to list all SSIS component, I found writing a C# program might be the best solution to solve the problem. Therefore, below I wrote a VS 2008 compatible program to finish my task. Below written the program which will list all SSIS related component, and write the result to excel file. The solution will save you a lot of time clicking on component shown in VS 2008 to view component property one by one. Since I am not a C# expert, the program may not well coded. But at least it works!

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;


namespace Project1
{
    public class SSISFinder
    {

        public static void Main()
        {
            // Set ssis app
            Microsoft.SqlServer.Dts.Runtime.Application ssisApp = new Microsoft.SqlServer.Dts.Runtime.Application();
            ssisApp.PackagePassword = "admin_monkey4ccc";

            // Loading dtsx package
            Package pkg = ssisApp.LoadPackage("D:\\SummaryETL.dtsx", null);

            // Open Excel Sheet
            Excel.Application oXL = new Excel.Application();
            Excel.Workbook oWB;
            Excel.Worksheet oSheet;            
            string fileName = "D:\\test.xls";
            oWB = oXL.Workbooks.Add(Missing.Value);
            oSheet = (Excel.Worksheet)oWB.ActiveSheet;

            // List data flow package
            List<DtsContainer> containers = FindExecutablesByType((IDTSSequence)pkg, "PIPELINE");
            int counter = 1;

            foreach (DtsContainer exec in containers)
            {
                TaskHost th = exec as TaskHost;
                MainPipe pipe = (MainPipe)th.InnerObject;

                foreach (IDTSComponentMetaData100 comp in pipe.ComponentMetaDataCollection)
              {
                  if (comp.Description == "OLE DB Source")
                  {
                      oSheet.Cells[counter, 1] = comp.Description;
                      oSheet.Cells[counter, 2] = th.Properties["Name"].GetValue(th).ToString();
                      oSheet.Cells[counter, 3] = comp.Name;                      
                      oSheet.Cells[counter, 4] = comp.CustomPropertyCollection["SqlCommand"].Value;
                      Console.WriteLine("  Component Name = " + comp.Name);
                      counter++;
                  }
                  else if (comp.Description == "OLE DB Destination")
                  {
                      oSheet.Cells[counter, 1] = comp.Description;
                      oSheet.Cells[counter, 2] = th.Properties["Name"].GetValue(th).ToString();
                      oSheet.Cells[counter, 3] = comp.Name;                      
                      oSheet.Cells[counter, 4] = comp.CustomPropertyCollection["OpenRowset"].Value;
                      Console.WriteLine("  Component Name = " + comp.Name);
                      counter++;
                  }
              }

            }

            oWB.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
    Excel.XlSaveAsAccessMode.xlShared, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            oWB = null;

            oXL.Quit();
            oXL = null;

        }

        static List<DtsContainer> FindExecutablesByType(IDTSSequence sequence, string typeName)
        {
            string typeNameUpper = typeName.ToUpper();
            List<DtsContainer> matchingExecutable = new List<DtsContainer>();
            foreach (Executable e in sequence.Executables)
            {
                if (e.GetType().ToString().ToUpper().Contains(typeNameUpper))
                {


                    matchingExecutable.Add((DtsContainer)e);
                }
                if (e is TaskHost)
                {
                    TaskHost taskHost = (TaskHost)e;

                    if ((typeNameUpper.Contains("DATA FLOW")
                                || typeNameUpper.Contains("DATAFLOW")
                                || typeNameUpper.Contains("MAINPIPE")
                                || typeNameUpper.Contains("PIPELINE")
                                ) && taskHost.InnerObject is IDTSPipeline100
                       )
                    {
                        matchingExecutable.Add((DtsContainer)e);
                    }
                    else if (taskHost.InnerObject.GetType().ToString().ToUpper().Contains(typeNameUpper))
                    {
                        matchingExecutable.Add((DtsContainer)e);
                    }
                }
                if (e is IDTSSequence)
                {
                    matchingExecutable.AddRange(FindExecutablesByType((IDTSSequence)e, typeNameUpper));
                }
            }
            return matchingExecutable;
        }
    }
}

Answer 3

I don't have SQLPSX installed but I can tell you how to decrypt the package without it. The important thing to do is to assign the package password to the application and so that it can decrypt the package.

Given a package like this where each execute sql task has a statement of SELECT N AS test

The following script will decrypt a package saved as EncryptAllWithPassword and has an assortment of tasks with some embedded in various containers. It's not beautiful PowerShell by any stretch but it gets the job done.

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.ManagedDTS") | out-null

Function ProcessExecutable
{
    param
    (
    [Microsoft.SqlServer.Dts.Runtime.Executable]$item
    )

    $t = $item.GetType()
    if ($t.Name -eq "TaskHost")
    {
        #$th = New-Object Microsoft.SqlServer.Dts.Runtime.Task
        #$es = New-Object Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask
        $th = [Microsoft.SqlServer.Dts.Runtime.TaskHost]$item
        try
        {
            $es = [Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask]$th.InnerObject
            Write-Host($es.SqlStatementSource)
        }
        catch
        {
        }
    }
    elseif ($t.Name -eq "Sequence")
    {
        $sequence = [Microsoft.SqlServer.Dts.Runtime.Sequence]$item
        foreach ($subitem in $sequence.Executables)
        {
            ProcessExecutable $subitem
        }
    }
    elseif ($t.Name -eq "ForLoop")
    {
        $sequence = [Microsoft.SqlServer.Dts.Runtime.ForLoop]$item
        foreach ($subitem in $sequence.Executables)
        {
            ProcessExecutable $subitem
        }
    }
    elseif ($t.Name -eq "ForEachLoop")
    {
        $sequence = [Microsoft.SqlServer.Dts.Runtime.ForEachLoop]$item
        foreach ($subitem in $sequence.Executables)
        {
            ProcessExecutable $subitem
        }
    }    
}


$app = New-Object Microsoft.SqlServer.Dts.Runtime.Application
$app.PackagePassword = "password"
$packagePath = "C:\sandbox\SSISHackAndSlash\SSISHackAndSlash\Encrypted.dtsx"
$package = $app.LoadPackage($packagePath, $null)

foreach($item in $package.Executables)
{
    ProcessExecutable $item
}

Ouput

SELECT 1 AS test
SELECT 2 As test
SELECT 5 AS test
SELECT 4 AS test
SELECT 3 AS test

Answer 4

Not that this is the best solution but something to try. Instead of trying to go through SSIS to obtain the information why not go through the file itself. DTSX files are XML format and PowerShell plays pretty well with these type files.

I tried this on one of my dtsx files and was able to return information:

[xml]$package = Get-Content C:\Myfile.dtsx
$package.Executable.Executable | 
   Select -ExpandProperty ObjectData | 
      Select -ExpandProperty SqlTaskData | 
         Select SqlStatementSource

For some reason I do get an InvalidArgument error saying it cannot find the property "SqlTaskData". I believe this is from it hitting the data flow task I have in the package, and it does not have the property/attribute. This is what I mean in that it might not be the perfect solution, so I offer no guarantees. One thing to point out is I do not have my packages set to be encrypted by a password.

Update

You might try SQLPSX that include a library for SSIS.