CODEWITHSUNDEEP
sql-serverssissql-server-2016
tonyd
tonyd

Reputation: 340

SSIS DataFlow File to Table Loading Very Slowly

I'm having an issue with an SSIS data flow. I'm simply trying to load a flat file to a table. It's a very simple load from a small file with 6 columns and 2600 rows to a table that is exactly the same. There is not a transformation, its just straight to the table.

The problem is, with the data flow it's taking 6 minutes to load the file. If I import it with SQL Server file importer, it only takes a few seconds.

For troubleshooting, I've tried setting delay validation to true on the data flow. I've set ValidateExternalMetadata to false on the Flat File Source and OLE DB Destination. I've even tried setting the Data Flow Transactions property from Serializable to Read Committed and Read Uncommitted.

In the execution log, I'm seeing only one warning and no errors. The warning is:

[SSIS.Pipeline] Warning: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available. To resolve, run this package as an administrator, or on the system's console.

Nothing I've read about this warning would indicate that it affects performance. Later today, I will be attempting to run this as admin when the DBA is available.

What's interesting is, it looks like it immediately sends the rows to the buffer but doesn't write them to the table for 6 minutes. See the screenshot below.

enter image description here

Upvotes: 0

Views: 799

Answers (2)

praveen kumar
praveen kumar

Reputation: 56

Did you try using the fastload option in target!? . It is the property of data access mode available in the destination. Try setting it to Table or view -Fast load. It would solve the issue.

Upvotes: 2

perry147
perry147

Reputation: 197

If you want to prevent this warning from occurring, you can add the user account used to execute the package (e.g. your account and/or the SQL Server Agent account) into the local group "Performance Monitor Users".

If this change is made for any services e.g. SQL Server Agent, the service will need to be restarted for the change to take effect.

Upvotes: 2

Related Questions