CODEWITHSUNDEEP
sql-serverssis
Bruno Lamps
Bruno Lamps

Reputation: 646

Error bulk loading XML file in SSIS package

I'm updating a SSIS task and getting a weird error: When executed, it says it can't bulk load a file the same SSIS task created.

This task retrieves a .zip file, extracts it in a path and then runs a stored procedure to bulk load the XML file, insert it's contents in some tables etc.

It's printing the following error in the logs:

Empresas:Error: Executing the query "execute carga.sp_cargaInicialEmpresas ?, ?" 
failed with the following error: "Erro na procedure xxxxx2016_CI.carga.sp_cargaInicialEmpresas)
Linha: 1Mensagem: Cannot bulk load because the file "C:\xxxxx2016\arquivos\Empresa\2017\2403\02\151423_ExecucaoEmpresas\ExecucaoEmpresas.xml" could not be opened. 
Operating system error code 3(The system cannot find the path specified.).". 
Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

This is a local path, not a network path, and the .xml file is created by the SSIS task itself. I checked the path in windows explorer and the file is there, waiting to be read.

Also the SQLSERVERAGENT user has all permissions in the "C:\xxxxx2016\arquivos\Empresa\2017\2403\02\151423_ExecucaoEmpresas\" directory.

How can I solve this?

Upvotes: 0

Views: 917

Answers (2)

Bruno Lamps
Bruno Lamps

Reputation: 646

That was a very misleading error message. After a few days trying to work it out, I found out that the database was misconfigured in my SSIS task connections.

Upvotes: 0

CRAFTY DBA
CRAFTY DBA

Reputation: 14915

This is an OS issue -- Error 3. The path does not exist. Please see system error codes page. Please check on the full path to the file.

An access issue would an error code 5.

Also, it depends on who is executing the SSIS package. Are you doing it from the server using your login? Are you running the package from SQL Agent under that credential? Did you change the run as credential in the job.

Like someone said, try executing it by hand. If it fails, then it is the Stored Procedure code. If it passes, then it is the path passed from SSIS to the SP. Make sure you know what your working directory is.

Tell me how you make out.

Upvotes: 1

Related Questions