CODEWITHSUNDEEP
sql-serverssis
pkExec
pkExec

Reputation: 2066

Mass import txt files in a single SQL Server table, using filename as key column

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?

Upvotes: 0

Views: 159

Answers (1)

pkExec
pkExec

Reputation: 2066

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)

Upvotes: 1

Related Questions