AIM – Execute package through command prompt.
Description – This article will enlighten you with a process to execute package via Command Prompt. What will be learn from this article? You do not have to waste time to read all the article to know the outcome of this post. We will be learning –
- How to create a Folder (named TEST in this post) by using “File System Task“.
- How to execute package by using “Command Prompt“.
STEP 1. Creating a folder named TEST using “File System Task”.
I know it is very easy and simple to create it by using default options, existed in that task. Let’s learn another method. I am going to use a variable to do this thing.
- Open your BIDS known as Business Intelligence Development Studio and create a SSIS project.
- Create a new package and name it as say “createdir“.
- Now, Let’s drag a File System Task and drop it to Control flow.
STEP 2. Creating a Variable named “file”.
We will create a variable and will use this variable at the time of Filesystem task Configuration.
- Variable name – file.
- Scope – createdir (This is my package name).
- Data type – String.
- Value – E:\TEST.
Follow the below screenshot, if anybody faces difficulty in understanding this.
STEP 3. TEST folder exists or not in E drive.
Screenshot clearly states that TEST folder is not present. Please note we check E drive before executing the package.
STEP 4. Configuring File System Task.
- Double click on “File System task” for configuring it. Once you double click on file system task the below Wizard will appear.
- In the above wizard, go to Operation and select “Operation = Create Directory“.
- After that, Go to Source connection and choose
- “IsSourcePathVariable = True“.
- “SourceConnection = use::file” (This is our variable, which we just created in step 2).
- Now, Go to Destination directory options and make “UseDirectoryIfExixts = True“. This option is used to overwrite the folder TEST if it exists already.
Finally click on OK button. This step finishes our ‘File system task” configuration.
STEP 5. Execute Package.
1. It’s the time to run our package by clicking on Start debugging icon. But, In our case I am not going to do that. I am going to execute package via “Command prompt“.
2. To do this, Select your package name and press “F4“. Once we click on F4 the following window will pop up –
Just select and copy that path we can use this path name in command prompt, in this case our path is
“C:\Users\avinash reddy\documents\visual studio 2010\projects\Integration Services Project1\Integration Services Project1\createdir.dtsx”.
3. After copying the path, Open Command prompt.
By default it opens “C” drive ,but sometimes we can save our project solution in some other drives at that time you must be in that drive, in our case my project is located at C drive only that’s why am not going to change my drive here leave it as usual.
4. Once we open command prompt just type –
“cd documents\visual studio 2010\projects\Integration Services Project1\Integration Services Project1”
Here “cd” indicates Change directory, if you are aware of Ms DOS or Linux, at there we will see all these types of shortcuts. We will not go into this. Let’s stick to our topic. After typing this path, press enter and we will get the below screen.
5. After this, type “dir“. dir means Directory. It will open the all packages existed in our project folder. Once we type dir and press enter key it appears like below.
In the above screen shot we can clearly see that different types of packages existed in our project folder. For this demonstration, we can choose package “createdir.dtsx“.
NOTE :- Before going to run these packages through command prompt we must know some information.
We can use some “utilities” to run our packages through command prompt. There are number of client side utilities that are installed on the client machine at the time of SQL Server installation.
Here I am going to use one of the utility that is “dtexec” to run our package via command prompt. If you all want to know what is utility and what type of utilities installed in our machine at the time of SQL Server installation. You can visit MSDN website and if you want to know what is the exact work of “dtexec” utility you can visit – Micosoft’s Website.
6. Now, type – dtexec/f createdir.dtsx
Here ” /f “is used to specify the path to the package.
By seeing the above screen shot, we can notify few things –
- Package starting time.
- Finished time.
- Elapsed time and also the operation complete.
STEP 6. Successful Execution of our Package using Command prompt.
Once our execution is over we can go to E drive and see our Folder “TEST” is created or not.
By observing above screen shot we can concluded that our package “createdir.dtsx” was ran successfully without having any issues and also see the folder “TEST“.
- Create a package named as “createdir“.
- Create a variable and assign this to File System Task at the time of configuration.
- Open command prompt and use the utility “dtexec” to run our package via command prompt.
- Finally open E drive and see the folder “TEST“.
Free e-books and Pdf’s :-
Please Subscribe or Like PhpRinG Tutorials for free e-books and pdf’s.
- Launching Very Soon free e-books and pdf’s on SSIS – MS SQL Server Integration Services.
I hope you all enjoyed this article on how to execute Package using Command prompt. If you have any doubts and feedback, please drop those as comments.