1

I need to copy blob storage csv file data to sql server table. But I also required some additional column in destination while using copy activity in azure data factory pipeline.

I refer this link 'https://blog.pragmaticworks.com/using-stored-procedure-in-azure-data-factory'.

I created table type and insert data stored procedure. But when I'm trying to use that in copy activity 'sink', it gives me 2 textboxex in sink dataset table parameter. enter image description here As you can see in above image, we need to put parameter name in 'Table' text box. But now we have 2 textbox rather having only one.

Are there some update in azure data factory pipeline published which show this additional thing and not the way it was previously.

Stored procedure is as below.

ALTER PROCEDURE [dbo].[Insert_SFl_Data]
(
@Passing [dbo].[STG_SFl] READONLY,
@SourceFileName NVARCHAR(500)
)
AS
BEGIN
INSERT INTO [dbo].[SFl]
           ([DateExtracted]
           ,[Country]
           ,[SourceFileName])
SELECT *, @SourceFileName FROM @Passing
END
Manish Jain
  • 217
  • 1
  • 4
  • 16
  • You should probably post your proc code and a screenshot of these textboxes – Nick.Mc Aug 13 '19 at 10:21
  • @Nick.McDermaid Updated in the original question. – Manish Jain Aug 13 '19 at 10:41
  • Try dbo in the first text box and Insert_SFL in the second – Nick.Mc Aug 13 '19 at 10:56
  • @Nick.McDermaid In that case it throw me eroor 'Sink Stored Procedure table name is required'. – Manish Jain Aug 13 '19 at 11:57
  • What is your additional column? Is it a constant or a calculation? It might be easier to use ADF data flows or an UPDATE statement afterwards. – Nick.Mc Aug 13 '19 at 13:10
  • @Nick.McDermaid Thanks for the suggestion(really appreciated), but actually we are using service principal for external resource connection(ex. SQL Server db), and azure doesn't allow data flow with a service principal. An additional column is source file name from which data is coming. – Manish Jain Aug 13 '19 at 15:41
  • Depending on how your staging table works, you could leave the filename column NULL during import, and run a single UPDATE afterwards to populate it – Nick.Mc Aug 15 '19 at 04:51
  • @Nick.McDermaid Yeah, I could have done that, but let's take an example where pipeline run in parallel If I'm not adding filename column in the same activity and doing it later could have another file data also there, which malfunction the overall process. So the best option is adding data to that table via stored procedure way. Can you please guide me on how to add data from source to sink using a stored procedure. – Manish Jain Aug 16 '19 at 04:25
  • Yes, it depends on how your staging works. By itself it won't work concurrently. I Right now I can't guide you - I would basically be going in and troubleshooting it myself – Nick.Mc Aug 16 '19 at 04:59
  • @Nick.McDermaid Thanks for the conversation, and today morning 'Sink Stored Procedure table name is required' is gone. New update on azure data factory pipeline might resolve that bug. Now It worked in the old way with some modifications. – Manish Jain Aug 16 '19 at 07:18
  • I’m pleased your problem is solved. It’s certainly an extra element to consider when troubleshooting: did the vendor apply a patch that raised this bug!! – Nick.Mc Aug 16 '19 at 08:16
  • 1
    Yes, actually bug was from vendor end and not from the mechanism I used because I deployed lots of pipelines that way earlier. – Manish Jain Aug 17 '19 at 11:58

0 Answers0