Solved

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

Posted on 2008-10-19
12
506 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
Comment Utility
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
Comment Utility
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
Comment Utility
where is the flatfiles? :-)
regards!
0
 

Author Comment

by:synaqvi
Comment Utility
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
Comment Utility
send me for my email pedro.perfeito@cgd.pt and I will create the project and attach here!
Regards!
0
 

Author Comment

by:synaqvi
Comment Utility
mail sent...
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 22

Expert Comment

by:PedroCGD
Comment Utility
ok... give me some minutes
0
 

Author Comment

by:synaqvi
Comment Utility
ok... i am waiting...
0
 
LVL 22

Accepted Solution

by:
PedroCGD earned 250 total points
Comment Utility
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
Comment Utility
checking...
will get back to you in a while...
thanks.
0
 

Author Comment

by:synaqvi
Comment Utility
got the idea... thanks a lot...
0
 
LVL 22

Expert Comment

by:PedroCGD
Comment Utility
Very good!!
regards!!

Pedro
www.pedrocgd.blogspot.com
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now