I have followed the instructions for setting up the framework in Chapter 2, but I am now getting the follow error messages...

Error: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_PackageTaskLog_PackageLog". The conflict occurred in database "SSIS_PDS", table "dbo.PackageLog", column 'PackageLogID'.

[Execute SQL Task] Error: Executing the query "exec dbo.LogTaskPreExecute @PackageLogID = ? ,@Sou..." failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Any guidance on resolving the problem would be greatly appreciated.

Thanks,
Stuart

Please disregard this post. I found the error - the name of the LogPackageStart task was spelled incorrectly.
Thanks,
Stuart

Hi Stuart

I am getting the same error as you were but I don't understand your solution.

Can you please elaborate?

Thanks in advance

I have managed to debug the problem:

In the OnPreExecute event handler, the value of the variable PackageLogID is 0 whereas in the SQL Server Profiler, I can see that a new record was inserted into the table PackageLog and that a new primary key (PackageLogID) was returned.

In summary, the variable does't have the primary key which is why the insert into PackageTaskLog errors with referential integrity error.

Is there a setting that I have missed? How can I ensure the variable has a value before the stored procedure is called?

Thanks in advance.

Fixed!

My problem was that I had put the SQL LogPackageStart task inside a Sequence. The Sequence was casing the OnPreExecute event to fire which of course didn't have the primary key because the SQL LogPackageStart hadn't yet run.

Moved the SQL LogPackageStart outside of the sequence and now the error is gone.

Thanks stuartward

I had renamed the SQL execute tasks to meet our naming standard and you post led me to reviewing the associated SP which is hard coded with the templated task names.

I corrected the sp to match the updated name and the issue resolved.