Microsoft Business Intelligence: Reading the files (PDF, word, excel text etc.) From folder and loading it into the table using SSIS Package

Wednesday, 30 June 2021

Reading the files (PDF, word, excel text etc.) From folder and loading it into the table using SSIS Package

In the previous post, we saw that how to load the Reading the files (PDF, word, excel text, etc.) From folder and loading it into the table using SQL script.

Read here: Readingthe files (PDF, word, excel text, etc.) From folder and loading it into thetable using SQL script

https://bageshkumarbagi-msbi.blogspot.com/2021/06/reading-files-pdf-word-excel-text-etc.html

In this post, we will see how we load the files using SSIS.

Below are the files which we need to load into the table.

Taking Data flow task.

Taking Source as OLEDB source and selecting Data access mode as SQL Command.

And writing the below sql script

DECLARE @file_nm     VARCHAR(250),
        @Source_Path VARCHAR(500) ='G:\Books\SSIS',
        @file_path   VARCHAR(5000),
        @cmdstr      NVARCHAR(400);
DECLARE @files TABLE
  (
     id      INT IDENTITY,
     file_nm VARCHAR(500)
  )

SET @cmdstr = 'dir ' + @Source_Path + ' /b';

INSERT INTO @files
EXECUTE Xp_cmdshell
  @cmdstr

SELECT file_nm                      AS file_nm,
       @Source_Path + '\' + file_nm AS file_path
FROM   @files
WHERE  file_nm IS NOT NULL

Map the columns.

Now taking Import column Transformation. And mapping the file path with leanageID.

Read here : Uploading the Image into sqlserver using SSIS

https://bageshkumarbagi-msbi.blogspot.com/2021/03/uploading-image-into-sql-server-using.html

Now taking Oledb destination.

Now our package is ready to run.

See the records in the table.

Let’s run the package.

Ooooooooooooooo

My package get failed.

Below is the error.

[OLE DB Source [34]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E14 Description: "SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.".

We need to configure the 'xp_cmdshell' using the below script.

USE MASTER

EXEC sp_configure 'xp_cmdshell', 1;

RECONFIGURE;