Note: Parameters INPUT: UNCPathAndLogTable - UNC Path And LogTable OF Parameter file
RELATED SPROC: spManualRun PROCESSES THE PARAMETERS IN THIS FILE
UPDATED: 06/01/2020 TJC created for Automation
TESTING: EXECUTE [myDatabase].dbo.[spParamFileCheck]
@UNCPathAndLogTable=N'\\Server02\FTP\In\InParameters.csv';
RETURN OUTPUT: 0 GOOD RETURN, SSIS JOB CONTINUES
-1 NEUTRAL RETURN, SSIS JOB STOPS
ERROR, SSIS JOB STOPS
***************************************************************************************************/
CREATE PROCEDURE dbo.[spParamFileCheck]( @UNCPathAndLogTable varchar(512) = NULL)
AS
BEGIN
SET NOCOUNT ON;
declare @cmd varchar(8000), @CID INT = 0, @FileRecordCount int = 0, @CurrentDate Datetime = GETDATE(), @ErrorMessage varchar(max);
declare @dir table(FullName varchar(512));
IF (isnull(@UNCPathAndLogTable, '') = '') BEGIN
SET @UNCPathAndLogTable = N'\\Server01\FTP\In\InParameters.csv';
END;
BEGIN TRY
SET @cmd='exec xp_cmdshell ''dir '+@UNCPathAndLogTable+' /b /s''';
INSERT @dir(FullName) EXEC(@cmd);
SELECT @FileRecordCount = COUNT(*) FROM @dir WHERE FullName is not null;
IF (@FileRecordCount = 0) BEGIN
PRINT '-1';
RETURN -1;
END;
/*** LOGGING THE JOB PARAMETERS ***/
IF (object_id('tempdb..#ParametersFile') is not null) DROP TABLE #ParametersFile;
CREATE TABLE #ParametersFile (Column1 varchar(50), Column2 varchar(50), Column3 varchar(50), Column4 varchar(50));
SET @cmd = 'bulk insert #ParametersFile from '''+@UNCPathAndLogTable+''' WITH (FIELDTERMINATOR = '','' )';
EXEC(@cmd);
END TRY
BEGIN CATCH /* CATCH ERROR BUT DO NOT RAISERROR, RETURN -1 AS STOP JOB CODE */
--INSERT INTO LogTable (CID, FileName, loaddate, SourceType,NumItems,SecondsToLoad,ErrorCode)
-- VALUES (0, 'InParameters.csv',@CurrentDate, 'FTPsource',@FileRecordCount, 0, -1);
SELECT @ErrorMessage = 'spParamFileCheck: ' + ERROR_MESSAGE() + ' Error on Line: ' + cast(ERROR_LINE() AS VARCHAR(10));
PRINT '-1 ' + @ErrorMessage;
RETURN -1;
END CATCH;
BEGIN TRY
--SELECT TOP 1 * FROM #ParametersFile WITH(NOLOCK) WHERE Column1 IS NOT NULL AND ISNUMERIC(Column4) > 0;
SELECT @FileRecordCount = COUNT(Column1)
FROM #ParametersFile WITH(NOLOCK)
where RTRIM(ISNULL(Column1, '')) <> ''
and RTRIM(ISNULL(Column2, '')) <> ''
and RTRIM(ISNULL(Column3, '')) <> ''
and isnumeric(Column3) > 0
and isnumeric(Column4) > 0;
IF (@FileRecordCount > 0) BEGIN
SELECT TOP 1 @CID = ce.CID
FROM myDatabase.dbo.ssisInputProcessing AS irc WITH (NOLOCK)
INNER JOIN myOtherDatabase.dbo.CopeceticItems AS ce WITH (NOLOCK) ON irc.Column1 = ce.Column1
AND irc.Column1 in (SELECT TOP 1 Column1 FROM #ParametersFile WITH(NOLOCK)
WHERE Column1 IS NOT NULL AND ISNUMERIC(Column4) > 0);
IF (ISNULL(@CID, 0) = 0) BEGIN /* REPORT ERROR: INCOMING PARAMETERS FILE HAS INCORRECT DATA TO PROCESS */
RAISERROR ('spParamFileCheck InParameters.csv was found but contains invalid client data. Column1.',15,1)
END;
END ELSE BEGIN /* REPORT ERROR: INCOMING PARAMETERS FILE HAS NO DATA TO PROCESS */
RAISERROR ('spParamFileCheck InParameters.csv was found but contains no valid rows to import. missing parameters.',15,1)
END;
--INSERT INTO LogTable (CID, FileName, loaddate, SourceType,NumItems,SecondsToLoad,ErrorCode)
--VALUES (@CID, 'InParameters.csv',@CurrentDate, 'FTPsource',@FileRecordCount, 0, 0);
END TRY
BEGIN CATCH
--INSERT INTO LogTable (CID, FileName, loaddate, SourceType,NumItems,SecondsToLoad,ErrorCode)
-- VALUES (0, 'InParameters.csv',@CurrentDate, 'FTPsource',@FileRecordCount, 0, -1);
SELECT @ErrorMessage = 'InParameters Bulk Insert: ' + ERROR_MESSAGE() + ' Line: ' + cast(ERROR_LINE() AS VARCHAR(10));
PRINT @ErrorMessage;
RAISERROR (@ErrorMessage,15,1);
RETURN -1;
END CATCH
PRINT '0'
RETURN 0;
/* FUTURE: EXPORT THE INCOMING PARAMETERS FILE FROM THE INPUT FOLDER TO THE EXPORT FOLDER */
BEGIN TRY
declare @UNCPathAndFileOut varchar(512) = N'\\Server02\Output\InParameters.csv'
SET @SQL ='bcp "SELECT (Column1, Column2, Column3, CAST(Column4 AS VARCHAR(1)) AS Column4 from #ParametersFile " queryout ' +@UNCPathAndFileOut+' -t"|" -c -T ';
EXEC xp_cmdshell @SQL ;
END TRY
BEGIN CATCH
SELECT @ErrorMessage = 'Cmdshell Export: ' + ERROR_MESSAGE() + ' Error on Line: ' + cast(ERROR_LINE() AS VARCHAR(10));
print @ErrorMessage;
RAISERROR (@ErrorMessage,15,1);
RETURN;
END CATCH
