Solved

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

Posted on 2008-10-19
12
512 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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

Technology Partners: 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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Throw Error 7 34
Need help separating values from a column and creating a new record 6 46
Upgrading to SQL Server 2015 Express 2 34
Need help with another query 10 38
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

749 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