How to execute an SSIS package from the command line or a batch file

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9977

    Comments posted to this topic are about the item How to execute an SSIS package from the command line or a batch file

  • Neo Aurora

    SSC Veteran

    Points: 246

    Thank you very much for posting this, relating to the world of hassle that is SSIS package deployment, where we don't know the names of servers, users, or even method of how customers can connect to their databases.

    This is a very useful post, but the length of it is testament to how difficult Microsoft make these processes. It's about time they developed a much more user-friendly front-end UI for this (or if anyone knows of one, please inform me!)

    Thanks again Stan.

  • tskelley

    SSCommitted

    Points: 1870

    Is there a reason you chose 64-bit instead of 32-bit? (or is this another article...) In other words, are there circumstances where one is more preferable than the other?

    Thank you,

    Tom

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the article.

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9977

    tskelley (3/14/2016)


    Is there a reason you chose 64-bit instead of 32-bit? (or is this another article...) In other words, are there circumstances where one is more preferable than the other?

    Thank you,

    Tom

    Nope. We are just using 64-bit SQL Server.

    If you are running multiple versions of SQL Server on your workstation like I am, you need to use the path to the correct version for the actual server you are going to hit. If you don't use the full path you might be using the 2008 64-bit version by default when the server you are trying to execute on is 2012 32-bit.

  • Jack Corbett

    SSC Guru

    Points: 184381

    tskelley (3/14/2016)


    Is there a reason you chose 64-bit instead of 32-bit? (or is this another article...) In other words, are there circumstances where one is more preferable than the other?

    Thank you,

    Tom

    The only reason I'm aware of for using 32-bit is if you have a data source that only has a 32-bit driver. Excel/Office has been the most common culprit here.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • Jack Corbett

    SSC Guru

    Points: 184381

    Neo Aurora (3/14/2016)


    Thank you very much for posting this, relating to the world of hassle that is SSIS package deployment, where we don't know the names of servers, users, or even method of how customers can connect to their databases.

    This is a very useful post, but the length of it is testament to how difficult Microsoft make these processes. It's about time they developed a much more user-friendly front-end UI for this (or if anyone knows of one, please inform me!)

    Thanks again Stan.

    While not perfect I think MS did address some of these difficulties with SSIS 2012 by including the project deployment model and the SSIS Catalog.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • Jeff Moden

    SSC Guru

    Points: 996676

    Good article but, is there any way around this major security violation???

    6. Modify the configuration file to change the server name and [font="Arial Black"]user name and add the password [/font]

    Open the configuration file in XML Notepad or some other editor.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • hjp

    Default port

    Points: 1435

    Thank you for an interesting article. I find it less useful, though, when you rely on two assumptions:

    1. SQL Authentication (and the willingness to store credentials in plaintext in the configurations file)

    2. Package Deployment Model (and the absence of parameters)

    It would be really beneficial, if these two limitations were addressed with suggestions to how to circumvent them. As it stands, you have "just" inspired me to go look elsewhere for a way to do this.

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9977

    Jeff Moden (3/14/2016)


    Good article but, is there any way around this major security violation???

    6. Modify the configuration file to change the server name and [font="Arial Black"]user name and add the password [/font]

    Open the configuration file in XML Notepad or some other editor.

    Not that I know of, which is why I have been advocating in my organization for dispensing with the configuration file and running the SSIS package directly, since the password is encrypted in the package.

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9977

    hjp (3/14/2016)


    Thank you for an interesting article. I find it less useful, though, when you rely on two assumptions:

    1. SQL Authentication (and the willingness to store credentials in plaintext in the configurations file)

    2. Package Deployment Model (and the absence of parameters)

    It would be really beneficial, if these two limitations were addressed with suggestions to how to circumvent them. As it stands, you have "just" inspired me to go look elsewhere for a way to do this.

    Which is why I have been advocating in my organization to dispense with the configuration file and run the SSIS package directly, since the password is stored in encrypted form in the package.

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9977

    hjp (3/14/2016)


    Thank you for an interesting article. I find it less useful, though, when you rely on two assumptions:

    1. SQL Authentication (and the willingness to store credentials in plaintext in the configurations file)

    2. Package Deployment Model (and the absence of parameters)

    It would be really beneficial, if these two limitations were addressed with suggestions to how to circumvent them. As it stands, you have "just" inspired me to go look elsewhere for a way to do this.

    You may have already found it, but this might be a viable solution:

    https://ssiscipherboy.codeplex.com/%5B/url%5D

  • Jack Corbett

    SSC Guru

    Points: 184381

    hjp (3/14/2016)


    Thank you for an interesting article. I find it less useful, though, when you rely on two assumptions:

    1. SQL Authentication (and the willingness to store credentials in plaintext in the configurations file)

    2. Package Deployment Model (and the absence of parameters)

    It would be really beneficial, if these two limitations were addressed with suggestions to how to circumvent them. As it stands, you have "just" inspired me to go look elsewhere for a way to do this.

    Using the project deployment model and the SSIS catalog take care of both these issues and i is very easy to execute a package from the catalog using a batch file because you just need to use sqlcmd to execute a stored procedure which runs the package required. Environments give you an easy way to set package\project parameters at run-time and offer encryption for sensitive values.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9977

    Jack Corbett (3/14/2016)


    hjp (3/14/2016)


    Thank you for an interesting article. I find it less useful, though, when you rely on two assumptions:

    1. SQL Authentication (and the willingness to store credentials in plaintext in the configurations file)

    2. Package Deployment Model (and the absence of parameters)

    It would be really beneficial, if these two limitations were addressed with suggestions to how to circumvent them. As it stands, you have "just" inspired me to go look elsewhere for a way to do this.

    Using the project deployment model and the SSIS catalog take care of both these issues and i is very easy to execute a package from the catalog using a batch file because you just need to use sqlcmd to execute a stored procedure which runs the package required. Environments give you an easy way to set package\project parameters at run-time and offer encryption for sensitive values.

    Thanks. This solves the problem and gives me the topic of my next article.

  • Phil Parkin

    SSC Guru

    Points: 244602

    Jack Corbett (3/14/2016)


    hjp (3/14/2016)


    Thank you for an interesting article. I find it less useful, though, when you rely on two assumptions:

    1. SQL Authentication (and the willingness to store credentials in plaintext in the configurations file)

    2. Package Deployment Model (and the absence of parameters)

    It would be really beneficial, if these two limitations were addressed with suggestions to how to circumvent them. As it stands, you have "just" inspired me to go look elsewhere for a way to do this.

    Using the project deployment model and the SSIS catalog take care of both these issues and i is very easy to execute a package from the catalog using a batch file because you just need to use sqlcmd to execute a stored procedure which runs the package required. Environments give you an easy way to set package\project parameters at run-time and offer encryption for sensitive values.

    Well summarized, Jack. I should mention that Andy Leonard has covered much of this in the Integration Services Stairway series[/url].

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply