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
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.