Thursday, August 30, 2012

SSIS Date Error Going From SSIS Variable to Stored Procedure using OLE DB

Quick Description:  An SSIS package which populates an SSIS variable of type DateTime and passes the variable as a parameter to a stored procedure (expecting DateTime) using OLE DB blows up with the error below.
[Execute SQL Task] Error: Executing the query "exec <procedure>..." failed with the following error: "Invalid time format". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
One Solution:  In my situation it turned out to be another run-in with my nemesis, the deeply confusing datatype mappings in SSIS (2012 in my case).  According to  this Microsoft article the mapping for SQL Server DateTime  is DT_DBTIMESTAMP.  For an Execute SQL task calling a stored procedure (using OLE DB) expecting a DateTime parameter, however, this blows up with the error above.  Setting the parameter to type DATE (not DT_DATE), fixed the problem for me, and the parameter is populated with a full Date/Time (including fractional seconds).

No comments:

Post a Comment