title: Can I use a 'LIKE' statement in a SSIS job to check if filename exists?
body: <p>I have an SSIS job to load flat files into a database. I load the file name into a column in the SQL database. In the first step I check to see if the file name already exists. If it does the file gets deleted. If not it gets processed and loaded. The original file is a .dat. The problem is that because of another process the file gets loaded is renamed with a "_flat.txt" appended to the end of the name and does not match the original. </p>
  
  <blockquote>
    <p>"YC_HSO_PA_0_Test_1.dat"</p>
  </blockquote>
  
  <p>"YC_HSO_PA_0_Test_1_flat.txt"</p>
  
  <p>The variable that stores the original name of the file is 'User::FileNameCheck'. The table I check against is TOC.PA_STAGE. The expression I use to do the check is:</p>
  
  <pre><code>"DECLARE @FileName VARCHAR (100)
  SET @FileName =  '"+ @[User::FileNameCheck]  +"'
  
  IF EXISTS (SELECT 1
              FROM [TOC].[PA_STAGE]
              WHERE FileName = @FileName)
      BEGIN
          SELECT 0 AS LoadStatusFlag
      END
  ELSE
  BEGIN
  SELECT 1 AS LoadStatusFlag
  END"
  </code></pre>
  
  <p>I tried this:</p>
  
  <pre><code>"DECLARE @FileName VARCHAR (100)
  SET @FileName =  REPLACE(REPLACE(@[User::FileNameCheck],'.txt',''),'.dat','')  +'_flat.txt' 
  
  IF EXISTS (SELECT 1
              FROM [TOC].[PA_STAGE]
              WHERE FileName = @FileName)
      BEGIN
          SELECT 0 AS LoadStatusFlag
      END
  ELSE
  BEGIN
  SELECT 1 AS LoadStatusFlag
  END"
  </code></pre>
  
  <p>but get an error: SET @FileName =  R..." failed with the following error: "Must declare the scalar variable "@".". </p>
  
  <p>Can I use a 'LIKE' statement to find file names to match. Or somehow append the _flat.txt to the variable?</p>