aRookieBIdev avatar image
aRookieBIdev asked

SSIS Perfomance in local machine

Hi , I recently created a SSIS package which was previously a stored procedure to load data into several tables. By doing so I was hoping to gain some performance as well as organize the code. When i execute a particular task from my local machine connecting to the dev servers , it takes hours in the OLEDB task. However when i execute the select statement in sql server it takes hardly a minute. How big is the difference between executing a ssis package from desktop against executing from an integration server ?? Kannan
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

Tom Staab avatar image
Tom Staab answered
There are many variables at play here. 1. Where is the data coming from? Does it have to cross the network to get to the SQL Server box? 2. What type of source data are you importing? 3. What are the CPU, RAM, disk speed, etc. characteristics of the server vs. the desktop? 4. What do you mean by "OLEDB task"? Are you using a data flow? If so, are you using a "fast load" mode in your data flow? I'll give you one scenario based on hypothetical answers to those questions. If the source is a text file on a drive on the database server, running a bulk insert in T-SQL just pulls it from the local drive into the database. Running the SSIS package on your local machine, however, forces all traffic to be routed through the network to your machine and back.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
`+1` So many times have I seen a SSIS package that runs just a single SP, and I have to ask why? SSIS is a good ETL tool for processing data that is outside of, or crosses the boundary between SQL servers, but when it's a simple process on a single server, it's just SSIS for SSIS sake.
1 Like 1 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
1 Like 1 ·
aRookieBIdev avatar image aRookieBIdev commented ·
Thanks for the explainations. In my scenario .. I have two Steps in the package. step 1 : ETL (Data flow task) from server A to Server B step 2 : ETL (Data flow task) between DB 1 to DB 2 with in Server B In step 2, there is a union of 4 sql query with joins between two database tables fetching around 20 million records. The union sql query when executed from a management studio completes in minutes whereas when I executed it from my SSIS package it takes much much much more time just in the data flow task. My PC configuration: 16 GB RAM , 3.4 GHz i7 processor which is not bad i suppose. I assume the the problem is while the data moves through the network to my local machines buffer for SSIS to perform the ETL .
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.