Linked service structure

Dave Gray 586

Hi,

Was wondering if there's a way to have ADF create a SQL structure from a linked service and data set?

So for example, I want to pull data from the built-in "ServiceNow" connector and it returns a lot of columns. There appears no obvious way to even just get a list of the column names so that I can manually create a SQL table to take this data.

The column names are different to the ones served by the REST API so it's not even if I can use the JSON generated from that to help me create my structure.

Thanks

sn1a

ShaikMaheer-MSFT 38,456 Reputation points Microsoft Employee

2023-02-17T10:11:36.1866667+00:00

Hi Dave Gray, just checking if below answer helpful? If yes, kindly consider hitting Accept Answer button and hit yes button for the question was answer helpful? Please let me know if any further queries. Thank you.
PRADEEPCHEEKATLA 90,241 Reputation points

2023-02-21T04:13:24.25+00:00

@Dave Gray

We received your feedback that the answer provided on the thread was not helpful.

Kindly let us know what we could have done better to improve the answer and make your engagement experience good. We are here to help you and strive to make your experience better and greatly value your feedback.

Our engineer has provided a detailed answer with an official documentation which has clear steps for which you are looking for.

If you wish, you may re-surveying/rating for the engagement you received on the thread. Your feedback is very important to us.

Looking forward to you reply. Much appreciate your feedback!

Regards,

PRADEEPCHEEKATLA-MSFT
Dave Gray 586 Reputation points

2023-02-21T05:44:01.1566667+00:00

Hello, I never once said the feedback was not helpful, not sure where you're getting that from. Thanks.
PRADEEPCHEEKATLA 90,241 Reputation points

2023-02-21T06:00:08.0266667+00:00

@Dave Gray Thanks for re-surveying for the engagement. Much appreciated!

Regarding the negative feedback: We had recieved a feedback has not helpful on 02/14/2023 02:52 PM and this could be mistaken from you.
Jangu Sadiq (MINDTREE LIMITED) 0 Reputation points

2023-02-21T21:58:33.8266667+00:00

Testing
PNavaneethaKrishnanMINDTREELIMITED-9599 27 Reputation points

2023-02-21T22:35:36.2533333+00:00

Testing purpose

Accepted answer

ShaikMaheer-MSFT 38,456 Reputation points Microsoft Employee

2023-02-14T08:10:54.93+00:00

Hi Dave Gray,

Thank you for posting query in Microsoft Q&A Platform.

If I am understanding correctly then you want to take only few columns from source and create a data with that structure in SQL to copy that data. Please correct me If I am wrong.

You can use mappings tab in copy activity, to map only the columns which you wish to sink. And under sink tab of copy activity, select Auto create table option, to create table at sink database.

In below gif, I selected Auto create table option. This creates the table automatically. Under Mappings tab I am removing columns depName and gender from source.

Hope this helps. Please let me know if any further queries.

Please consider hitting Accept Answer button. Accepted answers help community as well.
Please sign in to rate this answer.

1 person found this answer helpful.
Dave Gray 586 Reputation points

2023-02-14T09:23:27.93+00:00

~~I have just typed a reply to this, pressed submit and it's vanished :-(~~

Dave Gray 586 Reputation points

2023-02-14T09:23:41.09+00:00

Hi @ShaikMaheer-MSFT

Thanks for your detailed reply.

The problem I'm facing is not the number of columns, it's the fact that I have not created a table in advance due to the large number of column and varying data types. Looking at your animated GIF (great idea btw) it looks like I should be able to do this, however I get an error saying that the destination table in the sink is not specified.

Steps so far:

Create a source data set based upon a ServiceNow linked service.

Create a Sink dataset based upon an Azure SQL database - database exists, however the table does not. I am leaving the table as "None"

Create a pipeline using the data copy activity - this is the part that fails validation and therefore cannot be published or ran.

Source dataset

Sink dataset

Pipeline Sink tab

Pipeline mappings tab

ShaikMaheer-MSFT 38,456 Reputation points Microsoft Employee

2023-02-14T09:55:28.36+00:00

Hi Dave Gray, Awesome. You are almost close. In your sink dataset. Since your table will not be present, you need to select the edit option below and enter the name of table which you want to be get created.

Hope this helps.

Please consider hitting Accept Answer and hit yes button for the question was answer helpful? Accepted answers help community as well. Thank you.

Dave Gray 586 Reputation points

2023-02-14T13:04:24.3566667+00:00

Thanks, will try that in a bit.

ShaikMaheer-MSFT 38,456 Reputation points Microsoft Employee

2023-02-15T11:23:33.58+00:00

Hi Dave Gray, just checking if above answer helpful? If yes, kindly consider hitting Accept Answer button and hit yes button for the question was answer helpful? Please let me know if any further queries. Thank you.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

1 additional answer

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

Linked service structure

1 additional answer

Your answer