Importing into an existing CSV file

Question

Importing into an existing CSV file

Omoba

SSCommitted

Points: 1910
More actions
March 24, 2009 at 6:52 am

#128828

Hi All,
i'm really new to using SSIS and so please bear with me, if i ask questions that you consider silly.
i'm trying to import a selection of columns into an existing CSV file (which has column headings). the first problem is having SSIS recognize the column headings as i'd like the contents of the file overwritten daily (in the mappings tab, the available destination columns do not show the column names), i've also had a bit of a challenge preserving the formatting of the datetime columns when data is written into it.
any help would be greatly appreciated.
Cheers
------------------------------------------------------------------------
All it takes, is a step in the right direction, your feet will manage to find the way. I didn't say it'll be easy!!![font="Comic Sans MS"]:cool:[/font]

Viewing 8 posts - 1 through 7 (of 7 total)

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

Dave23 Hall of Fame Points: 3903 More actions · Answer 1

I don't know if it's applicable in your environment, but when I needed to create a CSV out file, I ended up keeping a static header file, writing out my data into a headerless CSV file, then concatenating them together via a batch file command launched from an Execute Process task in SSIS:

COPY ProviderHeader.csv + ProvTemp.csv Provider.csv

Omoba SSCommitted Points: 1910 More actions · Answer 2

Thanks D Smith,

i'll try that and i hope it works for me.

thanks a lot!

------------------------------------------------------------------------
All it takes, is a step in the right direction, your feet will manage to find the way. I didn't say it'll be easy!!![font="Comic Sans MS"]:cool:[/font]

Omoba SSCommitted Points: 1910 More actions · Answer 3

thanks D Smith, it worked perfectly!

the other part of my question remains, can anyone help me with how to format the datetime field within the package?

------------------------------------------------------------------------
All it takes, is a step in the right direction, your feet will manage to find the way. I didn't say it'll be easy!!![font="Comic Sans MS"]:cool:[/font]

Dave23 Hall of Fame Points: 3903 More actions · Answer 4

All of my ETL work is going in to SQL Server tables from various flat and CSV files, but a sample of one of my date format statements from a Derived Column transform is:

LEN(TRIM(DOB)) == 0 ? NULL(DT_DATE) : (DT_DATE)(SUBSTRING(DOB,1,2) + "/" + SUBSTRING(DOB,3,2) + "/" + SUBSTRING(DOB,5,4))

I'm guessing since you're writing to a CSV that you'd want a string output type. What are the source and target formats that you're dealing with?

Omoba SSCommitted Points: 1910 More actions · Answer 5

i 'm trying to get the data out in a date time format, the source is also a datetime. however, when it gets into the csv file it's formatted as a string of numbers.

------------------------------------------------------------------------
All it takes, is a step in the right direction, your feet will manage to find the way. I didn't say it'll be easy!!![font="Comic Sans MS"]:cool:[/font]

Dave23 Hall of Fame Points: 3903 More actions · Answer 6

Whenever I'm working with ASCII text files, I always use string data types regardless of what "type" the field is, whether date, numeric, etc, since as long as it's plain text, it's really just a string of characters.

A DT_DATE is internally represented as an 8-byte floating point number, and a DT_DBTIMESTAMP is a structure, so neither play nicely with being implicitly converted to a textual representation of a date/time.

Since it's going out into a CSV, you might try using a string output data type, and use substrings and concatentation to force the format you want.

Omoba SSCommitted Points: 1910 More actions · Answer 7

great! thanks again, i really appreciate your help.

------------------------------------------------------------------------
All it takes, is a step in the right direction, your feet will manage to find the way. I didn't say it'll be easy!!![font="Comic Sans MS"]:cool:[/font]