0

I'm trying to triggering a DTS package from C# but no luck so far. I have a DTS package which I override some of the parameters FolderImportSource and FolderImportCompleted. My idea is something like 

                 RunDtsPackage("DTSX NAME", new Dictionary<string, object> {
                 { "FolderImportSource", @"C:\SourceFiles\ReadyToProcess\" },
                 { "FolderImportCompleted", @"C:\SourceFiles\Processed\" },
                 { "FolderExportTarget", @"C:\TargetFiles\Output\" }

RunDtsPackage should execute the following stored procs on the SSISDB database:

  • create_execution
  • set_execution_parameter_value
  • start_execution

I've checked this msdn link where I found examples of how to call these procs by using the “Tip” shown

Any idea?

Thank you

Toto07
  • 25
  • 1
  • 5
  • 1
    See the comment thread in this question: http://stackoverflow.com/questions/30765566/which-method-should-i-use-to-execute-an-ssis-package-remotely-from-a-net-applic – Tab Alleman Aug 31 '15 at 12:52
  • Open SSMS. Navigate to the Server where these packages are stored. Expand "Integration Services Catalog" Find your folder, find project under the folder and expand. Right click on package to run and select Execute. Configure params as desired. DO NOT CLICK OK. Instead, click that Script button on the top and Script to New Window. Those are your queries. Call it from ADO.NET. Done – billinkc Aug 31 '15 at 15:11

1 Answers1

0

[Assuming SQL 2014]

If you have access to the database itself, you can execute a package directly via a T-SQL query, like as such:

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'PackageName.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'YourSSISFirstFolderName', @project_name=N'YourSSISProjectName', @use32bitruntime=False, @reference_id=Null
Select @execution_id

DECLARE @var0 sql_variant = N'SomeVariableValue'
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=20, @parameter_name=N'SomePackageParameterName', @parameter_value=@var0

DECLARE @var1 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var1
EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

For any of your SSIS packages, you can generate the script, like the one above, via the SQL Server Management Studio.

When looking at the Object Explorer:

Database -> Integration Services Catalogs -> SSISDB -> [GenericProjectName] -> Projects -> [SomeSSISProjectName] -> Packages -> [PackageName].

From there, right-click the package and select "Execute". Fill in the required fields under the "Parameter" tab, then look at the top "Script" and select from the drop-down "New Query Editor Window". That should pop-up the T-SQL query specific to your package and parameters.

Hope that helps, or give other ideas.