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

check contents of a text file Expand / Collapse
Author
Message
Posted Monday, February 4, 2013 8:53 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
Guys,

I am using the Konesans file watcher to watch for a file (status.txt) to change.

The file contains only one line of text which will be 'Build In Progress' 'Build Failed' or 'Build Sucess'

I want to monitor the contents of the file and only continue processing if the status is 'Build Success'

The challenge is that I need to do it without using scripts.....



So far I am trying this - which seems a bit long winded...

DataFlow Task to convert the flat file to a raw file
DataFlow Task to read the raw file into ADO recordset

ForEachLoop container to read the ADO recordset and assign the value to a string variable
ForLoop container inside the ForEachLoop that does not process if the value != "Build Success"
Post #1415357
Posted Monday, February 4, 2013 9:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:00 AM
Points: 5,166, Visits: 12,016
Read the file in a dataflow.

Add a conditional split which redirects processing according to what has been read - two outputs: Build Success and the rest.

Each different path sets a variable appropriately.

Destination is irrelevant - use the trash destination.

Now you have a variable which you can use to decide what to do next.



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 #1415364
Posted Monday, February 4, 2013 11:30 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
Thanks Phil, That was a useful lesson.

Unfortunately I can't see how to assign the value to a variable without using a script. I have tried sending the output to a new flat file but the file gets written even if there is nothing to put into it.
Post #1415424
Posted Monday, February 4, 2013 11:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:00 AM
Points: 5,166, Visits: 12,016
aaron.reese (2/4/2013)
Thanks Phil, That was a useful lesson.

Unfortunately I can't see how to assign the value to a variable without using a script. I have tried sending the output to a new flat file but the file gets written even if there is nothing to put into it.


OK, my flippant comment about setting a variable was not very helpful - sorry.

But there is a way to do this, using the same technique I mentioned. After the conditional split, put a RowCount component in the 'Success' branch and fire the result into an int variable which has been initialised as zero.

At the end of the data flow, you have an int variable which is either 0 or 1 and you can use that in your precedence constraints.



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 #1415437
Posted Wednesday, February 6, 2013 3:12 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
Nice one Phil,

Worked a treat. I am up against it today but I will post a full solution later to help others.

Obiron
Post #1416358
Posted Tuesday, February 19, 2013 8:33 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
Ok,

This is what we did.

Package scoped variable intIsGoodToGo (Int32)

For-loop with Eval Expression of 1==1 to keep the package constantly running

Konesans File Watcher looks for the file to arrive or change (we also have it set to check on start up) On detection of a change

Data Flow Task:

Flat file source which is the status file we were monitoring
Conditional Split: The file contains one column which we called status. CS has two output streams 'Success' with a condition of Status == "Build Success" and 'Not Success' with a condition of Satus != "Build Success"

The Success stream goes to a rowcount destination which puts the rowcount into intIsGoodToGo. In our case the result will be either 0 or 1 because the file will only ever contain one line

Precedence Contstraint from the dataflow task on task Success and Expression of @intGoodToGo == 1


The package then processes until the termination point then the for-loop evaluation kicks in and waits for the next appearance of the file. The last task before termination is to delete the file being watched for (in our case the source application will re-create it if it does not exist)


Hope this is helpful to someone.
Post #1421678
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse