Post reply

SSIS Package Hangs

  • August 24, 2007 at 7:01 am

    #180480

    Hi,

    I am running a fairly simple data flow task that takes a flat file source into a derived column task then into a lookup. If the lookup succeeds it goes to an OLE DB Dest. If the lookup fails, the row goes to another derived column where I basically assign a default value and then send it to another OLE DB Dest.

    This has worked flawlessly on about 10 different flat files but on one file it stops executing and everything from the lookup down turns yellow and it just stops. There's nothing useful in the Progress list. I also checked the flat file and it is normal. Ctrl+F5 does the same thing.

    Any ideas of how I can debug this or the cause of this? Thanks in advance for your thoughts.

  • August 27, 2007 at 5:35 am

    #729499

    I do not know if this will be helpful, but I had a problem with a timeout occurring.   It caused the program to "hang".

    Can you make the file smaller just to see it it relates to file "size", and not file structure ? 

  • August 27, 2007 at 8:32 am

    #729556

    Update: It's not directly related to file size but to how many rows fail the lookup. If over 433 rows exactly fail the lookup and go to the second derived column task, the package fails.

    I've found the problem. I'm just not sure how to fix it.

  • August 27, 2007 at 2:13 pm

    #729641

    Try redirecting the flow of the second derived column task, as well.  It sounds like this new file has a value that is failing the second derived column task.

    Have you considered creating a logging process using package event handlers?  You can insert into a table whenever events happen (I record PreExecute, OnError, OnWarning and PostExecute).  I use the following expression in an ExecuteSQL Task to do it:

    "INSERT INTO [dbo].[SSISLog]

               ([PackageName]

               ,[EventType]

               ,[ExecutionID]

               ,[PackageID]

               ,[SourceName]

               ,[SourceID]

               ,[ErrorCode]

               ,[ErrorDescription]

               ,[InteractiveMode]

               ,[MachineName]

               ,[UserName]

               )

         VALUES

               ('"+ @[System:ackageName] +"'

       ,'"+ @[System::TaskName] +"'

               ,'"+ @[System::ExecutionInstanceGUID] +"'

               ,'"+ @[System:ackageID] +"'

               ,'"+ REPLACE(@[System::SourceName],"'","''") +"'

    ,'"+ @[System::SourceID] +"' 

     ,"+ REPLACE((DT_STR, 15 , 1252) @[System::ErrorCode],"'","''") +"

     ,'"+ REPLACE(@[System::ErrorDescription],"'","''") +"'

     ,'"+ (DT_WSTR, 6) @[System::InteractiveMode] + "'

    ,'"+ @[System::MachineName] + "'

    ,'"+ @[System::UserName] +"'  )"

    Hope this helps...

    Dan

  • August 29, 2007 at 10:37 am

    #730188

    Thanks for your suggestions Dan. I almost positive it's not the second derived column task that's failing. The numbers that appear next to the arrows in the Data Flow tab that tell you how many rows are flowing through that direction are all correct. i.e. The number next to the green arrow coming out of the second derived column and into the OLE DB Dest is correct. Besides, all that task does is replace my "EventID" column that failed the lookup earlier with a "1".

    The problem is that the package doesn't fail. It just seems to stop executing and doesn't throw any errors. I am now running almost every event handler possible but with no new helpful info. If anyone has any suggestions, please let me know.

  • January 24, 2008 at 8:39 am

    #771313

    I have the same issue.

    But there are suggestion from Mircosoft, to set Partial or None to Cache property of the lookups.

    But this didn't work for me. Buit worthwile trying.




    My Blog: http://dineshasanka.spaces.live.com/

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply