We can create a batch file to run the SSIS package, using DTExec utility (32 bit or 64 bit utility). We can pass variables value also through batch file.
Note: Follow the below command and prepare a text file. Save the text file with ".bat" extension. If we double click on the batch file, then package execution will start.
Batch (.bat) file for SSIS package using 32 bit utility:
"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /f "D:\Sample_Packages\Test_Package.dtsx"
Batch (.bat) file for SSIS package using 64 bit utility:
"C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /f "D:\Sample_Packages\Test_Package.dtsx"
Passing variable value through batch file:
"C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /f "D:\Sample_Packages\Test_Package.dtsx" /SET \Package.Variables[User::Variable_Name].Value;"Variable_Value"
Note: If we need to pass variable value as a path (file or folder), in that path we need to replace "\" with "\\".
Example: If required path is D:\Sample_Packages\Source_Folder\Sample.txt. We need to pass this path like this D:\\Sample_Packages\\Source_Folder\\Sample.txt in a batch file.
DTExec File paths: DTExec file path is depends on SQL Server/SSIS version.
Note: Follow the below command and prepare a text file. Save the text file with ".bat" extension. If we double click on the batch file, then package execution will start.
Batch (.bat) file for SSIS package using 32 bit utility:
"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /f "D:\Sample_Packages\Test_Package.dtsx"
Batch (.bat) file for SSIS package using 64 bit utility:
"C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /f "D:\Sample_Packages\Test_Package.dtsx"
Passing variable value through batch file:
"C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /f "D:\Sample_Packages\Test_Package.dtsx" /SET \Package.Variables[User::Variable_Name].Value;"Variable_Value"
Note: If we need to pass variable value as a path (file or folder), in that path we need to replace "\" with "\\".
Example: If required path is D:\Sample_Packages\Source_Folder\Sample.txt. We need to pass this path like this D:\\Sample_Packages\\Source_Folder\\Sample.txt in a batch file.
DTExec File paths: DTExec file path is depends on SQL Server/SSIS version.
| Note: | 1.
The below listed paths are available in 64 Bit computer. 2. On a 64-bit computer, Integration Services installs a 64-bit version of the dtexec utility (dtexec.exe). If you have to run certain packages in 32-bit mode, you will have to install the 32-bit version of the dtexec utility. To install the 32-bit version of the dtexec utility, you must select either Client Tools or SQL Server Data Tools (SSDT) during setup. |
||
| SQL Server Version | DTExec File Path | ||
| 32 Bit | 64 Bit | ||
| SQL Server 2005 | <drive>:\Program Files(x86)\Microsoft SQL Server\90\DTS\Binn | <drive>:\Program Files\Microsoft SQL Server\90\DTS\Binn | |
| SQL Server 2008 | <drive>:\Program Files(x86)\Microsoft SQL Server\100\DTS\Binn | <drive>:\Program Files\Microsoft SQL Server\100\DTS\Binn | |
| SQL Server 2008 R2 | <drive>:\Program Files(x86)\Microsoft SQL Server\100\DTS\Binn | <drive>:\Program Files\Microsoft SQL Server\100\DTS\Binn | |
| SQL Server 2012 | <drive>:\Program Files(x86)\Microsoft SQL Server\110\DTS\Binn | <drive>:\Program Files\Microsoft SQL Server\110\DTS\Binn | |
| SQL Server 2014 | <drive>:\Program Files(x86)\Microsoft SQL Server\110\DTS\Binn | <drive>:\Program Files\Microsoft SQL Server\110\DTS\Binn | |
| SQL Server 2016 | <drive>:\Program Files(x86)\Microsoft SQL Server\110\DTS\Binn | <drive>:\Program Files\Microsoft SQL Server\110\DTS\Binn | |
Video: Execute SSIS Package from Batch file or Command prompt
Hi, the article is great. But I face a small issue when I run the batch file. The error says as "missing argument for option file" and then a execute package utility window opens up.
ReplyDelete