File Import and Processing

[Rhys666]

I need to create a DTS Package / Series of sProc.'s that can iteratively perform file import and validation.

I want to iteratively import data contained in files into Sql Server (I can calculate the filename per file as they contain a formatted date). How can I set up DTS or a sProc to iteratively examine a source directory for files, then import each file, validate it and move the source file to either an errors or processed folder?

I can write the vbScript to do the file movement, I can write a DTS process or sProc to do the file import, it's the iterative processing all files that's killing me. Basically each file should be checked for existence, imported, validated ,the import table cleaned down, and then the source file moved to either a processed or errors directory... then the next file, etc. etc.

A file failing validation should stop the process as should any other failure. How do I make the DTS package iterate the files tho'?



Rhys

""Vampireware /n/, a project, capable of sucking the lifeblood out of anyone unfortunate enough to be assigned to it, which never actually sees the light of day, but nonetheless refuses to die."

My Home

 

[donutman]

HAL is 4 years overdue (j/k). I think you're talking an application, but you might try posting in the DTS forum.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Rhys666]

No, not an app, thats my issue at the mo. This needs to be a mix of DTS and sProcs, and I only posted here to avoid double posting and getting a slapped hand and to be fair I don't need the help with using the ActiveX FSO to manage file movements, but either a DTS or T-Sql method of achieving this.

Rhys

""Vampireware /n/, a project, capable of sucking the lifeblood out of anyone unfortunate enough to be assigned to it, which never actually sees the light of day, but nonetheless refuses to die."

My Home

 

[flaniganmj]

I think this may be kinda what you are looking for...

If you are trying to get this all into sp's, these three code secs should do the trick.

This first part simply examine as directory

-- --------------------------------------------------------------------------------
-- script temp table and populate with directory contents
-- --------------------------------------------------------------------------------
IF object_id('tempdb..#files') IS NOT NULL DROP TABLE #files

CREATE TABLE #files
 (
   Files                varchar(100)
 )

SELECT @l_Cmd='dir "'+ myspParamFilePath +'"' 

INSERT INTO #files EXEC master..xp_cmdshell @l_Cmd 

-- --------------------------------------------------------------------------------
-- remove unnecess items from dir table
-- --------------------------------------------------------------------------------
UPDATE #files SET Files=RTRIM(Files)
DELETE FROM #files WHERE Files IS NULL
DELETE FROM #files WHERE -- what ever else you need to remove

-- --------------------------------------------------------------------------------
-- if there are no records (files) bail
-- --------------------------------------------------------------------------------
IF (SELECT COUNT(*) FROM #files)=0 
   BEGIN
      SET @o_RetVal=1 RETURN
   END

Do what ever you need to do with the table and then import.

SET @l_Command=''

SET @l_Command=@l_Command+'BULK INSERT ' 
SET @l_Command=@l_Command+'   #previouslyscriptedtemptable '
SET @l_Command=@l_Command+'FROM '
SET @l_Command=@l_Command+''''+@i_FilePath +''' '
SET @l_Command=@l_Command+'WITH '
SET @l_Command=@l_Command+' ('
SET @l_Command=@l_Command+'   FIELDTERMINATOR = ''|'', '
SET @l_Command=@l_Command+'   KEEPNULLS, '
SET @l_Command=@l_Command+'   FIRSTROW=1, '  
SET @l_Command=@l_Command+'   ROWTERMINATOR = ''\n'''
SET @l_Command=@l_Command+') '

EXEC (@l_Command)

IF @@ERROR!=0
   BEGIN
      PRINT 'Error with file - bulk insert.'
      RETURN
   END

IF (SELECT COUNT(*) FROM #previouslyscriptedtemptable)=0
   BEGIN
      PRINT 'No records exist.'
      RETURN
   END

Then if all goes well archive or whatever.

      -- --------------------------------------------------------------------------
      -- archive file via xp_cmdshell in similar manner
      -- --------------------------------------------------------------------------
      SET @l_Cmd='COPY /Y "'+@l_DistFileRoot+@c_BaseFile+@c_DateFile+@l_DistFileExt+'" '
      SET @l_Cmd=@l_Cmd+'"'+@l_DistArchiveRoot+@c_BaseFile+@l_DistArchiveSfx+@l_DistFileExt+'"'
      INSERT INTO #xpcmdshell_output EXEC master..xp_cmdshell @l_Cmd
      
      DELETE FROM #xpcmdshell_output WHERE CmdOutput IS NULL
      SELECT @l_xpRetCode=RTRIM(LTRIM(MIN(CmdOutput))) FROM #xpcmdshell_output

      IF @l_xpRetCode!='1 file(s) copied.'
         BEGIN
            SET @o_RetVal=3 RETURN
         END
      ELSE
         BEGIN
            SET @l_xpretcode=NULL
            TRUNCATE TABLE #xpcmdshell_output
         END

These are all portions of different sp's I currently use; so, sorry the parameters are not consistant. You get the point though. These can easily be mixed with DTS though.

Regards,

MF