error when executing DTS
-
Hi guys!
Hello from Lisbon, PORTUGAL!
Here's my problem:
I'm trying to execute a DTS package which will fill a db with data from another db.
When I execute the DTS the following error message pops up:
"Cannot truncate table 'table_name' because it is being referenced by a FOREIGN KEY constraint".
Any thoughts on this matter will be welcomed!
THX! Bye!
Bruno
-
You most likely have an ExecuteSQL task in the package that's trying to issue the TRUNCATE statement against the mentioned table. As the error message suggests, you can't truncate a table that has foreign key constraints.
Should the foreign key constraint be there in the first place? If so, then you'll need to use the DELETE statement to remove rows from the table. If not, then remove it and the TRUNCATE will work as normal.
--------------------
Colt 45 - the original point and click interface
-
Thx for the reply!
The first task on my DTS is as follows:
truncate table dm_conso.dim_base_natural
truncate table dm_conso.dim_campanha
truncate table dm_conso.dim_caracteristicas_cabelo
truncate table dm_conso.dim_codigo_postal
truncate table dm_conso.dim_escalao_idade
truncate table dm_conso.dim_fonte_contacto
truncate table dm_conso.dim_local_compra
truncate table dm_conso.dim_modo_contacto
truncate table dm_conso.dim_motivo_contacto
truncate table dm_conso.dim_ofertas
truncate table dm_conso.dim_procedimento
truncate table dm_conso.dim_produto
truncate table dm_conso.dim_tempo
truncate table dm_conso.dim_tipo_accao
truncate table dm_conso.dim_tipo_contacto
truncate table dm_conso.dim_tipo_pele
truncate table dm_conso.dim_titulo
truncate table dm_conso.tmp_localidade
truncate table dm_conso.tmp_clients_affaires
truncate table dm_conso.tmp_historique_lien_htc
truncate table dm_conso.indicadores
What you suggest is having a task prior to this one that issues a DELETE statement?
Thx, Bruno.
-
If the foreign key constraint is correct, then you need to replace the TRUNCATE statements with DELETE statements.
Sounds like you're loading directly into "production" tables, which would explain why you have the foreign key constraints. You should really load the data into staging tables that don't have any constraints. Then perform a series of INSERT/UPDATE/DELETE statments against the production tables.
--------------------
Colt 45 - the original point and click interface
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply