August 7, 2014 at 9:51 am
We are running SQL Server 2012 on Windows 2008 Server using Visual Studio 2010/2012 and SSDT. We are a small shop with about four SSIS Developers. We have some SSIS Packages provided by the Vendor. Using SSIS Packages is a 'new thing' for us. This is an attempt to establish some guidelines in creating, testing, modifying and deploying packages. Please read over the following and provide any helpful input to these guidelines by referring to the steps (numbers/letters). Remember, we would like to keep it as simple as possible.
1) Developers should test all packages in the test environment first.
2) Copy package from Vendors Interfaces Folder To Developers' Folder. This may be done using one of two methods.
A. First Method:
(NOTE: Vendor has many packages stored in one solution.)
Copy Vendor Package (PackageName.dtsx) from:
\\ServerA\Vendor_Work\Interfaces\
To
Developers specific folders:
Y:\SIS\SQLServer\SSISPackages\Development\John
Y:\SIS\SQLServer\SSISPackages\Development\Mary
Y:\SIS\SQLServer\SSISPackages\Development\Tom
Y:\SIS\SQLServer\SSISPackages\Development\Susan
B. Second Method:
Use Visual Studio to Open a new Project and Add an existing package.
3) Developers should make changes\modifications in their folder.
If developers decide to make changes\modifications in the Vendor's Folder than fine; just please be consistent. The DBAs need to know where to grab the latest, tested package for code review, test and deployment.
The DBAs would then move the tested package to the DBAs' test and production locations:
I:\SIS\SQLServer\SSISPackages\Test\PackageFolder (for TESTDBServer Packages)
I:\SIS\SQLServer\SSISPackages\Production\PackageFolder (for PRODBServer Packages)
4) Use the following in-house SSIS Package Guidelines.
A. Use Project Deployment Model (new) instead of Package Deployment Model (legacy).
B. Use EncryptSensitiveWithPassword for the Project and Package Protection Levels.
C. Use window's login to create packages when using Visual Studio. (Logged in with windows login.)
D. Use SQL Server Specific Logins for DB Connections within the package.
5) DBAs should perform Package Deployment Tasks or the process of getting the package out to the SQL Server.
A. In SSMS, open SSISDB under Integration Services Catalog.
B. Right-click and create folder. Give it a name and description.
C. Return to Visual Studio or SSDT. Right-click the solution (or project ?) and select Deploy.
D. In Select Destination' select the server and the folder you just created.
E. Press Next and Next and Finish.
In Step 5.A, the Integration Services Catalog (SSISDB) was created in SSMS by right clicking Integration Services Catalogs and selecting Create Catalog.
Question Step 5.C - should we right click the solution or project? I think deploy shows up if we select the Project folder.
6. For Redeployment of a Package:
Follow C. through E. from Step 5 above.
7. Other notes:
A. The whole solution gets deployed. All packages in a Solution gets deployed.
B. We plan on using one package per Solution, one Solution per Job, instead of putting multiple packages in a Solution.
C. We don't have a Source Control Solution but I guess that would be the next thing to store the packages and implement true change control procedures.
Thanks in advance, Kevin
August 7, 2014 at 10:28 am
You don't mention Source Control until the end of your post and I'd recommend starting with Source Control if you are developing standards. There are plenty of free/open source source control products out there (subversion, git, mercurial) so that there really isn't valid reason not to have source control and having a branching & merging strategy. So having said that this is how I'd do it:
1. Projects/solutions added to source control. You'll have to determine your branching strategy, but to keep it simple I'd at least have a Production branch and a development branch. You can get more complex with versioning if you want.
A. If vendor supplied check that in as a branch, then create a development branch you work in so you always have the base release branch from the vendor.
2. Changes made and checked into source control - I prefer a distributed development model where the developer has all they need installed locally in order to unit test, then check-in to source control, then deploy to a shared dev environment for integration testing vs. the shared development model where there is a dev environment that everyone works in.
3. Test in dev - more integration than unit testing
4. Deploy to QA for QA testing
5. Deploy to Production
For 7A below you don't have the option of deploying a single package in the Project Deployment model so you have to deploy an entire Project (not solution as a solution can consist of multiple projects).
You don't mention configurations at all, so you really need, especially for the package deployment model, a defined process for configurations so you can move packages between environments without having to change connection strings, etc.. as they should be dynamic based on package configurations. You can do it differently with the project deployment model, but you still need to have standards for that.
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
August 7, 2014 at 12:03 pm
Thanks Jack. I will make note of your suggestions regarding using source control. However, for now (without the source control), are there any suggestions to the above mentioned guidelines to test/deploy packages?
Regarding your comment on Solutions and Projects, what is the difference between Solution vs. Project vs. Package? I see a Solution as having one or more projects, a Project as having one or more packages, and a package as the smallest building block. The Solution is the .sln file, the Project is the .dtproj file and the package is the .dtsx file. I am just not sure as to when someone should open which file type? For example, I normally open the .sln file but could I also open the .dtproj and .dtsx files? What is the downfall of working with the .dtproj and .dtsx files?
In Step 2.A above, is it okay for the Developers' to copy just the .dtsx file from the Vendors Folder to the Developer's Folder for testing purposes?
In Step 3, above is it okay for the DBAs to copy just the .dtsx file from the Developers' Folder for code review, test and deployment?
As you can see, I just want to be sure as to which file (.sln, .dtproj or .dtsx) we should be working with. (Not to mention the .ispac file in the \bin\Development directory.)
Thanks, Kevin
August 7, 2014 at 1:28 pm
Solution = Container for projects. For example I have a solution that has an SSIS project, a database project, and an SSRS project that all relate to one process.
Project = specific type of container. C#, SSIS, SSRS, Database, etc...
Packages = specific building block within and SSIS project.
Whenever you create or open a project in visual studio either the existing solution is opened or a new solution is created. You really can't open a project without a solution, but you can save a project and not save the solution.
In the example you give, I'd be opening the solution in most cases.
All of the things you list in your original approach are valid, but I definitely see problems with copying files around based on developer without using source control as a potential issue as you may have multiple developers making changes to the "same" package and then only get one change in production and then have that be overwritten by the 2nd developer's changes. You really need to have one solution/project that the developers work from so you have one version of the truth. That's where source control comes into play because that is the truth, and it manages all the copying around for you as you have a central repository on a server, and then each developer has a local workspace they check files out to to work on them.
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
August 18, 2014 at 4:22 pm
I had posted this original post on August 7, 2014. The original post contained
Step 3 below (in between the lines):
----------------------------------------------------------------------
3) Developers should make changes\modifications in their folder.
If developers decide to make changes\modifications in the Vendor's Folder than fine; just please be consistent. The DBAs need to know where to grab the latest, tested package for code review, test and deployment.
The DBAs would then move the tested package to the DBAs' test and production locations:
I:\SIS\SQLServer\SSISPackages\Test\PackageFolder (for TESTDBServer Packages)
I:\SIS\SQLServer\SSISPackages\Production\PackageFolder (for PRODBServer Packages)
----------------------------------------------------------------------
Now we are trying to implement Environments and Variables. Again, we are a small shop, trying to keep things simple and not using a source control system.
By using Environments and Variables, is there a need to have two directory locations (as stated in Step 3 above) one for test and production? Seems like we could have one directory location and control which environment (test or production) the package runs against via Environments, Variables and package parameters.
Where should the package parameters (which define the Environments and Variables) be changed? Should they be changed within Visual Studio and saved within the Solution? Or should they be changed within the Integration Services Catalogs (in SSMS) by right clicking the package and selecting Configure? Or should they be changed within the Job (in SSMS) by opening the Job Properties, selecting Steps Page, clicking Edit, and selecting the Configuration Tab?
Still wishing that there was one document to explain the deploying and implementation of SSIS Packages and Jobs: http://www.sqlservercentral.com/Forums/Topic1602521-2799-1.aspx
Thanks, Kevin
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply