CODEWITHSUNDEEP
biztalkbiztalk-2010flat-file
superschaf
superschaf

Reputation: 239

Is it possible to set the Flat File Source Component in Biztalk to read "Ragged Right" Files?

There is the need to import a "Ragged Right" flate File with Biztalk into some Destination places like a SQL Database and to a CRM Webservice.

The File is from the following format

Column1(10characters)Column2(13characters)Column3(17Chars.)Column4(till Row deliminator CRLF)

Where can I set this in the Biztalk in the Flat File Schema Wizard that the last column has no fixed width?

I know it will work in SSIS but this is no option. We now need to do this with Biztalk2010

Upvotes: 0

Views: 155

Answers (2)

Dijkgraaf
Dijkgraaf

Reputation: 11527

Here is a better answer

From the blog Early Termination with Position Flat Files

To make BizTalk recognize an early terminator you need to open your schema in a text editor and add allow_early_termination = “true” to the xs:annotation node. This property is not exposed in the BizTalk XSD editor.

This article was probably about BizTalk 2004, but it still works in BizTalk 2013 R2. So just right click your schema, select Open With and select the XML (Text) Editor and make the change as above.

Update: Just checked in Visual Studio 2013 and those properties are now available in the Schema Properties

enter image description here

Upvotes: 3

Dijkgraaf
Dijkgraaf

Reputation: 11527

One way to tackle it.

Given a sample file like 

Column1   Column2      Column3          Column4             {CR}{LF}
123456789012345678901231234567890123456712345678901234567890{CR}{LF}
Test1A    Test1B       Test1C           Test1D{CR}{LF}
Test2A    Test2B       Test2C           Testing2D{CR}{LF}

Set the last column to Positional Length of 1 and Max Occurs of unbounded afterwards by editing the schema.

Your XML output will look like the below. You can then re-assemble the last column with a map using the Cumulative Functiod with the scope parameter set to 1. Note: It does not however preserve white space, which could be an issue that would need to be addressed.

XML Output

<Ragged xmlns="http://Scratch.Ragged">
    <Ragged_Child1 xmlns="">
        <Column1>Column1</Column1>
        <Column2>Column2</Column2>
        <Column3>Column3</Column3>
        <Column4>C</Column4>
        <Column4>o</Column4>
        <Column4>l</Column4>
        <Column4>u</Column4>
        <Column4>m</Column4>
        <Column4>n</Column4>
        <Column4>4</Column4>
        <Column4/>
        <Column4/>
        <Column4/>
        <Column4/>
        <Column4/>
        <Column4/>
        <Column4/>
        <Column4/>
        <Column4/>
        <Column4/>
        <Column4/>
        <Column4/>
        <Column4/>
    </Ragged_Child1>
    <Ragged_Child1 xmlns="">
        <Column1>1234567890</Column1>
        <Column2>1234567890123</Column2>
        <Column3>12345678901234567</Column3>
        <Column4>1</Column4>
        <Column4>2</Column4>
        <Column4>3</Column4>
        <Column4>4</Column4>
        <Column4>5</Column4>
        <Column4>6</Column4>
        <Column4>7</Column4>
        <Column4>8</Column4>
        <Column4>9</Column4>
        <Column4>0</Column4>
        <Column4>1</Column4>
        <Column4>2</Column4>
        <Column4>3</Column4>
        <Column4>4</Column4>
        <Column4>5</Column4>
        <Column4>6</Column4>
        <Column4>7</Column4>
        <Column4>8</Column4>
        <Column4>9</Column4>
        <Column4>0</Column4>
    </Ragged_Child1>
    <Ragged_Child1 xmlns="">
        <Column1>Test1A</Column1>
        <Column2>Test1B</Column2>
        <Column3>Test1C</Column3>
        <Column4>T</Column4>
        <Column4>e</Column4>
        <Column4>s</Column4>
        <Column4>t</Column4>
        <Column4>1</Column4>
        <Column4>D</Column4>
    </Ragged_Child1>
    <Ragged_Child1 xmlns="">
        <Column1>Test2A</Column1>
        <Column2>Test2B</Column2>
        <Column3>Test2C</Column3>
        <Column4>T</Column4>
        <Column4>e</Column4>
        <Column4>s</Column4>
        <Column4>t</Column4>
        <Column4>i</Column4>
        <Column4>n</Column4>
        <Column4>g</Column4>
        <Column4>2</Column4>
        <Column4>D</Column4>
    </Ragged_Child1>
</Ragged>

Map

Map

Map Output

<ns0:Ragged xmlns:ns0="http://Scratch.Ragged">
    <Ragged_Child1>
        <Column1>Column1</Column1>
        <Column2>Column2</Column2>
        <Column3>Column3</Column3>
        <Column4>Column4</Column4>
    </Ragged_Child1>
    <Ragged_Child1>
        <Column1>1234567890</Column1>
        <Column2>1234567890123</Column2>
        <Column3>12345678901234567</Column3>
        <Column4>12345678901234567890</Column4>
    </Ragged_Child1>
    <Ragged_Child1>
        <Column1>Test1A</Column1>
        <Column2>Test1B</Column2>
        <Column3>Test1C</Column3>
        <Column4>Test1D</Column4>
    </Ragged_Child1>
    <Ragged_Child1>
        <Column1>Test2A</Column1>
        <Column2>Test2B</Column2>
        <Column3>Test2C</Column3>
        <Column4>Testing2D</Column4>
    </Ragged_Child1>
</ns0:Ragged>

Upvotes: 2

Related Questions