April 9, 2013 at 11:30 pm
Comments posted to this topic are about the item SSIS Deployments
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 10, 2013 at 6:55 am
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. 🙂
April 10, 2013 at 7:15 am
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.
April 10, 2013 at 7:40 am
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.
April 10, 2013 at 8:04 am
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.
April 10, 2013 at 8:19 am
Yes, we are executing several hundred SSIS packages via DTEXEC.
I guess I must not know what "deploy" means.
April 10, 2013 at 8:27 am
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?
April 10, 2013 at 8:32 am
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.
April 10, 2013 at 8:34 am
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.
April 10, 2013 at 9:09 am
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.
🙂
April 10, 2013 at 9:18 am
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.
This is what we do too. Have 100+ .dtsx files that we invoke via SQL Agent jobs. Our deployment method involves simply moving a file from a development share to a production share.
I'm not sure what you mean by saying that this imposes using Windows authentication. You can embed connection information in config files. Actually, you could even hardcode them in the project file thou I'm not sure why anyone would want to.
Ken
April 10, 2013 at 9:32 am
ken.trock (4/10/2013)
...
I'm not sure what you mean by saying that this imposes using Windows authentication. You can embed connection information in config files. Actually, you could even hardcode them in the project file thou I'm not sure why anyone would want to.
Ken
I think in our testing we found that SSIS refused to { either build or execute } the package under any other user context than the developer's if a connection was configured to use SQL authentication and the package wasn't encrypted... or something like that? Maybe you can put the whole string in the ConnectionString property rather than using the Password property. It was a while ago I can't remember..
April 10, 2013 at 9:34 am
p.s. all of which has meant I struggle to understand why an Integration Server is needed at all. An engine to execute packages may as well be included in the SQL Agent, or SQL Server, or just out of process...
I'm sure I'm missing something though.
April 10, 2013 at 9:37 am
How to deploy several packages by a batch job?
dtsx and configuration files?
April 10, 2013 at 10:00 am
reuben.anderson (4/10/2013)
p.s. all of which has meant I struggle to understand why an Integration Server is needed at all. An engine to execute packages may as well be included in the SQL Agent, or SQL Server, or just out of process...I'm sure I'm missing something though.
True, a separate Integration server isn't needed. Our SSIS packages, probably like most people's, share a machine with SQL Server production databases. Our setup has all those .dtsx files on the same machine as our our ETL staging databases.
Ken
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply