CODEWITHSUNDEEP
sql-servert-sqlssis
Serdia
Serdia

Reputation: 4428

Using T-SQL to execute SSIS package does not return any result

I have a simple SSIS package that uses C# to generate an Excel file and put it to assigned location.

Package works fine in design mode.

Package been deployed successfully:

enter image description here

However when I try to execute it from SSMS - then then its only displays @execution_id. 

DECLARE @execution_id BIGINT

EXEC [SSISDB].[catalog].[create_execution] 
        @package_name=N'ExcessCopy1.dtsx',
        @execution_id=@execution_id OUTPUT,
        @folder_name=N'MonthlyReports',
        @project_name=N'MonthlyReports',
        @use32bitruntime=False,
        @reference_id=Null

SELECT @execution_id

DECLARE @var0 smallint = 1

EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
        @execution_id,
        @object_type=50,
        @parameter_name=N'LOGGING_LEVEL',
        @parameter_value=@var0

EXEC [SSISDB].[catalog].[start_execution]  @execution_id
GO

enter image description here

If I comment out SELECT @execution_id, then I get

Command completed successfully

However it does not deliver file to assigned location.

enter image description here

What can be the problem and how can I troubleshoot this issue?

I can see the package under Integrational Service Catalog. But I was unable to find this package in SSISBD:

enter image description here

I was able to see what is the error:

enter image description here

Upvotes: 1

Views: 546

Answers (2)

digital.aaron
digital.aaron

Reputation: 5707

Your SQL Server is missing the ACE OLEDB 12.0 driver. You can get it here: https://www.microsoft.com/en-us/download/details.aspx?id=13255

If you need both 64-bit and 32-bit versions installed for some reason, you can install the 64-bit from the link above, but it won't let you also install the 32-bit version, so you can use an older version of the 32-bit driver, which can coexist with the 64-bit one. You can get that one here: https://www.microsoft.com/en-us/download/details.aspx?id=23734

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31785

What can be the problem

The most likely explanation is that the package puts the file in a place you don't expect because it is now executing on the server and not your local box. Almost equally likely is that the package is encountering an error that it is not passing back to the SSMS window.

and how can I troubleshoot this issue?

If the package is in SSISDB, you can right click on it and look at the "All Executions" report to verify that the package did execute, and then drill down into the "All Messages" for that execution to see what happened.

Upvotes: 2

Related Questions