January 13, 2015 at 12:52 pm
From the attached txt file, I need to be able to extract the rowcount for the file having the name '2014-06-20_1403294747_settings_placement.txt.zip' and place it into a variable but I don't know how to write the script for the script tasktransformation.
Can someone look at the attached txt file and share with me a C# script that will do the equivalent of the below sql?
select rows from <manifest.txt> where file like '%settings_placement%'
January 15, 2015 at 6:18 pm
It looks like your manifest.txt may be tabbed delimited, or delimited in some manner. Why not use a flat file source within a data flow? The "rows" header (and other headers) would come through as a column with its values and go from there...
January 15, 2015 at 10:19 pm
there is no transformation that allows me to run the select statement I mentioned against the flat file source connection.
Perhaps you can elaborate "go from there"....as I see it the next transformation needs to be a script task.
I hope that for someone who knows C# the script to get that value is not so difficult?
January 15, 2015 at 11:50 pm
you can do it in two ways on your preference
1) Import the data into a newly creating staging table using FLAT File Source, simple fetch an dump in the database. after that you can execute any type of query you want to run against it. Link
2) Use Script Transformation/ Task as Data source, read the text file using this Link and do the decision in memory.
hope it helps.
January 18, 2015 at 3:33 pm
well, those are not exactly helpful.
The first link is a tutorial for how to set up a flat file connection (which I have already done) and while the second is following the process I would like to follow....since I do not know C# I don't know how to modify the information to my needs.
I need a script task following the flat file connection but don't know how to write C#.
Is there anyone who can show me the C# script for the select statement I postedin the opening statement for the file I uploaded? If this is the beginning of my learning C# I would be quite grateful. Certainly I do not expect someone will always write C# for me but it would be a great leg up.
Then I can use the script task and out put the rowcount to a variable.
January 18, 2015 at 10:50 pm
whats wrong with the option 1 exactly?
January 19, 2015 at 2:21 am
What I would do:
* Read the flat file like you normally would, i.e. with a flat file source. You can use a for each loop container with a wildcard to find the correct flat file.
* Use a conditional split to implement the "like" logic. You can use FINDSTRING for this.
* Redirect the rows that match the condition to a rowcount component. This transformation will store the row count into a variable.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 19, 2015 at 5:20 am
Koen, I followed what you were saying and that sounds very good. I will try that today. thanks very much.
twin.devil, Actually, suggestion one is viable but it requires a lot of transformations. First a FF source connection, next ole db destination to hold table, next another data flow task with ole db source connection, finally another data flow task for the execute sql task connection to output rowcount to an output variable. It's a lot of work for the rowcount of a single file name and I plan to get rowcount for all 14 and put them into variables. The very fastest way to get the row count is with C# script....
And....I actually meant that the link in first suggestion wasn't helpful (a tutorial on how to create a flat file connection).
Best of all (I believe though not authoritatively) would be a flat file source connection followed by a C# script task which outputs rowcount to variable. Don't you think?
January 22, 2015 at 6:35 am
Log Parser was made for this sort of problem
Might be a bit of overkill if it's only a one off issue though!
https://technet.microsoft.com/en-gb/scriptcenter/dd919274.aspx
SQL Bits session on it here too:
https://sqlbits.com/Sessions/Event9/LogParser-quicker_than_SSIS_easier_than_BCP
January 22, 2015 at 7:05 am
Yes, I thank you but like you say it's probably overkill. I have to learn C# for making the most of the script task transformation (Control Flow).
See here, 'How to Read From a Text File'?
the C# example requires only 10 lines of code.
https://msdn.microsoft.com/en-us/library/ezwyzy7b.aspx
Alas, I don't know how to modify it to work for my example.... and must muddle through and then probably post to C# forum to experience rotten egg and tomato throwing. Ug :pinch: The stuff of learning.
January 22, 2015 at 10:42 am
I did reply with a package for you... not sure what happened to it
Sam
January 22, 2015 at 11:07 am
Samuel Vella (1/22/2015)
I did reply with a package for you... not sure what happened to itSam
I think the spam filter removed it.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 22, 2015 at 11:34 am
Koen Verbeeck (1/22/2015)
I think the spam filter removed it.
probably too many links.. I'm surprised that posts (especially by established members) are automatically deleted
here's the package again with out the reference list of useful links:
https://dl.dropboxusercontent.com/u/1630056/ReadFileValueToVariable.dtsx
January 22, 2015 at 1:09 pm
That's awesome you did that. I've opened it and I will try it today.
January 22, 2015 at 4:08 pm
KoldCoffee (1/22/2015)
That's awesome you did that. I've opened it and I will try it today.
no probs
I should have pointed out that the file path is defined in the file connection manager NOT in the script task.
The script task uses the connection manager to get the file path.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply