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

Question

Post reply

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;

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

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

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

kishoremania Ten Centuries Points: 1059 More actions · Answer 2

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 · Answer 3

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

Anywayz thanks for helping Phil.