RE: Newbie Flat File Schema question

From: David Downing [MSFT] (ddowning_at_online.microsoft.com)
Date: 02/15/05


Date: Tue, 15 Feb 2022 19:36:31 GMT

One comment regarding date/time fields. For flat file, you can specify a
CustomDateTime format for the field. When the value is parsed, it will be
normalized to ISO format.

Glad you were able to get things working!

Dave
--------------------
>>Thread-Topic: Newbie Flat File Schema question
>>thread-index: AcUTd9Q8TA+Ovq0CTEidrzblHjb9Kg==
>>X-WBNR-Posting-Host: 68.157.154.79
>>From: "=?Utf-8?B?TWF0dEJlbGw=?=" <MattBell@discussions.microsoft.com>
>>References: <8D107597-F7E5-4C4F-88E9-50E73EF248F6@microsoft.com>
<7VOeoSvEFHA.2152@TK2MSFTNGXA01.phx.gbl>
<FD43FB2A-AC83-424F-92A3-E25D3414ACD3@microsoft.com>
>>Subject: RE: Newbie Flat File Schema question
>>Date: Tue, 15 Feb 2022 08:03:03 -0800
>>Lines: 100
>>Message-ID: <B4470818-1679-4AAD-8E0C-3411A356135C@microsoft.com>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="Utf-8"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>Content-Class: urn:content-classes:message
>>Importance: normal
>>Priority: normal
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>>Newsgroups: microsoft.public.biztalk.general
>>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>>Path: TK2MSFTNGXA01.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.biztalk.general:11241
>>X-Tomcat-NG: microsoft.public.biztalk.general
>>
>>Maybe I should wait a little bit before posting questions :)
>>
>>I figured it out, I have to convert my SQL select on the JOB to ISO
format
>>ie SELECT CONVERT(varchar(30),InstallDate,126)
>>XML likes that format
>>
>>"MattBell" wrote:
>>
>>> Alright, I've got it mostly working now, thanks for the advice!
>>>
>>> One quick question, when I set my InstallDate field to be of a
xs:datetime
>>> datatype it gives this error:
>>> The 'InstallDate' element has an invalid value according to its data
type.
>>>
>>> is there something you have to do to make it recognize a datetime? Or
does
>>> the datetime field need to be formatted differently from the database?
>>>
>>> ""David Downing [MSFT]"" wrote:
>>>
>>> > There is no way of telling the flat file parser to ignore data, but
you can
>>> > use a map to eliminate unwanted data. I would try to structure the
schema
>>> > something like the following:
>>> >
>>> > <Root> delimited postfix child_delimiter_type="hex"
child_delimiter="0x0D
>>> > 0x0A"
>>> > <Line1>
>>> > <Line2>
>>> > <Line3>
>>> > <Line4>
>>> > <Line5>
>>> > <Line6>
>>> > <PositionalRecord>
>>> > <LaunchId>
>>> > <InstallDate>
>>> > <BlankLine>
>>> > <RowsLine>
>>> >
>>> > You can create a map that will map to itself with only the <LaunchId>
and
>>> > <InstallDate> links specified to eliminate everything else.
>>> >
>>> > Let mw know if you need clarification.
>>> >
>>> > Dave
>>> >
>>> > --------------------
>>> > >>Thread-Topic: Newbie Flat File Schema question
>>> > >>thread-index: AcUS2odlUaV/7WUJRI6XGRNuxQw72A==
>>> > >>X-WBNR-Posting-Host: 68.157.154.79
>>> > >>From: "=?Utf-8?B?TWF0dEJlbGw=?="
<MattBell@discussions.microsoft.com>
>>> > >>Subject: Newbie Flat File Schema question
>>> > >>Date: Mon, 14 Feb 2022 13:17:03 -0800
>>> > >>Lines: 20
>>> > >>Message-ID: <8D107597-F7E5-4C4F-88E9-50E73EF248F6@microsoft.com>
>>> > >>MIME-Version: 1.0
>>> > >>Content-Type: text/plain;
>>> > >> charset="Utf-8"
>>> > >>Content-Transfer-Encoding: 7bit
>>> > >>X-Newsreader: Microsoft CDO for Windows 2000
>>> > >>Content-Class: urn:content-classes:message
>>> > >>Importance: normal
>>> > >>Priority: normal
>>> > >>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>>> > >>Newsgroups: microsoft.public.biztalk.general
>>> > >>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>>> > >>Path:
TK2MSFTNGXA01.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGXA03.phx.gbl
>>> > >>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.biztalk.general:11166
>>> > >>X-Tomcat-NG: microsoft.public.biztalk.general
>>> > >>
>>> > >>I'm having a hard time getting my flat file schema to work, just
>>> > wondering if
>>> > >>someone could maybe give some advice.
>>> > >>My file is very simple:
>>> > >>
>>> > >>Job 'LaunchJob' : Step 1, 'Create Launch File' : Began Executing
>>> > 2022-02-14
>>> > >>10:09:55
>>> > >>
>>> > >>launch_id InstallDate
    
>>> > >>-----------------
------------------------------------------------------
>>> > >>000001 2022-01-01 00:00:00.000
>>> > >>
>>> > >>(1 rows(s) affected)
>>> > >>
>>> > >>
>>> > >>All I want is the two fields which are positional delimited.
>>> > >>I made a root node, and put 3 child field elements delimited by hex
0x0A
>>> > >>Then I made a child record which is positional with two child field
>>> > elements.
>>> > >>
>>> > >>Am I doing it wrong? I really don't care about anything in the
file
>>> > except
>>> > >>for the two fields, is there anyway to just ignore everything up to
line
>>> > 6?
>>> > >>
>>> >
>>> > This posting is provided "AS IS" with no warranties, and confers no
rights.
>>> >
>>> > EBusiness Server Team
>>> >
>>> >
>>

This posting is provided "AS IS" with no warranties, and confers no rights.

EBusiness Server Team



Relevant Pages

  • Re: Trying to export flat file
    ... I tried building that in a macro, using text format instead of XLS, and I get ... the same thing as if I export with the File->Export command, ... > Substitute your own query name and the name you want for the export file. ... >>I am trying to export a flat file of data, ...
    (microsoft.public.access.gettingstarted)
  • Re: SSIS - Flat Files
    ... format files through the SSIS Designer. ... current import tool - these are stored as csv files that specify column ... Is there an easy method to create a flat file connection from these format ...
    (microsoft.public.sqlserver.dts)
  • Re: convert from char(6) to datetime
    ... using one of the format codes ... I receive a very much unstructured flat file, which I import into a SQL ... one of the flat file columns is ... "The conversion of char data type to smalldatetime data type resulted in an ...
    (microsoft.public.sqlserver.connect)
  • Re: port binding in flat file receive sample
    ... You can treat the flat file assembler/disassembler the same way you would ... format for the disassembler is flat file, and the destination format for the ... > 1)i did not undestand how output port got binded to the orchestration. ...
    (microsoft.public.biztalk.general)
  • Re: receiveing flat files
    ... I think is the file format, it seems likely that we can parse it. ... > the flat file disassembler in the receive pipeline. ... > map just the vaule part with the schema i provide. ...
    (microsoft.public.biztalk.general)