0

I am trying to understand what happens when I extract data from DB2 from a SSIS package and load it into SQL Server.

My question is: where is the data when it is between DB2 and SQL Server? On my personal computer? I mean does the data "pass through" the memory on my personal laptop and then gets sent to SQL Server?

Improve this question

2 Answers 2

Reset to default
4

It depends where the SSIS Package is running, but yes the machine or server it's running on is a passthrough between your DB2 server and your SQL server. So the data is loaded into memory of the machine where the SSIS Package is running on, and if you're using certain data transformations and variables, those consume additional memory on the SSIS server also.

This article goes a little more in depth and has good information on the memory consumption of SSIS.

Improve this answer
3
  • 1
    Thanks JD. makes sense.
    – xhr489
    Feb 12, 2021 at 20:22
  • Can you please read the comment I wrote in the other answer?
    – xhr489
    Feb 12, 2021 at 20:50
  • 1
    @xhr489 Unfortunately I don't do a lot of SSIS, but I generally find Microsoft's documentation pretty good. You check out this walkthrough they have for SSIS.
    – J.D.
    Feb 12, 2021 at 21:30
2

A well designed package will never write the data to disk and the data will remain entirely in memory between the source and destination.

As J.D. indicates, there are three players in this game: The source, the destination and the machine coordinating the data movement. You could have DB2 LUW running on a windows Server and also host SQL Server on that same box, Box1, but if you take an execution server running, Box2, the data will stream off of Box1 to Box2 only to flow back to Box1. The data packets sure enjoyed the journey but processing would be faster if it never left.

Depending on your situation, it might be a good idea to run the SSIS package on the Source or Destination server and eliminate an extra network hop but that may foul up other things.

Not only should total server memory be of concern, but you can also spill to disk. That's the "worst" sin that can happen with SSIS as your performance is going to the toilet. SSIS is an in-memory Extract, Transform, and Load engine and if it cannot keep the data in memory, it pages to disk so now you paid for

  1. disk access to get the data from the source,
  2. write and then read it wherever it is being processed (write down the excess but eventually, it's time to use it so we have to read it back in to the pipeline)
  3. pay for it to be written to disk at the destination.

Ouch town, party of you. Tim's article calls out the need for memory, the patterns you might use in your packages that can impact available memory and if he didn't mention it, the data types (max types, CLOB/BLOB) can also cause those disk spills.

Improve this answer
2
  • Hi, thanks. When I try to read up on SSIS I never find any good resources. E.g. with SQL Server there are many books but with SSIS I have only found one which I feel is not so good... Any good sources to learn SSIS good?
    – xhr489
    Feb 12, 2021 at 20:49
  • 1
    I think I'm probably one of the worst people on the site to ask about SSIS books as I've been doing it since it came out in 2005 so the books I read are quite dated (VB was the only scripting language, lookup didn't have a no-match output path, etc). I do think Andy Leonard's Stairway to Integration Services series is a good one.
    – billinkc
    Feb 16, 2021 at 16:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.