2

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

Improve this question
6
  • You should add the error message, more information about the Foreach loop container and the data flow task, how variables are used to import the data dynamically. Moreover, adding screenshots will make it easier to understand more details. Commented Feb 2, 2022 at 8:22
  • Sure, will add the screenshots.. Actually its not giving any error message, whenever I execute the package it crashes and closes the visual studio.. I have to kill the debug task in task manager before the next execution of the pkg.. Commented Feb 2, 2022 at 8:52
  • you should also mention that in the question Commented Feb 2, 2022 at 8:54
  • okay, will include this info as well, thanks.. Commented Feb 2, 2022 at 8:57
  • still there are several required screenshots: (1) The ForEach Loop Container Editor: (a) the variables mapping tab, (b) foreach configuration tab, (c) Expressions used (since the screenshots show the fx mark on the container). (2) The Flat File connection manager expressions. (since you are loading file dynamically) Commented Feb 2, 2022 at 9:54

1 Answer 1

Reset to default
1

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.
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks a lot for your inputs! Bulk Insert ran without any issue. Is it possible to configure Bulk Insert to skip headers(FirstRow) in the file?
@Vicky stackoverflow.com/questions/1029384/…
@Vicky If this answer solved your issue feel free to mark it as accepted
sure, thanks so much for your help!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.