question

ChristopherHardage-9100 avatar image
0 Votes"
ChristopherHardage-9100 asked Monalv-msft edited

Where Can I Find DTDs for SSIS Artifacts

Does Microsoft make the DTDs for SSIS artifact files available for viewing/download, and if so, where? Specifically I am seeking DTDs for:

  • @Project.manifest

  • *.conmgr

  • *.dtsx

  • *.params

Context: I am working on a .NET Core application that pulls specific information from these files for further analysis (for example, extracting SQL for review). Having complete DTDs for these file types would allow me to generate XML serialization classes so I can pull information more easily from the files. Alternatively, if a serialization library already exists for these XML schemas, I would gladly use that instead.

What I have tried: I have attempted the following and found the results problematic.

Using LINQ to query the XML: My first attempts at this utilized LINQ to query the XML for the data elements I need to pull. I found this error-prone due to a lack of a complete specification for where I can find the elements I need. For example, attempting to gather the SQL used in a Data Flow task requires searching in the task for a command, a table name value, or a variable name. Each Task type can handle this information slightly differently which requires probing the XML until the required information is found. Deserializing the XML would make this process easier to code and easier to read/maintain.

Using the Microsoft.SqlServer.Dts libraries: My next attempts used the SSIS libraries (like Microsoft.SqlServer.ManagedDTS). This presented several problems. First, it restricted the product I am building to .NET Framework since these libraries are not available in .NET Core form and do not necessarily adhere to the latest .NET Standard implementation. Second, there is a great deal of overhead that I do not require. A specific example is an Execute SQL task where a parameter did not have a default value and could not be bound which prevented loading the package at all.

Creating XSDs for each type: I turned next to using xsd.exe to generate XSDs for each file type, then used xsd.exe to generate C# classes from those XSDs. Unfortunately, I do not have examples of every widget. For example, a given DTSX will not contain an example of every possible task/data flow that can be included in an SSIS package. This results in incomplete XSDs which then result in incomplete class definitions.

Using Paste Special > Paste XML as Classes in Visual Studio: This suffers from a similar problem as the previous. Depending on which exemplar I choose, I get an incomplete set of classes. Further, the process regularly results in code that is correct for that specific file but but not for others. For example, a DTSX with one connection manager results in a field for a connection manager instead of a field that is an array of connection manager instances.

Google: I have googled various ways attempting to find these DTDs. The results invariably lead me to how to use DTDs within an SSIS package to assist in reading, writing, or loading data from an XML file.

I provide this list to illustrate what I have tried without or with little success. I am seeking either a set of libraries that can be used to deserialize the SSIS XML or the complete DTDs so I can generate the necessary classes for this deserialization. Thanks for any assistance you can provide!




dotnet-csharpsql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ChristopherHardage-9100 avatar image
0 Votes"
ChristopherHardage-9100 answered YitzhakKhabinsky-0887 commented

After extensive googling and a large number of false leads, I located the XSDs for SSIS projects and packages here:

SQL Server File Formats Technical Documents


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

👍
Great discovery.

1 Vote 1 ·
YitzhakKhabinsky-0887 avatar image
1 Vote"
YitzhakKhabinsky-0887 answered ChristopherHardage-9100 commented

Hi @ChristopherHardage-9100,

You are asking a great question.

Overall, in XML technology stack, DTDs were replaces by XML Schema Definitions (XSDs) in 2001.
Microsoft never published officail XSDs for .dtsx files.

I personally query SSIS .dtsx files directly from the file system in SSMS by using XQuery in T-SQL. If you will search the old MSDN forums, you might find me answering questions how to query .dtsx files. And it is always try-and-see exercise.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for the response, Yitzhak! I have used XQuery before but had not thought about it in this case. I have the XML files stored locally in our code repository and connecting to the database to load the XML and query it strikes me as being a bit costly in network traffic and time. I suspect I could also query the SSIS Catalog on the server to get the information I seek, but the idea is to compile information about the code that is actually in the repository.

That being said, you do offer an interesting option. I will keep it in mind should I need it! Once again, thanks!

0 Votes 0 ·
Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft edited

Hi @ChristopherHardage-9100 ,

We can set SSIS logging and choose Provider type as SSIS log provider for XML files in ssis package.

SQL Server Integration Services includes log providers that you can use to implement logging in packages, containers, and tasks. With logging, you can capture run-time information about a package, helping you audit and troubleshoot a package every time it is run. For example, a log can capture the name of the operator who ran the package and the time the package began and finished.

Best regards,
Mona


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.




· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Mona,

Thank you for your response, but I do not see how this allows me to deserialize the @Project.manifest, .conmgr, and .dtsx files so that I can pull the specific information I need from them. I provided the example of SQL but that is not the extent of what is required. The idea is to harvest metadata about the packages so that we can perform static analysis to ensure compliance with our standards, assist code reviews, and most importantly, perform impact analysis of proposed changes to the application for which the SSIS packages perform ETL.

I'm afraid runtime logging does not help me very much.

0 Votes 0 ·