Click here to Skip to main content
15,867,765 members
Please Sign up or sign in to vote.
3.00/5 (2 votes)
See more:
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).

SQL
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]
    );
Posted
Updated 9-Aug-17 13:24pm
v5
Comments
CHill60 12-Apr-17 10:11am    
Hate to state the obvious, but have you debugged the faulty package?
#realJSOP 12-Apr-17 10:16am    
Like I said, the package works as designed, and generates no warnings or errors in VS2008...
Bryian Tan 12-Apr-17 14:34pm    
Did you check if Agent Error Logs capture something?
#realJSOP 12-Apr-17 15:03pm    
The error is that the job had warnings. Like everything else associated with SQL server, the messages are "cryptic".
CHill60 12-Apr-17 16:11pm    
So there are warning messages in the log from this package? Cryptic or not, can you share one of them?

1 solution

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.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900