CODEWITHSUNDEEP
sql-server-2008csvssis
seadrag0n
seadrag0n

Reputation: 848

Modify the CSV file to add addtional comma using script task in SSIS

I have a CSV file in the following format

"Sometext","Sometext","Sometext","Sometext","Sometext","Sometext","Sometext",
"Sometext","Sometext","Sometext","Sometext","Sometext","Sometext","Sometext",
"Sometext","Sometext","Sometext","Sometext","Sometext","Sometext","Sometext",
"300","331"
"Sometext","Sometext","Sometext","Sometext","Sometext","Sometext","Sometext",
"Sometext","Sometext","Sometext","Sometext","Sometext","Sometext","Sometext",
"Sometext","Sometext","Sometext","Sometext","Sometext","Sometext","Sometext",
"Sometext","Sometext","Sometext","Sometext","Sometext","Sometext","Sometext",
"700","701

In the above data, I want to add additional comma's after the numeric values in the 4th and the last row after the file is read using the Flat File Source, in this case 6 additional comma's should be added in the 4th and the last row so the correct data would be like this

"Sometext","Sometext","Sometext","Sometext","Sometext","Sometext","Sometext",
"Sometext","Sometext","Sometext","Sometext","Sometext","Sometext","Sometext",
"Sometext","Sometext","Sometext","Sometext","Sometext","Sometext","Sometext",
"300","331",,,,,,
"Sometext","Sometext","Sometext","Sometext","Sometext","Sometext","Sometext",
"Sometext","Sometext","Sometext","Sometext","Sometext","Sometext","Sometext",
"Sometext","Sometext","Sometext","Sometext","Sometext","Sometext","Sometext",
"Sometext","Sometext","Sometext","Sometext","Sometext","Sometext","Sometext",
"700","701,,,,,,

I have to add the comma's in the dataset only without modifying the existing file How can this be achieved in SSIS?

Upvotes: 1

Views: 2061

Answers (1)

Milen Kindekov
Milen Kindekov

Reputation: 1973

One way to go would be to use a Script Component set as your source in your Data Flow Task. Find a detailed example in the following article on what it should include:

http://beyondrelational.com/modules/2/blogs/106/posts/11128/ssis-script-component-add-missing-column-or-ignore-extra-columns.aspx

This would add missing columns, as well as ignore any extra ones depending on your first header row.

Upvotes: 0

Related Questions