Solved

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

Posted on 2008-10-19
12
510 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
OSQL to execute sql command 26 24
Amazon RDS migrate to SQL Server 3 24
tools to scan a SQL server's problem 14 26
Help With SQL Query 9 30
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

860 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