Sql-server – SSIS package with BULK data load

database-designsql-server-2008-r2ssis

I analyzed a C# program over the weekend for a customer that basically does the following:

Truncate Table
Fetch Data (source on these is either remote database or local database where job runs)
SQLBulkCopy (.NET class, into destination table of database where job runs)

For the most part (1) and (2) are done through stored procedure calls. I believe SSIS will be more efficient in handling this process. My end result is to determine if SSIS can do a better job and then the most efficient way of designing the package.

Question(s)
In cases where there are a large number of records would SSIS get better performance with the BULK INSERT task when (2) and (3) are the same database on the same instance? Or just change the procedure call in (2) to be a INSERT INTO, taking out the need for (3)?

In cases where (2) is a remote source (another SQL instance on the same network), does SSIS handle pulling data from a remote source more efficiently than doing it through the .NET SQLClient? I would not think there is a huge difference.

As well, when (2) is a remote source that is rather large amount of data (8 million +)would it be better to have a package on the remote instance export the data; then move that file to the destination server and bulk load it?

Best Answer

For 8 million rows, I don't think there will be a lot of difference between SSIS and the SQLBulkCopy class. In SSIS, you do have to make sure you are using the fast load option in the OLE DB Destination, otherwise it will surely be slower. If the source is local, you could try a SQL Server Destination, it might be a tad quicker than the OLE DB Destination.

Related Solutions

Sql-server – Login failure when running a SSIS package from a SQL Server job

I had a similar problem while importing data from an Excel file using SSIS packages. I found this tutorial and solved the problem.

EDIT: Important steps:
1 - When saving the SSIS package to the file system, select the "Package protection level" to "Do not save sensitive data".

Then, follow these steps:
1 - Open the SSIS package in Visual Studio
2 - Select SSIS -> Package Configurations
3 - On the Package Configurations Organizer click Add
4 - On the window that'll open click Next
5 - Set the configuration type to XML configuration file
6 - On the Configuration file name click Browse, select the folder in which you want to store the configuration file, choose a name to this file and then hit Save
7 - When you're done here, click Next
8 - Choose the right connection manager and click on the + sign to expand it, then expand Properties and check the checkbox correspondent to Password. Then click Next.
9 - Choose a name to your configuration file and click Finish.

The following XML configuration file will be created on the location you specified previously (here I'm using the code from the tutorial as an example):

<?xml version="1.0"?>

<DTSConfiguration>

      <DTSConfigurationHeading>

            <DTSConfigurationFileInfo GeneratedBy="Domain\UserName" GeneratedFromPackageName="Package" GeneratedFromPackageID="{77FB98FB-E1AF-48D9-8A43-9FD6B1790837}" GeneratedDate="22-12-2009 16:12:59"/>

      </DTSConfigurationHeading>

      <Configuration ConfiguredType="Property" Path="\Package.Connections[runeet2k8.sa].Properties[Password]" ValueType="String">

                  <ConfiguredValue></ConfiguredValue>

      </Configuration>

</DTSConfiguration>

The only thing you'll have to do here is edit the XML file and change this:
<ConfiguredValue></ConfiguredValue>
into this:
<ConfiguredValue>YourPasswordGoesHere</ConfiguredValue>

Then save the package.

When creating your SQL job, don't forget to add the configuration file to the job in the tab Configurations.

Sql-server – Simple SSIS Package Failed User does not exist or has not permission

The solution was that job needed to has an access to the default folder (on the server)

C:\Users\Default\AppData\Local\Microsoft\SQL Server\Smo

My colleage solve it and according to him it is some kind of bug which is patched in newer version of SQL Server.

Related Question