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

Question

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

Stan Kulp-439977

SSChampion

Points: 10062
More actions
March 13, 2016 at 10:54 am

#313935

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

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

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

Neo Aurora SSC Veteran Points: 246 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

akljfhnlaflkj

SSC Guru

Points: 76202

March 14, 2016 at 7:15 am

#1864904

Thanks for the article.

Stan Kulp-439977 SSChampion Points: 10062 More actions · Answer 4

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: 184394 More actions · Answer 5

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: 184394 More actions · Answer 6

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: 1004323 More actions · Answer 7

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.

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

hjp Default port Points: 1437 More actions · Answer 8

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 SSChampion Points: 10062 More actions · Answer 9

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 SSChampion Points: 10062 More actions · Answer 10

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 SSChampion Points: 10062 More actions · Answer 11

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: 184394 More actions · Answer 12

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 SSChampion Points: 10062 More actions · Answer 13

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: 246960 More actions · Answer 14

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin