SSIS deployment to DEV, QA and Prod

• malepati

  SSC Enthusiast

  Points: 147

  More actions

  February 24, 2009 at 9:23 am

  #131391

  1.) I developed SSIS packages that are invoked from the vb.net code by adding reference to ManagedDTS.

  The package runs perfectly fine. Now I want to promote the packages and the related code to the DEV server. The DEV server doesnt have SSIS. The package didnt run. I want to know what does it take for the packages to deployed to app server and also what is required if I have to put on the Database Server.

  2.) If I have to run my packages on the DEV server (Application Server) , do I need to have SSIS on that box ? If so, what are the alternatives if I cant have SSIS on that box ?

  3.) If I have to put the packages on the Database Server , is there any thing I should do for the code to invoke SSIS packages in configuration perspective ?

  4.) Also, my packages needs input and output folders as well as interacting with flat files. Where should I manage those folders if I put my packages on the Database server and call from vb.net code ?

  5.) Is there a way to put the packages on a different application server than those of where my Code resides, but still being able to manage the packages invocation etc., ?

  6.) If I want to use stored procedures with XP_CommandShell to invoke the packages and the code will inturn call the stored procedures, what do I require to do other than giving permissions for the XP command shell in the surface area configuration in 2005 or using facets in 2008 version.

  7.) Is there any document that describes the different approaches for deployment and what will be the best approach in terms of security, maintainance ?

  Please provide me the answers to these questions.

  -Amar

• Tim Mitchell

  SSCoach

  Points: 15685

  More actions

  February 25, 2009 at 2:21 pm

  #950281

  Amar,

  First, you've got a lot of questions in a single post. For best results in the forums, you should try to separate your queries so that a single post has one or two closely related questions. I'll do my best to address the bulk of your concerns here.

  If you intend to run SSIS packages from any machine, server or workstation, you must have the SSIS engine installed on that machine. That being said, an approach that I have taken is to set up a central server that I use almost exclusively for ETL. This server runs most of my SSIS packages, and reduces the need to have the SSIS components installed on all of my production servers. If you use this approach, you can store the input and output flat files on your integration server, and archive them if you choose.

  If you are running custom code in an SSIS script task/component, you must have either the code in the script task/component, or if you are using a compiled DLL, you must have that DLL registered on the machine on which SSIS is running.

  hth,

  Tim

  Tim Mitchell
  TimMitchell.net | @tmitch.net | Tyleris.com
  ETL Best Practices