Best Practices Implementing Packages and Control Flow in SQL Server Integration Services 2008
In this article i will explain the best top 20 practices for ssis packages and control flow and ssis deployment
- Create an SSIS package that performs a single operation to simplify troubleshooting of packages.
- Create package templates that contain common SSIS package components.
- Define data sources within Solution Explorer that need to be available for multiple packages.
- Create connection managers in an SSIS package so that the connection information is embedded within a package.
- Identify the Control Flow tasks that are required to complete operations of the package.
- Identify the Control Flow tasks that will provide error control within a package.
- Use precedence constraints to control the flow of task execution within a package.
- Use the Data Profiling task to gain familiarity with new data or identify quality issues with existing data.
- Use the Bulk Insert task to move data from a text file to a SQL Server table when no error checking or transformations are required.
- Use the Data Flow task for data loads that requires error checking and transformations.
- Use the Script task to create custom Control Flow tasks that cannot be met by existing tasks.
- Use the Execute Package task to control the flow of multiple packages using precedence constraints.
- Use Sequence containers to group related tasks within a single container that can have package properties set from one object.
- Use For Loop containers when you know the amount of times the iteration of a loop needs to occur.
- Use the Foreach Loop when you are required to iterate through objects but do not know how many times the loop should iterate.
- Use variables as the platform for passing the values between tasks when you are required to pass values from one Control Flow task to another.
- Ensure that variables are set at the correct scope to operate correctly.