asked on

SQL 2005: SSIS QUERY

HI ALL

i am currently developing my first package in ssis, one element of the package is a data flow task that uses an ole db source - which points towards an excel file - thinking being that it will pick up this file when its saved in the correct location process it accordingly and move the file when fisnished

this is all working perfectly in debug, until when the excel document isnt there - it then gives back an error!

how would i best achieve this process? eg i will want to check the folder daily and if its not there simply do nothing and return no errors..

cheers

mal

lludden

Here is what I use as the first step in my SSIS jobs that import files. It checks to see if the file is there and hasn't already been processed. As the final step, I update the table ImportLog with the finished time.

DECLARE @ImportName varchar(50)
SET NOCOUNT ON
SET @ImportName = 'DailyTotals'
DECLARE @FileInfo varchar(500)
DECLARE @CreateDate datetime
CREATE TABLE [dbo].[#tbl_temp_Timestamp](vchFileData varchar(2000))
INSERT INTO #tbl_temp_Timestamp
EXEC [master].[dbo].xp_cmdshell 'dir \\server\share\filename.txt /T:W | FIND "filename"'

SELECT @FileInfo = vchFileData FROM #tbl_temp_Timestamp WHERE #tbl_temp_Timestamp.vchFileData IS NOT NULL
DROP TABLE [dbo].[#tbl_temp_Timestamp]
IF @FileInfo = 'File Not Found'
BEGIN
      raiserror ('File not present',16,1)
      return
END
SELECT @CreateDate = CAST(LEFT(@FileInfo,20) AS SMALLDATETIME)

IF (SELECT COUNT(*) FROM ImportLog WHERE ImportName = @ImportName AND FileDate = @CreateDate AND ImportComplete IS NOT NULL) > 0
BEGIN
      raiserror ('File already processed',16,1)
      return
END

INSERT INTO ImportLog (ImportName, FileDate, ImportStart) VALUES (@ImportName, @CreateDate, getdate())

ASKER CERTIFIED SOLUTION

Christopher Gordon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Christopher Gordon

Some more additional explanation might be in order,

With the above logic, the ssis package will first execute the Foreach loop task. If it finds a file that matches the criteria specified, it will execute the code inside the container. If it doesn't, it will close without throwing an error.

malraff

ASKER

hi gohord

i tried the foreach loop container - but it still returns the exact same error when it does not exist? it works when the file is there

malraff

ASKER

1.JPG

Christopher Gordon

hi malraff

Sorry never had that issue before.

Do you have all of your code inside the foreach loop container?

Are there any other files in the target folder? If so, is the configuration information within the task specific enough to the files you're looking to process?

SOLUTION

Christopher Gordon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

malraff

ASKER

brilliant gohord! i changed delay validation to true and viola - no errors!

this isnt sitting in a loop if the files are not there?

mal

Christopher Gordon

You won't have loop issues if there are no files in the specified folder in the foreach file task. In fact, the foreach file loop won't allow the code to "kick off" if there are no files that match the criteria specified.

Glad I could help!