title: Passing parameter in Execute sql task in SSIS
body: <p>i have a SP which is accepting file name as parameter.</p>
  
  <p>This SP i want to call through Execute SQL Task In SSIS (using 2008)</p>
  
  <p>But my problem is each time filename is got changed. how to handle this kind of scenerio.</p>
  
  <p>Please help me, as i am pretty new to SSIS</p>
  
  <p>SP</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[Xml_FULL_insert]
  
  @XMLFileName NVARCHAR(256)
  
  AS
  
  BEGIN
  
  SET NOCOUNT ON
  SET XACT_ABORT ON
  
  BEGIN TRY
  
  DECLARE @Query NVARCHAR(max)
  
  IF OBJECT_ID('dbo.tblFile', 'U') IS NOT NULL DROP TABLE dbo.tblFile;
  
  CREATE TABLE dbo.tblFile (xmldata XML)
  
  BEGIN TRANSACTION
  
  SET @Query = 'INSERT INTO tblFile(XMLData) SELECT CONVERT (XML, 
  BulkColumn,2) FROM OPENROWSET( BULK '''+@XMLFileName+''', SINGLE_BLOB) AS     
  x;'
  
   EXEC (@Query) 
  
  
  COMMIT TRANSACTION
  
  END TRY
  
  END
  </code></pre>
  
  <p>i followed the below step:-</p>
  
  <ol>
  <li><p>declare variable with full file path in a value. (Variable Name: Filename )</p></li>
  <li><p>calling SP from Execute SQL TASK EXEC [Xml_FULL_insert] ?</p></li>
  <li><p>PARAMETER Passing :</p></li>
  </ol>
  
  <p>Variable name: Filename
  Direction: Input
  Dtattype VARCHAR
  Parameter Name: 0
  length: 50</p>
  
  <p>whenever filename is differ than the specified in the value of variable, the package failed.</p>
  
  <p>Though file structure is same.</p>
  
  <p><strong>my variable have value (D:\SSIS_Packages\AutoGrade.xml) full path where file name is ACES_2015.10_AutoGrade.xml
  and in a same folder  new file appear as mynewfile.xml. package fail inspite of parameter mapping</strong></p>
  
  <p>do i need to do some changes in SP? Please suggest if possible.</p>