Newbie to DataFlow in Azure Data Factory - split data to multiple files

Question

Newbie to DataFlow in Azure Data Factory - split data to multiple files

Scott Klein 161

OK, I am new to Azure Data Factory and Data Flows, so I have some questions, but we'll start with this one first. I am trying to read data from SQL Server into Azure blob storage, and I have a copy activity in ADF working great. Well, I need to split the incoming data into separate files (for example, columns 1-3 into file1, columns 4-6 into file2), so splitting columns, not rows.

So, I built data flow that reads data from SQL and splits the data. However, I get the "Linked Service with self-hosted integration runtime is not supported in data flow." error. In doing some looking around, what I am finding is that I first need to pull the data from SQL into blob storage first (such as a csv file first), then use the data flow to use the new blob file as the source to parse into multiple files.

Is that really the case??? That seems like a huge extra and unnecessary step. The source setting on my data flow points to my SQL dataset, so do I have something configured wrong?

User's image

Uwe Fuchs 150 Reputation points

2023-12-01T01:51:12.1933333+00:00

Could you please specify the Integration Runtime utilized by your Linked Service? Considering the encountered error, I infer that a self-hosted Integration Runtime is in use to establish a connection with an on-premises SQL Server - is this accurate? If your SQL Server is not on-premises, I would recommend exploring the possibility of switching to an Azure Integration Runtime as a potential solution.
Scott Klein 161 Reputation points

2023-12-01T02:31:23.56+00:00

My SQL Server is on-premises, thus the specified runtime which is the self-hosted runtime.

Accepted answer

2 additional answers

Your answer

Uwe Fuchs 150 Reputation points

2023-12-01T01:51:12.1933333+00:00

Could you please specify the Integration Runtime utilized by your Linked Service? Considering the encountered error, I infer that a self-hosted Integration Runtime is in use to establish a connection with an on-premises SQL Server - is this accurate? If your SQL Server is not on-premises, I would recommend exploring the possibility of switching to an Azure Integration Runtime as a potential solution.
Scott Klein 161 Reputation points

2023-12-01T02:31:23.56+00:00

My SQL Server is on-premises, thus the specified runtime which is the self-hosted runtime.

Answer 1

AnnuKumari-MSFT 34,556 Microsoft Employee Moderator

Hi Scott Klein ,

Thanks for using Microsoft Q&A platform and thanks for posting your query here.

As per my understanding you are trying to split the columns from your on-prem SQL database and then load it into multiple files in Az blob storage using mapping dataflow.

Yes, your understanding is correct. Dataflow doesn't support self hosted IR. However, you can use inline dataset in dataflow that allows to connect with on-Prem SQL Server.

User's image

Other workaround is to load the table into blob storage using copy activity and then create multiple branches from the source transformation and use 'select transformation' to split your columns , attach it with multiple corresponding sinks to load it into different files .

Relevant document: https://techcommunity.microsoft.com/t5/azure-data-factory-blog/new-data-flow-connector-sql-server-as-source-and-sink/ba-p/2406213

Hope it helps. Kindly accept the answer by clicking on Accept answerbutton. Thankyou

Scott Klein 161 Reputation points

2023-12-01T16:49:37.4966667+00:00

Hi Annu, thank you for the information. I am trying to use an inline dataset but I am getting the following when I test the connection:

"Only one valid authentication should be used for SQLV01OnPremSQL2. SQLAuthentication is invalid. One of user/password is missing.","detailedMessage":"Failure 2023-12-01 16:45:00.456 failed DebugManager.processJob, run=8af6e026-c4af-4729-b577-1fdb0e8f2526, errorMessage=com.microsoft.dataflow.broker.InvalidOperationException: Only one valid authentication should be used for SQLV01OnPremSQL2. SQLAuthentication is invalid. One of user/password is missing."}"

I am I am not sure what this means. The linked service is not used anywhere else in the data flow or ADF.
Scott Klein 161 Reputation points

2023-12-01T23:28:47.4766667+00:00

Hi Annu, I did a bit of research and found out that using the SQL Server linked service requires a virtual network and express route or vpn. We may go that route but for now I need to test, so I did the other option, which is to load the table into blob storage using copy activity and then create multiple branches from the source transformation and use 'select' transformation to split your columns. It works, but I need to make a few adjustments for file naming and passing parameters, etc.

Thank you for your help. Greatly appreciated. I will mark your answer as the answer and post other questions regarding my other two issues.
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-12-04T04:39:11.28+00:00

Scott Klein ,

Thanks for sharing more insights and workarounds around your issue . Thanks for accepting the answer.

Answer 2

Uwe Fuchs 150

With regards to splitting data read from a SQL server into multiple files, maybe try use a copy activity instead. This can be achieved without a data flow, and just using the copy activity. See screenshot below on how you can set up the sink.

User's image

Hope it helps. Kindly accept the answer by clicking on Accept answer button - It would be greatly appreciated.

Scott Klein 161 Reputation points

2023-12-01T02:30:27.67+00:00

That does not solve my problem Uwe. Please re-read my post. I need to split columns, not rows.
Scott Klein 161 Reputation points

2023-12-01T02:32:49.0133333+00:00

I appreciate the input Uwe, but I don't think the copy activity does not do what I need it to do.

Answer 3

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more

Share via

Newbie to DataFlow in Azure Data Factory - split data to multiple files

2 additional answers

Your answer