You are currently viewing the BOOK: Microsoft SQL Server 2008 Integration Services Problem-Design-Solution section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
Foreign Key issues with framework when starting several packages from a master.
We have a master package that runs several other packages all using the framework from this book. I think we've set something up wrong because we're gettting the following error every tme the job runs from SQL Server Agent:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_PackageTaskLog_PackageLog". The conflict occurred in database "ETL_TBL", table "dbo.PackageLog", column 'PackageLogID'. End Error Error: 2010-09-22 03:30:01.53 Code: 0xC002F210 Source: SQL LogTaskPreExecute Execute SQL Task Description: 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,
Has anyone else experienced this error when running subpackages in parallel?
Are you getting the same error if you run a single (non-parallel) package? I wonder if the foreign keys have gotten out of sync. If possible, try truncating all framework tables in a test environment and see if that works for you.
I am new to the frame work and I am seeing the same error but in a slightly different circumstance.
When I execute a child package as a stand-alone then the package executes without error and all log entries are complete.
When I execute the child package from an execute package task in the parent I get these errors in the package error log:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_PackageTaskLog_PackageLog". The conflict occurred in database "SSIS_Config", table "dbo.PackageLog", column 'PackageLogID'.
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.
I've triple checked the variable mapping and the result sets and do not see an error. What I am really struggling with is why it works on its own but not as a child package.
I have a package template that I use for both child and master and when I am using it as a master I just remove the configuration setting for parent package batch id. The execute package tasks are in the sequence container of the parent package.
I believe your last statement is the root of your problem. You need to store the batch id in the parent package to be able to pass it to your child package. Add that variable back in, make sure to set it to 0, and you should be good to go!
I have the variable in the parent package and it is set to zero. What I removed from the parent is the package configuration line 4 that looks for the parent id from a parent package. With that back in I get a warning that it is missing.
Even with it in I am getting the errors on the pre-execute event and the doubled entries in the log. What seems to be happening is that both the parent and child preexecute events attempt to log all child tasks.
I have been getting along without the logging but I know I am going to need it as this project grows.
Since I suspect I am missing something obvious and don't want to waste your time I would appreciate a link to a paper that goes into more detail than the book on getting package tasks and logging to play nicely.
Thanks again for a wonderful book and for your help.