COMMANDLINE OPTIONS IN SSIS

1. COMMANDLINE OPTIONS IN SSIS -ABHIJIT -SANJAY -SUSHANT

2. Execute Package Utility • You can use the Execute Package Utility dialog box to specify package run-time configurations and run packages on the local computer. You also can use this utility to generate command lines for use with the dtexec command prompt utility.

3. To open Execute Package Utility in Integration Services service • Click Start, point to All Programs, point to Microsoft SQL Server, and then click SQL Server Management Studio. • In the Connect to Server dialog box, select Integration Services in the Server type list, enter the server name in the Server name list, and then click Connect. • Expand the Stored Packages folder and subfolders, right-click the package you want to run, and then click Run Package.

4. You can also open the Execute Package utility at the command prompt by typing dtexecui.

6. dtexec Utility • The dtexec command prompt utility is used to configure and execute SQL Server Integration Services packages. The dtexec utility provides access to all the package configuration and execution features, such as connections, properties, variables, logging, and progress indicators. The dtexec utility lets you load packages from three sources: a Microsoft SQL Server database, the SSIS service, and the file system.

7. Exit codes returned from dtexec utility • Value --Description • 0 --The package executed successfully. • 1 --The package failed. • 3 --The package was canceled by the user. • 4 --The utility was unable to locate the requested package. The package could not be found. • 5 --The utility was unable to load the requested package. The package could not be loaded. • 6 --The utility encountered an internal error of syntactic or semantic errors in the command line.

8. Dtutil • You can use the dtutil command prompt utility to manage existing packages at the command prompt. You can access packages that are stored in the SQL Server msdb database, the SSIS Package Store, and the file system, and perform tasks such as copying, deleting, moving, and signing packages. You can also verify that a specified package exists.

9. The dtutil command prompt utility includes the following features: • Remarks in the command prompt, which makes the command prompt action self-documenting and easier to understand. • Overwrite protection, to prompt for a confirmation before overwriting an existing package when you are copying or moving packages. • Console help, to provide information about the command options for dtutil.

10. dtutil Exit Codes • Value --Description • 0 --The utility executed successfully. • 1 --The utility failed. • 4 --The utility cannot locate the requested package. • 5 --The utility cannot load the requested package • 6 --The utility cannot resolve the command line because it contains either syntactic or semantic errors.

11. Copy Examples • To copy a package that is stored in the msdb database on a local instance of SQL Server using Windows Authentication to the SSIS Package Store, use the following syntax: • dtutil /FILE c:\myPackages\mypackage.dtsx /COPY FILE;c:\myTestPackages\mynewpackage.dtsx

12. Delete Examples • To delete a package that is stored in the msdb database on an instance of SQL Server that uses Windows Authentication, use the following syntax: • dtutil /FILE c:\delPackage.dtsx /DELETE

13. Exists Examples • To determine whether a package exists in the msdb database on a local instance of SQL Server that uses Windows Authentication, use the following syntax: • dtutil /FILE c:\srcPackage.dtsx /EXISTS

14. Using dtexec from the xp_cmdshell • EXEC xp_cmdshell 'dtexec /f "C:\UpsertData.dtsx"'

15. THANK YOU