SSIS使用日期时间输入参数执行SQL查询错误

Question

我花了几天时间试图解决这个问题。我有一个 SSIS 包，在序列容器内有 2 个执行 SQL 任务，一个是从表中简单删除，下一个是简单插入（删除先于插入）。删除工作正常，因此连接等正常。

插入失败，并显示以下模糊且无用的消息。

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. 

该插入有 1 个输入参数，该参数是绑定到设置为 01/01/2011 的日期时间变量的日期。当我更换？在具有硬编码日期的 SQL 任务查询中，任务有效。我还查看了插入任务上预执行事件断点处的局部变量，并且变量正常。

我还启动了 SQL Profiler 会话，我看到删除查询命中了数据库，但没有插入任何内容（当它使用输入参数时）。

我正在使用 Visual Studio 2005 Pro SP1（不是我的选择）和 SQL Server 2005 SP3。

问候

马克

Answer 1

我知道您已经找到了问题的答案。但是，我想在此澄清一下，您使用 OleDb 连接执行的以下查询是有效的，并且它确实在 SSIS 中的执行 SQL 任务 中工作代码> 包。

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

以下示例显示了使用 SSIS 2005 (Business Intelligence Development Studio (BIDS) 2005) 成功实现上述查询

分步过程：

1. 使用脚本部分下提供的脚本创建两个名为dbo.Source和dbo.Destination的表。使用屏幕截图 #1 中所示的数据填充表 dbo.Source。表 dbo.Destination 最初为空，并将使用执行 SQL 任务填充源数据。

2. 在 SSIS 包上，在连接管理器 中创建一个名为 SQLServer 的 OLE DB 连接，指向您首选的 SQL Server 实例。请参阅屏幕截图 #2。

3. 在 SSIS 包上，创建一个名为 RecordDate 的变量，如屏幕截图 #3 所示。

4. 在 SSIS 包上，放置一个执行 SQL 任务，如屏幕截图 #4 所示。配置任务，如屏幕截图 #5 和 #6 所示。

5. 屏幕截图 #7 显示示例包执行。

6. Screenshot #8 显示包执行后表 dbo.Source 和 dbo.Destination 中的数据。

希望有帮助。

脚本：
。

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

屏幕截图 #1：

屏幕截图 #2：

屏幕截图 #3：

屏幕截图 #4：

屏幕截图 #5 ：

屏幕截图#6：

屏幕截图 #7：

屏幕截图#8：

Answer 2

您需要确保您的 SQL 语句的类型正确，以便根据您的连接管理器进行参数化。

如果您使用的是 OLE DB - 您的插入语句需要是某种类似 INSERT INTO Tbl(col) VALUES (?)

结果集应该是“None”（因为您的 INSERT 上没有任何可返回的内容） ），并且“参数映射”选项卡应该有一个参数（或与您拥有的一样多的？，并且参数名称应该（对于 OLE DB）从 0 开始，然后是 1, 2 ... ,n。如果您使用的是对于 ADO 连接，您必须将参数命名为 Param1、Param2、... ParamN。

您可以在此处查看将变量传递到相应连接管理器的参考：http://technet.microsoft.com/en-us/library/cc280502.aspx

根据您的评论给出正确答案：
您不能在此类查询中使用参数映射。但有一个简单的方法可以做到这一点。

创建一个新变量 sqlCommand（字符串类型）。在变量列表中单击它，然后按 F4 查看属性。将“EvaluateAsExpression”更改为true，然后单击表达式框以打开表达式窗口。在这里以如下格式进行查询 "INSERT INTO tbl(dateCol,intCol,charCol) SELECT CONVERT(DATETIME,'" + (DT_STR,20,1252)@[User::dateVar] + "', 104) AS dateCol, intCol, charCol from anotherTbl"

当您单击“评估表达式”时，您将看到正在形成的有效 SQL 语句，您可以使用它。

现在返回执行 SQL 任务，删除参数映射。关闭对话框，单击执行 SQL 任务，按 F4，找到表达式行，单击 ... 并在属性“SqlStatementSource”上添加一个表达式，其中表达式为 @[User::sqlCommand]（或您为变量命名的任何内容） ）。

现在运行该任务，它应该可以正常工作。

Answer 3

您可以在这里找到该表达式：

Answer 4

Objective:
To update datetime field (date+time) in parameter in sql task editor of SSIS package.

Description:
Step01: Created a variable having datatype as string.
e.g: 
Variable name: ReconcileStartDateTime
Data Type: String

Step02: Assign value to a variable. create 'Execute SQL Task'
General:
SQL Command-> select cast(getdate() as nvarchar(100)) as StartDateTime
ResultSet-> Single Row
ByPassprepare-> True

Result set tab:
Result Name: StartDateTime
Variable Name: User::ReconcileStartDateTime

Step 03: Create Execute SQL Task and use query as below:
SqlStatement: Update OrderDetail set StartDate = cast(? as datetime) where ID= 101;
Parameter mapping: click ADD button, set variable name User::ReconcileStartDateTime; Datatype as Nvarchar; Parameter to 0

Result: When execute the SSIS package, datetime is set accordingly. SQL Profile would help to see the output.