locked
Can I use WF as a replacement to SSIS RRS feed

  • Question

  • HI, My company has developed many SSIS packages for data transformation for a client. Now client wants to move to cloud and has decided to shut down his data center(so we don't have On-Premise option). As client moves to cloud, we also need to move packages to the cloud (Azure). Since azure does not support SSIS, we now have to change these SSIS packages to worker roles. I have no idea about WF.Someone told me that we can use WF  as a replacement of SSIS packages. My question is , Is it possible? and if so, can anyone please provide link to samples where WF is created for ETL purpose.

    thanx


    • Edited by Zafar.Yousafi Tuesday, February 19, 2013 1:08 PM title changed
    Tuesday, February 19, 2013 12:09 PM

Answers

  • Workflow Foundation is a framework for creating and hosting workflows. WF allows to combine different Workflow Activities into a sequential flow (like in SSIS), but  there are no built-in ETL activities. Therefore you will need to create a set of custom Workflow Activities that perform these tasks.

    As a starting point you can have a look at WF ADO.NET Activity Pack. There are several ExecuteSqlQuery activities very similar to Execute SQL Task. You could use them in conjunction with Control Flow  activities to perform some simple transformation tasks.

    • Proposed as answer by Horizon_Net Wednesday, February 20, 2013 5:09 PM
    • Marked as answer by Zafar.Yousafi Friday, February 22, 2013 5:30 AM
    Wednesday, February 20, 2013 9:27 AM
  • Hi,

    in my opinion it is possible with a lot of work. You have to implement the actions SSIS normally does for you yourself. Like you already mentioned it is not possible to use SSIS with Windows Azure SQL Database, but an option you have is to set up a SQL Server with the Windows Azure Virtual Machines (currently available as a preview). On this SQL Server you can run your SSIS packages as usual.


    Best Regards. When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer. This helps us build a healthy and positive community.

    @Horizon_Net | Blog

    • Marked as answer by Zafar.Yousafi Friday, February 22, 2013 5:30 AM
    Tuesday, February 19, 2013 10:45 PM

All replies

  • Hi,

    in my opinion it is possible with a lot of work. You have to implement the actions SSIS normally does for you yourself. Like you already mentioned it is not possible to use SSIS with Windows Azure SQL Database, but an option you have is to set up a SQL Server with the Windows Azure Virtual Machines (currently available as a preview). On this SQL Server you can run your SSIS packages as usual.


    Best Regards. When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer. This helps us build a healthy and positive community.

    @Horizon_Net | Blog

    • Marked as answer by Zafar.Yousafi Friday, February 22, 2013 5:30 AM
    Tuesday, February 19, 2013 10:45 PM
  • Thanx Horizon_Net for your time. I don't know my client will agree on VM or not. But in any case, Is it good to use WF for ETL tasks? Can I have a tutorial which points me how to do this? I have no idea about WF.

    Wednesday, February 20, 2013 5:32 AM
  • Workflow Foundation is a framework for creating and hosting workflows. WF allows to combine different Workflow Activities into a sequential flow (like in SSIS), but  there are no built-in ETL activities. Therefore you will need to create a set of custom Workflow Activities that perform these tasks.

    As a starting point you can have a look at WF ADO.NET Activity Pack. There are several ExecuteSqlQuery activities very similar to Execute SQL Task. You could use them in conjunction with Control Flow  activities to perform some simple transformation tasks.

    • Proposed as answer by Horizon_Net Wednesday, February 20, 2013 5:09 PM
    • Marked as answer by Zafar.Yousafi Friday, February 22, 2013 5:30 AM
    Wednesday, February 20, 2013 9:27 AM