none
Export date from source: OLE DB to destination: flat file

    Question

  • Hi evereyone,

    Using SSIS I added a connection to "Connection managers" and "New OLE DB" connection whice is a table from a database in my server.

    I added OLE DB Source  to the "DATA FLOW" and "Flat File Destination". In the "Flat file destination" i dfined width of a row and width of columns , gave names to the columns and mapped table's columns to flat file's columns.

    I signed "Column names is the first data row" and for "Format" i selected "Fixed width".

    I ran the package and recieved a flat file in which the first row contained column names plus data from the first data row !

    The first data row (second at the table) started from the second column and its width was wider then defined.

    Columns at the flat file were inconsistent and it was all messy and unreadable.

    Anyone is familiar to that phenomena ? Can I get an advise of how to export data to a flat file with fixed width columns ?

    Thanks

    Sunday, July 27, 2008 2:36 PM

Answers

  • Can you try this. Really tight char format.

    Code Snippet

     

    SELECT

    CASE

    WHEN name1 IS NOT NULL

    THEN convert(char(30),LEFT(LTRIM(RTRIM(name1)),30) )

    ELSE convert(char(30),'no name')

    END "name1",

    CASE

    WHEN name2 IS NOT NULL

    THEN convert(char(30),LEFT(LTRIM(RTRIM(name2)),30))

    ELSE convert(char(30),'no name')

    END "name2",

    CASE

    WHEN lastname IS NOT NULL

    THEN convert(char(50),LEFT(LTRIM(RTRIM(lastname)),50) )

    ELSE convert(char(50), 'no name')

    END "lastname",

    char(10) as "NL"

    FROM

    dbo.double_magazined_members

     

     

    Let us know if works.
    Monday, July 28, 2008 10:23 AM

