SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Deployments


SSIS Deployments

Author
Message
Koen Verbeeck
Koen Verbeeck
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35061 Visits: 13270
Comments posted to this topic are about the item SSIS Deployments


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
mtassin
mtassin
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5374 Visits: 72521
Awesome article... though these days I'm in the land of the 2012 Project Deployment way.

However, you missed the Pragmatic Works way...

http://www.pragmaticworks.com/Products/BI-xPress.aspx

Besides the other cool tools that are in here... (Auditing being a huge win), the deployment wizard is a nice touch. Smile



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Stan Kulp-439977
Stan Kulp-439977
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1380 Visits: 1131
Our DBA went with the dtexec method with a config file.

http://msdn.microsoft.com/en-us/library/ms162810%28v=sql.105%29.aspx

You put the SSIS packages and configuration files in folders on the server and create a batch file referencing the package and config file of the form:

"c:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTEXEC.exe" /File "\\Server\SSISPackages\Packages\SSIS_Package.dtsx" /Conf "\\Server\SSISPackages\Configs\SSIS_Package.dtsConfig" /CHECKPOINTING OFF /REPORTING D

SQL Server has to be installed on the workstation from which the batch file is executed.
tim.gray 23818
tim.gray 23818
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 24
I don't think the "Pragmatic" way is actually very pragmatic. In lots of places, developers aren't supposed to be touching production servers and development tools should not be installed on production servers. So using a development tool for deployment is not feasible. And if you are building your SSIS package as part of a product that is installed by implementation guys at a customer site, the BIDS options are not available.
reuben.anderson
reuben.anderson
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 219
Hi,

Stan Kulp-439977 (4/10/2013)
Our DBA went with the dtexec method with a config file.


That's how you're executing it, not deploying it, yes? At my organisation we don't "deploy" at all. Maybe I'm missing something fundamental, but I don't see the value in deploying a package either to MSDB or to the SSIS package store.

Instead we just keep packages and configs on a fileshare and execute them using SQL jobs. The only constraint this imposes is that we use windows authentication on our connections. It's extremely simple, flexible, easy to support and rapid to deploy. It's not highly secure, but security operates at 2 levels - file ACL and database security.
Stan Kulp-439977
Stan Kulp-439977
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1380 Visits: 1131
Yes, we are executing several hundred SSIS packages via DTEXEC.

I guess I must not know what "deploy" means.
JustMarie
JustMarie
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1350 Visits: 1335
We deploy to the SSISDB catalog at a project level using the .ispac file. Then the DBAs use the environment to set up the connections. Is anyone else using this nifty addition to 2012?
mtassin
mtassin
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5374 Visits: 72521
JustMarie (4/10/2013)
We deploy to the SSISDB catalog at a project level using the .ispac file. Then the DBAs use the environment to set up the connections. Is anyone else using this nifty addition to 2012?


SSISDB Catalog? Every day... I find managing environments via the SSISDB catalog to be 1000% easier and more flexible than using config files. Some of the things I'm finding amusing with it are configuring most of the parameters via an environment, and making those default in, then passing in additional parameters via a stored proc call to start up a job.

It made setting up controlled parallel processing of thousands of inbound xml files per hour not only easier, but performance has improved immensely.

For those of you who do this all via the command line. If you need to change the parameters of execution of a package each time its run, and you have enough parameters that may or may not change, how do you get around the 255 character limitation of the CMD prompt? I used to do them via command line execution until I found out that some of my parameters were getting truncated by windows.



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
reuben.anderson
reuben.anderson
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 219
Stan Kulp-439977 (4/10/2013)
Yes, we are executing several hundred SSIS packages via DTEXEC.

I guess I must not know what "deploy" means.


Stan, don't be like that, I know what you mean and I basically agree as I indicated. It's just that the article is discussing means of deploying packages to an SSIS server, not how / where they are then executed.
Stan Kulp-439977
Stan Kulp-439977
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1380 Visits: 1131
I wasn't "being like that." Honestly.

I honestly thought that "deploying" it must be different than using DTEXEC in a bat file.

There is a lot I don't know about SQL Server, but at least I know I don't know it.

:-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search