BIDS 2008 - can i insert a column into a comma-delimited flat file after it has been written to?

Question

BIDS 2008 - can i insert a column into a comma-delimited flat file after it has been written to?

VoldemarG

Hall of Fame

Points: 3883
More actions
August 16, 2018 at 7:35 pm

#360437

Is it possible?
If my file has 50 columns that had been written to it by a Select Statement in a data flow in one of the previous tasks in the package
can i (possibly using script task ? or what else and how?) can I insert a column between column 13 and column 14 in the text file that is comma-delimited? the new column will have the same value for all rows = 'Hello, column'
Thanks..
Likes to play Chess

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99133 More actions · Answer 1

You could probably achieve this with a Script task, yes; you could certainly do it with a Script Transformation (but those were introduced with SSDT 2012). Your post, however, seems that you are simply suggesting that you want to add the same value to end of every row though, is that correct? If that's the case, then this would be quite the simple task for a Script; at the end of the day a CSV is simply a text file so if you treat it like you you can easily append more text to the end of each line. Have you tried anything at all so far? If you can post your code then we can see where you might have gone wrong, or push you in the right direction.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Phil Parkin SSC Guru Points: 246985 More actions · Answer 2

VoldemarG - Thursday, August 16, 2018 7:35 PM
Is it possible?
If my file has 50 columns that had been written to it by a Select Statement in a data flow in one of the previous tasks in the package
can i (possibly using script task ? or what else and how?) can I insert a column between column 13 and column 14 in the text file that is comma-delimited? the new column will have the same value for all rows = 'Hello, column'
Thanks..

It is possible, but not without rewriting the file. I think I'd probably use Powershell, given that the new column will be the same throughout.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Why not just make sure that the full monty of data is present to begin with instead of writing out a file, then reading it back in, adding a column to it, and then writing it out again?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246985 More actions · Answer 4

Jeff Moden - Friday, August 17, 2018 8:57 AM
Why not just make sure that the full monty of data is present to begin with instead of writing out a file, then reading it back in, adding a column to it, and then writing it out again?

I am hoping/assuming that there's a very good reason ...!

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

drew.allen SSC Guru Points: 76997 More actions · Answer 5

Phil Parkin - Friday, August 17, 2018 9:00 AM
Jeff Moden - Friday, August 17, 2018 8:57 AM
Why not just make sure that the full monty of data is present to begin with instead of writing out a file, then reading it back in, adding a column to it, and then writing it out again?
I am hoping/assuming that there's a very good reason ...!

I'm sure there's a reason, I just doubt that it's good.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA