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

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

  • 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.

  • 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

  • Thanks for the article.

  • 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.

  • 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

  • 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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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.

  • 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.

  • 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.

  • 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

  • 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

  • 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.

  • 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