Importing Datetime data into SQL Server Warehouse using BCP

Question

I receive a text file each day that looks like the following:

128CAFEE-38B0-5A6A-8C21-E2BDA1E57FE4    353386068937487 10320   2019-06-20T23:56:14Z
128CAFEE-38B0-5A6A-8C21-E2BDA1E57FE4    353386068937487 10320   2019-06-20T23:56:14Z
128CAFEE-38B0-5A6A-8C21-E2BDA1E57FE4    353386068937487 10320   2019-06-20T23:56:14Z

I am trying to import this into an Azure SQL Server Warehouse using BCP.

The definition of the table being loaded into is as follows:

CREATE TABLE
 mytable
(var1 VARCHAR(36) NOT NULL, 
 var2 BIGINT NOT NULL, 
 var3 INTEGER NOT NULL, 
 var4 DATETIME2 NOT NULL)

I am using a fmt file that looks like this:

13.0
4
1       SQLCHAR             0       36      "\t"   1     var1                                         SQL_Latin1_General_CP1_CI_AS
2       SQLBIT              0       1       "\t"   2     var2                                         ""
3       SQLINT              0       4       "\t"   3     var3                                         ""
4       SQLDATETIME         0       8       "\n"   4     var4                                         ""

I am having problems with the datetime element of this - I get the following error:

Error = [Microsoft][ODBC Driver 13 for SQL Server]Invalid date format

Is there a way to define what the datetime informat is, i.e. can I give BCP a YYYY-mm-DD\THH:MM:SS style string so that it can interpret my date properly?

Please restrict answers to use of BCP.

Answer 1

You will not be able to import that date format directly into a datetime2 column with bcp (as far as I am aware - happy to be corrected). Use a staging table, import into a char(20) column, then use CAST to convert the, eg

SELECT CAST( yourCharDateColumn AS DATETIME2 ) AS x
FROM yourStagingTable

You could forget the format file with this approach and simply using the -c switch with bcp. The cast was successful in my tests.

I notice the second column in your format file is marked as SQLBIT when it should be SQLBIGINT.

Regarding the use of bcp, I am interested to understand why you have chosen the use of this legacy command tool where Polybase is the recommended way of getting data quickly into Azure SQL Data Warehouse. CREATE EXTERNAL FILE FORMAT even has a DATE_FORMAT switch, which although I have not tried, could work with this format. If you must bcp and the source data is coming from another SQL Server then using the native (-n) switch on export works well and will also remove the need for the format file. Alternately consider Data Factory which should be able to handle the transform required on your date column.