Nov
26
2013

How to Execute Package through Command Prompt in SSIS

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 -

  1. How to create a Folder (named TEST in this post) by using “File System Task“.
  2. 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.

Drag and Drop File system task 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.
  • Scopecreatedir (This is my package name).
  • Data typeString.
  • ValueE:\TEST.

Follow the below screenshot, if anybody faces difficulty in understanding this.

Create a variable named file in SSIS

STEP 3. TEST folder exists or not  in E drive.

Folder exists or not in SSIS

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.

File system task editor in ssis

  1. In the above wizard, go to Operation and select “Operation = Create Directory“.
  2. After that, Go to Source connection and choose
    • IsSourcePathVariable = True“.
    • SourceConnection = use::file” (This is our variable, which we just created in step 2).
  3. Now, Go to Destination directory options and make “UseDirectoryIfExixts = True“. This option is used to overwrite the folder TEST if it exists already.

File system task Configuration in SSIS

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  -

Package execution via Command prompt

 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.

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.

Change directory in MS DOS

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.

Command prompt create dir

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 visitMicosoft’s Website.

6. Now, type - dtexec/f  createdir.dtsx

Here ” /f “is used to specify the path to the package.

SSIS Package details in command propmt

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.

Successful Execution of 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“.

 

Summary :-

  1. Create a package named as “createdir“.
  2. Create a variable and assign this to File System Task at the time of configuration.
  3. Open command prompt and use the utility “dtexec” to run our package via command prompt.
  4. 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.

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 9.9/10 (11 votes cast)
VN:F [1.9.22_1171]
Rating: +11 (from 11 votes)
How to Execute Package through Command Prompt in SSIS, 9.9 out of 10 based on 11 ratings
Enjoyed this post or Have Queries ? Please consider leaving a Comment below

Related Posts

About the Author: Avinash Munnangi

Am always a Learner. My aim is to learn something new of every single day, Learning one thing in different views is always Fun!!!

10 Comments+ Add Comment

  • […] This article will enlighten you with Creating a folder using File system task and How to execute package by using "Command Prompt". I am going to use a variable to do this thing.  […]

  • Nice article:-)

  • Need help : How to create a package with the following requirement? ( I need the logic flow / containers to be used please]

    I have two set of SQL queries each deriving data from seperate servers ( and their correspoding databases).The output/result of those two have to be put in seperate TEMP tables ( lets say X1, X2).
    Next, the third query is there which pulls data with a logic joining these two temp tables.

    So , in short , if I run the package designed with the above requirement….it should give us the output of the thrid query.

    • Hey souji today morning we solved your problem right ,i don’t have multiple servers to satisfy your requirement.i already sent few links to you .

      • Hi Avinash,
        Can you please provide us the step by step solution?
        Thanks,
        Oindrilla

        • Hai oindrilla,
          very glad you liked it. am sorry i dnt have multiple servers to write article please drop your mail id here i will send few links to u .you can go through those.

          Thanks

    • Hi Souji,
      Thanks for sharing sucha nice question. Although not matching the article’s scenario :)
      Avinash, Very nice article. :)

  • Hai Avinash, can you please share those links here.. or my mail id srinivaspatha@gmail.com thanks for your blog..

    • Hai srinivaspatha welcome to PHPRING.
      Thanks my friend

      What type of links you want can you please elaborate bro.
      Thanks

Leave a comment

Current day month ye@r *