KNIME to replace SSIS?

[roblu001]

Hello All,

I’m looking to see if I can use KNIME to replace SSIS. I want to do more with KNIME but I’m hoping to use this as a use case to get the foot in the door. Can someone let me know where I should be looking for support?

At this point I’ve got a workflow that outputs to CSV the table that I want, however I’d like to get that table into SQL. What I’m looking is performing validation steps on the KNIME table, clearing the existing table in the database prior to insert and/or incremental insert (if value not in DB insert, otherwise skip row).

I think that the DB writer node is what I’ll need, but what other nodes should I be adding prior to this?

Thank you,

[izaychik63]

Welcome to the forum.
Watch some videos

Accessing Databases

Manipulating Data on Databases

on KNIME and DBs.

[mlauber71]

@roblu001 another source and overview might be the:

https://docs.knime.com/latest/db_extension_guide/index.html?u=mlauber71#query_generation

And maybe these example workflows

Databases - Advanced Usage – knime

Example how to use H2 database to create table with upload and from scratch –...

Here are some more examples using the standalone H2 database to demonstrate more database functions:

Workflows: H2, Sql – KNIME Community Hub

[ipazin]

Hello @roblu001,

SSIS is data integration tool while KNIME, although extensively used for ETL, is a data science tool.

Check this topic (and links in it) for more:

Can Knime replace talend or informatica

I have just seen talend 1 time, informatica I only know by name. Are there any experts here who can share their knowledge and tell me wgether KNIME could replace them even if it is not KNIMEs primary purpose? If so why / why not? Thanks

Besides what is mentioned in above topic(s) there are two important things to note:

• KNIME Database extension received a lot of improvements lately. First it was rewritten and from then continuously improved with new functionality. It’s not impossible to see more “data integration” features within KNIME in future
• With KNIME Analytics Platform, as opposed to SSIS, there is no built-in scheduling option. For more on KNIME workflow scheduling and automation see here

Welcome to KNIME Community and hope this helps!

Br,
Ivan

[e2dboden]

Hi there,

I am using SSIS and KNIME simultaneously at work. I think KNIME has a couple advantages over SSIS and is quite capable as an ETL Tool (and more).

I am not sure how you want to do the insert exactly, but I can tell you how to clear the table prior to the insert. Use the SQL Executor Node prior to the insert and use the TRUNCATE TABLE statement.

Regarding the sheduling: You can use the Execute Process Task in SSIS and run a workflow with it. The SSIS package can then be sheduled and executed with the SQL Server Agent. In this scenario I recommend to work with try and catch in KNIME and a timeout in the the Execute Process Task.

Another option would be the nodepit runner (a sheduler for KNIME workflows) or to wait a little bit, I heard KNIME is working on something as well.

[ipazin]

Hello @e2dboden,

@e2dboden

Regarding the sheduling: You can use the Execute Process Task in SSIS and run a workflow with it. The SSIS package can then be sheduled and executed with the SQL Server Agent. In this scenario I recommend to work with try and catch in KNIME and a timeout in the the Execute Process Task.

My boss had this same idea a day ago and I told her that although it should work it feels somehow wrong to schedule KNIME workflow with SSIS

Br,
Ivan

[Ergonomist]

Another option would be the nodepit runner (a sheduler for KNIME workflows) or to wait a little bit, I heard KNIME is working on something as well.

I’m pretty sure what KNIME has is KNIME Server, but just for scheduling you could also go with a cron job or a Windows scheduler record to launch the KNIME workflow in question in batch execution mode.

Cheers,
E