Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need to check file name before it get process Expand / Collapse
Author
Message
Posted Thursday, April 10, 2014 8:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 2:18 PM
Points: 27, Visits: 304
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\ABCSourceFiles

and file name like ABC_YYYYMMDDHHMMSS

Example

SourceFile1_20140803073209.txt

SourceFile2_20130904071210.txt

SourceFile3_20120703041530.txt

For 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 YYYYMMDDHHMMSS

and 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,
Unnati






Post #1560443
Posted Thursday, April 10, 2014 9:26 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 5:53 PM
Points: 143, Visits: 316
Hi 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.


Good Luck :) .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
Post #1560500
Posted Thursday, April 10, 2014 10:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 2:18 PM
Points: 27, Visits: 304
Hi,
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
Post #1560526
Posted Thursday, April 10, 2014 10:52 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 5:53 PM
Points: 143, Visits: 316
Unnati, see this.. You will be needing to use a Select statement like this and capture the result.

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]



Good Luck :) .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
Post #1560537
Posted Thursday, April 10, 2014 11:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 2:18 PM
Points: 27, Visits: 304
Hi, Thank you very much for the code

But 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 31
My File format like FileName_YYYYMMDDHHMISS.txt


I appreciate if you give me idea on that.

Thanks,
Unnati
Post #1560567
Posted Thursday, April 10, 2014 1:34 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 5:53 PM
Points: 143, Visits: 316
unnati.patel513 (4/10/2014)
Hi, Thank you very much for the code

But 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 31
My File format like FileName_YYYYMMDDHHMISS.txt


I appreciate if you give me idea on that.

Thanks,
Unnati


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..
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) <= GETDATE() THEN 1 ELSE 0 END



Good Luck :) .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
Post #1560605
Posted Sunday, April 13, 2014 4:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:15 PM
Points: 5,245, Visits: 12,163
a4apple (4/10/2014)
Unnati, see this.. You will be needing to use a Select statement like this and capture the result.

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]



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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1561270
Posted Sunday, April 13, 2014 10:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 2:18 PM
Points: 27, Visits: 304
Thank 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,
Unnati
Post #1561282
Posted Tuesday, April 15, 2014 10:03 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:28 AM
Points: 1,044, Visits: 1,839
unnati.patel513 (4/10/2014)
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\ABCSourceFiles

and file name like ABC_YYYYMMDDHHMMSS

Example

SourceFile1_20140803073209.txt

SourceFile2_20130904071210.txt

SourceFile3_20120703041530.txt

For 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 YYYYMMDDHHMMSS

and also we need to reject this file if the file name like SourceFile1_uuu2019hhj --- Reject this file because it is not Filename_YYYYMMDDHHMMSS.



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 approach


Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
Post #1561959
Posted Tuesday, April 15, 2014 3:19 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 5:53 PM
Points: 143, Visits: 316
Phil Parkin (4/13/2014)
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.


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.. :)


Good Luck :) .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
Post #1562063
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse