-3

In the company where I work we receive daily TXT files exported from SAP via batch jobs. Until now I’ve been transforming and loading some files into SQL Server manually using Python scripts, but I’d like to fully automate the process.

I’m considering two options:

  1. Automating the existing Python scripts using Task Scheduler.

  2. Rebuilding the ETL process using SSIS (SQL Server Integration Services) in Visual Studio

Additional context:

The team currently maintains many Access databases with VBA/macros using the TXT files.

We want to migrate everything possible to SQL Server

Which solution would be more reliable and maintainable long-term?

Improve this question
New contributor
Ni04 is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
6
  • 2
    Question is likely to attract opinion. MY opinion is use the available sql server tools ie ssis Commented Nov 18 at 9:34
  • But my opinion (used to be a SQL Server MVP) is to use Dagster to orchestrate the scripts, and libraries like DLT to to improve loading and transformations. Microsoft effectively abandoned the "extra" services, SSIS, SSRS and SSAS. It already removed SSRS from SQL Server 2025. SSIS is still there but hasn't received any meaningful updates in ages. All effort goes to migrating people to Fabric, an Azure service Commented Nov 18 at 10:02
  • So this is doubly opinion-based. Do you want "free" as in beer, or subscription based? Even the open-source tools have cloud-based versions with nice UIs, but at least the code and libraries are still free. Commented Nov 18 at 10:05
  • You can use Sql Server Agent to automate your python scripts. No need to use task scheduler if you have a really good scheduling software built-into sql server itself Commented Nov 18 at 10:09
  • 1
    maintains many Access databases with VBA/macros using the TXT files. why? That sounds like the worst possible option. The "why" matters - were you trying to allow people to analyze the data ? In that case Power BI, the cloud service, may be a far better option than using SQL Server. In fact, PBI is the "replacement" for Reporting Services, and has SAP connectors. You can publish SAP data as PBI datasets to the cloud and allow people to create and share any dashboards they want. You do pay something like 20/user/month. Commented Nov 18 at 10:09
Related questions
4342
How do I UPDATE from a SELECT in SQL Server?
3327
How to add a column with a default value to an existing table in SQL Server?
2450
How to concatenate text from multiple rows into a single text string in SQL Server
Load 7 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.