• 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!

SQL Server / SSIS Data Import Question

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

    SQL Server / SSIS Data Import Question

    Something I've never had to do before, but need to get some data from a DB2 database into SQL Server 2008 and wondering which is the preferred way to do it. I'm sure there are many ways to do this so I was thinking of;

    a) Using OpenRowset without using a Linked Server, via SQL query
    b) Creating a linked server via sp_addlinkedserver on the SQL Server
    c) Creating a connection via a SSIS package and using a Data Flow task to move data.

    Which is the best method, or should I be looking at another method?

    I'm concerned about testing this on the Test environment, and then not working on Prod so which woule be the most portable and least likely to fail?

    TIA

    #2
    ever thought about employing a proper SQL Server resource?
    alternatively, it might be time for you to open your wallet and buy some books/training
    Coffee's for closers

    Comment


      #3
      Thanks for the informative response.

      Comment


        #4
        Originally posted by ChrisPackit View Post
        Thanks for the informative response.
        Don't mention it

        To answer your question - it depends.

        Done properly they are all equally portable.
        I would normally recommend SSIS, it gives the greatest amount of flexibility and can handle row level errors as part of the dataflow. However if you have no experience with SSIS then I suggest you avoid it. Also avoid if no one at the client co uses it.

        Using open rowset with out the linked server means the entire script can be lifted from test to live and reran.
        Coffee's for closers

        Comment


          #5
          I don't mind admitting I'm a little out of my depth with this, but it's such a small part of a larger project that it wouldn't warrant bringing someone in for a day, when I could probably figure it out in a few days.

          Thanks for the advice.

          I'm using SSIS at the moment, but finding it very slow to append 1.2M records. Had to kill it after 10 mins after moving only 60K records so need to investigate further.

          The drivers for DB2 aren't installed on the server to use the OpenRowset without a Linked Server, and just gives me the message to use a Linked Server instead! Would've preferred this method myself.

          Any performance tips you could offer in SSIS why it may take so long from DB2 into SQL?

          TIA

          Comment


            #6
            Originally posted by ChrisPackit View Post
            Any performance tips you could offer in SSIS why it may take so long from DB2 into SQL?

            TIA
            Generally :
            Use the OleDB destination
            make sure that the "Table or view - fast load" option is selected - do not do any error handling on the destination.
            as with any bulk load operation, the target table needs to be empty or have the primary keys,indexes and foreign keys disabled or removed.

            If you want to check data types, do it on the source component - right click and select advanced edit, then set the data types in the output. You'll then need to set the task to redirect rows on error or truncation and then pipe these to a different table

            What method are you using to connect to the DB2 database? if it's still slow then start a new package, create your DB2 connection again and then take the output to a union all transformation and run. Check to see if the data is being slowed down coming out of the DB2 system
            Coffee's for closers

            Comment

            Working...
            X