Post reply

Data migration using SSIS

  • December 9, 2009 at 9:04 pm

    #1090332

    Thanks amol , but the issue is that when i put the query to fetch all the records in source component i get some memory related exception as the size of table is enormous(23 gb) as compared to ssis internal buffers which hold the intermediate values before dumping into source.

    So can it be possible to put limits on source component itself .. or we may take some variable to hold the no of record to fetch and put our data flow task in for loop.. please correct me if i am wrong ...

    all this i am saying in theoretical basis .. and i am trying to implement it ...but facing some challenges like in ODBC source component we are not able to put parameters in query....

    please pour in your suggestions... 🙂

  • December 9, 2009 at 11:03 pm

    #1090355

    23 Gigs of data...that's a lot of data to select. I would suggest, fetch selective records using id range in a Loop. You need to code the logic in such a manner that the records that are processed are not selected again. This could be achieved by identifying a numeric identifier in the table. The query could be built using expression in SSIS.

    for example:

    Iteration 1:

    Set @From = 1

    Set @To = 50000

    SELECT * FROM dbo.Customer WHERE CustomerID BETWEEN @From AND @To

    Iteration 2:

    Set @From = @To + 1

    Set @To = @To + 50000

    This could be achieved in a For Loop container and the query could be dynamically built using expressions.

    Hope this helps !

    Amol

    Amol Naik

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply