Making a mistake is easy done, but what did I do wrong?
(Error in SSIS when running package HRESULT_FAIL has been returned from a call to a COM component)
Today i ran in the following problem. I have a simple package template with the below Sequence contair in it.
The first SQL-Task gets the row count from the source table used as Source in the second SQL-Task MERGE command.
This second SQL-Task is doing a T-SQL MERGE command to insert new dimension records to a dimension table. Running this SQL statement in SQL Server Management Studio doesn’t trigger any error.
The third task gets the row count from the target table used in the second SQL-Task MERGE command.
I used the template on many packages without any problem, but when I ran it this time I do have the following problem.
When running the package in BIDS the container gives me an error:
[Execute SQL Task] Error: Executing the query “MERGE
[Dimensions].[tblDim_.....] AS Target
U…” failed with the following error: “Error HRESULT E_FAIL has been returned from a call to a COM component.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.
So what is causing this error? The MERGE commands has a T-SQL Statement that contains a WHERE clause using a variable. something like:
The error doesn’t realy give me a clue on what is going wrong.
When we look at the task that errors I do see the following on the parameters tab:
Do you see what’s wrong at first glance? I had to look twice or maby 3 times 
The below image shows the correct version.
This time the package runs without any error.
Next time I would need to be more carefull when clicking with my mouse somewhere or just give it more attention when configuring parameters….
