I have a text file I would like to import into SQL Tables.
The text files has a variable amount of rows and each row starts with a
character that identifies which table it should go into. A typical text file
might look like this:
"h","3","5","44"
"h","5","4","777"
"s","3","4"
"s","1","0"
"f","6","a","55","a1"
"g","0"
The "h" records have to go into one table, the "s" records into another and
so on.
The number of "h", "s"... can change.
What would be the best way to accomplish this?
Thanks

I would do this as follows:
- create a staging table, e.g. STG_IMPORT with the maximum number of columns
in your source file. Eg if B has 6 columns and that's the max, create a table
with 6 columns
- 1) create a execute sql task: TRUNCATE TABLE STG_IMPORT
- 2) create a dts package with a text file source and a db destinator.
- create a datapump transformation for the 6 columns from source to target
- 3...)then create a execute sql task for each h, s, f, g, etc like
INSERT INTO TBL_G (col2) SELECT col2 FROM STG_IMPORT WHERE col1 = 'G'
and
INSERT INTO TBL_G (col2, col3) SELECT col2, col3 FROM STG_IMPORT WHERE col1
= 'S'
and...

That should do the trick...

Regarding the Staging Table:
How do I define the columns if each column can have a different data type,
i.e.
Column 2 for "h" might be Numeric, while Column 2 for "s" might be Datetime...
Thanks

"Frans van Bree" wrote:

I would make them all VARCHAR(4000) or NVARCHAR(4000) at first. You can
reduce that later.

For transformations to your "final" table you need to be aware of decimal
separators for numbers (eg . or ,) and datetime format (eg dd/mm/yyyy or
mm/dd/yyyy), etc. So, indeed, that is a bit more work...