Thursday, February 6, 2014

Different ways to execute/store SSIS packages

Before going ahead, we should be aware about how many ways we can store SSIS packages

  1. File system
  2. SQL Server (Integration Service)
  3. SQL Server Integration Service Catalog

Now, we will discuss how to execute SSIS packages for above storage.

1) Execute Package Utility (DtExecUI)

image

a) Go to Run –> Type “DtExecUI” –> Enter will open DtExecUI Utility

b) You can choose package source from all above storage and execute SSIS package.

c) You can follow below link to know more about this tool.

http://technet.microsoft.com/en-us/library/ms180378.aspx

2) dtexec Utility (SSIS Tool)

a) Execute SSIS package located on File System. Here, we are calling package which is located on “C:\Sample.dtsx”­­ and setting 2 variables of package.

"C:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTExec.exe"

/f "C:\Sample.dtsx"

/Set \Package.Variables[User::Var1].Properties[Value];"Val1"

/Set \Package.Variables[User::Var2].Properties[Value];"Val2"

b) Execute SSIS package located on SQL Server.

"C:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTExec.exe"

/dts "\"\File System\Sample\""

/SERVER ServerName

/Set \Package.Variables[User::Var1].Properties[Value];"Val1"

/Set \Package.Variables[User::Var2].Properties[Value];"Val2"

c) Execute SSIS package located on Integration Service Catalog.

"C:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTExec.exe"

/ISSERVER "\SSISDB\SSISFolder\SSISProject\Sample.dtsx"

/SERVER "ServerName"

/Par "$ServerOption::SYNCHRONIZED(Boolean)";True

/Set \Package.Variables[User::Var1].Properties[Value];"Val1"

/Set \Package.Variables[User::Var2].Properties[Value];"Val2"

Reference: http://technet.microsoft.com/en-us/library/ms162810(v=sql.105).aspx

3) Execute package using xp_cmdshell for above DTExec.exe command. If we will use this command, it will refer DTExec.exe from database server. Below is script to execute SSIS package located on Integration Service Catalog.

exec master.dbo.xp_cmdshell 'C:\"Program Files"\"Microsoft SQL Server"\110\DTS\Binn\DTExec.exe /ISSERVER "\SSISDB\SSISFolder\SSISProject\Sample.dtsx"

/SERVER "ServerName"

/Par "$ServerOption::SYNCHRONIZED(Boolean)";True

/Set \Package.Variables[User::Var1].Properties[Value];"Val1"

/Set \Package.Variables[User::Var2].Properties[Value];"Val2"'

4) Execute Package from SQL Server Agent Service

a) Got to SQL Server Agent Service –> Jobs –> Right click New Job. Give job name “SampleJob”.

imageb) Go to Steps –> New. Give step name “Execute SampleJob” and select type as “SQL Server Integration Services Package”. You also can change Run as if you have proxy account created for SQL Server Integration Service.

imagec) You can choose package source from all above storage and complete other steps for jobs.

5) Execute Package using SSISDB.Catalog procedure. Below is script to execute package which is located at below location.

image 

Query:

Declare @p_execution_id int

-- Creates an instance of execution in the Integration Services catalog.

EXEC [SSISDB].[catalog].[create_execution]

@folder_name = 'SSISFolder',

@project_name = 'SSISProject',

@package_name = 'Sample.dtsx',

@reference_id = null,

@use32bitruntime = 0,

@execution_id = @p_execution_id OUTPUT

-- Set property value OR Set variable value.

exec [SSISDB].[catalog].[set_execution_property_override_value]

@execution_id = @p_execution_id,

@property_path = '\Package.Variables[User::Var1].Properties[Value]',

@property_value = 'Val1',

@sensitive = 0

-- Set parameter value to wait till package execution is not completed.

EXEC [SSISDB].[catalog].[set_execution_parameter_value]

@p_execution_id,

@object_type=50,

@parameter_name=N'SYNCHRONIZED',

@parameter_value= 1;

-- Starts an instance of execution in the Integration Services catalog.

EXEC [SSISDB].[catalog].[start_execution] @p_execution_id

-- Get the status of package execution.

Select status from [SSISDB].[catalog].[executions] where execution_id = @p_execution_id

No comments:

Post a Comment