Solved

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

Posted on 2008-10-19
12
509 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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 250 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

770 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