Compare fileName coming in againts Name column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Compare fileName coming in againts Name column

Author

Topic

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-12-08 : 16:09:37

All,

Ive been struggling with this for some time.
I have number of files that come indaily,with the following format
yyyymmdd_01.zip,yyyymmdd_09.zip.The '09' and '01' refer to dept codes from where the files are originated.I have a SQL Table with all the dept code's that are valid.I have to come up with a way to do the following
1)Ensure only current days file is picked up with dept code that exist in my table.
2)For files that do not have matching dept code,they should be left alone.

Here is what I have done so far
1) Execute SQL Task, Do a SELECT dept_code from DEPT table and store values in RecordSet. User::fileExt
2)Drag a for each loop container,that loops over the C:\Input folder and looks for only files with *.zip extension.Store fileName in variable User::fileName
3) Here is where Im lost,what transformation and where should I place it to strip rest characters from User::fileName and compare the dept code with existing values in User::fileExt.Keeping in mind to also validate only files with todays date 20081208.

I know the easier way to do all of the above is to use Script Task, and I have all that done.Which does most of what I need to do BUT my PM wants me to make full use of the other SSIS transformation and see how it can be done and compare the results/performance of both process.

Thank You

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 03:16:46

for striping part of name, what you need is derived column task.get that value onto variable. then use sql command task to get records where deptcode = the got value. map parameter onto variable.

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-12-09 : 09:30:03

visakh,
Appreciate if you could explain in more detail..Let me know if this is correct ?

1) For each loop to loop and store all.zip fileNames into var User::fileName only those with current date.I have added the following expression for FileNameRetrival
SUBSTRING( @[User::FileName], 5,8) == (DT_WSTR,4) YEAR(GETDATE()) + RIGHT("00" + (DT_WSTR,2) MONTH(GETDATE()),2)+ RIGHT("00" + (DT_WSTR,2) DAY(GETDATE()),2)
2) Place a data flow task in the for each loop
3) Place a derived column transformation into the data flow tab
4) Strip out the first 13 characters of the file name "xxx_20081209_"
5) Strip out the last 4 characters of the file name ".zip"
6) Now how do I store and compare the fileName after stripping with all the available dept_codes in the sql table.Do I do some sort of a lookup ?

Thank You

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 11:13:19

quote:
Originally posted by LOOKUP_BI

visakh,
Appreciate if you could explain in more detail..Let me know if this is correct ?

1) For each loop to loop and store all.zip fileNames into var User::fileName only those with current date.I have added the following expression for FileNameRetrival
SUBSTRING( @[User::FileName], 5,8) == (DT_WSTR,4) YEAR(GETDATE()) + RIGHT("00" + (DT_WSTR,2) MONTH(GETDATE()),2)+ RIGHT("00" + (DT_WSTR,2) DAY(GETDATE()),2)
2) Place a data flow task in the for each loop
3) Place a derived column transformation into the data flow tab
4) Strip out the first 13 characters of the file name "xxx_20081209_"
5) Strip out the last 4 characters of the file name ".zip"
6) Now how do I store and compare the fileName after stripping with all the available dept_codes in the sql table.Do I do some sort of a lookup ?
store the above two values in variables and use variable in SQL Oledb task to map to parameter in query which corresponds to dept_code.
Thank You

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-12-09 : 11:32:39

quote:
Originally posted by visakh16

quote:
Originally posted by LOOKUP_BI

visakh,
Appreciate if you could explain in more detail..Let me know if this is correct ?

1) For each loop to loop and store all.zip fileNames into var User::fileName only those with current date.I have added the following expression for FileNameRetrival
SUBSTRING( @[User::FileName], 5,8) == (DT_WSTR,4) YEAR(GETDATE()) + RIGHT("00" + (DT_WSTR,2) MONTH(GETDATE()),2)+ RIGHT("00" + (DT_WSTR,2) DAY(GETDATE()),2)
2) Place a data flow task in the for each loop
3) Place a derived column transformation into the data flow tab
4) Get the dept code value from the fileName using derived clmn.How do I store the value in a variable.
REPLACE(REPLACE(SUBSTRING(REVERSE(@[User::FileName]),1,6),"txt.",""),"_","")
6)My How's ?
a) Strore results of derived clmn to var ?
b) Compare value of var a) with Dep_Code clmn in Dept Table.

Im not very clear with your previous explanation.Do help.Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 12:05:01

quote:
Originally posted by LOOKUP_BI

quote:
Originally posted by visakh16

quote:
Originally posted by LOOKUP_BI

visakh,
Appreciate if you could explain in more detail..Let me know if this is correct ?

1) For each loop to loop and store all.zip fileNames into var User::fileName only those with current date.I have added the following expression for FileNameRetrival
SUBSTRING( @[User::FileName], 5,8) == (DT_WSTR,4) YEAR(GETDATE()) + RIGHT("00" + (DT_WSTR,2) MONTH(GETDATE()),2)+ RIGHT("00" + (DT_WSTR,2) DAY(GETDATE()),2)
2) Place a data flow task in the for each loop
3) Place a derived column transformation into the data flow tab
4) Get the dept code value from the fileName using derived clmn.How do I store the value in a variable.
REPLACE(REPLACE(SUBSTRING(REVERSE(@[User::FileName]),1,6),"txt.",""),"_","")
6)My How's ?
a) Strore results of derived clmn to var ?
b) Compare value of var a) with Dep_Code clmn in Dept Table.

Im not very clear with your previous explanation.Do help.Thanks

create variables in package to store the values of stripped filename (hope you know how to create variable in ssis). now cant you use sql command or execute sql task with this variable value passed as a parameter?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources