Flat File Source using a Flat File Connection

Question

Flat File Source using a Flat File Connection

rew-370421

Hall of Fame

Points: 3640
More actions
February 10, 2010 at 11:09 am

#137139

I've made a Flat File Source (test.txt file) using a Flat File Connection. This TXT file contains one string value. I've assigned a COLUMN NAME in the FLAT FILE SOURCE.
Now, how do I get that value in the COLUMN NAME? Do I create a VARIABLE for it and if so, how. What is the next "task" in my data flow to enable me to use that data?
I need to read that text in that Flat File and then INSERT it (with some other data) into a database table.
thanks in advance

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

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

Phil Parkin SSC Guru Points: 246986 More actions · Answer 1

Create a Dataflow. In the Dataflow pane, create source and destination components and link them. Create a mapping from source to destination and then you can run the package.

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

rew-370421 Hall of Fame Points: 3640 More actions · Answer 2

Thanks, Phil. I did that.

I'm getting error

"Error1Validation error. Data Flow Task: SQL Server Destination 1 [1814]: Columns "InputFileName" and "FileName" cannot convert between unicode and non-unicode string data types."

1) I've Googled for solutions to that and everything I see says to put a DATA CONVERSION TRANSFORMATION between the Source & Destination. Is that correct?

2) Also, within the DATA FLOW, where can I write my INSERT statment in order to use the InputFileName from my Flat file, along with some other info, and then INSERT it into my TABLE?

Phil Parkin SSC Guru Points: 246986 More actions · Answer 3

1) Yes

2) Add a Derived Column transformation between your source and destination and assign these items to new columns which can then be used further along your dataflow to INSERT to your destination along with the other data.

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

rew-370421 Hall of Fame Points: 3640 More actions · Answer 4

sorry, can you point me to a web example of DERIVED COLUMN? I'm looking and playing around with it. IT, along with all the other "tasks" in BIDS have a lot of configurations! It's very confusing to someone whose never used it (like me!)

I'm looking at stuff like this and can not see where I can build a QUERY with a INSERT?

http://technet.microsoft.com/en-us/library/ms141719.aspx

Phil Parkin SSC Guru Points: 246986 More actions · Answer 5

The Derived Column transform is not a place for building queries - it is a place for adding new columns to your dataflow which can then be used later to fire into your destination.

Trial and error and BOL will get you there fairly quickly - for simple dataflows, SSIS isn't that tough, once you get into a certain mindset.

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