x

Import Multiple Flat Files to Multiple tables

Hi, I need to import around 250 Flat files with different schemas to their corresponding Tables. I have tried using a Dataflow task with for each loop to assign FileName Var to Table Var in OLEDB dest, to no avail. I have aslo tried using a SQL Script task with BCP to no avail. Any thoughts?

more ▼

asked Feb 19, 2013 at 10:57 AM in Default

avatar image

nicbyte
50 1 3 5

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

HI, I managed to get this working with the help of Cozyrocks Data flow plus which allowed me to load dynamic data to dynamic tables.

Kind Regards

more ▼

answered Feb 22, 2013 at 07:39 AM

avatar image

nicbyte
50 1 3 5

(comments are locked)
10|1200 characters needed characters left

How are the files associated to the databases? Have you tried LogParser? It might let you tackle the multiple files pretty simply but the multiple database destinations could be troublesome unless there is a clear link between source and destination.

more ▼

answered Feb 19, 2013 at 12:03 PM

avatar image

Fatherjack ♦♦
43.8k 79 99 118

Hi Jonathan

Each File has an Table already created for it in the DB. So lets say I have a branch file with column 1,2,3 there would be a branch file table with column 1,2,3.

Feb 19, 2013 at 12:12 PM nicbyte

Hold on. Your question says "corresponding databases" but you now say "Each file has a table". Can you confirm the situation please?

Feb 19, 2013 at 12:13 PM Fatherjack ♦♦

Sorry I meant tables. Each file has to go into its corresponding table. Sorry for any confusion.

Feb 19, 2013 at 12:15 PM nicbyte

OK, when you get a chance please edit the question to avoid misleading other readers.

Personally, I would use LogParser to import all the data into a single table in an import schema and then use TSQL to move it from there to the locations you choose.

LogParser will import the data with the details of the source file for each row so you can easily identify where the data needs to be placed

Feb 19, 2013 at 12:18 PM Fatherjack ♦♦

Just updated the question, I had to run into a meeting when I typed that out and obviously did not give the information needed. The Schemas of the files are all different therefore I tried using a table name variable for the OLE destination. The variable gets its value form the for each loop filename variable. I managed to write an expression to parse only the filename less path and extension but still fails. I also wrote in a small c# messagebox.show cmd to confirm the variable expression only parses the filename which exactly matches the table name.

Feb 19, 2013 at 12:32 PM nicbyte
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1219
x22

asked: Feb 19, 2013 at 10:57 AM

Seen: 1908 times

Last Updated: Feb 22, 2013 at 09:56 AM

Copyright 2017 Redgate Software. Privacy Policy