Salute.. I am new to DTS.. I am trying to import the contents of a text file into a SQL server table. The text file consists of one column(non-delimited)that have to be parsed as follows: e.g. A123452002/08/13:08:30F1 will trnslate to the following columns Gate = A EmployeeNo = 12345 LoginDate = 2002/08/13 08:30 A.M. Function = F1
I tried to do that in the package designer..but I never managed to map the string into the columns of the table..should the text string be parsed?..and how using DTS?? Thanks In Advance
Two Three options. 1. Just dump the entire row into a single column 'staging' table, and use T-SQL to parse out the data to your live table. 2. Write a custom ActiveX Script transformation to use VBScript to parse out the single field into your multiple columns. 3. Do what NR suggests
Actually, you should try both all three, record some metrics and tell us which way was faster ...
Create a source text file and point it at this file. Set the columns so that they are in the correct place. Create a destination table connection. Create a data transformation between the two and create copy column transformations between the relevant fields.
Now when you excecute that package it shold import the data.
If you have to do this regularly then I would advise importing into a single column table (plus an ID, lastupdated..) and then calling a stored proc to do the move into columns. (Just sent graz an sp to do this from col defs and mappings in tables - don't know whether this has been done before or will be put on the site.) This enables you to change the way you import the data without affecting the rest of the processing.
========================================== Cursors are useful if you don't know sql. DTS can be used in a similar way. Beer is not cold and it isn't fizzy.
Thanks For The Fast Reponses Ppl.. What I am doing now is that I am parsing the string using VB.NET and filling a DataTable then sending it to the DB using a SQLDataAdapter Fill method. It is working fine but I wanted to learn DTS. Second..nr..I could NOT do what U said
quote:Set the columns so that they are in the correct place.
The column lines overwrote text in the preview windwo. I mean i couldnt squeezing the line bwtween two adjacent charaters!!..how can I do that??... and should I write my transformations using script?..no other way?
Should always create everything via scripts and keep them in sourcesafe. One of my complaints about the was DTS is used.
Don't understand your problem. You just click where you want the column to end - it sould give a vertical line and the next column starts on the next character.
========================================== Cursors are useful if you don't know sql. DTS can be used in a similar way. Beer is not cold and it isn't fizzy.
well believe it or not BUT IT DOES NOT WORK ON WIN98... I mean the COLUMNS designer is MESSED when i work with the Enterprise manager in Win98 but it WORKS fine in WIN2000..!!..It worked now for me!
DTS newbie question
Posted - 08/13/2002 : 09:00:45
...
