January 5, 2008 at 6:32 pm
Hi,
I'm having the darndest time trying to upgrade an old SQL 2000 DTS to SQL 2005 SSIS. I first tried just running the DTS Migration Wizard, but the end result wasn't schedulable within SQL Server Agent. It kept throwing an error on execution. I can run the job manually from BIDS, but that has me getting up early every morning to run the thing before the client gets in the office.
In trying to re-create the job in SSIS, I'm trying to build Data Flow Tasks to pull data from Mas90 tables and write the data to SQL Server tables (Mas90 is an accounting software package that exposes an ODBC interface). When I execute one of these tasks, however, for all the DateTime columns I get an error like the following:
The column "DateLastActivity" can't be inserted because the conversion between types DT_DBDATE and DT_DBTIMESTAMP is not supported.
But the weird thing is that there aren't any Timestamp columns in either the source or the destination. They're all DateTimes.
Any Suggestions?
Thanks!
January 17, 2008 at 12:27 pm
In the SSIS world, the DT_DBTIMESTAMP is a SQL DATETIME. They are one in the same.
Have you tried a data conversion task (between the data source and the destination)? Some data type transforms aren't allowed implicitly - but you can explicitly do it.
hth,
Dan
January 17, 2008 at 1:43 pm
Hi,
the problem that you could not schedule the package sounds like a security problem. If you want to access the odbc connection using a user and a password you have to change the package security mode to save sensitive data.
Then you usually have to encrypt your package giving it a password or similar possibilities.
The problem with the datatypes you could solve by using the data conversion task and converting it to DBTimestamp.
SK
January 18, 2008 at 1:57 pm
Hi
Every DBMS has its own datetime definition
I supposes that SSIS gets back the information of the field DATETIME of MAS90 as DBTimestamp
At any rate you can solve the problem by using the DATA CONVERSION component
of DATAFLOW pane
:hehe:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply