SSIS OLE DB Command Date Parameter Format

1
votes

I am using an OLE DB Command task to execute an INSERT statement. The INSERT statement accepts a number of parameters represented by ?. SSIS maps SQL Server DATETIME columns to parameters of type DT_DBTIMESTAMP which I think is fine.

The INSERT query fails as the DT_DBTIMESTAMP is passed to SQL Server as a string in format 'yyyy-MM-dd hh:mm:ss' but the database is expecting 'dd/MM/yyyy hh:nn:ss'. The error is due to SQL Server treating the 'day' and 'month' parts the wrong way around.

I've seen responses to questions around formatting dates in SSIS using derived columns etc. but I already have the DT_DBTIMESTAMP value (it has no format!) and the problem occurs when SSIS sets the parameter value as a string, and I can't see how to control the format so it outputs in 'dd/MM/yyyy hh:mm:ss'.

I've tried setting LocaleID and language but still seems to make no difference. An interesting observation is that this error does not occur when running through Visual Studio, only from a SQL Agent job.

Any help greatly appreciated.

1
sql-serverdatetimessis
What is the data type of the column you are trying to insert into? What is the data type of the SSIS variable you are mapping to the parameter? - Nick.McDermaid
and what is the error message that you get? also it would help top post the insert query. I know you feel you've isolated the problem but often if you take time to lay it all out in detail the answer becomes obvious - Nick.McDermaid

1 Answers

0
votes

What errors do you get, when running this? The only way to solve this, I see in converting DT_DBTIMESTAMP to DATETIME between the reading of the source file and the writing in SQL table.