Introduction
According to Microsoft, DTSX is an XML-based file format that stores the instructions for the processing of a data flow from its points of origin to its points of destination, including transformations and optional processing steps between the origin and destination points. In a nutshell, when you are creating your SSIS package, SQL Server Data Tools SSIS Designer creates and saves all the data into an XML. As you probably know, XML files in general are not designed for human consumption (anyone that opened and tried to read an XML file can attest to that), so is there any good use case scenario? This blog post is going to discuss the some bad and good of the DTSX file.
Since we are already know how DTSX file is being created, let’s see how you can see/read it. You can accomplish that by right-clicking on your package and selecting view code (Fig #1).
As you will quickly realize by looking at the XML, the text inside the file is mostly unreadable and only the computer can convert text in A into a visual representation in B (Fig #2).
So far, we have covered the downside of the DTSX XML file. Let’s do the opposite. Let’s find something useful that we can do with that XML file.
Problem
One of the most basic problems with SSIS Designer UX is trying to consume text files (a.k.a. flat-files) and hitting the Microsoft data type defaults wall. Unfortunately, Microsoft decided that for flat files, the column width or the max number of characters would be 50 (Fig #3). While in some cases 50 characters would be enough, in most cases 50 characters would need a bump. To fit the extra text, you will need to increase the number of allowed characters to prevent the truncation error, an error that will occur when SSIS run-time engine will attempt to pull the data from a text file into a output buffer that is too small.
Fixing truncation error for all 3 columns will require you to go column-by-column and change 50 to something bigger which is a bit painful, but still manageable. What about 50 columns? What if you need to change 50 characters limit on 50 columns? Now that becomes much more painful and not really manageable.
Solution
The solution is to leverage the DTSX file. While that file contains plenty of unreadable gibberish tags which helps the computer, it does have some text that we humans can read and use. We can use the text editor to find and replace specific text. Specifically, we can target DTS:MaximumWidth=50 (DTS:MaximumWidth tag defines the max number of characters for a column) and replace it with 250. Now all we have to do is to hit Ctrl+F for a popup Find and Replace window, type DTS:MaximumWidth=50 into a find textbox, type DTS:MaximumWidth=250 into a replace textbox (Fig #4), hit Replace All , and hit X to close XML editor while saving the changes. It’s all that simple.
Disclaimer
This blog post is partially based on the following resources:
DataSteve 