• 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.
  • Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

Importing a data from a flat file to SQL server.

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

    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.

    #2
    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?

    Comment


      #3
      Use SSIS - exactly what it is built for.
      The greatest trick the devil ever pulled was convincing the world that he didn't exist

      Comment


        #4
        Cheers. I shall have a play.

        Comment


          #5
          One does not simply play with SSIS
          The greatest trick the devil ever pulled was convincing the world that he didn't exist

          Comment


            #6
            Originally posted by LondonManc View Post
            One does not simply play with SSIS
            Indeed, one becomes a SSISguru

            Comment


              #7
              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

              Comment


                #8
                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
                The greatest trick the devil ever pulled was convincing the world that he didn't exist

                Comment

                Working...
                X