ansaurus

Question

Convert date from access to SQL Server with SSIS

Answer 1

A:

Looks like you'll need to add a specific conversion to convert this column to the correct type. It's the DT_DBTIMESTAMP that you need to convert to, not DT_DATE. Using a Derived Column Transformation for instance, you could use an expression like:

(DT_DBTIMESTAMP)YourDateColumn

Valentino Vranken 2010-06-15 10:38:47

Thanks for the reply. When I add a derived column tranformation and I cast the column to DT_DBTIMESTAMP I get the error that the conversion from DT_DATE to DT_DBTIMESTAMP is not supported.

Arne 2010-06-15 12:05:04

That's weird. I tried it out by downloading the old Northwind Access DB sample from 1999, converting it to Access 2003, then creating an SSIS package with an OLE DB Source connecting to that Access MDB, retrieving the Orders table (which contains several DT_DATE - or in Access known as date/time - columns), attaching a Derived Column Transform to the OLEDB source, and I could cast to DT_DBDATETIME without any problems.Are you sure that the previous conversion is not causing this error (was it removed)?

Valentino Vranken 2010-06-15 20:53:45

Yes, I removed the previous conversion. Now I have "AccessDB -> Derived Column Transformation -> SQL DB". But the error is shown in the last Box, the SQL DB. Could this be the problem? I created a new table in the db with SSIS using the standard SQL that is created when you click on the new button in the properties of the SQL DB. Which data types did you use for the target DB?

Arne 2010-06-17 19:18:07

Here's the statement as it was generated in my case:CREATE TABLE [dbo].[AccessImportTest]( [OrderID] [int] NULL, [EmployeeID] [int] NULL, [OrderDate] [datetime] NULL, [RequiredDate] [datetime] NULL, [ShippedDate] [datetime] NULL, [ShipVia] [int] NULL, [ShipAddress] [nvarchar](60) NULL, [Derived Column 1] [datetime] NULL) ON [PRIMARY](removed some unrelated fields to make it fit in a comment)That last field is the one added by the Derived Column Transform, so a DT_DBTIMESTAMP. The other 3 datetime fields are DT_DATE in the flow (ctd next comment).

Valentino Vranken 2010-06-18 16:11:57

Funny enough, I didn't have any issue inserting the DT_DATE columns in a datetime field in the SQL DB.This is my data flow: OLE DB Source (connecting to Access) > Derived Column Transformation > OLE DB Destination (connecting to SQL2008)Just to be complete, these are the full names of the providers in the connection managers:Source: Microsoft Office 12.0 Access Database Engine OLE DB ProviderDestination: SQL Server Native Client 10.0

Valentino Vranken 2010-06-18 16:13:13

If that doesn't help: have you got SP1 installed on your client?

Valentino Vranken 2010-06-18 16:23:12

Answer 2

A:

You use another Provider on the source side. I used the JET provider. I am doing this on a computer where no Access is installed. When I try to create a data connection using the Office 12.0 Access Database Engine OLE DB Provider I get an error message that the workgroup information file is missing. I get this message on the connection test.

The question is if the conversion depends on the db driver you use to get the data. I cannot install Access on the server I am working, so I cannot try it. Could this be the cause of the problem?

Arne 2010-06-18 16:51:51

oops, I cannot answer your comment any more ...I have version 10.0.1600.22 and the SP1 version on the MS site says 10.00.2531.00, so I guess I dont have it.

Arne 2010-06-18 16:58:41

Valentino Vranken 2010-06-21 19:50:43

ansaurus

tags:

views:

answers:

Convert date from access to SQL Server with SSIS

related questions