 | |
Re: Need help with data conversion
microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Re: Need help with data conversion -
06-22-2004
, 11:13 AM
|
While an IT 'old hand', I am a 'newbie' when it comes to DTS. My problem is the need to load SQL Server from .txt files generated on a Unix machine |
conversion function is needed. Given that my current knowledge of ActiveX /
VB is limited, can anyone provide or point me to a good example of such a
script?
Quote:
|
-- Forefront Technical Services |
#2
| |||
| |||
|
|
While an IT 'old hand', I am a 'newbie' when it comes to DTS. My problem is the need to load SQL Server from .txt files generated on a Unix machine |
conversion function is needed. Given that my current knowledge of ActiveX /
VB is limited, can anyone provide or point me to a good example of such a
script?
Quote:
|
-- Forefront Technical Services |
#3
| |||||
| |||||
|
|
Allan, What I am dealing with are primarily 'dollar amounts' and 'percentages' generated within RM/COBOL programs. For example, a field for recording the |
COBOL FD) as 'jcmasth-insure-amt PIC9(8) COMP-6', followed by the deductible
defined as 'jcmasth-insure-deduct PIC 9(6)V99 COMP-6'.
Quote:
|
I can get DTS to read these files as .txt, in fixed field format, with me setting the end of record mark and column delimeters. What I am trying to do |
analysis / reporting, using, in this case, MicroStrategy. (Client has an
extensive, home-grown legacy system in COBOL that they are not ready /
willing to abandon yet, but have to improve analysis / reporting
capabilities of business).
Quote:
|
I have been able to load database tables whose 'source' file contains straight alphabetic / numeric (PIC 9)...and have been able to use this info |
into columns defined as numeric or decimal (whatever), I encounter
conversion errors and DTS fails to run.
Quote:
|
I am going to try a run shortly so I can send specific errors, if that will help. Your suggestion of staging tables, then use of T-SQL, also makes sense. Over the week-end, I tried loading into staging tables (created using |
DTS using staging table as source and target table as destination, but had
same conversion problems (I did not have a transformation script).
Quote:
|
Your advice is truly appreciated. Frank -- Forefront Technical Services "Allan Mitchell" wrote: It may be that you need an active Script transform, it may also not be the case. If the format from the Unix box is of a stright forward format then chances are you will not need anything else. What does it look like? Have you tried importing it yet? If yes then did you get any errors and if so what were they? What is it you want to do with the text file? I find that if I want anything other that COPY --> COPY then I import to a staging table first then use TSQL to do the move into the actual real table. This is often faster. -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Frank Hannaford" <forefront (AT) cox (DOT) net.(donotspam)> wrote in message news:8E5F9B6F-5FFC-4815-AFA4-65F55A401735 (AT) microsoft (DOT) com... While an IT 'old hand', I am a 'newbie' when it comes to DTS. My problem is the need to load SQL Server from .txt files generated on a Unix machine running RM/COBOL. I have been advised that an ActiveX script invoking VB conversion function is needed. Given that my current knowledge of ActiveX / VB is limited, can anyone provide or point me to a good example of such a script? -- Forefront Technical Services |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.