Transcript COMMANDLINE OPTIONS IN SSIS

COMMANDLINE OPTIONS IN
SSIS
-ABHIJIT
-SANJAY
-SUSHANT
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.
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.
• You can also open the Execute Package utility
at the command prompt by typing dtexecui.
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.
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.
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.
The dtutil command prompt utility
includes the following features:
• Remarks in the command prompt, which
makes the command prompt action selfdocumenting 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.
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.
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
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
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
Using dtexec from the xp_cmdshell
• EXEC xp_cmdshell 'dtexec /f
"C:\UpsertData.dtsx"'
THANK YOU