MS ACCESS Date fields to SQL 2005 Date fields in SSIS
-
Hello,
I took an SSIS Class 1 year ago, but didn't have a project I could work on it with until this past week (figures right?). Anyhow, I am a newbie using SSIS and I am trying to prove it's use for certain ETL functions within my project. Currently our ETL Team uses Informatica for all ETL functions, but Informatica charges extra to connect to MS ACCESS dbs, and we don't pay for that. So I want to start using SSIS to do this type of work and save money, with the goal to drop Informatica since it will save us about $70K.
Anyhow, my package is very simple, monthly we need to import an access table into our staging area, truncating the table first. I have all of those pieces in place, but SSIS keeps giving me the error about converting the MS ACCESS dates (Error3Validation error. Import dtsxpackagename: SQL Server Destination [88]: The column "usage_start_time" can't be inserted because the conversion between types DT_DATE and DT_DBTIMESTAMP is not supported.). I have tried doing Data conversions, but still couldn't stumble onto the right method. Then I tried a derived column but again couldn't seem to make the right connections here to get this conversion to work.
If I let the import wizard do this work for me, the table imports correctly with the date fields. I have saved the SSIS package I created to do this import on my local text instance, but as of yet have not figured out this correct way to do this import.
Any help is appreciated, the more simple the better.
Thanks!
-
Oh one more point. My input is an OLE DB source, and my destination is a SQL Server destination. My import version had these both as OLE DB and I think that is why it is working. But I want to do more then just make it work, I want to learn about the right way to do this.
-
jweyl (4/9/2009)
Hello,I took an SSIS Class 1 year ago, but didn't have a project I could work on it with until this past week (figures right?). Anyhow, I am a newbie using SSIS and I am trying to prove it's use for certain ETL functions within my project. Currently our ETL Team uses Informatica for all ETL functions, but Informatica charges extra to connect to MS ACCESS dbs, and we don't pay for that. So I want to start using SSIS to do this type of work and save money, with the goal to drop Informatica since it will save us about $70K.
Anyhow, my package is very simple, monthly we need to import an access table into our staging area, truncating the table first. I have all of those pieces in place, but SSIS keeps giving me the error about converting the MS ACCESS dates (Error3Validation error. Import dtsxpackagename: SQL Server Destination [88]: The column "usage_start_time" can't be inserted because the conversion between types DT_DATE and DT_DBTIMESTAMP is not supported.). I have tried doing Data conversions, but still couldn't stumble onto the right method. Then I tried a derived column but again couldn't seem to make the right connections here to get this conversion to work.
If I let the import wizard do this work for me, the table imports correctly with the date fields. I have saved the SSIS package I created to do this import on my local text instance, but as of yet have not figured out this correct way to do this import.
Any help is appreciated, the more simple the better.
Thanks!
Welcome to SSIS! These Informatica people are outrageous 😉
Check this post. Also make sure you check the posted comments. There are comments with solution in it.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply