Can you try the following SQL code: INSERT INTO Table1 (DateCol,OtherCol,OtherCol1....) SELECT '?',SourceCol1,SourceCol2.... FROM sourceTable I think the problem lies in the fact that when the OLE DB provider substitutes the ? with the value of the parameter, you are basically trying to pass a hard-coded value in the INSERT ... SELECT clause. However, that value is not enclosed by quotation marks, so SQL Server will probably fail on that. MCTS, MCITP - Please mark posts as answered where appropriate

Can you try the following SQL code: INSERT INTO Table1 (DateCol,OtherCol,OtherCol1....) SELECT '?',SourceCol1,SourceCol2.... FROM sourceTable I think the problem lies in the fact that when the OLE DB provider substitutes the ? with the value of the parameter, you are basically trying to pass a hard-coded value in the INSERT ... SELECT clause. However, that value is not enclosed by quotation marks, so SQL Server will probably fail on that. MCTS, MCITP - Please mark posts as answered where appropriate Thanks Koen, I tried that, and it fails with failed with the following error: "Conversion failed when converting datetime from character string.", when I look at sql profiler the '?' is actually sent to sql i.e. its not being replaced with variable. I am giving serious thought to using a string variable and converting it to a date in the query. Mark

Hmmm, I was afraid that the OLE DB provider would be difficult about the quotation marks. Maybe try the following: DECLARE @myDate DATETIME; DECLARE @SQLStatement VARCHAR(100); SET @myDate = ?;SET @SQLStatement = ' INSERT INTO Table1 (DateCol,OtherCol,OtherCol1...) SELECT ''@myDate'',SourceCol1,SourceCol2... FROM sourceTable'; SET @SQLStatement = REPLACE(@SQLStatement,'@myDate',@myDate); EXEC(@SQLStatement); I hope the SET @myDate = ?; statement will work without giving troubles. MCTS, MCITP - Please mark posts a

Hi Folks, I have spent a few days trying to fix this problem. I have a SSIS package with 2 execute SQL tasks within a sequence container, one is a simple delete from table and the next one an simple insert the delete precedes the insert. The delete works fine so the connection etc is ok. The Insert is failing with the following vague and unhelpful message. failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. The insert has 1 input parameter which is a date which is bound to a datetime variable set to 01/01/2011. When I replace the ? in the sql task query with a hard coded date the task works. I have also looked at the locals at a pre-execute event break point on the insert task and the variable is ok. I also fired up a SQL Profiler session and I see the delete query hitting the DB but nothing for the insert (when it uses the input parameter). I am using Visual Studio 2005 Pro SP1 (Not my choice) and SQL Server 2005 SP3. Regards Mark

Can you post the actual SQL query where that parameter is used? What I usually do when SSIS is giving my troubles with dates (because it is picky about format and the SSIS date data types lack precision) is to use a string variable inside SSIS to store the date and convert it in the Execute SQL Task to a valid date. TSQL has much more capabilities regarding the conversion of dates than SSIS and it also has better precision regarding milliseconds.MCTS, MCITP - Please mark posts as answered where appropriate.

Hi, Have you configure the RESULTSET property as 0? If not, try this one.. Thanks, Guru

Can you post the actual SQL query where that parameter is used? What I usually do when SSIS is giving my troubles with dates (because it is picky about format and the SSIS date data types lack precision) is to use a string variable inside SSIS to store the date and convert it in the Execute SQL Task to a valid date. TSQL has much more capabilities regarding the conversion of dates than SSIS and it also has better precision regarding milliseconds. MCTS, MCITP - Please mark posts as answered where appropriate. I am using OLE DB my query is of the form INSERT INTO Table1 (DateCol,OtherCol,OtherCol1....) SELECT ?,SourceCol1,SourceCol2.... FROM sourceTable ResultSet is none and parameter mapping has one item User::RecordDate, Input, DATE, 0 – Mark 4 mins ago edit

I had this same issue. The problem is OLEDB doesn't recognize the SSIS datetime variable format (ending in "AM" or "PM") as a valid datetime format. Apparently it tries to interpret the AM/PM part of the string as milliseconds. Lucky for us .NET does recognize that format so I worked around it by using a script task to parse the datetime variable into a string variable that had a 24 hour SQL datetime format (without the AM/PM) that OLEDB would recognize, which I passed into the query instead of the SSIS "datetime" variable. Yeah, it's frustrating.