Scrambled Column values in destination csv file

Scrambled Column values in destination csv file

You run the data flow task and feel happy that everything was soooo easy.

You take a little peek inside the created CSV File only to find the below

What is this ? the values in the CSV Destination flat file is all scrambled

Now this happened due to the fact that the destination file is a Comma delimited file. And the UserNames contained commas. So when the destination file was created the comma was seen as a new column thus creating the scramble you see above.

There are 2 ways to fix this

• Method 1 : SQL Script Replace Function &

• Method 2 : Regular expression Scripting Task

So lets look in detail at each of the methods

Method 1 : SQL Script with Replace Function

This is the simple solution and will only work for the removing the comma (Excludes any other funky charters you might have in the source field).

Change the OLE DB Source to QUERY and paste the query as below

That should be it.

The drawback of this method is that it only caters for the comma character – now in a large data set you might sit with a large range of weird characters that could scramble the csv. That is why I recommend Method 2 rather (if you know for a fact it’s only comma’s causing issues – then stick to method 1)

Method 2 : Scripting Task – Regular Expression Pattern

So this one is quite cool it will enable you to remove any non special character in your data that might scramble your csv output.

Add a scripting task.

Edit the scripting component.

Setup the Inputs under Input Columns as below

2 – Select the Column you would like to purge of special characters

3 – Set the UsageType to READWRITE (this is important as you would like to write and read the value in the script)

Go back to the Script Tab

Select Edit Script.

Visual studio will now open in a new page.

Step 1 – Add the regular expression namespace at the top of page

Step 2 – Create a new method

This method uses the regular expression pattern to remove all special charcters and only keep numbers spaces and letters.

Step 3 – Call method

And then call the method associated with the OutputColumn.

Bascially it will push all of the rows from the outputcolumn UserNameChanged through the method and it will remove the special charcters.

Connect it up to the Flat file in the destination as below.

Make sure to map it in the flat file destination task

Run it and you’ll find in the output csv file everything is correct. (columns are intact no scrambling of values)