SSIS - ForEach Loop with Insert in Execute SQL Task Failing

• Meatloaf

  SSCrazy

  Points: 2564

  More actions

  October 19, 2015 at 3:46 pm

  #301656

  Hello,

  I have ForEach Loop using Foreach File Enumerator. Within this loop I have SQL Task containing an Insert statement. When I run the Insert statement in query builder the transaction inserts data into a table as expected.

  However, when actually running the process I am getting the error message:

  Executing the query "INSERT INTO dbo.TEST_TABLE

  ..." failed with the following error: "Value does not fall within the expected range.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

  I currently have the ResultSet to "None" and have defined the parameter I am using. Where the process seems to joke is on my file_Name variable will I am trying to insert only part of the file name.

  My insert statement looks as follows:

  INSERT INTO dbo.TEST_TABLE

  (IID, AN8, File_Type, IB_OB, File_Name, Processed_Flag, ReceiptDateTime)

  VALUES

  (

  '1111',

  '123',

  '888',

  'IB',

  RIGHT(LEFT(?, LEN(?) - 4), LEN(?) - 24),

  'I',

  GETDATE())

  Can someone help me determine the problem?