SQLServerCentral.com / SQL Server 2005 / SQL Server 2005 Integration Services / Need to check file name before it get process / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 03 Mar 2015 16:37:53 GMT20RE: Need to check file name before it get processhttp://www.sqlservercentral.com/Forums/Topic1560443-148-1.aspx[quote][b]Phil Parkin (4/13/2014)[/b]Repeatedly hitting the database engine with queries which have nothing to do with data is not a good practice, in my opinion. You are generating unnecessary network traffic and needlessly adding to the load on the DB server.Nothing you have done here is difficult to do within a Script Task - keeping all of the processing entirely within SSIS and not hitting the DB server at all.[/quote]Phil,What you said is absolutely true. A script task would be a best choice to do this. I provided a way to get started. I only provided the TS a tip to get started and from there make it effective. Thanks for sharing.. :)Tue, 15 Apr 2014 15:19:48 GMTa4appleRE: Need to check file name before it get processhttp://www.sqlservercentral.com/Forums/Topic1560443-148-1.aspx[quote][b]unnati.patel513 (4/10/2014)[/b][hr]Hi,I need help in checking the file name before i load it to staging tables.I have all the file stored in C:\Database\SourceFiles\ABCSourceFilesand file name like ABC_YYYYMMDDHHMMSSExampleSourceFile1_20140803073209.txtSourceFile2_20130904071210.txtSourceFile3_20120703041530.txtFor i took foreach loop container and point to this location and only selected .txt files and file name put it into @Filename variable.I took data flow task on control flow and take flat file connection i am using this variable in flat file connection order to pull the data from that local location.So my question is how i will do the file validation for file name.We need to reject the file if the file name format is not valid Like SourceFile1_2014073209.txt --Reject this file because --it is not YYYYMMDDHHMMSSand also we need to reject this file if the file name like SourceFile1_uuu2019hhj --- Reject this file because it is not Filename_YYYYMMDDHHMMSS.[/quote]This is a classic case for using script task in SSIS for the load. Using the Script task, create a list of valid file names that you want to parse using a basic REGEX expression or a format validation. Use a FEL (For each loop) to iterate the variable and load the data...this is a very high level approachTue, 15 Apr 2014 10:03:49 GMTRaunak JhawarRE: Need to check file name before it get processhttp://www.sqlservercentral.com/Forums/Topic1560443-148-1.aspxThank you very much for your help.Yes this is good solution.I will put this code in store procedure and run it through Execute sql task.Thanks,UnnatiSun, 13 Apr 2014 10:59:53 GMTunnati.patel513RE: Need to check file name before it get processhttp://www.sqlservercentral.com/Forums/Topic1560443-148-1.aspx[quote][b]a4apple (4/10/2014)[/b][hr]Unnati, see this.. You will be needing to use a Select statement like this and capture the result.[code]DECLARE @String VARCHAR(MAX) = 'SourceFile1_20140803073209.txt'DECLARE @String1 VARCHAR(MAX) ='SourceFile1_2014073209.txt'SELECT @String AS [String] , CASE WHEN LEN(PARSENAME(REPLACE(@String, '_', '.'),2)) = 14 THEN 1 ELSE 0 END AS [Return_Value], @String1 AS [String1] , CASE WHEN LEN(PARSENAME(REPLACE(@String1, '_', '.'),2)) = 14 THEN 1 ELSE 0 END AS [Return Value][/code][/quote]Repeatedly hitting the database engine with queries which have nothing to do with data is not a good practice, in my opinion. You are generating unnecessary network traffic and needlessly adding to the load on the DB server.Nothing you have done here is difficult to do within a Script Task - keeping all of the processing entirely within SSIS and not hitting the DB server at all.Sun, 13 Apr 2014 04:43:26 GMTPhil ParkinRE: Need to check file name before it get processhttp://www.sqlservercentral.com/Forums/Topic1560443-148-1.aspx[quote][b]unnati.patel513 (4/10/2014)[/b][hr]Hi, Thank you very much for the codeBut I need to check that if the Date is valid or not Lets say if we pass this SourceFile1_99999999999999.txt then should not return 1.We need check the condition YYYY should be between 1950 to Current year month is between 1 to 12 and day between 1 to 31My File format like FileName_YYYYMMDDHHMISS.txtI appreciate if you give me idea on that.Thanks,Unnati[/quote]Yes, It was just a code I gave you to see how you should be writing it. see below.. feel free to add more based on that..[code]DECLARE @String VARCHAR(MAX) = 'SourceFile1_20140403073209.txt'DECLARE @String1 VARCHAR(MAX) ='SourceFile1_2014073209.txt'SELECT @String, CASE WHEN CONVERT(date, LEFT(PARSENAME(REPLACE(@String, '_', '.'), 2), 8), 112) &lt;= GETDATE() THEN 1 ELSE 0 END[/code]Thu, 10 Apr 2014 13:34:35 GMTa4appleRE: Need to check file name before it get processhttp://www.sqlservercentral.com/Forums/Topic1560443-148-1.aspxHi, Thank you very much for the codeBut I need to check that if the Date is valid or not Lets say if we pass this SourceFile1_99999999999999.txt then should not return 1.We need check the condition YYYY should be between 1950 to Current year month is between 1 to 12 and day between 1 to 31My File format like FileName_YYYYMMDDHHMISS.txtI appreciate if you give me idea on that.Thanks,UnnatiThu, 10 Apr 2014 11:58:23 GMTunnati.patel513RE: Need to check file name before it get processhttp://www.sqlservercentral.com/Forums/Topic1560443-148-1.aspxUnnati, see this.. You will be needing to use a Select statement like this and capture the result.[code]DECLARE @String VARCHAR(MAX) = 'SourceFile1_20140803073209.txt'DECLARE @String1 VARCHAR(MAX) ='SourceFile1_2014073209.txt'SELECT @String AS [String] , CASE WHEN LEN(PARSENAME(REPLACE(@String, '_', '.'),2)) = 14 THEN 1 ELSE 0 END AS [Return_Value], @String1 AS [String1] , CASE WHEN LEN(PARSENAME(REPLACE(@String1, '_', '.'),2)) = 14 THEN 1 ELSE 0 END AS [Return Value][/code]Thu, 10 Apr 2014 10:52:41 GMTa4appleRE: Need to check file name before it get processhttp://www.sqlservercentral.com/Forums/Topic1560443-148-1.aspxHi,Thank you for quick reply but i do not understand the SQL function for this?Can you please tell me about that function?I appreciate if you can give me steps for that.Thanks,Unnati Thu, 10 Apr 2014 10:21:21 GMTunnati.patel513RE: Need to check file name before it get processhttp://www.sqlservercentral.com/Forums/Topic1560443-148-1.aspxHi Unnati, see below.1) Have a SQL Function to validate the file name. Make sure the sql function returns either 0 or 1 upon failure or success.2) Pass the file name you are getting through the loop to the sql function using a Execute sql task.3) If the return value is 0, do nothing but if its 1 add whatever task you want to do with that file. I would assume a Data flow task.4) Using precedence constraints, check the variable to pass the values to the next tasks.another method, not a great one though.Have all file names into a sql table and preprocess them with the same function and keep only ones you want to use. Rest you can delete from the table and use the file names that are needed only directly.Thu, 10 Apr 2014 09:26:28 GMTa4appleNeed to check file name before it get processhttp://www.sqlservercentral.com/Forums/Topic1560443-148-1.aspxHi,I need help in checking the file name before i load it to staging tables.I have all the file stored in C:\Database\SourceFiles\ABCSourceFilesand file name like ABC_YYYYMMDDHHMMSSExampleSourceFile1_20140803073209.txtSourceFile2_20130904071210.txtSourceFile3_20120703041530.txtFor i took foreach loop container and point to this location and only selected .txt files and file name put it into @Filename variable.I took data flow task on control flow and take flat file connection i am using this variable in flat file connection order to pull the data from that local location.So my question is how i will do the file validation for file name.We need to reject the file if the file name format is not valid Like SourceFile1_2014073209.txt --Reject this file because --it is not YYYYMMDDHHMMSSand also we need to reject this file if the file name like SourceFile1_uuu2019hhj --- Reject this file because it is not Filename_YYYYMMDDHHMMSS.I am not sure how i will do it before i process this file.I appreciate for your help in advanced.Thanks,UnnatiThu, 10 Apr 2014 08:03:41 GMTunnati.patel513