General Rules & STD

General Rules STD SQL SERVER

  1. All File Paths and Connections should be setup as Variables and apart of the Configuration file so that it can be changed within the Configuration file when moving the package from one environment to another.
  2. All SSIS packages should called from SQL Agent Jobs.
  3. Use the native OLE DB provider instead of the ODBC provider when importing and exporting data because it provides better performance.
  4. The Bulk Insert task provides the fastest data loads into SQL Server
  5. SSIS lookups slow down performance. Use a T-SQL statement (stored procedure is best) to perform the same function within the SSIS package.
  6. Execute SQL Task is used to run a single or multiple SQL statement at a time. The results can be returned as a single row, full result set and XML.
  7. Avoid using global variables or COM objects for performing lookup type functions as they are slower than using a SSIS lookup.
  8. Use the SQL Command to select data from the Source rather than the View or Table. Do not use Select (*) Statements as it will take up more memory to determine the column Metadata.
  9. When working with a relational source data use a WHERE clause to filter the data to the rows you need rather than rely on a transformation in the pipeline to eliminate rows. When working with large flat file(s), break up the source file where possible and process the resulting files using sequential Data Flow Tasks to minimize I/O contention.
  10. The column width is affected by the data type. You might need to recast data types in a relational source query or manually edit column widths for other sources to reduce the column width to the smallest possible size that can still accommodate the data coming from the source.
  11. The SQL Query should contain code to do data type conversions that might be required. For e.g. Convert and Cast Statements can be embedded in the SQL, rather than adding a separate transformation to convert data types
  12. Add an order by to the SQL Statement rather than adding a Sort Transformation.
  13. The Data Pump Task is faster than a Data Driven Query within a SSIS package, if there is a one-to-one mapping of the columns and no transformations are involved when moving data between tables. If there are transformations involved, then a Data Driven Query will offer better performance.
  14. For transformations, setting an error threshold greater than 0 is NOT recommended unless the package is designed to only load new records. Otherwise, there will be duplicate records loaded when rerunning the package after a failure.
  15. Reduce ‘On Warning’ events at execution time. This helps in avoiding extra execution time overhead of package and in turn improves overall performance of package execution.
  16. When calling an SQL Server stored procedure that will execute a BCP (Bulk Copy Process) command, the SSIS package needs to commit all transactions before calling the procedure, because a BCP command will control its own transactions and does not allow a calling procedure to handle it. Therefore, the previous step before the BCP step must commit its transactions on successful completion of package.
  17. Do not use ActiveX scripts because they will not be supported in future versions of SSIS. ActiveX scripts also do not migrate during migration from DTS Package to SSIS Package. They should be replaced with .NET Scripts
  18. Use Sequence Containers to organize package structure into logical units of work. This makes it easier to identify what the package does and helps to control transactions.
  19. Use caching in your LOOKUP components where possible. It makes them quicker.
  20. If you want to conditionally, execute a task at runtime use expressions on your precedence constraints. Do not use an expression on the ‘Disable’ property of the task.
  21. Don’t put all configurations into a single XML configuration file. Instead, put each configuration into a separate XML configuration file. This is a ‘modular’ approach and means that configuration files can be reused by different packages more easily.
  22. While configuring any OLEDB connection manager as a source, avoid using ‘Table or view’ as data access mode, this is similar to ‘Select * From’. Always try to use ‘SQL Command’ data access mode and only include required column names in your SELECT T-SQL statement.
  23. Sorting of data is a time consuming operation, in SSIS you can sort data coming from upstream using ‘Sort’ transformation, however this is a memory intensive task and may degrade the overall package execution performance. It’s better to perform the sorting operation at the database level where sorting can be performed within the query. SQL Server database sorting is much refined and happens at the SQL Server level, which results in overall performance improvement in package execution.
  24. Use Flat File Connection Manager very carefully, creating Flat File connections with default setting will use data type string [DT_STR] as a default for all the column values. This may not be the right option because you might have some numeric, integer etc. in your source, passing them as a string to downstream tasks would take unnecessary memory space and may cause errors at tasks that follow in the package execution.
  25. Use the SSIS feature, Checkpoint, to restart failed packages from the point of failure.
General Rules & STD was last modified: October 1st, 2017 by Justin Antony

Leave a Reply