question

arkiboys avatar image
0 Votes"
arkiboys asked SaurabhSharma-msft commented

copy activity - select * from .csv dataset

Hello,
At present I read all the data inside a .csv file which sits in the blobstorage container.
On each run of the data factory, based on the parameters based I want to select only certain rows, i.e.
select * from (dataset of the csv) where field1 = @param1 and field3 = @param2

Is this possible?
I can do the exact same thing in copy activity pointing to sql server but not sure how this is achieved reading from csv.

Thank you

azure-data-factory
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SaurabhSharma-msft avatar image
0 Votes"
SaurabhSharma-msft answered SaurabhSharma-msft commented

Hi @arkiboys,

Thanks for using Microsoft Q&A !!
This is not possible in Copy Activity and you need to use other alternatives like Data Flow to filter out unwanted records. OR you can try using lookup activity to get the records and then add logic to exclude the records if it works for you.

Thanks
Saurabh

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,
I have now achieved the objective using a lookup to read the csv and then filter activity to filter the csv rows and then copy activity to process each one in a foreach activity.
No need for a dataflow.
Thank you

0 Votes 0 ·

Sure. Thanks

Thanks
Saurabh

0 Votes 0 ·
arkiboys avatar image
0 Votes"
arkiboys answered arkiboys commented

I see.
In that case:
1-
In lookup activity, I read the .csv file
2-
create a dataflow to filter the rows being read from .csv
i.e. Field3 = @param1
How is this achieved?

Thank you

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@arkiboys
No, you don't need to use lookup and dataflow together. Either you can use dataflow or use lookup. I would suggest you to use Dataflow and read files from CSV in dataflow and then add transformations like filter row in dataflow to achieve your required results. I will try to create a sample at my end and provide here in case that helps.

Thanks
Saurabh

0 Votes 0 ·
arkiboys avatar image arkiboys SaurabhSharma-msft ·

This is what I have at present but I get an error as you see.
Lookup is reading a csv file and filter is to filter the csv rows based on the parameter passed in...
132479-image.png


0 Votes 0 ·
image.png (45.8 KiB)

@arkiboys

Please see the below gif which I have used to filter rows using data flow. You could something like below for your scenario.
132563-dataflow-filterrows.gif
You can set parameters in Dataflow and then pass those parameters from your calling pipeline.

Please let me know if you have any questions.

Thanks
Saurabh

0 Votes 0 ·
Show more comments