October 30, 2009 at 4:45 pm
I am new to SQL Server and it would be great if someone could help me.
I am using SSIS Package to import *.log files into database. The format of the log files are yyyymmddXYZ.log.I am using a foreach loop as well.
I want to know if there is way to import all the log files till yesterday. Example: Yesterday: 10/29/2009, Today:10/30/2009. I want all the files till 10/29 excluding 10/30.
I want to do this as i am getting an error "[Flat File Source [1]] Warning: The process cannot access the file because it is being used by another process." for the last (10/30) .log file.I think this is a valid message as there is another process that is updating "Today's file. All the other (older than 10/29) .log files are getting imported into database without any errors.
Please Help !
October 30, 2009 at 11:42 pm
You can do this with a precedence constraint. I wrote a blog about this method recently: http://www.sqlservercentral.com/blogs/tim_mitchell/archive/2009/9/16/skipping-items-in-a-foreach-loop.aspx
Ping me again if I can help further.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
October 31, 2009 at 10:11 am
Thanks a lot Tim. I will try this technique.
I would need some help with the expression as i am new to it.
I tried creating a variable(varFile):
@[User::FilePath] +
+ RIGHT((DT_WSTR,4)YEAR(GETDATE()),2) + (DT_WSTR,2)MONTH(GETDATE()) + (DT_WSTR,2)DAY(GETDATE()) + "XYZ" + ".log")
\\servername\logfiles\20091031XYZ.log
I put this variable in the Precedence COnstraint Editor as @[User::varFile] != "yes"
I could be totally wrong. Can someone please help me. The above expression doesnt seem working.
I need an expression to skip the "today's" .log file and i will place it in a Precedence Constraint (as per Tim's blog)
November 2, 2009 at 12:16 pm
I am completely stuck and would appreciate any help!
November 5, 2009 at 10:27 am
that !="yes" will never work because you will never have a file name called "yes". instead what I would is exactly what Tim Mitchell has nicely laid out.
You have to assign another variable that grabs the current file name then check to see if that CurrentFileName = TodaysFileName, if so skip.
November 9, 2009 at 9:29 pm
Sorry for the delayed response, I've been at the SQL PASS summit and a bit behind on replies.
You're actually closer to a solution than you think...
If you're mapping the file name to a variable in the for each loop, you can compare that variable to the expression you used earlier:
@[User::MyCurrentFilename] !=
@[User::FilePath] +
+ RIGHT((DT_WSTR,4)YEAR(GETDATE()),2) + (DT_WSTR,2)MONTH(GETDATE()) + (DT_WSTR,2)DAY(GETDATE()) + "XYZ" + ".log")
Something similar to this will yield a False boolean result when you hit the current date's file, but will return True otherwise. Simply configure your precedence constraint expression to proceed only if the value is True.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
November 10, 2009 at 3:53 am
Thanks a lot. I figured it out last week 🙂 and my package is working.
November 10, 2009 at 6:57 am
Excellent. Let me know if you run into similar problems.
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply