Possible to use bulk insert in SSIS package in diffrent server

Question

Hi Team,

Have flat file in one server and have created SSIS package in different server. Am trying to load data from flat file to SQL database using SSIS package and inside package am using bulk insert task to insert data to table.

when am trying to execute, am getting access denied error or resultset not set properly error

Could anyone help on this ?

Answer 1

Hi @Bala Narasimha Challa ,

It is better to use a built-in SSIS Bulk Insert Task.
Check it out here: bulk-insert-task-in-ssis

One of the benefits of the SSIS Bulk Insert Task is that it allows to configure a connection to the source file.
That file location and name could be different for dev. vs. test vs. prod. environments.

So, you will have two tasks on the Control Flow:

1. Execute SQL Task, to truncate a destination table.
2. Bulk Insert Task, to load data from the file into a destination table.

Answer 2

Error Message:
-load " failed with the following error: "Cannot bulk load because the file "D:\FTP\DailySales.txt'" could not be opened. Operating system error code 5(Access is denied.).".
Possible failure reasons: Problems with the query,
"ResultSet" property not
set correctly, parameters not set correctly, or connection not established correctly.

SQL Script
TRUNCATE TABLE SalesData

-- Toad data from text file
BULKINSERT MyDB.dbo.SalesData
FROM 'D:\FTP\DailySales.txt'
WITH
(
FIELDTERMINATOR = '\t'
FIRSTROW = 2.
KEEPNULLS,
ROWTERMINATOR = \n'
)
GO

Answer 3

Hi @Bala Narasimha Challa ,

"Cannot bulk load because the file "D:\FTP\DailySales.txt'" could not be opened. Operating system error code 5(Access is denied.)

From the error message, it is a permission issue.

Try to give SQL server account permission to access to the file.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.