?
Solved

Importing Multiple Flat Files in a Single Table and Moving and Renaming Them Also

Posted on 2008-10-19
12
Medium Priority
?
525 Views
Last Modified: 2013-11-10
Hi all

I am newbie to SQL Server 2005 SSIS. I have a very basic and simple problem. I have a folder say "import" in which multiple flat files (same structure) are coming on hourly basis. I need to read each flat file and import it into a single SQL table. Then I want to move each flat file already read from the "import" folder to another folder say "backup". I also want to rename the files with respect to date and time stamp.

Please tell me how would I achieve. Also please remember that the files are continuously copying into the "import" folder and I only want to move a file from "import" folder to "backup" folder once I read and imported it.

Waiting for your replies.
Thanks in advance.

synaqvi
0
Comment
Question by:synaqvi
  • 6
  • 6
12 Comments
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22756296
Create a new SSIS Project
add a ForEachLoop container to your controlflow.
add a dataflow and a script task to you ForEachLoop container added in the previous step.

In the dataflow you import data from each flatfile and insert into SQL database
in the script task you will move the flat file to another filesystem directory.
helped?

Do you need an example? I can do it for you, but attach here some example flatfiles
Regards!
0
 

Author Comment

by:synaqvi
ID: 22773335
Pedro

First of all thanks for your response.

Secondly, it will surely help me a lot if you can send me an example of the proposed solution. Three sample flat files (in zip format) are attached for your reference.

Thanks in advance.
synaqvi
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22774648
where is the flatfiles? :-)
regards!
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:synaqvi
ID: 22774724
Dear Pedro

I am unable to attach the files. For your reference, I am mentioning the rows in each of the file so you can save them at your end.

File No. 1:
PR1|True|Purchase Requisition No. 1|Full|Computer
PR2|True|Purchase Requisition No. 2|Full|Furniture
PR3|True|Purchase Requisition No. 3|Full|Fixture

File No. 2:
PR4|True|Purchase Requisition No. 1|Full|Computer
PR5|True|Purchase Requisition No. 2|Full|Furniture
PR6|True|Purchase Requisition No. 3|Full|Fixture

File No. 3:
PR7|True|Purchase Requisition No. 1|Full|Computer
PR8|True|Purchase Requisition No. 2|Full|Furniture
PR9|True|Purchase Requisition No. 3|Full|Fixture


Sorry for any inconvenience.
Thanks in advance.

synaqvi
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22774732
send me for my email pedro.perfeito@cgd.pt and I will create the project and attach here!
Regards!
0
 

Author Comment

by:synaqvi
ID: 22774763
mail sent...
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22774768
ok... give me some minutes
0
 

Author Comment

by:synaqvi
ID: 22774806
ok... i am waiting...
0
 
LVL 22

Accepted Solution

by:
PedroCGD earned 750 total points
ID: 22775001
OK...
Open a new SSIS project
add the attached package to it
Update the filesystem path on variables uvFolder and uvFolderAndFilename
Update the filesystem path inside ForEachLoop container
Create the backup Folder in the root of your project
Helped?!
regards!
Pedro
Package-EE34-dtsx.txt
0
 

Author Comment

by:synaqvi
ID: 22775034
checking...
will get back to you in a while...
thanks.
0
 

Author Comment

by:synaqvi
ID: 22775107
got the idea... thanks a lot...
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22775116
Very good!!
regards!!

Pedro
www.pedrocgd.blogspot.com
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question