December 2, 2005 at 3:33 am
we run a DTS for loading DATA from txt files. ANd it runs every third time without errors. That 's very stange. The data size is about 200 000 rows. When it stucks (deadlock between select and insert queries) we kill the processes , remove the indexes and run it again. And it works. Have somebody any idea of that trouble?
Thanks
December 2, 2005 at 12:28 pm
Would you describe your DTS package in more detail? What tasks and connections are in the package and what is the workflow?
Greg
Greg
December 6, 2005 at 2:37 am
Greg,
after connecting to the network, the DTS take a text file and insert into the Database. And then there is following Task where it 's stucks:
SELECT dbo.Tb_Temp_Pere.Marche, dbo.Tb_Temp_Pere.CodeBarre,
dbo.Tb_Temp_Pere.Titre, dbo.Tb_Temp_Pere.Auteur, dbo.Tb_Temp_Pere.Editeur,
dbo.Tb_Temp_Pere.Editeur_Grp, dbo.Tb_Temp_Pere.Diffuseur,
dbo.Tb_Temp_Pere.Theme, dbo.Tb_Temp_Pere.Theme_Grp,
dbo.Tb_Temp_Pere.Collection, dbo.Tb_Temp_Pere.Serie,
dbo.Tb_Temp_Pere.Destination, dbo.Tb_Temp_Pere.Destination_Grp,
dbo.Tb_Temp_Pere.Nationalite, dbo.Tb_Temp_Pere.Prix,
dbo.Tb_Temp_Pere.DateParution,dbo.Tb_Temp_Pere.AuteurNom,
dbo.Tb_TopPere.Titre AS TitrePere
FROM dbo.Tb_Temp_Pere LEFT OUTER
JOIN
dbo.Tb_TopPere ON dbo.Tb_Temp_Pere.Marche
= dbo.Tb_TopPere.Marche AND dbo.Tb_Temp_Pere.CodeBarre = dbo.Tb_TopPere.CodeBarre
where dbo.Tb_Temp_Pere.Marche=5.
This the 1st step (select). The second step (insert) look like this:
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
DTSDestination("Marche") = DTSSource("Marche")
DTSDestination("CodeBarre") = DTSSource("CodeBarre")
DTSDestination("Titre") = DTSSource("Titre")
DTSDestination("Auteur") = DTSSource("Auteur")
DTSDestination("Editeur") = DTSSource("Editeur")
DTSDestination("Editeur_Grp") = DTSSource("Editeur_Grp")
DTSDestination("Diffuseur") = DTSSource("Diffuseur")
DTSDestination("Theme") = DTSSource("Theme")
DTSDestination("Theme_Grp") = DTSSource("Theme_Grp")
DTSDestination("Collection") = DTSSource("Collection")
DTSDestination("Serie") = DTSSource("Serie")
DTSDestination("Destination") = DTSSource("Destination")
DTSDestination("Destination_Grp") = DTSSource("Destination_Grp")
DTSDestination("Nationalite") = DTSSource("Nationalite")
DTSDestination("Prix") = DTSSource("Prix")
DTSDestination("DateParution") = DTSSource("DateParution")
DTSDestination("AuteurNom") = DTSSource("AuteurNom")
if isnull(DTSSource("TitrePere")) then
Main=DTSTransformstat_InsertQuery
else
Main=DTSTransformstat_UpdateQuery
end if
End Function
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply