how to read contents of flat file in SSIS and extract from it just one value for use in variable?

Question

how to read contents of flat file in SSIS and extract from it just one value for use in variable?

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

KoldCoffee (1/13/2015)
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%'

Is the filename of "manifest.txt" constant? Also, where is this file? On your desktop box, on the SQL Server, or on some other box? You certainly don't need to learn a whole new bloody language for this simple task.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

KoldCoffee SSCoach Points: 18971 More actions · Answer 2

Hi Sam, script task parses with your script and even turns green. how to check the value returned?

Also, not completely sure it's ok because inside Edit Script there are areas of the script where I see 'squiggly red lines'

Here's what I did.

I copied the contents of just the script from the link into the Script task.

From

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Runtime;

using System.Windows.Forms;

To

// Finish and return success

Dts.TaskResult = (int)ScriptResults.Success;

}

and made edits on these lines.

String filter = (string)Dts.Variables["FilterValue"].Value;

String FileName = (string)(Dts.Connections["manifestFF"].AcquireConnection(Dts.Transaction) as String);

Dts.Variables["RowCount"].Value = returnvar;

But I'm getting this error when I hover over the squiiglies:

Cannot apply indexing with [] to an expression type Microsoft. SQLServer.Dts.Runtime.Variables

other info

I have created a FF connection called manifestFF and in SSIS Variables:

FilterValue as String = *settings_campaign.txt*

RowCount as Int32 = 0

In script task I defined

User::FilterValue as the ReadOnlyVariable

User::Rowcount as the ReadWriteVariable.

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 3

KoldCoffee (1/28/2015)
Cannot apply indexing with [] to an expression type Microsoft. SQLServer.Dts.Runtime.Variables

I've never seen that before, Doing some research it seems to be an issue if you have multiple versions of SSIS installed:

https://connect.microsoft.com/SQLServer/feedback/details/744390/ssis-any-pre-2012-error-cannot-apply-indexing-with-to-an-expression-of-type-microsoft-sqlserver-dts-runtime-variables

http://support.microsoft.com/kb/938608/en-us/

http://stackoverflow.com/questions/24409771/cannot-apply-indexing-with-to-an-expression-of-type

KoldCoffee SSCoach Points: 18971 More actions · Answer 4

. I have multiple versions of visual studio installed....2008 and 2010. I wonder if this means I Ihave multiple ssis installed. Will check it out.

KoldCoffee SSCoach Points: 18971 More actions · Answer 5

OK, it appears that despite the intellisense feedback it is building successfully. To be sure I'd like to see the output pushed out to a message box.

Samuel......

Can you give me one more bit of script (incorporated with your C#) which will send the output of the rowcount variable to a message box? I will then strip it back out.

Message box to say something like "the rowcount for <var_file> is <var_rowcount>?

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 6

KoldCoffee (2/1/2015)
OK, it appears that despite the intellisense feedback it is building successfully. To be sure I'd like to see the output pushed out to a message box.
Samuel......
Can you give me one more bit of script (incorporated with your C#) which will send the output of the rowcount variable to a message box? I will then strip it back out.
Message box to say something like "the rowcount for <var_file> is <var_rowcount>?

This (or a slight derivation) would do it:

MessageBox.Show(String.Format("the rowcount for {0} is {1}", filter, returnvar));

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

KoldCoffee (1/22/2015)
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).

IMHO, learning C# to pull off such a simple task would be the real overkill here. I can fire up an example when I get home tonight but I need you to answer the questions that I previously asked. Here it is again...

Is the filename of "manifest.txt" constant? Also, where is this file? On your desktop box, on the SQL Server, or on some other box? You certainly don't need to learn a whole new bloody language for this simple task.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MMartin1 One Orange Chip Points: 27799 More actions · Answer 8

Koen Verbeeck (1/19/2015)
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.

Have did it go with this suggestion above with the conditional split? I would take this approach.

----------------------------------------------------

KoldCoffee SSCoach Points: 18971 More actions · Answer 9

Samuel, The message box line of code works.

When I execute I get

'the rowcount for settings_campaign.txt* is 0'

I am feeding the filename to the script via the User::FilterValue which equals string with hardcoded value of '*settings_campaign.txt*'

In the manifest the filenames look like this, with an epoch stamp in the middle that changes. That's why I'm wanting to pass wildcards.

2014-06-20_1403294306_settings_account.txt.zip

2014-06-20_1403294308_settings_publisher.txt.zip

2014-06-20_1403294309_settings_campaign.txt.zip

2014-06-20_1403294311_settings_group.txt.zip

2014-06-20_1403294332_settings_keyword.txt.zip

2014-06-20_1403294561_settings_creative.txt.zip

2014-06-20_1403294747_settings_placement.txt.zip

2014-06-20_1403294749_settings_conversion_events.txt.zip

2014-06-20_1403294751_settings_dimension.txt.zip

2014-06-20_1403294907_fact_keyword.txt.zip

2014-06-20_1403294930_fact_creative.txt.zip

2014-06-20_1403294937_fact_placement.txt.zip

2014-06-20_1403294939_settings_product_target.txt.zip

2014-06-20_1403294944_fact_group_device.txt.zip

Even when I make FilterValue = 'bogus' the script returns 'the rowcount for bogus is 0' though no such filename exists.

The names and file extensions will always be the same ones with the only variability being in the date_epoch. The name.txt.zip will always be same.

How to pass so it can return the rowcount for the filename settings_campaign etc no matter what manifest it is reading?

ps. I like the script task most of all because I can reuse this script in the future for all rowcounts. It's less work, once I have it done...

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 10

change the line

if(data[0] == filter)

to

if(data[0].Contains(filter))

The search expression will not need an asterix (*).

KoldCoffee SSCoach Points: 18971 More actions · Answer 11

Samuel, that works so awesomely. I appreciate.

Recently I had to put a foreach loop around each DFT for which I wanted to dynamically supply the flat file connection name (having a wildcard). Having experienced the tedium and hours of doing that for 45 files (naturally I was error prone and had to revisit many of them) before I could get this working, I am not excited about configuring multiple tasks for each filename on each manifest that I want to get the rowcount from.

I suspect I will keep this code in my repertoire for much time to come....even though I will not know C# for some time, it is so handy.

Thanks so much!