I wrote a SSIS script task where I use C# code to read data from a database using ODBC and then dynamically create an excel spreadsheet using the Microsoft.Office.Interop.Excel object. The code then saves the spreadsheet to a directory folder .
When I execute the package in the SQL Server Business Development Studio, it works perfectly and the package writes the spreadsheet to the folder without any problem, but when I run through T_SQL code it fails script task.See the following Script task code:-
It works fine when I execute directly from BI tool, but fails when I execute through T_SQL Code-
@Cmd VARCHAR(4000),
@ReturnCode INT,
@Msg VARCHAR(1000)
SELECT @Cmd = 'DTEXEC /FILE "' + @Path + 'Package.dtsx" /MAXCONCURRENT 1 /CHECKPOINTING OFF /REPORTING EW'
EXEC @ReturnCode = xp_cmdshell @Cmd
Error:-
Microsoft (R) SQL Server Execute Package Utility Version 12.0.5000.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. NULL Started: 12:05:47 PM Error: 2016-09-08 12:05:49.42 Code: 0x00000001 Source: Script Task Description: Exception has been thrown by the target of an invocation. End Error Warning: 2016-09-08 12:05:49.42 Code: 0x80019002 Source: Package Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specifie d in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:05:47 PM Finished: 12:05:49 PM Elapsed: 1.828 seconds NULL
