How to upload text files which no fixed name into SQL Server

Question

How to upload text files which no fixed name into SQL Server

cooperkuo

SSC Journeyman

Points: 75
More actions
June 27, 2012 at 7:43 pm

#263329

Hi
My ERP system will generate text data into one folder. The file name rule is data-YYYYMMDDHHMM.TXT I need to create SSIS job to read them and upload to SQL server and after that. delete those text file. What my problem is those text files name does not fixed. How to read them in the SSIS and how to delete them when it finish the upload?. Please advise and thanks advance.
Data example:
data-201206280800.txt
data-201206281200.txt
data-201206281500.txt
data-201206281730.txt
......

Viewing 8 posts - 1 through 7 (of 7 total)

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

Rock from VbCity Hall of Fame Points: 3991 More actions · Answer 1

Hi cooperkuo

It will best for you to get a copy of the following book: Microsoft SQL Server 2008 Integration Services: Problem, Design, Solution

The book is excellent, full of examples including how to handle the problem you described; the solution will required a FOREACH LOOP and FILE SYSTEM components, plus package level variables, it will be too much to explain on this reply.

Hope this helps,
Rock from VbCity

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

This article explains it all:

Looping over files with the Foreach Loop

In your case you need to use the wildcard data*.txt.

But I would recommend you still read the book mentioned by Rock from VbCity, it's one of the best SSIS books around.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

cooperkuo SSC Journeyman Points: 75 More actions · Answer 3

Hi Rock from VbCity & Koen Verbeeck

Thanks your reply.

I use another way to fix my problem.

1. Create a batch file to merge those text file

Code is in below

for %%X in ("C:\TEMP\DATE-*") do (

type "%%X" >> C:\temp\Data_all.txt

echo. >> C:\temp\Data_all.txt )

2. In the SSIS, call merge batch file first.

3. In the SSIS, read the Data_all.txt and upload into SQL Server.

4. In the SSIS, call delete batch file

Code is in below

for %%X in ("C:\temp\Date-*") do (

del "%%X" )

5. Done.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

Nice alternative solution. However, if processing fails midway, you have to start all over again.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Rock from VbCity Hall of Fame Points: 3991 More actions · Answer 5

It does work, but you are creating extra dependencies in your solution, it might seem nice today, while it could become a maintenance monster in the future.

Hope this helps,
Rock from VbCity

cooperkuo SSC Journeyman Points: 75 More actions · Answer 6

Hi Rock from VbCity & Koen Verbeeck

Thanks your comment.

For now, it's easy way to handle it for me. But I will buy that book to check it. Thanks again.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

Koen Verbeeck (6/28/2012)
Nice alternative solution. However, if processing fails midway, you have to start all over again.

It also takes twice the disk space and leaves no chance of identfying the file bad data came from.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)