• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.

Importing a data from a flat file to SQL server.

Collapse
X
  •  
  • Filter
  • Time
  • Show
Clear All
new posts

  • LondonManc
    replied
    Originally posted by dx4100 View Post
    You can actuallly become a guru in SSIS ? I thought it was the tulip you gave to the junior
    That was DTS. SSIS got far more functional

    Leave a comment:


  • dx4100
    replied
    Originally posted by DimPrawn View Post
    Indeed, one becomes a SSISguru
    You can actuallly become a guru in SSIS ? I thought it was the tulip you gave to the junior

    Leave a comment:


  • DimPrawn
    replied
    Originally posted by LondonManc View Post
    One does not simply play with SSIS
    Indeed, one becomes a SSISguru

    Leave a comment:


  • LondonManc
    replied
    One does not simply play with SSIS

    Leave a comment:


  • ASB
    replied
    Cheers. I shall have a play.

    Leave a comment:


  • LondonManc
    replied
    Use SSIS - exactly what it is built for.

    Leave a comment:


  • DimPrawn
    replied
    I'd be tempted to create a staging table, that mimics the structure of the file, and use SSIS to create a package to import the data.

    Then call a sproc to process the data in the staging table further.

    How to import a fixed width flat file into database using SSIS? - Stack Overflow

    Don't know if this helps?

    Leave a comment:


  • ASB
    started a topic Importing a data from a flat file to SQL server.

    Importing a data from a flat file to SQL server.

    Trying to figure out a sensible way to achieve this.

    I receive a data file which is a straight structured fixed length file with cr/lf delimters. Something like:

    aaaaaaaaaaaBBBBBBBBBBB123.45

    My objective is simply to import this into rows in a table.

    This needs to be within a transaction, and can be part of a .NET process. It's sql server 2008.

    Ideally I would like to pass an array of "records" to a stored procedure, because further processing is required on them.

    It seems to me that I could achieve this by:-

    1. Create a TYPE as a table in SQL server
    2. Read the file in .NET and create a DataTable from it
    3. Pass said datatable and any other parameters to the stored procedure
    4. In the stored procedure, create a transaction, iterate the table via a cursor, do stuff with the data, end the transaction

    Is this the usual sort of way or should I be looking at something different ??

    Another hiccup.

    The file should only be able to be processed once and needs to be deleted, but only in the success case.

    Not sure how best to achieve this because it could fail after the sp completes and before the file gets deleted.

    My thought is to also have a table containing the details of files that have been processed, then when I find a file if it is in there I know it has already been done and can ignore it. This table could be updated in the transaction in the stored procedure.

    Again a bit klunky, so any other suggestions are welcome.

Working...
X