Feedback on using Prefect when migrating from SSIS ETL

[anna_geller]

View in #prefect-server on Slack

@Tony_Liberato: Good morning, Prefectures! We are sailing. I have successfully deployed two very simple Workflows about a month ago and everything is working beautifully. I’m a DBA for a small non-profit and we handle a ton of data. We decided to implement prefect so I can automate many of the data importing and ETL tasks I perform manually.
I wanted to share a couple of things Prefect taught me.
1:Register the flow on the agent its intended to run on. for some reason this resolved all the import statements correctly.
2:Translating your workflow into prefect code helps to refactor the steps of the flow. I would do these in SSIS, and SSIS made the “theory” of the flow easy, but it didn’t truly represent the logic of the flow. In doing so, I developed a much more concise workflow, which had a few more tasks. Let this happen: its a good thing.
3: Python is amazing. And since Prefect doesn’t get in the way of that, prefect is amazing. It quite literally can do anything.
4: Docker flows: This can get messy. I found about 52 containers that were left over from all my flows being run. This is a really intelligent solution regardless, it just leave a trail. I’m sure there is a setting to automatically remove these that I just haven’t found. Don’t be alarmed if you have clutter in your docker instance. It well worth it.
5: When deciding to implement a flow in Prefect, you are forced to design the whole thing. No more “Oh, we’ll deal with that when we get there…” This is a steep hill to climb, but you once you do, its a never-ending coast down the other side. This forces questions to the beginning of the process and helps get solid answers for them right away. Its possible that a workflow may not be a good candidate for Prefect, or maybe parts of the workflows are candidates. You actually save a lot of time designing end to end.
Thanks for reading.

@Anna_Geller: We usually call Prefect users “Prefectionists”!

Thank you so much for sharing your experience!

#1 - nice that your imports work as intended - if you face any issues with packaging your code dependencies, let us know, we have some resources that can help with that

#2 - I definitely empathize with you since I also used SSIS, Pentaho Data Integrations, and similar drag-and-drop ETL madness and I can attest that it’s not a good experience, creates a ton of tech debt, is hard to scale, and makes it really hard to implement best engineering practices

#3 That’s true!

#4 - You could use a ShellTask in a flow running e.g. every day/week and running the command

docker container prune

This allows you to remove stopped containers and reclaim the disk space. You may even do:

docker system prune

#5 - I agree, Prefect allows you to build workflows in a very modular and flexible way.

Again, thanks for sharing!

[ennieY]

Hi @anna_geller , could you share how you migrated SSIS ETL using Prefect. Is there any doc or references you can share with me? I’m new to SSIS and Prefect. My task is to migrate SSIS ETL using Prefect. I’d appreciate some starting point. Thanks very much in advance.

[anna_geller]

Given that SSIS is essentially a drag-and-drop interface, you would need to evaluate your workflow and rebuild it in Python. Once you have it in Python, making it to a Prefect flow is as easy as adding a single @flow decorator.

So the part to get it into Python logic is the most challenging, and I don’t have any resources for that, unfortunately. If you need paid support to help you with this migration, you can reach out to our consultants and engineers at cs@prefect.io.

[ennieY]

@anna_geller

it into Python logic is the most challenging, a

Thank you.