RE: SSIS datetime conversion Error

Hello SSIS,

Use a Derived Column Transform and follow the article

Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

yes I read. but That doesn't help as i am just using the current
year.

The source data is ' 20060425' for all the rows. if i change the data
type to date time stamp in advance page of Flat file source task, the
source task fails in the execution, if i add a data conversion step
beween flat file task and oledb destination task then the conversion
task fails. With this problem of not being able to load the flat file
to table which is a common and frequent task we do and the SSIS
hanging problem, which frequently hangs, we are kind of in a direction
of not going for 2005 until we find some more service pack releases.

Thanks for your help
Swayam
"SSIS 2005 Hangs" wrote:

Hi,

I have a package which load a flat file to a SQL server table and
runs fine in SQL 2000. There is a column in the flatfile "orderdate"
which has the data like '20060423', I load this column data to the
'Orderdate' column of the sql table which is the datatype 'datetime'
. The sql 2000 transformation automatically convert the data
'20060423' to 2006-04-23 :00:00:000' in the table which solves my
purpose.

Now i tried to redesign this simple task in SSIS. the package
execution fails.
error" The value could not be converted because of potential loss of
data".
I notice there are three choice of datetime in the source file.
1- database date
2- date
3- database timestamp
I changed the input data type of this source column to all the 3
above
choices but nothing worked. By default it takes DB_STR type, I added
a
conversion step in the transformation to change the datatype of the
source
column to datetime, nothing worked. I am wondering is not possible to
change
the string to datetime datatype in SQL 2005.
I just want to load a flat file , thats it, i don't want to get into
manual
datatype changes in the transformation. So i tried the bulk insert
also .
That also failed saying can't convert string data to datetime.
Please suggest.

Thanks
Swayam


.

Relevant Pages

  • SSIS datetime conversion Error
    ... I have a package which load a flat file to a SQL server table and runs fine ... I notice there are three choice of datetime in the source file. ...
    (microsoft.public.sqlserver.dts)
  • Re: SSIS datetime conversion Error
    ... I have a package which load a flat file to a SQL server table and runs ... 'Orderdate' column of the sql table which is the datatype 'datetime'. ...
    (microsoft.public.sqlserver.dts)
  • Transformation findet *WO* statt?
    ... Feldtyp hier als DateTime werden sollte. ... Dabei erkennt die Transformation alle Felder auser dem Datumsfeld ... welches implizierte Konvertierungen ausführt (SQL nach SQL) mittels ...
    (microsoft.public.de.sqlserver)
  • Re: Trouble getting DTS to properly format Excel file
    ... the datetime from SQL and gives me text in Excel. ... wanted is to take the integer portion of the datetime SQL ... >> I see now that using a query before the transformation ...
    (microsoft.public.sqlserver.dts)
  • Re: chooses not to generate code at all
    ... representation, then SQL is one Transformation ... Well, to start with, SQL is not the model, but relational algebra is, ... meta-model that provides the specific mappings necessary for the ... even constitute a "model" in MDA terms? ...
    (comp.object)
Quantcast