Flat file and extra data column

Question

Post reply

Flat file and extra data column

forum member

Ten Centuries

Points: 1290
More actions
August 29, 2009 at 7:20 am

#141126

Hi,
i would like to import a flat file to a table but while importing data i would like to add extra column to the imported data (in staging area) which contains todays date or file creation date
I started using Data flow task but couldnt move further.please suggest on how can i achieve this
Thanks

Viewing 13 posts - 1 through 12 (of 12 total)

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

dbo.benyos SSCommitted Points: 1944 More actions · Answer 1

Your 15 second solution is not SQL related at all:

download microsoft logparser and run it on your data.

if, for example, your data is in CSV file, you can run the following command:

LogParser "SELECT *, TO_STRING( SYSTEM_TIMESTAMP(), 'yyyy-MM-dd hh:mm:ss.ll' ) AS ImportedOn INTO MyNewTable FROM c:\FlatFile.csv" -i:CSV -o:SQL -server:MyServer -database:MyDatabase -driver:"SQL Server" -username:TestSQLUser -password:TestSQLPassword -createTable:ON -clearTable:ON

Regards

Tal Ben Yosef

Tal Ben Yosef
www.TalBenYosef.com
(visit my LinkedIn profile)

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

benyos (8/29/2009)
Your 15 second solution is not SQL related at all:
download microsoft logparser and run it on your data.
if, for example, your data is in CSV file, you can run the following command:
LogParser "SELECT *, TO_STRING( SYSTEM_TIMESTAMP(), 'yyyy-MM-dd hh:mm:ss.ll' ) AS ImportedOn INTO MyNewTable FROM c:\FlatFile.csv" -i:CSV -o:SQL -server:MyServer -database:MyDatabase -driver:"SQL Server" -username:TestSQLUser -password:TestSQLPassword -createTable:ON -clearTable:ON
Regards
Tal Ben Yosef

Huh? What does that do? The five-minute solution is to add a suitable derived column transformation & set it to the required date using an expression. Then map this new column to the appropriate field in the staging area.

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

dbo.benyos SSCommitted Points: 1944 More actions · Answer 3

I know the SQL way to achieve this.... however, this is a 10 seconds solution for people who know their way around data pumps, like me and you.

I referred to the following line:

"I started using Data flow task but couldnt move further.please suggest on how can i achieve this "

🙂

Tal Ben Yosef

Tal Ben Yosef
www.TalBenYosef.com
(visit my LinkedIn profile)

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

OK, now that I have spent a bit more time on your solution, I can see what it's doing. Apologies for my previous post, but I haven't seen a solution like that before - good to know, thank you!

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

Animal Magic SSChampion Points: 13964 More actions · Answer 5

why not just put an extra column in your destination table and give it a default of getdate(). that way you wont need to change your current SSIS job because no new columns need to be mapped. I can see why the logparser approach will work as i have used it myself to import windows security logs into SQL, however i personally dont think its the best approach to the OP's question.

Feel free to shoot me down tho 🙂

forum member Ten Centuries Points: 1290 More actions · Answer 6

Hi there,

I tried derived column and that worked,thanks very much.i would like to set the flatfile source which is to a dynamic file which has a value of foreach iteration variable

Please suggest on this

Thanks

forum member Ten Centuries Points: 1290 More actions · Answer 7

Hi,

Sorry for sequence of ?'s

I can dynamically map the flat file source value to the variable value but i get metadata error when i run it

[Flat File Source 1 [3441]] Error: The column data for column "Column 35" overflowed the disk I/O buffer.

Thanks for the help

Phil Parkin SSC Guru Points: 246990 More actions · Answer 8

Do all of your flat files have the same schema?

What is in column 35? This is a tough one to answer without knowing a bit more about your 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

forum member Ten Centuries Points: 1290 More actions · Answer 9

Hi,

Thanks for the reply,every file has different schema, some of them straight way work and some them break

current datatype of columns is set DT_STR with a length of 250

I get a error of external metadata column collection is out of synchronization with the output columns

please suggest on this

thanks

John Rowan SSC Guru Points: 56440 More actions · Answer 10

SSIS is very metadata dependant. Each source, destination, and the transformations that you put in between them rely heavily on the metadata that SSIS gets from the source and destination components. Long story short, you can do what you are trying to do with the same source component. You need to set up a different data flow for each file type that you have to import.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Phil Parkin SSC Guru Points: 246990 More actions · Answer 11

John meant can't, not 'can'!

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

John Rowan SSC Guru Points: 56440 More actions · Answer 12

Good catch Phil. You are absolutely correct, I meant can't. Thanks for catching me!

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden