Below are various ways in which you can execute ssis package
1. Opening SSIS package in Business Intelligence development studio and executing
This is usually done during development where package would be build in business intelligence studio and will be tested by executing in it. This has the advantage of seeing the package execution status pictorially.
2.Executing the package from SQL job
Add a job step which will call the ssis package and execute the ssis package. This method is commonly used for automated execution of packages based on schedule.
3.Executing package from integration services server
If you’re choosing to store package in server you can execute it from there by right clicking on package and executing it
4. Executing from dtexecui utility
dtexecui is command line tool which will provide ui for executing packages. it will be similar to ui you get when trying to run package from server. here also you can set values for connections, config values through ui
5. Executing the package from dtexec utility
dtexec utility is used for executing package from command line. you can see full options by typing dtexec /? in cmd.
dtexec /F “Package.dtsx” /CONFIGFILE “config file path”
/SET “package.Variables[User::variable].Properties[Value]”;”value”“
In case of connection properties you need to pass them as either of the below
dtexec /F “” /SET Package.Connections[<
onString];””Data Source=;Initial Catalog=;Provider= ;Integrated Security=SSPI;Auto Translate=False;””
or use CONNECTION switch as
dtexec /F “” /CONNECTION ;””Data Source=;Initial Catalog=;Provider= ;Integrated Security=SSPI;Auto Translate=False;””
assuming default windows authentication setting for the connection
this has the advantage of passing config file or even direct values to package connections and variables through command line.You can also run package from filesystem,package store or sql server in this method using /F,/DTS or /SQL arguments
In case you want to use windows scheduler instead of sql agent you can put above call inside .bat file and schedule it from windows scheduler
Another flexibility with this method is you can even call this from xp_cmdshell so that you can create sql procedures to call this utility to execute packages programmatically by passing package name,connection property etc dynamically as parameters to procedure
