March 24, 2009 at 6:52 am
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]
March 24, 2009 at 8:42 am
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
March 24, 2009 at 9:47 am
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]
March 26, 2009 at 10:30 am
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]
March 26, 2009 at 10:46 am
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?
March 26, 2009 at 11:02 am
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]
March 26, 2009 at 11:32 am
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.
March 27, 2009 at 3:36 am
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]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply