Generate a .tab file by creating a pipeline in Azure Data factory and upload to FTP

I need to create a pipeline where I have to exceute some SQL query, the output of which I have to write to a .tab file(template already have) . After creating this.tab file, I need to upload this file to FTP.

As I am new to this anyone please help me , how I can implement this scenario.

EDIT [not in original scope]

In my .tab file, I need to have first 4 line some static text. 5 th row should be kept empty.The sql data should be added from line 6 onwards. Any leads for this?

If I get your question correctly, you are trying to move a .tab file from Azure ADLS to some SFTP server.
In order to achieve this,

1. You can create a linked service which can connect to your SFTP server and then create a dataset.
2. Create a copy activity and use a dataset which points to the .tab file as source and the above SFTP dataset as Sink
   This should do the job of copying files from ADLS/Blob to SFTP folders

Let's break this down into its component parts:

Execute a SQL query and save results as TAB file:

• Create a SQL Dataset with no table:

• In your pipeline, add a Copy activity. Under the Source tab, to this SQL dataset. For "Use Query", select the "Query" option and insert your SELECT statement:

• Create another Dataset for the TAB file. I'm using ADLS in this example, but the concept is the same for other storage types:

This example is built to be as generic as possible, so it uses Dataset parameters to configure the options at runtime:

NOTE:
Feel free to hard code any of these values if you prefer. It is easier to select the Tab delimiter in the Dataset: supplying it in this manner requires you to copy and paste a Tab (like from Notepad) which you cannot see in the UI.

The most important step is to NOT define a Schema:

• In the Copy activity, under the Sink tab, select this Dataset and supply the appropriate values:

Now when this activity runs, it will copy the results of the query to the text file in storage.

Upload TAB file to FTP site:

Use Copy activity for this as well. For pure data transfer, you should use Binary Datasets.

• Create a Binary Dataset for your storage account:

Again, this Dataset uses parameters to make it reusable across a wide array of situations.

• Create a Binary Dataset for your FTP account:

This example also uses parameters.

• Add a Copy activity in your pipeline using the storage Dataset as your Source and the FTP Dataset as your Sink. Fill in the appropriate parameter values in each and you should have a finished pipeline something like this:

NOTES:

• As you can tell, parameterized Datasets are very powerful. Here is an article I wrote that goes into more detail.

• If you don't need to retain the TAB file, you can try to use SQL as the source and FTP as the Sink, but the FTP Dataset could no longer be Binary which may hinder performance. I think the approach outlined here is better. If you want, you can add a Delete activity after the FTP upload to remove the TAB file from storage.

• If you want to ZIP the file in flight to the FTP, select the ZipDeflate compression type in the FTP Dataset. This will automatically zip the file (but you will need to add the ".zip" extension to the filename yourself).