Sunil Reddy Enugala

…..is just walking through MSBI Street.

OLE Automation Procedures to check the Source File Existence

Posted by Sunil Reddy Enugala on June 6, 2013

To check existence of file in a given folder, we can use OLE automation procedures. Please go through the below SQL Script to know the process.

We need to enable ole automation procedures property of the SQL Server to use it.

There are two ways to enable the property, first process is using below code.

   1:  Sp_configure 'show advanced options', 1;
   2:  Go
   3:  Reconfigure;
   4:  Go
   5:  Sp_configure 'ole automation procedures', 1;
   6:  Go
   7:  Reconfigure;
   8:  Go

Second process is to select SQL Server Properties –> Click on Facets –> Select Surface Area Configuration as a facet –> set OLE automation procedures to True.

Please go through the below script code:

   1:  DECLARE 
   2:  @Filename VARCHAR(100) = 'D:\DelmeSoon.txt'  
   3:                 --give your filename with folderpath
   4:  ,@hr INT 
   5:  ,@objFileSystem INT
   6:  ,@objFile INT
   7:  ,@ErrorObject INT
   8:  ,@ErrorMessage VARCHAR(255)
   9:  ,@Path VARCHAR(100)
  10:  ,@size INT
  11:  
  12:  
  13:   
  14:  EXEC @hr = sp_OACreate 'Scripting.FileSystemObject'
  15:      ,@objFileSystem OUT
  16:   
  17:  IF @hr <> 0
  18:  BEGIN
  19:      SET @errorMessage = 'Error in creating the file system object.'
  20:   
  21:      RAISERROR (
  22:              @errorMessage
  23:              ,16
  24:              ,1
  25:              )
  26:  END
  27:  ELSE
  28:  BEGIN
  29:      EXEC @hr = sp_OAMethod @objFileSystem
  30:          ,'GetFile'
  31:          ,@objFile out
  32:          ,@Filename
  33:   
  34:      IF @hr <> 0
  35:      BEGIN
  36:          SET @errorMessage = 'File not found '
  37:   
  38:          SELECT @Filename AS [FileName]
  39:              ,@errorMessage AS [Status]
  40:      END
  41:      ELSE
  42:      BEGIN
  43:          -- to get the file path                        
  44:          EXEC sp_OAGetProperty @objFile
  45:              ,'Path'
  46:              ,@path OUT
  47:   
  48:          -- to get the file size                        
  49:          EXEC sp_OAGetProperty @objFile
  50:              ,'size'
  51:              ,@size OUT
  52:   
  53:          IF @size = 0
  54:          BEGIN
  55:              SET @errorMessage = 'Empty file  '
  56:   
  57:              SELECT @Filename AS [FileName]
  58:                  ,@errorMessage AS [Status]
  59:          END
  60:          ELSE
  61:          BEGIN
  62:              SET @size = (@size / 1024.0)
  63:              SET @errorMessage = 'File Exists  '
  64:   
  65:              SELECT @Filename AS [FileName]
  66:                  ,@errorMessage AS [Status]
  67:                  ,@size AS [FileSize_kb]
  68:          END
  69:      END
  70:  END
  71:   
  72:  EXEC sp_OADestroy @objFileSystem
  73:   
  74:  EXEC sp_OADestroy @objFile

Let me know your comments and Queries.

About these ads

One Response to “OLE Automation Procedures to check the Source File Existence”

  1. URL said

    … [Trackback]

    [...] Find More Informations here: msbimentalist.wordpress.com/2013/06/06/ole-automation-procedures-to-check-the-source-file-existence/ [...]

Leave a Reply...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 662 other followers

%d bloggers like this: