Using a date variable inside of a bulk insert query

[luiz_Guilherme]

Hi,

i’m using the assign activity to colect the date of the month and trying to use this variable inside of my bulk insert query but it’s not working, i need to write the date of the file after the name, this is my bulk query:
DECLARE @SQL VARCHAR(MAX) Set @SQL = ‘BULK INSERT Personal.STG.my_mailing from ‘’\C:\Users\luiz.guilherme\Documents\myfile_’+“+daymonth.ToString+”+‘.txt’’ WITH (FIRSTROW = 2,CODEPAGE = ‘‘ACP’’,FIELDTERMINATOR = ‘’|‘’,ROWTERMINATOR = ‘‘0x0a’’)‘’‘’’ exec (@SQL)

in case the date should be writen like this: 27NOV2023, so the name would be like this: myfile_27NOV2023