SSIS tip: converting datetime
Alright, so I’m going to try to update this more often. I feel there’s a lot I could post about from my work, but I just haven’t made time. Time to change that!
Today I’m importing data from a MS Access .mdb file to SQL Server. The one-off way to do this would be SQL Server’s Import and Export Data wizard, but I want to automate it–for this particular task, 28 files monthly.
I would prefer to script it using
OPENDATASOURCE, but for some reason I can’t get either of these to work in my current environment–maybe due to a mismatch between 32-bit Office and 64-bit Windows Server.
This is where SQL Server Integration Services come in, so that I can script it graphically. We start a new project, and are met with the Control Flow screen. We want to go to the Toolbox and insert a Data Flow Task:
Double-clicking that Data Flow Task will take us into the Data Flow tab. Now the Toolbox shows Data Flow Sources and Destinations. Drag in an OLE DB Source and a SQL Server Destination:
The X’s are because we haven’t defined these elements yet. Double-click on each to set the Source and Destination information. (I’ve already created the destination table on SQL Server previously; the Import/Export wizard can be helpful for getting the right columns and column definitions, and then you can clone the table by right-clicking > Script Table As > CREATE to….) So go ahead and define these. Click and drag the green arrow to the destination box to connect them.
Here is where I hit a snag: I still have an error on the destination box, and when I mouse over it, I see the following error: “The column <columnname> can’t be inserted because the conversion between types DT_DATE and DT_DBTIM…”
I do have a datetime column in my source, as well as one in my destination. But SSIS uses its own datatypes, and for some reason it can’t convert Access -> SSIS -> SQL Server on the fly.
(As a side note, I find it a little ironic that SSIS has such trouble with this, considering these are all MS products, and that SQL Server does a great deal of implicit casting….)
I was stumped for a few minutes. But this very useful table provided a valuable clue. I didn’t have the full error message, but I could see that SSIS was reading Access’s datetime as DT_DATE, and needed to convert it to DT_DBTIMESTAMP for SQL Server. Poking around in the toolbox, in the Data Flow Transformations section I found the Data Conversion and Derived Column tasks. (This stackoverflow post also nudged me in the right direction.) I tried Data Conversion first, and couldn’t quite get it to work, but Derived Column did the trick. The idea is to create a temporary, computed column based on the datettime column in question, and then in our Destination task, map that derived column to the destination datettime column. We don’t need to adjust the number of columns in our destination, because we’ll discard the old DT_DATE that we couldn’t use.
So we’ll want to delete the green data flow arrow, and drag in a Derived Column task. Pull the data flow arrow into the Derived Column box, then double-click it to define it. The new dialog lets you drag and drop, though you’ll need to type in a Derived Column Name. In the right pane, I navigate to the Type Casts folder and find (DT_DBTIMESTAMP), and drag it into the Expression field. Then in the left pane, I find the column I want to transform, and drag it after the DT_DBTIMESTAMP function:
I leave the <add as new column> option, although you can choose to replace your original column.
Now in the destination task, I’ll need to adjust my mappings. You may need to delete the destination task and just create a new one. Connect your data flow arrow, then double-click the destination box to define it. After setting the destination table, go to Mappings in the left pane.
By default, SSIS will want to map based on name. But we need to delete the problematic mapping, and instead assign the derived column to the target datetime. Find the mapping in question, and click the black arrow to select it:
And of course, after importing, don’t forget to check for data integrity.