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

Hello SSIS,


Does this article help you any?

http://wiki.sqlis.com/default.aspx/S...nversions.html


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

I still could n't figure it how to change the string datatype from source to
Database datetimestamp data type in destination in SSIS package. It throws
error saying 'conversion fails from DB_STR to output column Date" when i try
to load a flatfile to a sql table.


Please suggest if anybody knows how to get around with this problem.

Swayam



"SSIS 2005 Hangs" wrote:

Hello SSIS,


Did you take a look at this?

http://wiki.sqlis.com/default.aspx/S...nversions.html

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:

Hello SSIS,

Use a Derived Column Transform and follow the article

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