Microsoft Sql Management Studio, SQL Server Agent, Build a Insert Bulk with dynamic parameters.

Question

Here is the syntax to start with (classified data omitted)

BULK INSERT 
[dbName].[dbo].[data_200801] FROM '\\servername\wwwroot\path\Files\data_200904.csv' WITH 
      (
         FIELDTERMINATOR = ';',
         ROWTERMINATOR =  '\n'
      );

This works fine as one of the step of the job. Where I hit a wall is that I need the file name to be dynamic with a SQL function such as

Filename = data_ & Year(GetDate()) & Month(GetDate()) & .csv

I can't seem to be able to concatenate the dynamic feature in my job.

Can someone who likes chalenges help me?

Thank you

Answer 1

create a stored procedure that executes this as dynamic sql, building the command in a string using your file name method. from the job run this new stored procedure.

CREATE PROCEDURE MyBulkInsert
AS
DECLARE @Query   varchar(500)
SET @Query='BULK INSERT [dbName].[dbo].[data_200801] FROM ''\servername\wwwroot\path\Files\data_'+CONVERT(varchar(4),Year(GetDate()))+RIGHT('0'+CONVERT(varchar(2),Month(GetDate())),2)+'.csv'' WITH ( FIELDTERMINATOR = '';'', ROWTERMINATOR = ''\n'' );'
EXECUTE (@Query)
Go

run that procedure from the job and not the bulk insert command