All replies

  • For config flat file dest component, did you set up row delimiter? and did you choose the right locale and code page if not dump as unicode?
    Sunday, July 27, 2008 3:30 PM
  • You can use SSIS import/export wizard.

     

    Select fixed width. Do not checkmark header.

     

    Let us know what happens.

     

    Sunday, July 27, 2008 8:35 PM
  •  SQLUSA wrote:

    You can use SSIS import/export wizard.

     

    Select fixed width. Do not checkmark header.

     

    Let us know what happens.

     

    You can use SSIS import/export wizard.

     

    Select fixed width. Do not checkmark header.

     

    Let us know what happens.

    1.       Sql server management studio

    2.       right  click on: Database:  "magazines" and select: "task", "export data"

    3.       At the "Choose data source"  from I assigned the following:

    a.       "Data souece":  "SQL Native client"

    b.      "Server name": "myServer"

    c.       "Authentication": "myAuthentication"

    d.      Database: "myDatabase"

    e.      NEXT

    4.       At the "Choose a destination"  form I assigned the following:

    a.       "Desination":  "Flat file destination"

    b.      "File name" : "myText.txt"

    c.       "Local" : "myCountry"

    d.      "unicode" : I left empty

    e.      "Code page" : "Ahnsi-mycode"

    f.        "Format": "Fixed width"

    g.       "Text qualifier": "none"

    h.      "Column names in the first data row": I didn't sign !

    i.         NEXT

    5.       At the:  "Specify Table Copy or Query"  I signed: "Write a query to specify the data transfer"

    6.       At the "Provide a Source Query" form I wrote: " SELECT * FROM myTable" and NEXT

    7.       At the "Configure Flat file Destination"  form I pressed "Preview"  and saw that data was presented like it should so I pressed NEXT

    8.       At the "Save and Execute package" I signed: "Execute immediately" and  "Save  SSIS Package" and "NEXT"

    9.       FINISH

    I opened: "myText.txt" and got  a whole mess. First row's width different of next row's width there ware no columns whatsoever…

    Any idea ?

    Thanks

     

     

    Monday, July 28, 2008 8:01 AM
  •  pssheba wrote:
     

     

    I opened: "myText.txt" and got  a whole mess. First row's width different of next row's width there ware no columns whatsoever…

     

     

    That may not matter.

     

    Run the wizard again, see if you can load it back to a test table.

     

    It is a challenge to work with fixed width.

     

    You may try to export using a QUERT source where you can set limits to column size, like

     

    Code Snippet

    LEFT (Address1, 20)

     

     

    You may also add CHAR(10) newline to select list in the query as rowdelimiter.

     

    Let us know what happens.

    Monday, July 28, 2008 8:11 AM
  • Hi SQLUSA

    Thanks or your efforts and skills

    This is what i did:

    1.       "Choose a Destination" form:

    a.       "Format": "Fixed  Width"

    b.      "Column names in the first row" not signed

    2.       "SQL Server Copy or Query" form:

    a.       "Write a Query to specify the Data to Transfer" – signed

    3.       "Provide a Source Query":

    [code]
    SELECT

    CASE

    WHEN name1 IS NOT NULL

      THEN LEFT(LTRIM(RTRIM(name1)),30)

      ELSE 'no name'

    END  "name1",

     

    CASE

    WHEN name2 IS NOT NULL

      THEN LEFT(LTRIM(RTRIM(name2)),30)

      ELSE 'no name'

    END "name2",

     

    CASE

    WHEN lastname IS NOT NULL

      THEN LEFT(LTRIM(RTRIM(lastname)),50) + CHAR(10)

      ELSE 'no name' + CHAR(10)

    END "lastname"

     

    FROM

    dbo.double_magazined_members

    [/code]

    Sorry to say, my efforts are in vain, the flat file remains messy..

    Thanks

     

    Monday, July 28, 2008 9:35 AM
  • Can you try this. Really tight char format.

    Code Snippet

     

    SELECT

    CASE

    WHEN name1 IS NOT NULL

    THEN convert(char(30),LEFT(LTRIM(RTRIM(name1)),30) )

    ELSE convert(char(30),'no name')

    END "name1",

    CASE

    WHEN name2 IS NOT NULL

    THEN convert(char(30),LEFT(LTRIM(RTRIM(name2)),30))

    ELSE convert(char(30),'no name')

    END "name2",

    CASE

    WHEN lastname IS NOT NULL

    THEN convert(char(50),LEFT(LTRIM(RTRIM(lastname)),50) )

    ELSE convert(char(50), 'no name')

    END "lastname",

    char(10) as "NL"

    FROM

    dbo.double_magazined_members

     

     

    Let us know if works.
    Monday, July 28, 2008 10:23 AM
  • Hi SQLUSA

    I added that column which didnt work, then i added "char(13)" to "char(10)" which didnt work either so i changed

    "CHAR(10) + CHAR(13)  'c'  to "CHAR(13) + CHAR(13)" and that finally worked !

    Tough...

    Thanks a lot ! 

    Monday, July 28, 2008 11:41 AM
  • Great. This is very educational. Can you post the final code?

     

    Monday, July 28, 2008 12:04 PM
  • If the following is helpful it'll be my pleasure:

    Export a table unto a "fixed width columns" flat file using:                  "SQL Server Import and Export Wizard"

    Select "Tasks", "Import Data" from "right click" menu on a database from which you want a table to be exported  unto a flat file destination (of fixed width columns).

    "Chose a data source " form

    Accept default values (except "authentication" if it needs  to be changed).

    NEXT

    "choose a destination" form

    DestinationSelect "Flat File Destination" From the selection list.

    File Name – Select location + name of a text file into which you want the table delivered (make sure it is empty if you don’t want remnants of old data) using "Browse"  button.

    Format – Select "Fixed Width" (assuming  that’s what you want)

    Column names in the first  data row – Make sure the selection box Is not signed !

    NEXT

    "Specify Table Copy or Query" form

    Write a query to specify the data to transfer – This is the Radio button you should select

    NEXT

    "Provide a Source Query" window

    The following code is an example of : "Name", "Lastname", "Phone", "City", "Street" and "Number" columns exported from OLE DB table unto a "Fixed width"  flat file:

    SELECT

    CASE

    WHEN name IS NOT NULL

      THEN LEFT(LTRIM(RTRIM(name)),30)

      ELSE 'no name                       '

    END  "name",

     

    CASE

    WHEN lastname IS NOT NULL

      THEN LEFT(LTRIM(RTRIM(lastname)),50)

      ELSE 'no lastname                                       '

    END "lastname",

     

    CASE

    WHEN phone IS NOT NULL

      THEN LEFT(LTRIM(RTRIM(phone)),9)

      ELSE 'no phone '

    END "phone",

     

    CASE

    WHEN city IS NOT NULL

      THEN LEFT(LTRIM(RTRIM(city)),40)

      ELSE 'no city                                 '

    END "city",

     

    CASE

    WHEN street IS NOT NULL

      THEN LEFT(LTRIM(RTRIM(street)),50)

      ELSE 'no street                                         '

    END "street",

     

    CASE

    WHEN number IS NOT NULL

      THEN LEFT(LTRIM(RTRIM(number)),8)

      ELSE 'no digit'

    END "number",

     

    CHAR(13) + CHAR(10) 'c'

     

    FROM

    dbo.double_magazined_members

    PARSE – To make sure the code is correct

    NEXT

    Configure Flat File Destination

    Make any correction if you need

    NEXT

    "Save and execute package" form

    Save SSIS Package If you want to transform the process  to a SSIS package. At this example you leave it unsigned.

    NEXT

    "Complete the wizard" from

    This is the simpliest of forms. Just press "Finish"

     

     

     

    Monday, July 28, 2008 1:27 PM