SSIS Execute SQL task Error An error occurred while assigning a value to variable

• mnr123

  SSC-Addicted

  Points: 427

  More actions

  July 24, 2018 at 9:03 am

  #372513

  I am running this statement on execute SQl task 

  /* drop function if exists */
  IF object_id(N'myfn_FileExists ', N'FN') IS NOT NULL
    DROP FUNCTION myfn_FileExists
  GO

  /* create if file exists function */
  CREATE FUNCTION myfn_FileExists
    (
    @fullfilename nvarchar(4000) /* 4000 characters or less limit is required - nvarchar(max) does not work! */
    )
  RETURNS bit
  AS
    BEGIN
     DECLARE @i int
     EXEC master..xp_fileexist @fullfilename, @i out
     RETURN @i
    END
  GO

  /* create table for file names */
  DECLARE @tbl_files TABLE
    (
    FullFileName nvarchar(4000)
    )

  /* insert source files into table */
  set nocount on
  INSERT INTO @tbl_files
    (
    FullFileName
    )
  SELECT '\\vhacdwdwhetl15.vha.med.va.gov\ETLFiles\ITRM_ITBF\Staging\Purchase_Travel_Analytical_Tool\Source\FederalHolidayMapping.xlsx' UNION ALL
  SELECT '\\vhacdwdwhetl15.vha.med.va.gov\ETLFiles\ITRM_ITBF\Staging\Purchase_Travel_Analytical_Tool\Source\TotalListofMCC.xlsx' UNION ALL
  SELECT '\\vhacdwdwhetl15.vha.med.va.gov\ETLFiles\ITRM_ITBF\Staging\Purchase_Travel_Analytical_Tool\Source\Transaction_Detail.xlsx' UNION ALL
  SELECT '\\vhacdwdwhetl15.vha.med.va.gov\ETLFiles\ITRM_ITBF\Staging\Purchase_Travel_Analytical_Tool\Source\VAPersonnel.xlsx' UNION ALL
  SELECT '\\vhacdwdwhetl15.vha.med.va.gov\ETLFiles\ITRM_ITBF\Staging\Purchase_Travel_Analytical_Tool\Source\RestrictedMCCList.xlsx' UNION ALL
  SELECT '\\vhacdwdwhetl15.vha.med.va.gov\ETLFiles\ITRM_ITBF\Staging\Purchase_Travel_Analytical_Tool\Source\Account_List.xlsx'

  --/* Check whether files exists */
  --SELECT
  --  f.FullFileName as [FileName],
  --  Dflt.myfn_FileExists (f.FullFileName) as [FileExists]
  --FROM
  --  @tbl_files f
  --ORDER BY
  --  f.FullFileName
  --GO

  select count([FileExists]) as Cnt from (

  SELECT
    f.FullFileName as [FileName],
    Dflt.myfn_FileExists (f.FullFileName) as [FileExists]
  FROM
    @tbl_files f
  ) a
  where a.[FileExists] = 1
  - I get teh count as 6 and assign to a variable as userCount.

  When i run the Execute SQl Task i get this error : [Execute SQL Task] Error: An error occurred while assigning a value to variable "TestCount": "Exception from HRESULT: 0xC0015005".

  I tried all possible options on google , Could not be successful .

  Please can you help me with this .
  

• Phil Parkin

  SSC Guru

  Points: 246965

  More actions

  July 24, 2018 at 9:31 am

  #1998818

  Using SQL Server to check for file existence from within an SSIS package seems inelegant.
  Have you considered creating a script task to do this?
  It's very easy in C# and does not stress the SQL DB engine:
     string path = @"c:\windows\temp\fred.txt";
    bool fileExists = File.Exists(path);
  All you may need to do is add 
  using System.IO;
  to your Usings block.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin