0

I have a folder of txt files. The filenames are of the form [integer].txt (like 1.txt, 2.txt and so on).

I have a table, let's say TableA (id int not null, contents varchar(max))

I want a way to mass import the contents of those files into TableA, populating the id column from the filename. Each file will be a single record in the table. It's not a delimited file.

I've looked into SSIS and flat-file source, but I could not find a way to select a folder instead of a single file (this answer claims it can be done, but I could not find out how).

Bulk Insert is my next bet, but I'm not sure how I can populate the id column with the filename.

Any ideas?

pkExec
  • 1,752
  • 1
  • 20
  • 39
  • 1
    That's already explained in *many* tutorials. The Flat File Source, as the name implies, is meant to read single files. You need a [ForEach Loop container](https://learn.microsoft.com/en-us/sql/integration-services/control-flow/foreach-loop-container?view=sql-server-2017) to read the files in a folder – Panagiotis Kanavos Sep 04 '18 at 08:01
  • Possible duplicate of [Read files from multiple Folders using SSIS?](https://stackoverflow.com/questions/8470732/read-files-from-multiple-folders-using-ssis) – Panagiotis Kanavos Sep 04 '18 at 08:04
  • @Thanks Pano. As you can see, I have already linked myself to the "possible duplicate". The ForEach loop container isn't mentioned anywhere, so that's a good start. Now, I only need to find out how to import the entire file, since the "row delimiter" option in SSIS doesn't contain the "EOF" delimiter. – pkExec Sep 04 '18 at 08:28
  • Google for `SSIS import folder`. The Foreach loop container is explained in the documentation (the best place to start) and any tutorial that explains how to load multiple files – Panagiotis Kanavos Sep 04 '18 at 08:32
  • As for the delimiter, [there's no EOF character](https://latedev.wordpress.com/2012/12/04/all-about-eof/), so it can't be used as a delimiter. The flat file source *can* use different row and field delimiters. Text typically files use CR, LF or CR + LF and SSIS allows that. The flat source property dialog can *detect* the delimiter and even offers {CR}, {LF}, {CR}{LF} as delimiter options in the delimiter field – Panagiotis Kanavos Sep 04 '18 at 08:36

1 Answers1

1

For anyone that might need it, I ended up solving this by:

  • Using a ForEach loop container (Thanks for the hint @Panagiotis Kanavos)

  • Using a flat-file source, setting as row delimiter and column
    delimiters a sequence I know didn't exist in the file (for example '$$$')

  • Assigning the filename to a variable, and the full path to a computed variable (check this great post on how to assign the variables)

  • Using a derived column to pass the filename in the output (check out this answer)

pkExec
  • 1,752
  • 1
  • 20
  • 39