In the event of technological advancement, some of the new jobs in the Data Science industry are Full-stack Data scientist, an MLops engineer where the main focus is to deploy or publish your Data science result for actual use.
The below article will describe one of the uses of a Machine learning model that is used by the industry to comply with their in-house system and business requirements. I am sure, you can find various articles on preparing a machine learning model which includes Data gathering, Data preprocessing(The most vital and time taking process), Choosing the best model through model selection, and finally getting the prediction we want. But, there are a lot fewer articles that describe How the prediction model is used in the real-life industry. So I will start from the end of the modeling which is Pickling the model with the following code
Now, this pickled code can be used anywhere, and I tend to use this to deploy our ML model as an API using the below flask code.
Now, as our model is deployed locally as an API, we can shoot or POST our data to put the ML model to work and get our predictions. You can use Postman to do it.
Now, as the API is deployed locally, your company cannot use this until it is deployed according to their standards. Here I will use the package deployment model in SSIS to receive data to be posted from a package and get or predictions to a SQL server database.
Your SSIS control flow should look like below:
To consume Web API into the SSIS package, first, create the SSIS application. You should have SSDT tool installed into your system. I have used SSDT 2015 in my sample application.
Perform the following steps,
1. Create package (package.dtsx) inside the SSIS project.
2. Go to the solution explorer and double click on project.params. It will provide you the screen to add project-level parameters. (refer below screen)
3. Drag the script task from the SSIS toolbox on the package design screen. Double click on the script task. It will open the window where you can configure the ReadOnlyVariables. Set project parameters.
4. Click on Edit Script.
5. It will open the VSTA project where you can write the C# custom code.
6. Go to the Solution Explorer.
7. Right click on Reference
8. Click on add the reference and add the Net.Http assembly.
9. Add the System.Net.Http.Formatting from the NuGet packages or from the Package Manage Console.
If you are installing through PMC then write below code.
Install –Package Microsoft.AspNet.WebApi.Client
It will add the required libraries the project.
10. After adding the library references, you can save the project and come out to verify whether these libraries are referenced properly or not.
What happens here, when you go out of the project, is it lost the libraries references. You can see in the below screen where Newtonsoft.json and System.Net.Http.Formatting DLL are missing the references.
To avoid this error, we need to register these DLL into GAC, so VSTA project can take reference directly.
11. Open Visual Studio command prompt and register the following DLL.
12. You can use PowerShell script also to register the DLL’s into the GAC.
After adding into the GAC, you can again open SSIS package script task and check whether the references are restored or not.
13. Follow the below code. Here, I am passing the transactionID and amount to the ProcessData()
When you debug the SSIS package, you get the response from Web API (refer below screen).
Now as, our script component is ready, now we have to link out the SQL server table with our Execute SQL task, and create a query that runs every time, whenever we fire data from a package. Here I can explain it by using the famous Worldwide Imported database and selecting 1, which means our connection is successful.
Let’s start by adding an Execute SQL Task to the control flow. You can either drag it from the SSIS toolbox to the control flow, or you can double click it
You can see there’s a red error icon on the task. That’s because we haven’t defined a database connection yet.
Double click the task to open it. In the editor, open the connection dropdown and click on <New Connection…>.
If you have already created connection managers, you can pick one from the list in the next window. However, you can also create a new one by clicking the New… button at the bottom.
This will open a connection manager editor. You need to enter the server name and select a database from the dropdown list. You can also optionally specify a username and password if you don’t want to use Windows Authentication.
Click OK two times to go back to the Execute SQL Task editor. You can either directly type a SQL statement in the SQLStatement property or you can click on the ellipsis to open up the editor. This editor is basically a notepad editor and it has no additional functionality. You are most likely better off writing SQL statements in Management Studio and copy-pasting them into the editor. Let’s enter a very basic statement: SELECT 1.
Click OK to close the editor.
Executing SSIS Packages
By simply connecting arrows between script task and execute SQL task, we can set up a connection where, after script task execution, the result is saved to a SQL server table with the help of Execute SQL task
We can now run the package to test our Execute SQL Task. You can click on the green arrow or just hit F5. This will start the debugger which will run the package.
When the task has finished, you will see a green icon in the corner of the task. You can click on the stop icon in the taskbar to stop the debugger or you can click on the sentence below the connection manager window
Then above project took around 3 months to complete so i know, everything will not come as expected for you, so if you are facing any errors or for more information, please find me on LinkedIn- https://www.linkedin.com/in/aroradeepak96/