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]
);