1.1.1 Create a project for...
Except for very small projects I create a solution with a project for my Project Templates and the main SSIS project. The others are optional.
SSIS Main Project
SSIS Project Templates
Templates can become very project specific. Even though I have some base templates used to enforce some best practices and some basic instrumentation, I do not want my project templates to get limited by them. Therefore my templates tend to get very project specific. Since this is the case, I want to keep them separate from my project packages, but part of the solution.
SSIS Project Sandbox
Used to..
· Test different designs
· Play with unfamiliar tasks and components
· Communicate Concepts without Instrumentation
SSIS Project Deployment
Database Project
Reporting Services Project
1.2 Package Design
1.2.1 Keep Packages as small as possible
Easier to understand
Easier to maintain
SSIS Designer slows down with large packages
More flexible
Use "Control" packages to call other packages
1.2.2 Use a consistent naming convention
1.2.3 Variables
Narrow Scope
If there is not a good reason for it to be global then scope it to the container it is used in.
This way, if a container is copied the variables will go with it.
Combine with Expressions
1.2.4 Use Expressions
Variable to set properties
1.2.5 Machine Independent
2 Phase Configuration
For me the easiest way to make my packages machine independent and easy to maintain is to do, what I call, a 2 phase configuration. I tried to find a blog post on this, but I was unable to. Either my search skills are very bad, this is a bad idea, or I need to write a post myself.
The 2 phased approach I like combines the indirect configuration method with the SQL Server Configuration method. Some people prefer the XML Configuration type as their second phase. First use the indirect method to store property values of only the properties that will change from server-to-server such as directory paths. Also have one SQL Connection string that points to the SQL Server Database that has the SQL Server Configuration Table.
1.2.6 Use RAW Files
Note: Set DelayValidation=TRUE
1.3 Auditing / Instrumentation
Andy Leonard: SSIS Design Pattern - ETL Instrumentation
Part 4
http://sqlblog.com/blogs/andy_leonard/archive/2007/12/26/ssis-design-pattern-etl- instrumentation-part-4.aspx
Kimball Group
1.4 Create Templates
1.4.1 Template Location
%ProgramFiles%\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems
Since template become project specific it is a good idea to create a folder for each project.
1.4.2 How to use
To Open
Right-click on a SSIS project in BIDS and select Add | New Item...
Individualize
SSIS does not have a true template system. An exact copy is made of the file. This means there are some properties that need to be updated each time a new package is made from a template. To expose the properties for the package right-click on the Control Flow design surface and select Properties
Create new GUID
Rename
Change the Name Property
Version
Change the ID property by clicking the drop-down arrow and selecting <Generate New ID>
CreationDate
CreatorName
1.4.3 If you are new to templates ...
This is a good place to start
1.5 Debugging
1.6 SSIS Report Pack
2 Tricks
2.1 OLE DB Inline Comments
3 My Favorite SSIS Resources
3.1 Books
3.2 Web Sites
3.2.1 BI Thoughts and Theories
3.2.2 SSIS Junkie
3.2.3 Arcane Code
3.2.4 BI Thoughts and Theories
3.2.5 SQL Server Data Mining
3.3 Podcasts
3.3.1 www.sqldownunder.com
3.3.2 Microsoft