CODEWITHSUNDEEP
sql-serverssis
Control Freak
Control Freak

Reputation: 13213

Importing Millions of Records with SSIS

Any tips for speeding up the import processes? Theres alot of Joins in the db.

Also, when a SSIS task is completed, would the best way to handle the next functions by code or using the Emailing notification SSIS has..?

Upvotes: 2

Views: 6771

Answers (2)

user756519
user756519

Reputation:

Here is a sample that I have used to illustrate loading 1 million rows in under 3 minutes from text file to SQL Server database. The package in the sample was created using SSIS 208 R2 and was executed on Xeon single core CPU 2.5GHz and 3.00 GB RAM.

Import records on SSIS after lookup

One of the main bottlenecks in importing large number of rows will be the destination component. Faster the destination component can insert the rows, the faster the preceding source or transformation components can process the rows. Again if you happen to have components like Sort transformation that will be different because Sort transformation would hold up all the data to sort before sending it down the pipeline.

Sending email depends on what you would like to do.

  • If you need simple success or failure, you could simply use Send Mail task. Other option is that you could also enable the Alert notification on SQL jobs from where you might schedule the package to run on regular basis.

  • If you need more information to be added to the email, then you might need to use a Script Task to formulate the message body. After creating the message body, you can send the mail from within Script Task or use Send Mail task.

Hope that example along with the article @Nikhil S provided should help you fine tune your package.

Upvotes: 4

Nikhil S
Nikhil S

Reputation: 1209

This SimpleTalk article discusses ways to optimize your data flow task

Horizontally partition your data-to-be transferred into N data flows. Where N is the number of cpu cores available on your server where ssis is installed. Play with the ssis buffer size property to figure our setting optimal for your kind of data.

Upvotes: 2

Related Questions