Failed to start project ; Exception deserializing the package "values doesn't fall within the expected range"

• kishoremania

  Ten Centuries

  Points: 1059

  More actions

  December 5, 2017 at 12:11 pm

  #402725

  I am seeing this error when I am running SSIS  (VS 2015) package with below sql in Execute SQL Task;
  All I am trying to see is if the file exists in the folder. It gives me the same error even if I pass real path instead of parameter.
  It runs absolutely fine if I run in SSMS. Appreciate if any help!

  DECLARE @path VARCHAR(256)=?;
  DECLARE @sql VARCHAR(1024);

  CREATE TABLE #tempDirResults
    (
    FileNamed VARCHAR(256)
    );

  SET @sql = 'DIR /B "' + @path + '"';

  INSERT #tempDirResults
     ( FileNamed )
     EXEC xp_cmdshell @sql;

  IF ( EXISTS ( SELECT  CONVERT(VARCHAR(50), FileNamed) AS Filename ,
          @path + '\' + FileNamed AS filePathName
       FROM  #tempDirResults new
       WHERE  FileNamed IS NOT NULL
          AND FileNamed like 'Check%' ) )
    SELECT CAST(1 AS BIT) AS checkFile;
  ELSE
    SELECT CAST(0 AS BIT) AS checkFile;

  DROP TABLE #tempDirResults;

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  December 5, 2017 at 12:49 pm

  #1970781

  Not sure about the error, but this task may work better using xp_Dirtree. Here is an example:

  DROP TABLE IF EXISTS #Results

  CREATE TABLE #Results
(
  Subdirectory VARCHAR(300)
, depth   INT
, [File]   BIT
);

  INSERT #Results
(
  Subdirectory
, depth
, [File]
)
EXEC sys.xp_dirtree 'c:\temp', 0, 1;

  SELECT * FROM #Results r WHERE [File] = 1

  ---

• kishoremania

  Ten Centuries

  Points: 1059

  More actions

  December 5, 2017 at 2:30 pm

  #1970805

  Phil Parkin - Tuesday, December 5, 2017 12:49 PM

  Not sure about the error, but this task may work better using xp_Dirtree. Here is an example:

  DROP TABLE IF EXISTS #Results

  CREATE TABLE #Results
(
  Subdirectory VARCHAR(300)
, depth   INT
, [File]   BIT
);

  INSERT #Results
(
  Subdirectory
, depth
, [File]
)
EXEC sys.xp_dirtree 'c:\temp', 0, 1;

  SELECT * FROM #Results r WHERE [File] = 1

  Thanks!
  This is great. It's (sys.xp_dirttree) working fine in my local, but not in shared location. Any idea?

• kishoremania

  Ten Centuries

  Points: 1059

  More actions

  December 5, 2017 at 6:07 pm

  #1970824

  I think my project got corrupted, when I create in new project, not seeing this exception anymore.

  Anywayz thanks for helping Phil.