Authoring: Failing to select datatypes for parameters or additional columns And variables not readable within if condition activity

Question

Hi,

Cannot select datatypes anymore when creating additional columns or when adding stored procedure parameters in activities, something is wrong with the designer(?).

Also in pipelines with If condition activity, the stored procedure cannot read from a variable anymore, parameterizing it when Sinking in Copy Activity. This happens while debugging the pipeline not sure what will happen if published.

Could you please advise?

Cheers,
Kostas

Answer 1

Hi @BitAday ,

Welcome to Microsoft Q&A Platform.

By default, Azure data factory considers string datatype when inserting additional columns and hence there is no provision to choose the datatype and this is as per current functionality. I was reproducing adding parameter for stored procedure in copy activity inside If condition activity and it can be read from a variable as below. And stored procedure parameter datatype is taken by Azure Data Factory based on the parameter type defined when creating stored procedure. Hope this helps! Please suggest if the scenario you are looking for is a different one.

Answer 2

Hello @BitAday ,

Let me try to answer your question here and the end we will need some more info , request you to share the same

Ask #1 :Cannot select datatypes anymore when creating additional columns or when adding stored procedure parameters in activities, something is wrong with the designer(?).

[Himanshu] As Haritha mentioned before , additional columns which we add are created as data type string and so we do not have the option to change that in ADF .

When we use the store proc , we get the data types from the SQL objects and it shows the data types which is defined on the SP side . We cannot update on the ADF UI . As a test if you change the data type of the dummy proc ( on SQL side ) . on the ADF UI if you refresh and import paramters you will see the data type updates .

Ask #2 : Also in pipelines with If condition activity, the stored procedure cannot read from a variable anymore, parameterizing it when Sinking in Copy Activity. This happens while debugging the pipeline not sure what will happen if published.
Ask #3:Please use a variable for your stored procedure and then try to execute your pipeline, does it work?

[Himanshu] For both #2 & #3 ,I think Haritha was able to share the GIF with something which was working . Are you still facing the issue . Can you share the error please ?

Ask #4:Also what if I want to change the "id" parameter on your GIF example from Int32 to Int64, would you be able to see the dropdown to change it?

[Himanshu]:please check my answer to Ask#1 , second part.

Ask 5#
I know how to get the stored procedure parameter's datatype when clicking on the "Import parameter" button.
Not sure since when this is the only normal way to develop my parameterization?
[Himanshu] : You can read more here : https://learn.microsoft.com/en-us/azure/data-factory/connector-sql-server#sql-server-as-a-sink

Ask 6#Not sure why the "New" button adding stored procedure parameters is still there then. Does the Azure Data Factory team plans to remove it?
[Himanshu] : The feature has been included recently only and it does make sense for few users . eg . Lets say someone is trying to copy data from a blob to SQL .
They want to capture the time when the ingestion happened . We can use new option and create a new parameter and pass the value .

Need some more info :

1. You did mentioned "aforementioned error" , can you please share the error with us .
2. Can you also share as to what is the source in your implementation ?

Please send an email to AzCommunity@microsoft.com with the below details. We will try to get more info on the issue .

Subscription ID:  
Subject : "Himanshu "

Please do let us know if you have any further queries .

Thanks
Himanshu

Answer 3

This has also been reinstated, the datatype listbox/ picker for parameters is now rendered properly in front of other objects/layers: