SSIS: How SSIS Configurations are applied

SSIS configurations are applied on runtime.  Here are 2 details it’s very important to know about how configurations are applied:

Applied once only: Configurations are applied once only, at the beginning of the Package running. All values that are configured are loaded in this time, and not at real-time when they are used in the control flow task. It means that you cannot edit SSIS Configurations sources after package started running (more exactly it won’t apply on current running).

Configurations Order:  When you have multiple SSIS configurations, SSIS applies them is the order they are defined in SSIS configurations window, except “Parent variable” that is always applied at the end, no matter where it’s positioned. Keep it in mind if 2 configs overwrite the same value, to ensure the final value will be the one you want! You can edit the order with the arrows on right side  of the edit window.

If you use dtexec command with configurations in command line, read Microsoft article about changes between SSIS 2005 and 2008.

SSIS: Issue when waiting for 2 tasks completion

Here is a case in SSIS I got. We have a package that performs a first task, then do 2 parallel actions (in screenshot there are data flows, but it doesn’t matter), and then a 3rd step that must wait for 2 parallel actions ending.

The critical fact is that one of 2 parallel actions is done only if condition is true.

Apparently it must be built like that:

The issue: In case the condition is not true, the package will never continue to “Next task”!!

In fact it’s very logic: “Next task” is waiting for 2 DFs to be done, and the first one doesn’t run because condition is false. So it will never send “completed” answer!

The solution is simple:

A new conditioned path was added with the contrary of first DF condition. It’s connected to dummy task (drug a script task without editing it at all).

Data flow is also connected to dummy task with OR logical connection between 2 sources. Then this task and DF2 are connected with AND connection.

This solution is equal to this logical expression:

(

([Condition] AND [Data Flow 1])

OR

(NOT [Condition])

)

AND

[Data Flow 2]

BIDS: “Insufficient Memory” error on running SSIS

On one of our servers, I got this error in Visual Studio BIDS 2005 when trying to run a SSIS package I built:

“Insufficient Memory to continue the execution of the program” in a message box.

It was solved by installing Visual Studio 2005 Service Pack 1.

Link: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=5553

SSIS: Performance in loading many Excel Files to SSIS

On my last project, I tested different methods in SSIS 2008 package to load 9 small Excel files to the same target table (about 10 rows in each file).

Each file is loaded in a  simple dataflow. My objective was to find the which method will run with best performance.

I tested:

·         Parallel running of all 9 dataflows (full parallel).

·         Serial connection between all dataflows (no parallel).

·         Make each dataflow as separate package, master package runs all in parallel.

·         Organize dataflows as 2 or 3 series (half parallel).

Well, here are the results:

Runtimes of ALL of above mentioned method were the same!! All run times were in the same small range, between 2:07-2:13 min.

Every dataflow separately took about 15s, in 2 series it took 25-40s for everyone, etc.. In full parallel design, first dataflow finished after 1:30 min only..

The conclusion is clear: Excel Provider works as one-instance process, that doesn’t use more resources to run faster.

Nice to know..