SSIS Execute SQL Query error with DateTime Input Parameter

Question

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

Answer 1

I know that you have found an answer to your question. However, I would like to clarify here that the following query you were executing using OleDb connection is valid and it does work within the Execute SQL Task in SSIS package.

INSERT INTO dbo.table1 (DateCol, OtherCol, OtherCol1) 
SELECT ?, SourceCol1, SourceCol2 FROM dbo.SourceTable

Following example shows a successful implementation of the above mentioned query using SSIS 2005 (Business Intelligence Development Studio (BIDS) 2005)

Step-by-step process:

1. Create two tables named dbo.Source and dbo.Destination using the scripts provided under the Scripts section. Populate the table dbo.Source with data as shown in screenshot #1. Table dbo.Destination will initially be empty and will be populated with source data using an Execute SQL Task.

2. On the SSIS package, create an OLE DB Connection named SQLServer in the Connections Managers pointing to a SQL Server instance of your preference. Refer screenshot #2.

3. On the SSIS package, create a variable named RecordDate as shown in screenshot #3.

4. On the SSIS package, place an Execute SQL Task as shown in screenshot #4. Configure the task as shown in screenshots #5 and #6.

5. Screenshot #7 shows sample package execution.

6. Screenshot #8 shows data in the tables dbo.Source and dbo.Destination after package execution.

Hope that helps.

Scripts: .

CREATE TABLE [dbo].[Destination](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [DateValue] [datetime] NOT NULL,
    [ItemNumber] [varchar](50) NOT NULL,
    [Qty] [int] NOT NULL,
CONSTRAINT [PK_Destination] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Source](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ItemNumber] [varchar](50) NOT NULL,
    [Qty] [int] NOT NULL,
CONSTRAINT [PK_Source] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

Screenshot #1:

Screenshot #2:

Screenshot #3:

Screenshot #4:

Screenshot #5:

Screenshot #6:

Screenshot #7:

Screenshot #8: