Can you delimit rows and columns by pipe in SSIS?

user_18153430 0
Viewed 37

From SSIS, I need to send an Excel file in the format of;

656265 | 56280212 ||
654581 | 56246894 ||
656111 | 56281475 ||

I currently have a package that generates an Excel file to be displayed as;

656265 | 56280212 
654581 | 56246894 
656111 | 56281475

as I have set the column delimiter as a Vertical bar (pipe) on the flat file connection manager.

How would I achieve attaching the 2 pipes to delimit the rows as SSIS does not allow you to set the same row and column delimiter?

flat-file etl ssis excel sql-server
2 Answers

You should open the flat file connection manager, go to the "Advanced" Tab and add two columns manually at the end of the file by pressing the "New" button:

enter image description here

Add a new column by clicking New. By default, the New button adds a new column at the end of the list.

Reference:

user_70312300

One of the solutions by Using "Script Task" and read the file data then edit it as below example:

 using System;

public class HelloWorld
{
    public static void Main(string[] args)
    {
      //string str = File.ReadAllText("textFilePath");  
        string str =@"656265 | 56280212 
654581 | 56246894 
656111 | 56281475";


str= str.Replace(Environment.NewLine,"||" + Environment.NewLine) +" ||";

        Console.WriteLine (str);
    }
}

Output

656265 | 56280212 ||
654581 | 56246894 ||
656111 | 56281475 ||
user_87941480
Related