Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

This tutorial will help you to get started with SSIS (SQL Server Integration Services)

Getting started with SSIS

Getting started with SSIS - Part 11: Package execution

Mar 8 2012 12:00AM by Sudeep Raj   

We now have a basic simple SSIS Package ready. We have also executed them to get some result. Are there any other ways to execute the SSIS Packages?

Yes, there are multiple ways to execute a SSIS package that you have. We will look into the various ways possible.

  1. BIDS
  2. DTEXEC.EXE command line utility
  3. DTEXECUI.EXE utility
  4. SQL Server Agent Job

BIDS: This is what we have been using all this while. You have the package in the solution explorer. If there is just one package in the solution, click F5 or Debug-> Start Debugging and the package will be executed for you.

Execute SSIS Package from BIDS Menu

If you have more than one package in your solution, you could right click the package in the solution and click Execute Package.

Execute SSIS Package from BIDS Solution explorer

DTEXEC.EXE Command line utility: Using the DTEXEC.EXE command line utility one can execute an SSIS package that is stored in a File System, SQL Server or an SSIS Package Store. The syntax to execute a SSIS package which is stored in a File System is shown below.

DTEXEC.EXE /F "C:\File Path\Package.dtsx"

DTEXECUI.EXE: In the Command prompt, type DTEXECUI.EXE. Execute Package Utility will open. You can browse your package from File System, SQL Server or SSIS Package Store. As of now we have seen just the File System, we will cover the others later. Select File System, browse the file and click Execute. Your package will be executed and you can see the progress and the output.

Execute SSIS Package from Execute Package Utility

SQL Server Agent Job: This is used to execute the job from SQL Server job and used to schedule the job so that the package can be run as we want automatically. Below are the steps to be followed:

Go to SQL Server Management Services and expand SQL Server Agent. Ensure that the Agent has started and you see the green icon next to SQL Server Agent. If not, right click SQL Server Agent and click Start.

Next go to Jobs and select New Job.

Create new SQL Server Agent

New Job window will open; provide a name to the job ensure that it is enabled .

SQL Server Agent – General tab

Go to Steps in the left panel and click New.

SQL Server Agent – Steps

The New Job Step window will pop up (figure below). Here put the Step Name.

Set Type to SQL Server Integration Services Package

Set Package Source to File System

Set the Package Path in the Package and click OK.

SQL Server Agent – Steps: New Job Step window

Next, go to Schedule on the left panel and click New. New job schedule window will pop up as in figure below. Here you will see all the options to schedule the job. It may be recursive or executed one time as per your wish. Play around to learn more.

SQL Server Agent – Schedule: New job schedule

Once you have set up the schedule, click OK and again OK on the New Job popup. You Job is ready to be fired. It will execute as per the time you set. In case you want to run it manually from here, you need to expand jobs go to the job you just created. Right click the job and select “Start Job at step…” (as in figure below)

SQL Server Agent – Start job at step

You will notice a Start Job window pop up showing the Progress of the job. Once it goes green (refer figure below) your job is successful and you can verify that the package has done its job.

Successful Job completion

That is it; I hope you have the basic understanding of SSIS. Will start on advance topics shortly. Till then explore the work of ETL.


Sudeep Raj
12 · 13% · 4306
12



Submit

19  Comments  

  • Hi,

    When will be next part posted ?

    I am waiting for that.

    Thanks

    commented on May 21 2012 4:41AM
    amittrivedimca
    2915 · 0% · 3
  • Hi Amit,

    Thanks for your comment. The next part will be out in a Month's time and would talk about individual Control Flow tasks.

    commented on May 21 2012 5:11AM
    Sudeep Raj
    12 · 13% · 4306
  • Hi Sudeep,

    Thanks great tutorial..

    Regards

    commented on Jun 15 2012 4:08AM
    Goods
    2436 · 0% · 5
  • Hi Sudeep,

    Thank you for sharing.

    Regards, Fazal Vahora

    commented on Jun 16 2012 2:00AM
    Fazal Vahora
    21 · 9% · 2818
  • For someone with no prior knowledge of SSIS, this has been a great tutorial series so far. When will the next part be available?

    commented on Aug 20 2012 7:37AM
    bart.bas
    2915 · 0% · 3
  • Before Package execution first you have to check System is 32 bit or 64 bit. it's very important, please check before go live.

    commented on Aug 21 2012 2:11AM
    Nirav Gandhi
    38 · 5% · 1567
  • Thanks Sudeep. Would you cover transformation in more details in the next posts? i.e. summerising data and then insert/update

    Thanks Shahab

    commented on Oct 19 2012 10:16AM
    shahab1344
    2915 · 0% · 3
  • This is a great series! Thanks!

    commented on Oct 19 2012 4:20PM
    Paras Doshi
    17 · 10% · 3265
  • --Also u can run on following method.....

    DECLARE @returncode int
    declare @ssisstr varchar(8000), @packagename varchar(200), @servername varchar(100)
    
    set @packagename = 'E:\bin\employee.dtsx'
    set @servername = 'E:\"SQL_BIN (x86)"\90\DTS\Binn\DTExec.exe /f ' --it's only required when u run on 64 bit
    set @ssisstr = @servername +  @packagename 
    EXEC @returncode = xp_cmdshell @ssisstr
    print @returncode ---0 means package excute sucessful...
    
    commented on Oct 22 2012 12:22AM
    Nirav Gandhi
    38 · 5% · 1567
  • hi Sudeep thanks for ur tut will u post the ssis scnerious line the tasks we use in real time projects pls regards sarma

    commented on Oct 26 2012 3:35AM
    sarma
    2915 · 0% · 3
  • Thanks for your comments and feedbacks. I do plan to write on other tasks in details will be doing it some time next year as I am busy with other work. Mean while you could check out my blog for practical scenarios:

    commented on Oct 26 2012 4:00AM
    Sudeep Raj
    12 · 13% · 4306
  • Hi Sudeep I am The Beginer Of Sql Server.After Reading this Blog I got Much enthusiasm to learn SSIS thanks and good job regards Ananth

    commented on Nov 6 2012 10:50PM
    aar.mfsgmail.com
    658 · 0% · 52
  • HI Sudeep,

    Your way of presentation is good and In Control Flow level, we can simply right click on the any task(For Ex:Data Flow Task) then execute task.

    And also Once we deploy the package into MSDB database in integration services there also we can execute our packages.

    commented on Jan 8 2013 1:54AM
    Bala Krishna
    84 · 2% · 676
  • Thanks bala for your inputs. Executing the package from MSDB is definitely an option, and I missed that. Hoever exuting individual tasks from control flow is used for debugging generally not for the entire package(hence I did not include it).

    commented on Jan 8 2013 2:00AM
    Sudeep Raj
    12 · 13% · 4306
  • First of all, Thank you for your efforts !

    I have gone through every chapter. Its very good for beginners.

    Keep posting :)

    commented on Jan 8 2013 8:21AM
    satish
    2826 · 0% · 4
  • Hi Sudeep

    Excellent introduction to SSIS, I wish I had it for the first time I needed to work with the software (bit of a nightmare as I was converting an existing DTS package from a 2000 server to 2005)

    Please keep up your good work - will be following you closely now!

    Best wishes

    commented on Jan 18 2013 1:25AM
    NigelBell
    2915 · 0% · 3
  • Hi Sudeep

    Its very good . I am beginner

    commented on Jan 23 2013 12:12PM
    wskamesh
    1807 · 0% · 11
  • Hi Sudeep,

    I am still waiting for your next advance SSIS parts. Please let me know when and how can I get that as early as possible. Thanks for your great work and perfect guidance on SSIS. :)

    Thanks & Best Regards, Sameer :)

    commented on Dec 29 2013 7:32AM
    sameer.hingwe
    2915 · 0% · 3
  • This is great work you did there its really helpfull, besides i have a question which i am trying to find a solution for how do we pass the parameters or Varibles dynamically using VB.net, i created a simple package with dateflow from one server to other. Source is accessing the data based on a sql command below is the sql command

    SELECT     Ticker AS securityid, TradeTime, AvgPrice , Volume
    FROM         table
    WHERE     (Symbol = ?) 
    AND (Time >= ?) 
    AND (Time <= ?)
    

    i created three varibles for symbol, start and end now i wanted to pass them from a .net application can you give me some idea on how to do it or any links to this

    commented on Jan 8 2014 4:32PM
    mak9789
    1571 · 0% · 13

Your Comment


Sign Up or Login to post a comment.

"Getting started with SSIS - Part 11: Package execution" rated 5 out of 5 by 12 readers
Getting started with SSIS - Part 11: Package execution , 5.0 out of 5 based on 12 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]