SSIS Package Error - BULK INSERT "file does not exist"

Question

I have a SSIS package. Inside it, are two Execute Package Task elements. The two packages are linked to run in order.

Package One - downloads CSV file from external site (SalesForce.com)

E:\SalesForce\MSSQL_Import\PricebookEntry.csv

E: is a mapped network drive.

Package Two - SQL task to BULK INSERT contents of CSV file into empty table.

BULK INSERT SalesForce.dbo.PriceBookEntry
FROM 'E:\SalesForce\MSSQL_Import\PricebookEntry.csv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = '\t',  
ROWTERMINATOR = '\n', 
CODEPAGE = 'ACP',    
ERRORFILE = 'E:\SalesForce\Data_Loader\Logs\Error\Bulk_Insert\BI_PriceBookEntry.csv',
TABLOCK
)

I am getting the following error when the second SSIS package is triggered:

[Execute SQL Task] Error: Executing the query "BULK INSERT SalesForce.dbo.PriceBookEntry FROM..." failed with the following error: "Cannot bulk load. The file "E:\SalesForce\MSSQL_Import\PricebookEntry.csv" does not exist.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I have checked the location of the file. The path and spelling are good. The file exists while the script runs.

I have tried saving the file on the C: just to check. The BULK INSERT package still fails.

I have run the BULK query in management studio as a command and it works fine.

If I chain the packages together inside another package, the BULK INSERT fails. If I run the SSIS packages separately in order, they work fine.

I have other SSIS packages that do the same thing and work fine.

I've been Googling high and low for a possible solution and still nothing. Anyone have any ideas on what might be the issue?

E: is a mapped network drive Usually the root cause is that the account executing the SSIS package does not have the same drives mapped as the user that developed it. The fact that it doesn't work when placed on the C drive leads me to believe it might also be a permission issue. You've specified this works elsewhere though so at first cut, I'm not sure where you've gone awry — billinkc

warren banks warren banks · Accepted Answer · 2016-12-02T20:37:24

I found the problem.

The quick answer is that the CSV file being downloaded is not completed before the BULK INSERT command is fired in SSIS. Thus SQL Server can't get access to the file.

Previous jobs were only working with very small files (2000 or so lines). This job is working with a lot more (~1 mil lines).

The solution will involve holding off the BULK INSERT until the file has completed download.

SSIS Package Error - BULK INSERT "file does not exist"

1 Answers