none
flat file format - importing vs typing it out

    Question

  • Hi.  I've heard there is a way to import the format (rather that typing it out in connection mgr) of a fixed width flat file.  What r the general steps or where is there a set of instructions online?  I generally have a txt doc with column names (lots of them) , widths etc that I would use for import and could easily transfer them to a spreadsheet if necessary.   So far the word import is bringing up a lot of unwanted subject matter in my web searches.   I am spending a lot of time typing out flat file formats in conn mgrs and am thinking it may be worth a look at ssis's ability to "import" a format.   We generally land our data in "untyped" staging tables 1st so ssis could assume everything is string.   I dont really want to read my records as one long string and parse them later.
    Thursday, April 03, 2014 2:08 PM

All replies

  • The easiest way is probably using the Data Export Import Wizard. It can be saved as package then that you can open.

    The file otherwise gets connected to w/o typing anything to the connection manager, just point it out to it. The mapping part may be tedious if you have an existing target, otherwise you can create at the time of mapping.


    Arthur My Blog

    Thursday, April 03, 2014 8:19 PM
    Moderator
  • thx.  The import export wizard in ent manager acts just like a connector in ssis, ie it takes u right to the same type of component in which u need to type col names out.   I'm not looking for that. 

    I'm looking for a tool that will lay out the columnar format for me from a document that has col names and lengths documented.

    Friday, April 04, 2014 3:42 PM
  • I think it is a naive question, what standard this document follows? If even there is such a tool you need to create the document to satisfy it. What I think though:

    You can potentially solve this by simply running BCP asking to emit the formatting file out of a file sample you have, then create a script or a program that would read this document and map the columns for you programmatically.



    Arthur My Blog

    Sunday, April 06, 2014 2:20 AM
    Moderator
  • You should be more careful about what u say.  

    Many upstream systems/files (from where the BI industry grew) are already documented.  And I'm asking how I can take advantage of that documentation.  

    Here is an example of a file format document...

    col name               width            from to

    A                          7                  1-7

    B                          8                  8-15...

    It is quite easy to lay this file out in any way that a useful conversion tool requires.   This is metadata.   I want a tool that can import something like this (I can persist it anywhere) and generate a fixed width ssis file connector for me.   Or even a significant portion of the underlying dtsx content so i can paste what is generated into dtsx instead of retyping the col names and widths.


    • Edited by DB042189 Sunday, April 06, 2014 9:21 PM more
    Sunday, April 06, 2014 8:59 PM
  • It is a fair question, being able to have your file format pre-defined would make inclusion of flat files a lot easier, can't say I've seen anything within SSIS or SQLServer that can help but if there was something, I would imagine it would be XML based (which would probably also eliminate the ease of creating such...)
    Sunday, April 06, 2014 9:36 PM
  • thx.   I suppose I can write and share with the community some general t-sql that generates the following but would like to know a couple of things. 

    First, is any old guid ok in each of the places where a guid is indicated?  I would generate a new one for each guid.

    Second, what is the locale id?

    Of course i would only paste the results in a pkg (dtsx file) that doesnt reference the cols (beyond the connection stuff) yet.

    What gotchtyas am I looking at if i try this with the intention of pasting the generated code over the top of what ssis already generated (inside the dtsx file) in a one column connector created the usual way?

    <DTS:ConnectionManagers>
        <DTS:ConnectionManager
          DTS:refId="Package.ConnectionManagers[Flat File Connection Manager]"
          DTS:CreationName="FLATFILE"
          DTS:Description="ZZZZZZ"
          DTS:DTSID="{some guid}"
          DTS:ObjectName="Flat File Connection Manager">
          <DTS:PropertyExpression
            DTS:Name="ConnectionString">@[User::fileName]</DTS:PropertyExpression>
          <DTS:ObjectData>
            <DTS:ConnectionManager
              DTS:Format="FixedWidth"
              DTS:LocaleID="1033"
              DTS:HeaderRowDelimiter="_x000D__x000A_"
              DTS:RowDelimiter=""
              DTS:TextQualifier="_x003C_none_x003E_"
              DTS:CodePage="1252"
              DTS:ConnectionString="c:...">
              <DTS:FlatFileColumns>
                <DTS:FlatFileColumn
                  DTS:ColumnDelimiter=""
                  DTS:ColumnWidth="2"
                  DTS:MaximumWidth="2"
                  DTS:DataType="129"
                  DTS:ObjectName="XXXXX"
                  DTS:DTSID="{some other guid}"
                  DTS:CreationName="" />
                <DTS:FlatFileColumn
                  DTS:ColumnDelimiter=""
                  DTS:ColumnWidth="2"
                  DTS:MaximumWidth="2"
                  DTS:DataType="129"
                  DTS:ObjectName="YYYYYY"
                  DTS:DTSID="{some other guid}"
                  DTS:CreationName="" />
    ...


    • Edited by DB042189 Sunday, April 06, 2014 10:20 PM clarity
    Sunday, April 06, 2014 10:17 PM