Important Takeaways from recent SQL Server Integration Services Training
Nov
11
Written by:
11/11/2009 12:55 PM
I attended a SQL Server Integration Services training last week conducted by PragmaticWorks and wanted to share some key takeaways with those of you who develop SSIS packages.
- I got into the habit of writing stored procedures to create database fields derived from other fields. I query our AS400 daily and use SSIS to place my query results into a SQL Server 2005 database so that I can develop reports for given subsets of my query population. Inevitably, there are fields that are needed in the report that are not stored in source data. As such, I would create stored procedures to derive payor group from insurance plan code, current account balance as a formula and other fields. Our instructor advised against this practice and advocated the use of the Derived Column transformation in SSIS. It's far more efficient to include this transformation in your SSIS package than calling the stored procedure as a separate Execute SQL task at the end of your control flow.
- When all else fails, use a Script Task. That is, when you have a task that just doesn't fit any of the existing transformations, rely on Script Task or Script Component to handle the task/transformation. These items permit the use of SSIS package variables and leverage Visual Basic to handle complex logic issues.
- Use variables to make your packages dynamic. Such uses of variables include defining a new destination file output name each time the package is run and setting a variable equal to a value and referencing the variable in a Conditional Split transform.
- Insert break points and data viewers to debug your packages effectively.
- If your data source contains more fields than you need to load in your destination, simply uncheck those fields from the source task so that they are not passed through the rest of the package. This optimizes the package performance and should cutdown on processing time.
I hope these tips are useful for you as well. Happy coding!!