SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 DTS newbie question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

BigRetina
Posting Yak Master

Jordan
144 Posts

Posted - 08/13/2002 :  09:00:45  Show Profile  Reply with Quote
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



Edited by - BigRetina on 08/13/2002 09:02:33

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 08/13/2002 :  09:04:40  Show Profile  Reply with Quote
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 ...

Jay White
{0}

Edited by - Page47 on 08/13/2002 09:51:06
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 08/13/2002 :  09:10:51  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

BigRetina
Posting Yak Master

Jordan
144 Posts

Posted - 08/13/2002 :  09:23:17  Show Profile  Reply with Quote
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?

Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 08/13/2002 :  09:33:16  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

BigRetina
Posting Yak Master

Jordan
144 Posts

Posted - 08/13/2002 :  09:54:03  Show Profile  Reply with Quote
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!

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000