I have an ETL solution which we have been doing in conventional SSIS. The job of SSIS is just to create star schema from the application database to dimension and fact tables. There is very little transformations happening.

The problem that we are facing is that on local machine (desktop with 32GB of RAM) this job takes 16 hrs + to complete if we are lucky. On most days the job fails when it runs out of memory with below error:

A buffer failed while allocating 104850152 bytes.

On the production server this job runs in 4 hrs, which has 64GB of ram.

We are trying to explore other possibilities to perform this ETL on local. Do you have any suggestion/strategies to leverage azure + python + HDInsights? Any other suggestion are also welcome.

  • Is it actually using >2Gb of memory at the point of failure? If not then you could be running the package in 32-bit mode. This is usually the case when launching SSIS packages from BIDS/SSDT/VisualStudio. You don't state exactly how you are running this "on local" currently. – David Spillett Feb 19 '19 at 16:30
  • Its running 64-bit mode. – imba22 Feb 19 '19 at 16:43
  • While you're welcome to rewrite into a different solution stack, as an SSIS person, it smells like the package(s) could use some severe optimization. Regardless of technology stack though, this question lacks enough information to be solvable. We'd need to see source and destination schemas, sample data, data delta volume, ssis package design, database metrics during data load, package execution dependencies, etc, etc – billinkc Feb 20 '19 at 15:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.