Bro you are really superb 🙏🙏.I struggled a lot to do this functionality 2 weeks back but u have done in very simple way and hatsoff to you .Most of APIs we use in projects are Authenticated .Could you please make video on calling API with authentication . 1stLevel Authentication Code 2nd level Access token .Also could you pls help us with how do you get C# code for these kind of tasks . I personally know many like me who are surviving in IT only because of your videos .May God bless you and give you back many more times .
Thanks so much for your kind words, I am planning to make more videos in future on API. Will see the api those needs authentication. Next video I am planning to make on Google geocoding api.
I can't thank you enough! Your awesome videos have helped me find solutions to every issue I've encountered in SSIS on your channel.thank you, you are a really superb.🙏
Sorry I missed to add your email snapshot actually I the snapshot I took from the youtube comment box. I will try to add one from you in next video on api.
I think one very important thing to note is that you’d want to use something like a prepared statement/parameterized statement for security purposes when interacting with a database instead of string concatenation in the insert statement. This helps avoid SQL Injection.
@@learnssis sure np. Thank you so much for this video. You are the man!! I am starting a new job working with SSIS soon and your videos have saved my life my friend. Thank you!
We can use VS 2012 for this. I have not used Web Service task much, although I have a video on how to call a web service task in SSIS, but not sure if we can use that in this particular case.
Hi Sir, It would be really greatful if you could help me on this I am having a variable in ssis package called response code And doing a post API call in script task in ssis I want to return API response status code in response code ssis variable. I am able to get response status code from API but unable to assign that to ssis variable because it is becoming unaccessible after api call This ssis variable is accessible before api call and unable to access after api call. Any reason please help me resolvethis issue I am actually getting element cannot be found in collection The same element is accessible everywhere before api post call but not after the post call Whats the reason for that How to overcome this
Yeah not sure about that. I had the similar situation once, so I imported the json into excel and then imported excel into sql server. This can work if you need to import the file only once or couple of times.
Looks like this is for Application Developer that wants to create SSIS package, but for Database Developer we don't normally write C# code, is that a way in SSIS to directly map the API output into database table? Using data transformation within SSIS, instead of using C# to do everything?
Thanks for the great video, I have a problem though with the code. "using Microsoft.SqlServer.Dts.Runtime" and "Dts.TaskResult = (int)ScriptResults.Success" ; give me an error indicating that a reference is missing to the managed data package. I can howevr not find how to six that problem. Do you know what I am missing?
Sorry I am really not sure what might be wrong here. I asked chatgpt the same question and it said to check few things, and somehow that makes sense. If you're encountering an error stating that a reference is missing to the managed data package while using Microsoft.SqlServer.Dts.Runtime, it's possible that the necessary assemblies or references are not properly added to your project. Here are some steps you can take to resolve this issue: Check References: Right-click on your project in the Solution Explorer. Select "Add" -> "Reference..." Navigate to the "Assemblies" or "Browse" tab and ensure that you have added the necessary assemblies for SQL Server Integration Services (SSIS). Look for Microsoft.SqlServer.ManagedDTS or similar. Use NuGet Package Manager: Open the NuGet Package Manager Console. Run the following command to install the SQL Server Integration Services NuGet package: Install-Package Microsoft.SqlServer.ManagedDTS Verify SQL Server Data Tools (SSDT) Installation: Ensure that you have SQL Server Data Tools (SSDT) installed, as it contains the necessary components for SSIS development. Target the Correct .NET Framework: Make sure that your project is targeting a .NET Framework version that is compatible with the SQL Server Integration Services version you are using. Check SQL Server Version Compatibility: Ensure that the version of Microsoft.SqlServer.ManagedDTS you are referencing is compatible with the version of SQL Server you are working with.
At the moment I don't have access to a rest api and also, I am kind of busy at the moment. If you can google the code to call rest api in C# then you can use the same code inside the script component. stackoverflow.com/questions/9620278/how-do-i-make-calls-to-a-rest-api-using-c
It depends from where you are reading the JSON data, if you are reading it from a script task then in the script task itself you can assign it to a variable. Add the variable to a ReadWriteVariableList and set the value as below Dts.Variables["SSISVariableName"].Value = "JSON data";
Currently I don't have an example for that one. I used Google and Bing api where they need a key to get authenticated but did not tried with username and password. Sorry for that.
Hi Mohamed Husain, there is no inbuild component in SSIS to connect to sharepoint site. You would need to write C# code for that or use a third party component. I have unfortunately not worked before with sharepoint thus not sure how to connect with it but you can find help on Google or at youtube or you can ask chatgpt as well to write the code to connect to sharepoint using C#.
Dear Aqil, First of all, thank you so much for replying. I have reached out to multiple people on TH-cam and posted my queries on Stack Overflow and even in the Microsoft community. You're the only one who responded to my message. I will definitely follow your advice and keep you updated on the status. Thanks again!🙂
@@mohamedhusain7807No problem. You can take a look at this url, here they are showing how to download files from sharepoint to local folder stackoverflow.com/questions/58034425/download-file-from-sharepoint-online-using-c-net
Thanks, Aqil. I tried C# code and found out that the corresponding SharePoint site does not allow outsiders to connect from external connections. We have raised a ticket for the same. Thank you.
Actually you would need to look for the code in C# and then use that code in Script task, although there is web service task in ssis but I don't find it much useful. So you would need to write code in C#, you can take help from chatgpt as well. Every api is different and the code to call every api will be different as well. I am not sure how your api will be called, normally the owner of the api provide some sample code like how you can call their api in .NET or JAVA or something.
Hi..bro...I have a doubt in Database connection In ssis connection manager I don't have ORACLE provider for OLEDB....I searched based on this issue but no use....please can u make a video...
We don't have ORACLE provider for OLE DB. If you want to connect to Oracle from SSIS then you would need to use the ODBC drivers, which then use the ODBC connection manager, ODBC source and ODBC destination to connect and fetch or write data to Oracle.
@@charancharan4429 You would need to install the ODBC drivers for Oracle from oracle website. And then you would need to create the DSN to connect to Oracle. And finally in the SSIS package in the ODBC connection manager you would use that DSN to connect to Oracle.
Thanks for another great tutorial! I love how you showed how to create the classes in ChatGPT!
Thank you so much Mike for your continuous support. Yeah we can take help from ChatGPT whenever required. Glad you found the video useful.
Bro you are really superb 🙏🙏.I struggled a lot to do this functionality 2 weeks back but u have done in very simple way and hatsoff to you .Most of APIs we use in projects are Authenticated .Could you please make video on calling API with authentication .
1stLevel Authentication Code
2nd level Access token .Also could you pls help us with how do you get C# code for these kind of tasks . I personally know many like me who are surviving in IT only because of your videos .May God bless you and give you back many more times .
Thanks so much for your kind words, I am planning to make more videos in future on API. Will see the api those needs authentication. Next video I am planning to make on Google geocoding api.
Hi Sai, could you please help me to create the package as the file is not showing which Aakil shown in this video
I can't thank you enough! Your awesome videos have helped me find solutions to every issue I've encountered in SSIS on your channel.thank you, you are a really superb.🙏
You are most welcome. Thanks for appreciating.
Finally I got to know how can I use chatgpt and use the code in ssis
Thank you for the video upload.
Thank you Abhilash, glad you found something useful in the video.
Thank you so much, mate. I sent you an email a few weeks ago to make a video on API data. Thanks again :)
You are most welcome. Yeah I got many requests on this topic to make video on.
Sorry I missed to add your email snapshot actually I the snapshot I took from the youtube comment box. I will try to add one from you in next video on api.
Superb, really outstanding, kindly make video on fetching data from external API.
Thanks for the video, but i have a senario on how to loop a parameter (lets say an id or page number ) in the Get request by this method
Can you take a look at this one ?
stackoverflow.com/questions/73712612/how-can-i-loop-through-api-response-in-c-sharp
I think one very important thing to note is that you’d want to use something like a prepared statement/parameterized statement for security purposes when interacting with a database instead of string concatenation in the insert statement. This helps avoid SQL Injection.
Yes that is a good point, totally agree with that.
@@learnssis sure np. Thank you so much for this video. You are the man!!
I am starting a new job working with SSIS soon and your videos have saved my life my friend. Thank you!
Excellent explanation as usual, thanks for this video
Thank you Sir.
Is it possible to do this without using C# or an external tool?
Hi Aakil, thanks for the video but i am unable to see the jason file raw data, it showing an error please let mw know how to fix this
thank you for sharing , very useful . looking forward to collaborate with you
Can you please let me know how to fetch data from the second URL returned by the API
Thanks for the video, Its super helpful! When you get a chance can you Post Similar video using authentication API. Thanks a lot!
Okay, I will see if I found any public api with authentication.
Great video. Can we achieve this vs 2012 and can't we use web services task in ssis.?
We can use VS 2012 for this. I have not used Web Service task much, although I have a video on how to call a web service task in SSIS, but not sure if we can use that in this particular case.
Well done keep uploading more videos sir
Thank you Harsh vardhan for appreciating.
Hi Sir,
It would be really greatful if you could help me on this
I am having a variable in ssis package called response code And doing a post API call in script task in ssis I want to return API response status code in response code ssis variable. I am able to get response status code from API but unable to assign that to ssis variable because it is becoming unaccessible after api call This ssis variable is accessible before api call and unable to access after api call. Any reason please help me resolvethis issue
I am actually getting element cannot be found in collection
The same element is accessible everywhere before api post call but not after the post call
Whats the reason for that
How to overcome this
Thank you very much for the helpful video, if you can help me I got error when run the package says that “The binary code for the script is not found”
This is a very generic error message. From the script task, Are you able to build the solution ?
How about if the json data have an Array? Thank in advance 😊
Yeah not sure about that. I had the similar situation once, so I imported the json into excel and then imported excel into sql server. This can work if you need to import the file only once or couple of times.
Looks like this is for Application Developer that wants to create SSIS package, but for Database Developer we don't normally write C# code, is that a way in SSIS to directly map the API output into database table? Using data transformation within SSIS, instead of using C# to do everything?
Unfortunately there is no inbuilt component in SSIS for API.
Thanks for the great video, I have a problem though with the code. "using Microsoft.SqlServer.Dts.Runtime" and "Dts.TaskResult = (int)ScriptResults.Success" ; give me an error indicating that a reference is missing to the managed data package. I can howevr not find how to six that problem. Do you know what I am missing?
Sorry I am really not sure what might be wrong here. I asked chatgpt the same question and it said to check few things, and somehow that makes sense.
If you're encountering an error stating that a reference is missing to the managed data package while using Microsoft.SqlServer.Dts.Runtime, it's possible that the necessary assemblies or references are not properly added to your project. Here are some steps you can take to resolve this issue:
Check References:
Right-click on your project in the Solution Explorer.
Select "Add" -> "Reference..."
Navigate to the "Assemblies" or "Browse" tab and ensure that you have added the necessary assemblies for SQL Server Integration Services (SSIS). Look for Microsoft.SqlServer.ManagedDTS or similar.
Use NuGet Package Manager:
Open the NuGet Package Manager Console.
Run the following command to install the SQL Server Integration Services NuGet package:
Install-Package Microsoft.SqlServer.ManagedDTS
Verify SQL Server Data Tools (SSDT) Installation:
Ensure that you have SQL Server Data Tools (SSDT) installed, as it contains the necessary components for SSIS development.
Target the Correct .NET Framework:
Make sure that your project is targeting a .NET Framework version that is compatible with the SQL Server Integration Services version you are using.
Check SQL Server Version Compatibility:
Ensure that the version of Microsoft.SqlServer.ManagedDTS you are referencing is compatible with the version of SQL Server you are working with.
Hi I need help on a task. Pls let me know how we can communicate
Hi
Can you please make a video, how to call rest api if it is in xml format
At the moment I don't have access to a rest api and also, I am kind of busy at the moment. If you can google the code to call rest api in C# then you can use the same code inside the script component.
stackoverflow.com/questions/9620278/how-do-i-make-calls-to-a-rest-api-using-c
@@learnssis Thank you! This helped me
Awesome! How can i store the json data into a variable so that it can be used in the data flow task
It depends from where you are reading the JSON data, if you are reading it from a script task then in the script task itself you can assign it to a variable. Add the variable to a ReadWriteVariableList and set the value as below
Dts.Variables["SSISVariableName"].Value = "JSON data";
@@learnssis Thank you
Awesome! Can you advise a good c# tutorial?
Frankly speaking I don't see any C# tutorial videos, thus I am really not sure which C# tutorial videos are good.
Sir ssis instalation lo lssu undi konchem solve cheyraa
Going forward plz make video 9n nested replace and nested charindex
Sure will do Aanchal.
Thanks such type of video, please make on xml api
Sure will try to make a video on xml api as well.
Can’t you just copy a sample json and paste as json class on visual studio just wondering
Sorry I don't know if that is possible.
Awesome! How can i use authenticated Api, like username and password
Currently I don't have an example for that one. I used Google and Bing api where they need a key to get authenticated but did not tried with username and password. Sorry for that.
Dear Aqil,
Could you please say how to access Sharepoint site from my ssis package?
Hi Mohamed Husain, there is no inbuild component in SSIS to connect to sharepoint site. You would need to write C# code for that or use a third party component. I have unfortunately not worked before with sharepoint thus not sure how to connect with it but you can find help on Google or at youtube or you can ask chatgpt as well to write the code to connect to sharepoint using C#.
Dear Aqil,
First of all, thank you so much for replying. I have reached out to multiple people on TH-cam and posted my queries on Stack Overflow and even in the Microsoft community. You're the only one who responded to my message.
I will definitely follow your advice and keep you updated on the status.
Thanks again!🙂
@@mohamedhusain7807No problem. You can take a look at this url, here they are showing how to download files from sharepoint to local folder
stackoverflow.com/questions/58034425/download-file-from-sharepoint-online-using-c-net
Thanks, Aqil. I tried C# code and found out that the corresponding SharePoint site does not allow outsiders to connect from external connections.
We have raised a ticket for the same. Thank you.
@@mohamedhusain7807 Sounds good.
How send request to rest API and get response back in Ssis. I mean I we send id column as request and get name column as response from API.
Actually you would need to look for the code in C# and then use that code in Script task, although there is web service task in ssis but I don't find it much useful. So you would need to write code in C#, you can take help from chatgpt as well. Every api is different and the code to call every api will be different as well. I am not sure how your api will be called, normally the owner of the api provide some sample code like how you can call their api in .NET or JAVA or something.
Hi..bro...I have a doubt in Database connection
In ssis connection manager I don't have ORACLE provider for OLEDB....I searched based on this issue but no use....please can u make a video...
We don't have ORACLE provider for OLE DB. If you want to connect to Oracle from SSIS then you would need to use the ODBC drivers, which then use the ODBC connection manager, ODBC source and ODBC destination to connect and fetch or write data to Oracle.
@@learnssis so many drivers are in ODBC..
but which one can support
@@charancharan4429 You would need to install the ODBC drivers for Oracle from oracle website. And then you would need to create the DSN to connect to Oracle. And finally in the SSIS package in the ODBC connection manager you would use that DSN to connect to Oracle.
@@learnssis OK.. bro...I will try....
Great thanks!
Thank you Alex.
How to make call with API Key?
awesome sir
Thank you Naveen Raja.