How do I prefix a constant to all the rows in a flat file from within an SSIS package?

Question

I have a SSIS package with an Execute Process Task on Control Flow tab. I am calling notepad.exe within the task to read a text file. I am trying to replace \r\n with \r\n\test so that the text test is prefixed on the first column of all the rows. Thie does not yield the correct results.

Text file data:

Line 1{CR}{LF}
Line 2{CR}{LF}
Line 3

Expected output:

TestLine 1{CR}{LF}
TestLine 2{CR}{LF}
TestLine 3

I am unable to achieve this output using notepad.exe using Execute Process Task. How can I achieve this output using Execute Process Task? Are there any other tasks in SSIS that can do this?

Answer 1

Execute Process Task:

Execute Process Task is not the appropriate task for what you are trying to do. You could Script Task to read the file contents and prefix the lines with a text of your choice or you could use the Data Flow Task to achieve the same through configuration without any coding.

SSIS 2012 package illustrating an example:

Below package created using SSIS 2012 illustrates how to read the contents of a text file and then prefix each line with a text phrase to write the output to a different flat file.

Let's assume that you need to read a file named Products.txt that has the following information with each line ending with carriage return and line feed characters {CR}{LF}

Create an SSIS package named SO_15213317.dtsx. This sample uses SQL Server Data Tools (SSDT) 2012 to create the package but the logic in this example is applicable to older SSIS versions 2005 through 2008 R2.

Drag and drop a Data Flow Task onto the Control Flow tab. Right-click on the Connection Manager tab at the bottom of the package and click New Flat File Connection.... This connection manager will be used to read the source file.

On the Flat File Connection Manager Editor, perform the following steps:

• Set the Name to FILE_Products
• Set the description to Read the products information from flat file.
• Browse to the location where the file is located and select the file.
• Rest of the properties will be inherited based on the data in the file.
• Check the box Column names in the first data row if there are column headers in the file
• Click Columns page.

Leave the Columns tab with default settings and click Advanced page.

Advanced page will automatically configure the column name based on the header information and SSIS will make a good guess about the data type too. In this case, it has decided that the column data type is string.

Click the Preview tab to simply look the file data. Click OK to close the Flat File Connection Manager Editor.

Double-click the Data Flow Task on the Control Flow tab to switch to the Data Flow tab. On the Data Flow Task, drag and drop a Flat File Source to read the source file. Configure the Flat File Source as shown below.

The columns will automatically configure. Click OK to close the Flat File Source Editor.

To prefix the text Test on all the rows, you need to make use of a Derived Column Transformation. Drag and drop a Derived Column Transformation after the Flat File Source. Connect the Flat File Source to the Derived Column Transformation. You can add a new column or replace an existing column. The example adds a new column with the following expression. This expression prefixes the text Test to the incoming values of column ProductName and then type casts them to SSIS data type DT_WSTR of length 54 characters.

(DT_WSTR,54)("Test" + ProductName)

Drag and drop a Flat File Destination to write the output to another text file. On the Flat File Destination Editor, click New... button to create a new Flat File Connection Manager for destination file.

Select Delimited on Flat File Format dialog and click OK.

On the Flat File Connection Manager Editor, perform the following steps:

• Set the Name to FILE_NewProducts
• Set the description to Write the data to the new product flat file..
• Browse to the location where the file should be saved and give a name to the file.
• Configure rest of the properties as per requirements.
• Check the box Column names in the first data row if you need column headers to be written to the file
• Click Columns page.

Configure the Columns page with Row and Column Delimiter.

On the Advanced page, click the column ProductName column and click Delete. You can name the column however you would like to.

Only the column NewProductName will remain on the destination connection manager. Click OK.

On the Flat File Destination Editor, make sure to select the correct flat file connection manager.

Click the Mappings page to configure the column mappings to write the data.

After the Data Flow Tasks are configured, the package should look as shown below.

Execute the package to read the source file and generate the new output file with text prefix.

The newly generated file NewProducts.txt will contain the prefix text phrase Test

Other options:

There are various different ways to read a file and you can also read a file using a Script Task. However, the example here illustrates an appropriate way to read text files in SSIS packages. This should give you an idea about how to design SSIS packages dealing with flat files or delimited files in future.