Search code examples
pythonmachine-learningssisetlexecuteprocesstask

Predicting data with Python script in an SSIS package

I'm aware of Microsoft's inclusion of Python in their Machine Learning Services for SQL server, however this is only available for SQL Server 2017 and up, which is a requirement my servers do not currently meet.

With that being the case, I wanted to deploy my generate-predictions-with-trained-model pipeline entirely within SSIS, I.E:

  1. Grab data from my DB
  2. Pass it to a Python Script Data Flow Task which imports the trained model, generates the predictions and passes them on to the next Data Flow Task
  3. Write the predictions to the DB

Is there a way to do that entirely within SSIS, or will it be necessary to just use Execute Process Task to kick off the Python script and have that stored separately on the server somewhere?

Cheers

Solution

  • The only way to do that is to save the the script into a file and execute it using an Execute Process Task .

    You cannot write python scripts within SSIS packages. Based on the official documentation :

    The Microsoft Visual Studio Tools for Applications (VSTA) development environment used by the Script task supports the Visual Basic 2005 and Visual C# programming languages.