SQL SSIS Package development standards

Developing a SQL  SSIS Package development standards is important to standardise the development and implementation of SSIS packages , allowing efficient deployment and common terminology for  troubleshooting. I’ve included an example list based on experience and good practise.

 Example Details

 1. Schedule large ETL on  production servers during out of business hrs or less busy periods to minimise contention

2. Before deploying a  resource-intensive SSIS packages create a performance impact analysis and confirm the system can absorb the resource requests.  

3. Use standard naming conventions. This will make it easier for you and SQL Team to debug or modify your packages.

Rename all default name and description properties using standardised naming conventions.

4. Include annotations in packages to make it easier to understand what is going on during the development period and post go-live

5. Use Sequence Containers to organize package structures into logical work units.

6. Scope variables for the containers for which they are required.

7. If  data is already pre-sorted, use IsSorted=TRUE , therefore decreasing unnecessary sorts – releasing resources for other processes.Read about SORT optimisation on SORT IN TEMPDB and Sort Warnings

8. Return only required columns . Avoid selecting an entire table. Use OLE DB Source component or the Lookup Component to select data. Added benefit of efficient buffer usage

9. Use the SQL Server Destination for inserting data, instead of the OLE DB Destination to boost performance. Weigh up against ease of implementation

10. Prioritise usage of custom \tasks   rather than script task based on reuse value

11. Performance Test everything before deploying on live. Use DTExec.

 12.  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.

13. Connection Managers : Try and use clear and information rich names. Avoid using server

 15. Use template packages for logging, event handling and configuration.Standardisation

 16. Use a common folder structure,for deployment purposes, rather than to SQL Server .This is to ensure consistency across all environments Use the following structure:

 ..\Root\CheckpointFiles

...\Root\Configurations

...\Root\ErrorFiles

...\Root\Logs

...\Root\Packages

...\Root\RawFiles

Read More

 SSIS version in SQL Server

SQL SSIS and service pack upgrade

SQL Server – Datetime date range , AS400 and OPENQUERY

I executed an OPENQUERY statement onto a Linked Server pointing to an AS400 database. For the datetime data types some problems can occur, if the dates are out of range.

The SQL Server Datetime function presents a date. The date is combined with the time based on 24 hrs clock. One of its characteristics is the Date Range. The Date Range according to BOL is January 1, 1753, through December 31, 9999.

For the YYYY   element   (representing the year ) of the datetime – this means a range of 1753 to 9999 .

One of the consequences is running an OpenQuery onto a AS400 – is the use of ISO 8601 date  '0001-01-01'.  The '0001-01-01' is acceptable to the iSeries, but will throw an error on SQL Server. The It is not uncommon for a AS400 developer to program for a 0001 year.The date data type columns on AS400  map to datetime data type in SQL Server. This causes an exception when the data range is out of 1753 - 9999

In SQL Server 2008 – the date functions complies with the ANSI SQL standard definition for the Gregorian calendar: "NOTE 85 - Datetime data types will allow dates in the Gregorian format to be stored in the date range 0001–01–01 CE through 9999–12–31 CE."

This gives some options to selecting AS400 date columns through OPENQUERY. You can either deal with it programmatically or request the date is dealt with at source.

Some  workarounds:

1)       Change the ‘0001’ type data to NULL at AS400

2)       Import into a SQL Server table as VARCHAR

3)       Use CASE statement to deal with 01/01/0001

Related Posts

SQL Server Import Data from DB2 AS400 iSeries

SQL Server - How to setup ODBC for IBM AS/400

SQL Server - Generate SQL INSERT from Excel

Generate SQL Server INSERT statements with Excel Concatenate.

A developer requested  access to a Production database. The details were to COPY and PASTE from a Excel spreadsheet into Access , which then updated SQL Server.  Upon asking him , why he used this method , he said “It’s the way I always insert this data to the lookup table”.

There are better ways . One way , is to use the “Concatenate” function in Excel.  It concatenates the ascii portions of the statement, adding single quotes where relevant.

In this example , there are four rows with the values in col A and B.  Create the formula for the first line, the copy to  the rest of the lines.

Copy and Paste the the INSERT statements directly into SQL Server Management Studio or create a text file.

See Also

Powershell , Excel charts and data presentation

Excel Connect to SQL Server

Data Loading Performance Guide

Bulk loading with large amounts of data  requires strategy.  The Data Loading Performance Guide for SQL Server 2008 and SQL Server 2005 is a very useful document.

 Highlights

Understanding Minimally Logged Operations
Trace Flag 610 (sql server 2008)
Summarizing Minimal Logging Conditions

Bulk Load Methods
Integration Services Data Destinations
BCP
BULK INSERT
INSERT ... SELECT
SELECT INTO

Choosing Between Bulk Load Methods
Data Compression and Bulk Load

Other Minimally Logged and Metadata-Only Operations
Solving Typical Scenarios with Bulk Loading
Optimizing Bulk Load

Read More

 View the the Data Load Performance Guide

Bulk Insert CSV into a SQL Server table - SQL Server DBA

SQL Server architecting write-intensive databases - SQL Server DBA

SSIS version in SQL Server

Integration Services is the management interface for  SSIS packages. SQL Server uses  the “MsDtsSrvr.exe”  for executing packages

To highlight the point, configuring the firewall to access Integration Services requires allowing access to port 135 for the “MsDtsSrvr.exe”. 

Access the MsDtsSrvr.exe file to view the version number

1. Find and open the following folder:

“%programfiles%\Microsoft SQL Server\90\DTS\Binn\”

1. Right-click the MsDtsSrvr.exe file |  click Properties.
2. Click the Version tab.
3. View the text after the File version label. The text is the SSIS version number.

The version number represents the SSIS service pack level or the SSIS hotfix level. For SQL SSIS and service pack upgrade