ETL TXT to SQL Server

Hello

Please read this if u can finish 3 or 4 days from now.

This is a ETL task. We have some txt files we need to move to a SQL Server.

I have SQL Server version 2010 or 2012 free edition. I also have a AWS server where u can work.

This task should be done in a couple of days, after that I will copy code to another Server and stop this EC2 image.

TXT files do not have header. Please provide information about how would u do this ETL task.

Steps:

1 - import txt files, all fields will be character.

2 - copy imported data to a final file

As we do not have headers, when u do step 1 u will populate a table with fields: field1, field2, field3,......

We will have a dictionary (table) and your code will populate a second table with proper name and format:

SourceField DestinyField Type

field1 ID Numeric

Field2 Name Text

Field3 Date Date

etc

We may have 50 fields in step1, but we will populate only some (maybe 28, for example) in step2.

I NEED TO BE ABLE TO CHANGE THIS DICTIONARY.

All txt files are “tab” separated. Some fields, not many, may require some transformation (for example date 20100301 will be 01/03/2010, I mean dd/mm/yyyy). This is some will be transformed from text to date and some from text to numeric. U will know ir because the format in tables in step 2 will be some character, date or numeric.

Files to import - step 1 - 5 destiny files

1 client

2 article

3 Family

4 Invoice Header

5 Invoice lines

Important: #4 we have one file for each year. We should have a way to import all files existing in a folder or with a specific name format.

Important: #5 we have many files per year. We need to import all files exisitng in a specific folder or with a specific name format.

When u start this process, u will delete all data and re-populate all tables.

Task u will do:

create tables in SQL Server (5 tables for step1 and 5 for step 2)

create code to import data (step 1 and 2)

Help me moving the code to “production server”

This task should be done ASAP.

Thanks

Administración de bases de datos SQL

Nº del proyecto: #4337424

Sobre el proyecto