Search code examples
sql-servervisual-studiossisetlforeach-loop-container

SSIS Foreach Loop Container to read files and load into DB getting crash during execution

I'm trying to load multiple files from a location into DB using Foreach Loop Container & DataFlow task in SSIS.

It's getting crashed while I try to execute the package. It's not giving any error message, whenever I execute the package it crashes and closes the visual studio app immediately. I have to kill the debug task in the task manager for the next execution of the package.

So I tried the below steps:

  1. I used a FileSystem task instead of DataFlow task to just move all the files from the source to the archive directory, which ran fine without any issues.

enter image description here

enter image description here

  1. Ran the DataFlow task individually to load a single file into DB, which was also executed successfully.

I couldn't figure out what was going wrong here. Any help would be appreciated! Thanks!

Screenshots

enter image description here

enter image description here

enter image description here

enter image description here

enter image description here

enter image description here

enter image description here

enter image description here

Solution

  • All screenshots look fine to me. I will give some tips to try to figure out the issue.

    Since the File System Task is executed without any problem, there is no problem with the ForEach Loop Container. You can try to remove the OLE DB Destination and replace it with a dummy task to check if it causing the issue. If the issue remains, it means that the Flat File Source could be the cause.

    Things to try

    1. Make sure that the TargetServerVersion is accurate. You can learn more about this property in the following article: How to change TargetServerVersion of my SSIS Project
    2. Try running the package in 32-bit mode. You can do this by changing the Run64bitRuntime property to False. You can learn more about this property in the following article: Run64bitRunTime debugging property
    3. Running Visual Studio in safe mode. You can use the following command devenv.exe /safemode.

    Workaround - Using Bulk Insert

    Since you are inserting flat files into the SQL database without performing any transformation. Why not use the SSIS Bulk Insert Task. You can refer to the following step-by-step guide for more information:

    As mentioned in the official documentation, make sure that the following requirements are met:

    • The server must have permission to access both the file and the destination database.
    • The server runs the Bulk Insert task. Therefore, any format file that the task uses must be located on the server.
    • The source file that the Bulk Insert task loads can be on the same server as the SQL Server database into which data is inserted, or on a remote server. If the file is on a remote server, you must specify the file name using the Universal Naming Convention (UNC) name in the path.