Sql-server – SSIS – insert system date variable as a execute sql task parameter

sql serverssisssis-2012

I am trying to insert the system:starttime variable into a table by passing a parameter into an execute sql task.

I dont have much knowledge of SSIS data types, and I am currently having trouble making this functionality work. When I set the parameters datatype to nvarchar, the date is always inserted as 1/1/1900. When I use the DBDATE type, the insert fails with the following error

unsupported data type on parameter binding 0

So my question is how do I pass a SSIS date variable into an execute sql task so that SQL Server can interpret it correctly?

Best Answer

According to BOL DT_DBDATE is "A date structure that consists of year, month, and day". Which can't be used to store System::StartTime output as its DATETIME by data type, so try to use datatype DT_DATE instead of DBDATE. As datatype DT_DATE "A date structure that consists of year, month, day, hour, minute, seconds, and fractional seconds. The fractional seconds have a fixed scale of 7 digits."

Related Solutions

Sql-server – error in Execute SQL Task with parameter

Have you actually tried executing the package or are you just trying to parse the query?

Attempting to parse OLE DB queries with params will fail with the error you've described. Don't parse the query if using OLE DB.

I've created your task and tested. It executes fine, but if you click "Parse query" it returns the same error that you are getting.

Sql-server – Error using an Execute SQL script to load a date variable in SSIS

Try mapping your result set, not parameters. The setting is similar; the Result Names are 0 and 1 and the ? = are not necessary in your select statement.

SELECT TOP 1 Id, BuildDate
FROM BuildControl
WHERE BuildStatus IS NULL
ORDER BY Id

Also, make sure your Result Set is set to Single row.

Best Answer

Related Solutions

Sql-server – error in Execute SQL Task with parameter

Sql-server – Error using an Execute SQL script to load a date variable in SSIS

Related Question