Browsed by
Category: SSIS

SSIS Packages with Unspecified Error

SSIS Packages with Unspecified Error

Have you ever been haunted by a failing SSIS package with an ‘Unspecified Error’, leaving no information to trace? I have, and I suspect so has everyone who ever deals with SQL Server. The reason for this is fairly simple to understand, it would just be helpful is Microsoft made their errors easier to understand. SQL Server was consuming almost all the memory and taking higher priority over SSIS.  As a general rule of thumb, I tend to limit SQL Server…

Read More Read More

SSIS is CaSe SeNsiTiVe

SSIS is CaSe SeNsiTiVe

[OLE DB Destination [16]] Warning: The external columns for component “OLE DB Destination” (16) are out of synchronization with the data source columns. The column “EmployeeId” needs to be added to the external columns. The external column “EmployeeID” (82) needs to be removed from the external columns. [SSIS.Pipeline] Error: “component “OLE DB Destination” (16)” failed validation and returned validation status “VS_NEEDSNEWMETADATA”. Even though SQL Server can be set to be case insensitive – SSIS isn’t! Make sure that when you…

Read More Read More

SSIS Internals / Execution Trees

SSIS Internals / Execution Trees

The SSIS Runtime Engine executes the Packages.  It executes every Task other than Dataflow Tasks in the defined sequence.  Whenever the SSIS Runtime Engine encounters a Dataflow Task, it hands over its execution to the Dataflow Pipeline Engine.  The Dataflow Pipeline Engine breaks the execution of a Dataflow Task into one or more execution tree(s) and may execute two or more Execution Trees in parallel to achieve high performance. An Execution Tree has a similar structure to a tree.  It…

Read More Read More

Effect of Rows-Per-Batch and Maximum Insert Commit Size Settings

Effect of Rows-Per-Batch and Maximum Insert Commit Size Settings

Rows per Batch The default value for this setting is -1 which specifies all incoming rows will be treated as a single batch.  You can change this default behaviour and break all incoming rows into multiple batches.  The allowed values are only positive integers which specify the maximum number of rows in a batch. Maximum insert commit size The default value for this setting is ‘2147483647’ (representing the largest value possible for a 4-byte Integer type) which specifies all incoming…

Read More Read More

Effect of OLEDB Destination Settings

Effect of OLEDB Destination Settings

There are couple of settings with OLEDB destination which can impact the performance of data transfer as listed below: Data Access Mode This setting provides the ‘fast load’ option which internally uses a BULK INSERT statement for uploading data into the destination table instead of a simple INSERT statement (for each single row) as in the case for other options.  So unless you have a reason for changing it, don’t change this default value of fast load.  If you select…

Read More Read More

Slowly Changing Dimension Wizard vs Kimball

Slowly Changing Dimension Wizard vs Kimball

The SCD Wizard has a few things going for it – it’s quick and easy to implement, it handles most SCD scenarios ‘out-of-the-box’, and its multi-component approach means you can customize it with the functionality you need. However, a major inhibitor is the performance of the transform.  It doesn’t perform that well for a couple of different reasons: The data Lookups are not cached – each row results in an SQL query; OLE DB Command does row-by-row updates; OLE DB…

Read More Read More

Sorting

Sorting

The Merge and Merge Join components require incoming data to be sorted.  The Sort transformation is a ‘Fully Blocking’ task, so if possible select a sorted result-set by using the ORDER BY clause at the source.  Check to see if this is necessary at all by looking for the Clustered Index on the table and the fields you need – however there are times you will be required to use the Sort transformation (eg: pulling unsorted data from flat files)….

Read More Read More

Beware of Implicit Conversion

Beware of Implicit Conversion

When you use Flat File Connection Manager all columns are treat as the String [DT_STR] data type.  You should convert all the numeric data to appropriate data types or else it will slow down performance.  By having all the columns as the String data type you are forcing SSIS to acquire more space in the buffer than is needed for numeric fields – hence the performance degradation.

Lookup Transformations and Cache

Lookup Transformations and Cache

The Lookup transformation has various options that should be considered when optimising the performance of your Packages: Full Cache/Pre-Cache By default, the Lookup uses ‘Full Cache’ mode.  In this mode, the database is queried once during the pre-execute phase of the dataflow.  The entire reference set is pulled into memory.  Afterwards, SQL is not used anymore to process this data.  This approach uses a lot of memory – while not needing SQL Indexes anymore, having enough RAM to hold all…

Read More Read More

Parallel Performance

Parallel Performance

Increasing the parallelism of Packages is probably one of the best ways of gaining dramatic performance increases.  SSIS has been designed to achieve high performance by running the executables of the Package and Dataflow Tasks in parallel.  In addition to efficient design, this parallel execution of the SSIS Package executables and Dataflow Tasks can be controlled by the two properties listed below: MaxConcurrentExecutables This Package property defines how many tasks (executables) can run simultaneously.  It defaults to -1 which is…

Read More Read More