Thoughts on Error Handling in SSIS

I have been thinking about a number of issues that need to be accounted for when developing solutions in SSIS.  Some of them relate to data profiling, but a number  of them relate to SQL tasks, Data Flow and referencing the file system.  Please feel free to add to this list or comment on any of these items.

I will follow up this post with some posts on logging and error handling.

Before package:

Security User/User group(service setup)

Zip and encryption changes /errors

Data Copy or Move file failed

Custom component configuration

Zero byte files

Files not present

Execute SQL Tasks:

Database not present

Insufficient drive space

Table not present

Inadequate permissions to SQL server

Inadequate space on SQL server

SQL Server Memory errors

SQL script errors: arithmetic, dates out of range

For Each Loop (FEL):

Path / sub drive not available

Drive letter issues:   UNC standard, source configurations

Inadequate permissions to folder

File not found / file moved

Duplicate file already exists in subdirectory

SSIS memory errors

Data Flow Task:

Data type issues  (Unicode, ASCII, EBCDIC)

Change in layout:  field types, field names, new fields, fields removed

Numeric field changes to Varchar          

Invalid / different delimiters

Unreadable characters (international characters)

Nulls / not nulls

Variable record length data (a number of issues to be addressed here)

 

File System Task:

Duplicate file in destination (can be overwritten)

Permissions to directory

Path / sub drive not available

Drive letter issues:  UNC standard, source configurations

Inadequate permissions to folder

Writing to an Access DB:

Access db over 2GB

Access db locked

Access db missing table or columns

Drive path invalid

FTP Task:

Server inaccessible

Permissions:  logon / read / write

Space issues

Connectivity break

Send Mail Task:

Email server interruption

Email address change

SMTP port not open on SSIS server

Advertisements

Tags: ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: