Error in using date data type in Execute SQL task in SSIS.

Asked By jackson.kennyy27 0 points N/A Posted on - 06/27/2011

Hi,

In the Execute SQL task in SSIS, I have written the following insertion SQL statement as the direct input:

Insert into Emp_new(E_Id, E_date, E_time) select E_id,E_date,E_time from Emp_old
where E_date between (start_date) ? and (end_date) ?

I have declared the start_data and end_date as variables with the data type “datetime” in the package. And I have called the variables in the task while running. But it was giving a syntax error while trying to run the task in SSIS. Please help to solve the error.

Thanks.

• Status: Open
• Question Views: 1421
• Answer Count: 1
• Vote Up 0 Vote Down

• Answer Accepted: No
• Question Category: MS SQL Server

Answered By hark.martin 0 points N/A #79996

Error in using date data type in Execute SQL task in SSIS.

It seems that there is no any syntax error in the INSERT query. Try the INSERT statement directly in the SSMS by giving direct date values instead of variables. Then you can come to know whether there is any syntax error in the query or not. If it works perfectly, then it confirms that there is no syntax error in the query.

Check whether you are trying to insert a lot of records, if so then the Execute SQL task is not suited for this purpose.

You can try the insertion using the Data Flow Task. If still the issue exists even after using the Data Flow Task, sometime error occurs due to mismatching of the data type of the variables, try to change the data type of the variables start_date and end_date as “date” instead of “datetime”.  Also try to “cast” those data types into different date format in SQL.

About hark.martin

Questions
0

Answers
1

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 06/27/2011
• Question Category: MS SQL Server