Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
BigRetina
Posting Yak Master
144 Posts |
 Posted - 2002-08-13 : 09:00:45
|
| 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:30F1will trnslate to the following columnsGate = AEmployeeNo = 12345LoginDate = 2002/08/13 08:30 A.M.Function = F1I 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 AdvanceEdited by - BigRetina on 08/13/2002 09:02:33 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-08-13 : 09:04:40
|
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 suggestsActually, you should try both all three, record some metrics and tell us which way was faster  ...Jay White{0}Edited by - Page47 on 08/13/2002 09:51:06 |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-08-13 : 09:10:51
|
| 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. |
|
|
|
BigRetina
Posting Yak Master
144 Posts |
Posted - 2002-08-13 : 09:23:17
|
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? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-08-13 : 09:33:16
|
| 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. |
|
|
|
BigRetina
Posting Yak Master
144 Posts |
Posted - 2002-08-13 : 09:54:03
|
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