batch files in SSIS
batch file
noun
noun: batch file; plural noun: batch files
- a computer file containing a list of instructions to be carried out in turn.
How can we create batch file in SSIS..?
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.
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.
Scheduling Batch file with Windows Task Scheduler.
Step1 : Create batch file with following code.
save it to “D:\Work\SSIS\BatchFiles\GetSpaceInfoWithWMI.bat”
Step2 : Create Windows Schedule Task.
Click on Start button — > Run –> and Type — > control schedtasks and click ok.
Step3 : Click on Task Scheduler Library on left pane of Task Scheduler, it will show a window to create new task.
Now on the right side of Task Scheduler –> click on Create Task under Actions pane. It will open one pop menu to create new task.
Give schedule name and select option “Run whether user is logged in or not”.
Step4 : Select Trigger tab from Create Task window –> click on new to schedule time.
Step5 : Select Action tab from Create Task window –> click on browse to map file path.
Click on “OK” button to complete it. windows will ask for user name and password.
Please apply administrator user and password or any other user which have a permission to execute schedule task.
Note: We can also schedule batch with SQL Server Agent Jobs by creating a new Job.
1 | "C:\Program Files\Microsoft SQL Server\110\DTS\Binn\dtexec.exe" /FILE "D:\Work\SSIS\Nirav's Diary\Nirav's Diary\bin\Development\GetSpaceInfoWithWMI.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI |
Step2 : Create Windows Schedule Task.
Click on Start button — > Run –> and Type — > control schedtasks and click ok.
Step3 : Click on Task Scheduler Library on left pane of Task Scheduler, it will show a window to create new task.
Now on the right side of Task Scheduler –> click on Create Task under Actions pane. It will open one pop menu to create new task.
Give schedule name and select option “Run whether user is logged in or not”.
Step4 : Select Trigger tab from Create Task window –> click on new to schedule time.
Step5 : Select Action tab from Create Task window –> click on browse to map file path.
Click on “OK” button to complete it. windows will ask for user name and password.
Please apply administrator user and password or any other user which have a permission to execute schedule task.
Note: We can also schedule batch with SQL Server Agent Jobs by creating a new Job.
No comments:
Post a Comment