importing csv datetime problems! Please Help

[tonygr]

Hi All,
I am Having Problems Importing a csv file. I have tried importing to an existing table, and import to a newtable.
At the moment it seems to error on date field.
the date data held in the csv is like this:
"01.01.2006 15:45"
The default datetime size is "8" in the existing table, but I cant seem to change this.
p.s. I am a newbie to sql server 2005 so please bare with me.
I can provide the csv for anybody if needed "Weather station data"
Thanks in advance
Tony

 

[mrdenny]

Your problem is that the date time format you are using isn't a recognized format. Import the data in to a text field, then replace the "."s with "/"s. From there you can load it into the production table using the datetime data type.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[MDXer]

Why not just do the replace within the dataflow? saving the overhead of inserting data then updating it.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[mrdenny]

This is true, is can be done within the dataflow as well.

I'm still in the DTS mindset and DTS isn't the greatest in the world for the more advanced stuff.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[tonygr]

Thanks to you both,
I thought it should be possible to transform this in the data flow. But I am a little lost in how to do this.
Any help would be fantasic.
At the moment I am importing the date/time as a text just to get the import to work, this is ok because the data in the table is flushed be fore the next import.
Thanks Again
Tony

 

[MDXer]

you may want to try the replace in your Datasource object and place the Replace function there.


I also am currently still in the DTS mindset as I haven't even begun to move my stuff to SSIS but from what I have seen and read you should be able to something this basic in the dataflow saving the IO overhead of updating.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[tonygr]

Hi All,
Sorry to reserect this thread.
I am still having problem with the date(s) (above mentioned) has anybody got any further, in there knowlege of ssis that can help with this one.
proving to be a real pain.
Thanks Again
Tony

 

[MDXer]

Was reading Kimball's latest book on DW Lifecyclce which focuses on the DW in SQL 05. In the portions where he addresses ETS using SSIS there sounded like a few pieces that might work. One being the Derived Column I believe it was. Sorry in the processes of cleaning my machine at home so I can have a good install of 05 and all the BI goodies.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[gradley]

MDXer and mrdenny are both correct that you should import as a text field and use the Derived Column task to attack this.
Derived column uses the expression language which is part of SSIS and a bit quirky to former DTS users. You can import the date as a text field and through the Derived Column task, perform a string replace and assign the value to a new field defined as DateTime.

BTW - we are all new to Sql Server 2005. I'm probably 2 pages ahead of a few others!!!

Good Luck!