Here is my issue I am new to 2005 sql server, and am trying to take my old data which is exported to a txt file and import it to tables in sql. The older database is non relational, and I had made several exports for the way I want to build my tables. I built my packages fine and everything is working until I start building relationships. I remove my foreign key and the table with the primary key will get updated for the package again. I need to update the data daily into sql, and once in it will only be update from the package until the database is moved over.
It will run and update with a primary key until I add a foreign key to another database.
Here is my error when running the package when table 2 has a foreign key.
[Execute SQL Task] Error: Executing the query "TRUNCATE TABLE [consumer].[dbo].[Client] " failed with the following error: "Cannot truncate table 'consumer.dbo.Client' because it is being referenced by a FOREIGN KEY constraint.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
You are trying to delete all data out of Client, but are prevent when there is FK. Find out table is related to Client by the FK, perhaps something relate dto teh client like the client's orders? You will need to delete that table first to keep with the current method.
If you do not want delete this other table, then you should not be deleting clients either. You cannot apply a relationship and then expect to be able to violate it during a load.
There are three methods youd could use to avoid this error -
Delete all tables, but in order.
Do not delete. When loading clients detect existing clients and update, versus inserting new clients.
Disable the constraint for the duration of the load, and then reenable afterwards. You can just write some simple T-SQL, ALTER TABLE... to do this, and run it at the start and end of your package so as to disable and enable the constriant(s) respectively. Use some Execute SQL Tasks to run the T-SQL.
|||
I totally understand what you are saying, and I can't delete the table while there is a relationship. I read this article http://blogs.conchango.com/jamiethomson/archive/2006/02/17/2877.aspx that was posted in the forum. I was unable to disable the constraint would I want to disable the PK or FK and from the table I am trying to update or the FK that has the issue.
In my job I have 4 seperate integration jobs that run should this be one large integration job?
