- To upload the list of available CSV or ‘^’ separated files in the shared location to the Sql server using SQL Bulk Insert and the ‘XP_cmdshell’.
- The SQL Bulk insert command is used to upload the special character separated files into DB at a time with Line feeds terminate.
- The ‘XP_CmdShall’ is used to list the files available the directory and accessing the files for bulk insert.
o For Running this we need a admin privilege and also need reconfigure to execute this Job.
o ‘sp_configure’ is used to reconfiguration.
- For Processing and Tracking Purpose we need 3 tables
o T_FILEDETAIL
This is used to Tracking the each files loaded and if made successfully then ‘FileStatus’ flage is to be ‘Y’, otherwise ‘N’.
o T_VikingData_Middleware
This table is act as a middleware for storing each file details temporaraly and moved to the T_Viking data orignal table.
o T_VIKINGDATA
This is the Main table to maintain all the values from the CSV or ‘^’ seprated files.
- The Table structures are:
CREATE PROCEDURE [dbo].[USP_BulkCSVUploader]
@DESTINATIONPATH VARCHAR(550)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @FILENAMES VARCHAR(64);
DECLARE @CMDSCRIPT VARCHAR(8000);
-- DECLARE @DESTINATIONPATH VARCHAR(512);
DECLARE @MINROW INT;
DECLARE @MAXROW INT;
DECLARE @QRYCMD1 VARCHAR(1500);
DECLARE @QRYCMD2 VARCHAR(1500);
DECLARE @FILETABLE TABLE (ROWID INT IDENTITY, FILENAMES VARCHAR(128))
---** CREATING TEMP TABLE ** ---
CREATE TABLE #ORIGINALFILELIST
(
ROWID INT IDENTITY,
FILENAMES VARCHAR(1000) NULL
);
---** DESTINATION PATH WHERE RPT FILES ARE KEPT ** ---
-- SET @DESTINATIONPATH = '\\10.16.105.171\VIKINGDATA\UHG\DCNLOG\'
--SET @DESTINATIONPATH = '\\10.16.131.141\e\error\'
DELETE
FROM T_FILEDETAIL
WHERE FILESTATUS='N'
BEGIN TRY
---- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options',
1
-- To update the currently configured value for advanced options.
RECONFIGURE
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell',
1
-- To update the currently configured value for this feature.
RECONFIGURE
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH
---** BUILD THE STRING TO CAPTURE THE FILE NAMES IN THE RESTORE LOCATION ** ---
SELECT @QRYCMD1 = 'MASTER.DBO.XP_CMDSHELL ' + CHAR(39) + 'DIR ' + @DESTINATIONPATH +'\*.* /L/B' + CHAR(39);
---** BUILD THE STRING TO POPULATE THE #ORIGINALFILELIST TEMPORARY TABLE ** ---
SELECT @QRYCMD2 = 'INSERT INTO #ORIGINALFILELIST(FILENAMES)' + CHAR(13) + 'EXEC ' + @QRYCMD1;
---** EXECUTE THE STRING TO POPULATE THE #ORIGINALFILELIST TABLE ** ---
EXEC (@QRYCMD2);
SELECT @DESTINATIONPATH,
FILENAMES ,
FILENAMES
FROM #ORIGINALFILELIST;
---** STORING THE RPT FOLDER FULL DETAILS
INSERT
INTO T_FILEDETAIL
(
FILENAMES ,
FILELOCATION,
FILESTATUS ,
FILEUPDATETIME
)
SELECT FILENAMES ,
@DESTINATIONPATH,
'N' ,
GETDATE()
FROM #ORIGINALFILELIST
---** STORING THE RPT FOLDER STATUS DETAILS ** ---
INSERT
INTO @FILETABLE
SELECT FILENAMES
FROM (SELECT FILENAMES,
FILESTATUS
FROM T_FILEDETAIL
WHERE (
FILENAMES LIKE '%.dcnlog'
OR FILENAMES LIKE '%.rpt'
)
AND FILESTATUS = 'N'
)
r
---** SELECTING ROWS ** ---
SELECT @MINROW = MIN(ROWID)
FROM @FILETABLE ;
SELECT @MAXROW = MAX(ROWID)
FROM @FILETABLE ;
IF EXISTS
(SELECT TOP 1
*
FROM @FILETABLE
)
BEGIN
WHILE @MINROW <= @MAXROW
BEGIN
TRUNCATE TABLE DBO.T_VikingData_Middleware;
SELECT @FILENAMES = FILENAMES
FROM @FILETABLE
WHERE ROWID = @MINROW;
UPDATE T_FILEDETAIL
SET FILESTATUS = 'W'
WHERE FILENAMES = @FILENAMES
---** INSERTING INTO INTERMEDIATE TABLE ** ---
BEGIN TRY
SET @CMDSCRIPT = 'BULK INSERT T_VikingData_Middleware FROM '''+@DESTINATIONPATH+ @FILENAMES+''' WITH (FIELDTERMINATOR = ''^'',ROWTERMINATOR = '''+CHAR(10)+''')'
EXEC(@CMDSCRIPT)
---** INSERTING INTO MAIN TABLE ** ---
INSERT
INTO T_VIKINGDATA
(
CUST ,
PROJ ,
JULIANDATE ,
FOLDER ,
DCN ,
STAGEMODE ,
USERID ,
COFNAME ,
STARTTIME ,
ENDTIME ,
VIKDOCUMENT ,
FIELDVISITED ,
KEYSTROKES ,
VALIDCHARS ,
VERCHAR ,
VERCHARCHANGE,
VERFLDCHANGE ,
TDIFF ,
EventON ,
LOC_ORIGINAL ,
location
)
SELECT CUST ,
PROJ ,
JULIANDATE ,
FOLDER ,
DCN ,
STAGEMODE ,
USERID ,
COFNAME ,
STARTTIME ,
ENDTIME ,
VIKDOCUMENT ,
FIELDVISITED ,
KEYSTROKES ,
VALIDCHARS ,
VERCHAR ,
VERCHARCHANGE ,
CAST(SUBSTRING(VerFldChange, 1, 1) AS INT),
DATEDIFF(SECOND,STARTTIME,ENDTIME) ,
GETDATE() ,
LOCATION ,
LOCATION
FROM T_VikingData_Middleware
UPDATE T_FILEDETAIL
SET FILESTATUS = 'Y'
WHERE FILENAMES = @FILENAMES
END TRY
BEGIN CATCH
PRINT 'IN ERR 1' +CONVERT(VARCHAR,ERROR_NUMBER()) + ERROR_MESSAGE()
UPDATE T_FILEDETAIL
SET ERR_REASON = ERROR_MESSAGE()
WHERE FILENAMES = @FILENAMES
END CATCH
SET @MINROW = @MINROW + 1;
END
UPDATE V
SET LOCATION=DBO.FN_SPLIT_N(LOCATION,'^')
FROM T_VIKINGDATA V
WHERE LOC_ORIGINAL LIKE '%^%'
END
END
0 comments:
Post a Comment