February 16, 2009 at 8:57 am
Hi,
I have a package that reads file from a folder a loads it one by one.I used a for each loop container and variable enumerator for saving the name of the file package is loading ,coz that name then goes into the fact table allowing us to slice the data depending on date and customer.
we are getting more file now that will go to the same folder.
i want to put a condition on the package that it should only read the file that are new and not been loaded.how i can do that.
PS.
we a table in the table that shows us which files the package is loading right now.or it loaded last.just a piece of info if it helps.
i will appreciate the help
thanks
Ashish
February 16, 2009 at 9:20 am
If you log the files that have been loaded then you have enough information to proceed.
Create a task that checks the table for the file to see if it has been processed if it has up update a variable,
put an expression in your precedence constraint reading that variable. If processed then it will skip, else continue.
This should help you out.
http://sqlis.com/post/Introduction-to-Expressions-on-Workflow.aspx
February 16, 2009 at 9:30 am
If you are willing to use a (free) 3rd party tool, Konesans' File Watcher Task sounds like it does exactly what you want.
NB: I haven't use this particular tool myself although I have used other tools from Konesans.
Derek
February 16, 2009 at 9:41 am
Hi Ray M,
thats exactly what i have in my mind.but i am not able to implement it.
as i mention before as well we have a table where we keep the info abt present file.
i am thinking to store all the files info in that table which are loaded and when the next sequence starts it checks the table and which ever file is not there it shoudl load.
but i m not able to do that.
PS.i am not very efficient with script task .
can u just descibe in detail.
how do i get that
February 16, 2009 at 9:42 am
thanks derek,
i will definetly look in that 3rd party software.
but as now we have a urgent requirement and i want to just finish my package generically and after this busy schedule is over i can test and use that application.
thanks
ashish
February 16, 2009 at 12:56 pm
[font="Verdana"]My suggestion would be that after you load a file, zip it. Then you can easily determine which files you have already loaded (and the loaded files take less space.)[/font]
February 16, 2009 at 1:18 pm
rayash16 (2/16/2009)
Hi Ray M,thats exactly what i have in my mind.but i am not able to implement it.
as i mention before as well we have a table where we keep the info abt present file.
i am thinking to store all the files info in that table which are loaded and when the next sequence starts it checks the table and which ever file is not there it shoudl load.
but i m not able to do that.
PS.i am not very efficient with script task .
can u just descibe in detail.
how do i get that
Ray M has the right idea but it sounds like you're not implementing it right.
At the start of your loop, check if the file has already been loaded. If it has then skip the loading for that file. Loop until you are done.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
February 16, 2009 at 2:39 pm
thats what i am not able to get.how to check the file wheather its loaded or not.
i am not getting the condition.
would be mind giving details abt it
February 16, 2009 at 3:02 pm
You need to query the table that has the kist of files loaded. If you find the current file in there then set the value of a variable so that you can use it to not run the load step.
How to do that? There's a 1001 ways.
If you did a select count(*) from "that table" where filename "is the current file" and assign the returned value to a variable, you could then use that variable to determine whether or not you should run the load step. When you set up the Precedence Constraint to the load step, set the constraint to use "Expression and Constraint". Set Value to Success, and Expression to "Variable" = 0.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
February 16, 2009 at 3:40 pm
Thanks for the reply.act I as trying some ways myself think this is the easiest .
February 16, 2009 at 3:56 pm
rayash16 (2/16/2009)
thats what i am not able to get.how to check the file wheather its loaded or not.i am not getting the condition.
would be mind giving details abt it
First set up variables
Filename -- Which I think you have becuase of the foreach contaner your using
and ISProcessed Boolean
Your first task Should be say an execute sql task
Set your connection (server and database where files are logged)
Make a statement that will query for the file
Something like
IF EXISTS (Select * from Mytable where Mytable.Filename = ? )
Select 1 as IsProcessed
Else
Select 2 as IsProcessed
Click ResultSet Drop down and select Single Row.
Click parameter mapping on left. Add --> Select User::Filename and in ParameterName put a value of 0
Then on Result set click add
Resultname is whatever Variable name is ISProcessed
then on the Precedence constraint between the lookup and the dataflow task double click, and change the evaluation operation from Constraint to expression and constraint
chang Expression to @ISProcessed == True
Then of course the last step should be to run a sql task that inserts a record into your table indicating the filename, and probably date.
This should be enough to get you going.
February 16, 2009 at 4:17 pm
thanks a lot Bruce .
definitely this will get me going.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply