Home > SSIS, SSIS 2008, SSIS Tasks > Executing SSIS Packages from command prompt / SSMS

Executing SSIS Packages from command prompt / SSMS

“Dtexec” utility can be used to run the SSIS packages from the command prompt. Using dtexec utility you can load packages from a Microsoft SQL Server database, the SSIS service, and the file system.

The general syntax is

dtexec /option [value] [/option [value]]…

When a package runs, dtexec can return an exit code. Given below are the various exit values.

0 = execution successful

1 = package failed

3 = execution canceled

4 = the package could not be located

5 = unsuccessful in loading the package for execution

6 = error in the supplied command line

An example of “dtexec” is as given below

dtexec /f D:\Testing_Packs\testing_team\package3.dtsx
/SET \package.Variables[User::MyVariable].Properties[Value];VarValue1
/SET \package\DataFlowTask1.Variables[User::newVariable].Properties[Value]; VarValue2

You can execute the packages from SSMS and stored procs/functions using “XP_CMDSHELL” .Given below is an example

Exec xp_cmdshell ‘dtexec /f D:\Testing_Packs\testing_team\package3.dtsx
/SET \package.Variables[User::MyVariable].Properties[Value]; VarValue1
/SET \package\DataFlowTask1.Variables[User::newVariable].Properties[Value]; VarValue2 ‘

You might get an error while executing the above script , which might look like

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1

SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure.

This is because, as a security safeguard, Microsoft has turned off the use of xp_cmdshell by default . This has to be enabled by the users.

Follow the steps given to enable XP_CMDSHELL

1. Click the Start button.
2. Navigate to the Microsoft SQL Server 2005 folder.
3. On the flyout, mouseover Configuration Tools.
4. Select SQL Server Surface Area Configuration.
5. At the bottom of the window, select Surface Area Configuration for Features. You will get a window as in the image below.
6. In the left pane, under Database Engine, select xp_cmdshell.
7. Check the Enable xp_cmdshell checkbox.

8. Click OK.

Alternatively you can also configure XP_CMDSHELL through the proc SP_CONFIGURE.

Enable the ‘show advanced options’ then reconfigure to install it. Once this is done you will be able to enable XP_CMDSHELL. The code is as given.

EXEC sp_configure ‘show advanced options’ , 1

EXEC sp_configure ‘xp_cmdshell’ , 1

Categories: SSIS, SSIS 2008, SSIS Tasks
  1. February 20, 2013 at 2:26 am

    “Executing SSIS Packages from command prompt / SSMS Awesome SQL” ended up being a beneficial
    blog. If only there were a lot more websites just like this
    particular one on the internet. Regardless, many thanks for your time, Blanca

  2. June 24, 2013 at 11:07 pm

    Hello just wanted to give you a quick heads up.
    The text in your content seem to be running off
    the screen in Internet explorer. I’m not sure if this is a format issue or something to do with web browser compatibility but I thought I’d post to let
    you know. The style and design look great though!
    Hope you get the issue fixed soon. Cheers

  3. July 5, 2013 at 11:57 am

    I think everything posted made a great deal of sense.
    However, what about this? suppose you composed a catchier post title?

    I mean, I don’t want to tell you how to run your blog, however suppose you added a post title to possibly grab a person’s attention?

    I mean Executing SSIS Packages from command prompt / SSMS |
    Awesome SQL is kinda vanilla. You should look at Yahoo’s front page and note how they create post titles to grab people to open the links. You might add a related video or a related picture or two to grab people excited about what you’ve written.

    In my opinion, it would bring your posts a little bit more interesting.

  4. July 8, 2013 at 2:45 am

    I think everything published made a bunch of sense. But, think on this, what if you wrote a catchier
    title? I mean, I don’t want to tell you how to run your blog, however suppose you added a title that makes people desire more? I mean Executing SSIS Packages from command prompt / SSMS | Awesome SQL is a little plain. You could look at Yahoo’s front page and see how they write post titles to grab viewers to click.
    You might add a video or a pic or two to grab readers
    interested about what you’ve got to say. Just my opinion, it would make your website a little bit more interesting.

  5. XandRia Discount Codes
    July 15, 2013 at 3:07 am

    If you would like to increase your know-how just keep visiting
    this site and be updated with the most up-to-date news
    update posted here.

  6. Stroller Depot Coupon Code
    July 19, 2013 at 10:32 pm

    An interesting discussion is worth comment.
    There’s no doubt that that you should publish more about this subject, it might not be a taboo matter but generally people don’t talk
    about such issues. To the next! Cheers!!

  7. July 26, 2013 at 3:37 am

    Thank you for sharing your info. I really appreciate your
    efforts and I am waiting for your next write ups thank you
    once again.

  8. July 29, 2013 at 7:01 pm

    There is certainly a great deal to know about this topic.
    I like all the points you made.

  9. August 31, 2013 at 9:25 pm

    Instead of printing the design on a paper, it will print on the t shirt.
    Till now Google installed different systems for tablets and smartphones.
    You can typically find these in home improvement stores and they’re not too expensive to buy.

  10. May 31, 2014 at 12:49 pm

    As the admin of this web page is working, no hesitation very soon it will be renowned, due to its quality contents.

  11. June 3, 2014 at 12:44 pm

    Heya i’m for the first time here. I came across this board and I find It truly useful & it helped me out a lot.
    I hope to give something back and aid others like you aided

  12. June 5, 2014 at 3:37 am

    My relatives every time say that I am wasting my
    time here at net, except I know I am getting familiarity every
    day by reading thes pleasant content.

  13. September 17, 2014 at 4:04 pm

    Real Racing 3, regarded among the teenagers as well as the multi purpose electronics
    devices bike race cheats that have spanned one or two programming languages.
    However, when you can to win all the guidelines discussed above in order to remain active from the
    basic brick based applications to pay a visit to play games on their cell
    phones. At the end, only two prominent examples are available for people.

  14. May 5, 2015 at 11:54 pm

    Although AMD Athlon processors aree manufactured to have lower heat production, cooling fans will help cool the processors more effectively especially if
    you plan to over-clock your CPU. By usong this AMD treatment three-pronged attack onn
    macular degeneration every day for sixx to twelve months patients are reporting improvements in their vision which
    was previously unheard of with this debilitating
    eye disease. too for a laptop oof this size and price but more of the price later.
    Yes there are bettsr options out there andd for some price does
    matter but when you aare with us, it doesn’t, because its always within your reach.
    6-inch ultra-extreme, thickness control in the range of values within 24.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: