Bulk insert does not work as dynamic sql

Question

I have this code in SP that sets the bulk insert:

begin try declare @sentFile nvarchar(255) declare @bulk_cmd nvarchar(1000) = '' declare @loadDate nvarchar(8) = Convert(nvarchar(8),@p_loadDate) -- @p_loadDate is char(8) set @StrImportFolder = N'D:\EMVImports\' set @sentFile = @StrImportFolder + N'etl_rnli_sent_'+ @loadDate + N'.txt' SET @bulk_cmd = N'BULK INSERT loadSent FROM ''' + @sentFile + N''' WITH ( FIRSTROW = 2 ,formatfile=''D:\EMVScripts\Sent_Format.xml'' )' Print @bulk_cmd EXECUTE sp_executesql @bulk_cmd -- more stuff happens here end try 

Inside my stored procedure, this fails with this error:

Unable to get row from OLE DB provider "BULK" for linked server "(null)".

But the printed code is:

 BULK INSERT loadSent FROM 'D:\EMVImports\etl_sent_20130529.txt' WITH ( FIRSTROW = 2 ,formatfile='D:\EMVScripts\Sent_Format.xml' ) 

works like a charm. I do not know why it fails in sp_executesql .

Answer 1

I use a lot of similar queries. And it works.

  DECLARE @filepath nvarchar (500)
 SET @filepath = N'e: \ 5-digit Commercial.csv '

 DECLARE @bulkinsert NVARCHAR (2000)

 SET @bulkinsert = 
        N'BULK INSERT ZIPCodes FROM '' '+ 
        @filepath + 
        N '' 'WITH (FIRSTROW = 2, FIELDTERMINATOR =' ',' ', ROWTERMINATOR =' '\ n' ')'

 EXEC sp_executesql @bulkinsert

How do you set the value of @sentFile?

Answer 2

We cannot set the From' file path dynamically to BULK INSERT .

Dynamically generated path

 set @sentFile = @StrImportFolder + N'etl_rnli_sent_'+ @loadDate + N'.txt' 

Here, @loadDate is the component variable of the file name.

The above work example uses a fixed path event with a variable:

 SET @filepath = N'e:\5-digit Commercial.csv', 

Here the variable has a correction path for each case.

So, try using the path to the file definition.