Is it possible to Decompile and read an SSIS package code?

Question

I am working on a project which was owned by a different person. He created a job to update bunch of fields in handful of tables in SQL server. That job was based on an SSIS package. Now I have the DTSX file and the disabled job. When I tried to enable the job after changing the old credentials in the DTSX file, I am getting weird errors. I want to see the code behind the SSIS package and want to see how the update process is coded. Is there a way to do this?

Antecedents

If I am not mistaken, DTSX files are XML files. Just rename to xml, and open with an editor. – Azevedo

Yeah that I can see using a wordpad. But is there a way to see how the 'Update' is coded? I don't see anything related to that requirement in the DTSX file. – Antecedents

Thanks. You idea was actually helpful. Found the requird statements. Can you make your comment as answer? – Antecedents

Answer 1

DTSX files are just XML files. Just rename to xml, and open with an editor.

The query is a string that is built up via string concatenation.

Azevedo

You do not need to rename it to *.XML because the X in DTSX stands for XML. Just open the *.DTSX in Notepadd++. – Jaimeejaimes

And if you want nice XML syntax highlighting, just add dtsx to the list of extensions that are associated with the XML language in Notepad++ under Settings -> Style Configurator. For example, I have added the following extensions: build dstx ds dsv cube dim dwproj xmla config nunit nbits. – Tupungato

Answer 2

I want to see the code behind the SSIS package and want to see how the update process is coded

As other answers mentioned, dtsx files are XML files, you can simply open these files using a text editor to check the code behind, or you can simply create a new Integration Services project using visual studio and add the package to check it's control flow.

• Integration Services (SSIS) Projects and Solutions

I added my own answer to list some of what i posted related to this issue, since it may give you some insights:

• Automate Version number Retrieval from .Dtsx files
• Reverse engineering SSIS package using C#
• Is there a way to save all queries present in a ssis package/dtsx file?
• Parse SSIS package
• Loading SSIS Package Programmatically in C#

Steeple

Answer 3

Create a new solution and import the DTSX file into that. Visual Studio just serves as a glorified XML editor for SSIS packages.

Jaimeejaimes

Answer 4

Depending on which version of SSIS you're working with, you'd want to look at the XML of the package (F7 if you have the package open) and find a block like the following

<DTS:Executable
  DTS:refId="Package\Execute SQL Task"
  DTS:CreationName="Microsoft.ExecuteSQLTask"
  DTS:Description="Execute SQL Task"
  DTS:DTSID="{93D895E6-A316-4718-9C97-4A5652ABD28C}"
  DTS:ExecutableType="Microsoft.ExecuteSQLTask"
  DTS:LocaleID="-1"
  DTS:ObjectName="Execute SQL Task"
  DTS:TaskContact="Execute SQL Task; Microsoft Corporation; SQL Server 2017; © 2017 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1"
  DTS:ThreadHint="0">
  <DTS:Variables />
  <DTS:ObjectData>
    <SQLTask:SqlTaskData
      SQLTask:Connection="{CEE71D69-1F9F-46FA-A22F-C9EB60670EA0}"
      SQLTask:SqlStatementSource="UPDATE T SET Count = Count+1 FROM dbo.Table AS T;" xmlns:SQLTask="www.microsoft.com/sqlserver/dts/tasks/sqltask" />
  </DTS:ObjectData>
</DTS:Executable>

This task uses a "hard coded" statement which is associated with the SQLTask:SqlStatementSource entity.

However, if the Execute SQL Task is driven by a Variable, then you'd see a block of XML as follows. There's no actual query there so you'd have to find the <Variables> collection that define User::MyQueryVariable and then you'd only have the design time value and not the run-time value

  <DTS:ObjectData>
    <SQLTask:SqlTaskData
      SQLTask:Connection="{CEE71D69-1F9F-46FA-A22F-C9EB60670EA0}"
      SQLTask:SqlStmtSourceType="Variable"
      SQLTask:SqlStatementSource="User::MyQueryVariable" xmlns:SQLTask="www.microsoft.com/sqlserver/dts/tasks/sqltask" />
  </DTS:ObjectData>

Finally, for completeness, if the developer had specified File Source, you'd see something like this

  <DTS:ObjectData>
    <SQLTask:SqlTaskData
      SQLTask:Connection="{CEE71D69-1F9F-46FA-A22F-C9EB60670EA0}"
      SQLTask:SqlStmtSourceType="FileConnection"
      SQLTask:SqlStatementSource="FileConnectionSource" xmlns:SQLTask="www.microsoft.com/sqlserver/dts/tasks/sqltask" />
  </DTS:ObjectData>

You would then need to find the definition for FileConnectionSource within the XML as well.

Retrorse