Sql代理错误（SQL server 2008R2） [英] Sql agent error (SQL server 2008R2)

问题描述

我有一个执行SSIS包的代理，它自己执行一些后续的子包。我们这样做是为了在需要从执行链中添加/删除子包时限制必要的交互量（使用DBA）。



所有除了一个成功运行之外的包，但最后一个包生成一个或多个警告，导致整个作业失败。我不知道列出警告的位置。



有问题的子包只是将电子表格导入到sql表中，并调用存储过程来插入数据。存储的proc运行正常。



所有其他子包都做同样的事情（使用不同的电子表格文件），它们运行正常。



我不知所措......



我尝试了什么：



当我在VS2008中运行有问题的子包时，不会生成错误或警告。



如果从执行链中删除有问题的子包，则sql作业会成功运行。



如果我在执行链中只有有问题的子包，则sql作业会因（假想的？）警告而失败。



孩子的最后一步package正在运行upsert存储过程。如果我禁用该步骤，作业执行没有问题，因此警告可能是由存储过程生成的。存储过程的执行计划中没有显示任何警告，它似乎执行正常。



============== ===



我知道我可以关闭警告失败的事情，但我不想发现警告是什么。



=================



我添加了一个审计表，所以我可以看到存储过程是否实际按预期工作，它似乎是。由于警告，工作仍然失败。我很困惑。



我有十几个其他存储过程就像有问题的一样，并且没有一个在相关的SQL作业中发出警告。



========================



这是存储过程（减去实际的表和列名称）。

I have an agent that executes a SSIS package, that itself executes a number subsequent child packages. We do it this way to limit the amount of interaction necessary (with the DBAs) whenever we need to add/delete a child package from the execution chain.

All of the packages except one run successfully, but the last one is generating one or more warnings that causes the whole job to fail. I don't know where the warnings are listed.

The child package in question is merely importing a spreadsheet into a sql table, and calling a stored procedure to upsert the data. The stored proc runs fine.

All of the other child packages do the same thing (with different spreadsheet files), and they run fine.

I am at a loss...

What I have tried:

When I run the child package in question in VS2008, no errors or warnings are generated.

If I remove the child package in question from the execution chain, the sql job runs successfully.

If I have just the child package in question in the execution chain, the sql job fails because of the (imaginary?) warnings.

The last step in the child package is running the upsert stored proc. If I disable that step, the job executes without a problem, so the warning is probably being generated by the stored procedure. No warnings are indicated in the execution plan of the stored proc, and it appears to execute fine.

=================

I know I can turn the "fail on warnings" thing off, but I don't want to until I find out what the warnings are.

=================

I added an audit table so I could see if the stored proc was actually working as intended, and it appears to be. The job still fails because of warnings. I'm baffled.

I've got a dozen other stored procs just like the one in question, and none of them are throwing warnings in their associated SQL jobs.

========================

This is the stored proc (minus the actual table and column names).

SET NOCOUNT ON;

IF OBJECT_ID('[table2]','U') IS NULL
BEGIN
    SELECT TOP 0 * INTO [dbo].[table2] FROM [dbo].[Imported_table1]
END
	
MERGE INTO [dbo].[table2] AS t
USING [dbo].[Imported_table1] AS s
ON
(
      t.[field1]     = s.[field1]
      AND t.[field2] = s.[field2]
)
WHEN MATCHED THEN
    UPDATE SET
        [field3] = s.[field3],
        [field4] = s.[field4]
WHEN NOT MATCHED BY TARGET 
    INSERT 
    (
        [field1],
        [field2],
        [field3],
        [field4]
    )
    VALUES
    (
        s.[field1],
        s.[field2],
        s.[field3],
        s.[field4]
    );

推荐答案

Excel和SSIS ......我存在的祸根。我的第一个猜测是SSIS在VS中以一种方式读取数据类型，而另一种在从代理运行时读取数据类型（我知道这听起来不合逻辑，但它很常见; excel喜欢用它的数据创造性）。



即使警告信息含糊不清，如果您可以发布它们可能有助于了解根本原因。

Excel and SSIS...Bane of my existence. My first guess would be that SSIS is reading the data types one way when in VS, and another when running from the agent (I know that doesn't sound logical but its a common occurrence; excel likes to get creative with its data).

Even though the warning messages are cryptic, if you can post them might help understand what the root cause is.

这篇关于Sql代理错误（SQL server 2008R2）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！