5 Replies Latest reply on Jul 25, 2018 8:37 PM by Andy Pullar

    Using SSIS to extract Marketo data into SQL database

    Andy Pullar

      Hi all,

      I want to use Microsoft SSIS 2015 to extract Marketo lead data and put it into a SQL database.  Has anyone successfully done this?  There is a post on this Discussion forum from August 2017 but it doesn't seem like it was successful.  There is a Web Service task in SSIS that looked promising, I am not sure on what URL to put in the Server URL field for the HTTP Connection Manager Editor setting.

      I want to automate the SSIS package to run overnight, so not interested in the Marketo Bulk Export option.

      Has anyone been able to do this data extract via SSIS?

       

        • Re: Using SSIS to extract Marketo data into SQL database
          Sanford Whiteman

          It's not possible to use a generic HTTP request for the REST API. You need an SSIS component that can manage the pagination, expiring access tokens, et al.

           

          Frankly you will be much better served by writing a workflow that uses the Bulk Export API (which is fully automatable, you just have to build the appropriate logic).

            • Re: Using SSIS to extract Marketo data into SQL database
              Andy Pullar

              Thanks for the quick reply Sanford.  Assuming I'm a complete novice with API's and workflows, can you point me in the direction of where to start please? How to write one?  What to write it in?  etc.

              Cheers,

              Andy

                • Re: Using SSIS to extract Marketo data into SQL database
                  Sanford Whiteman

                  The best language is in the one you know, the saying goes. I'm a Java or JavaScript (Node.JS) person these days, but you could use anything.  Go is good to learn in 2018. PHP isn't really good for this kind of app, though, IMO.

                   

                  But is this is your first development experience ever? If so, this isn't a good place to start. You have to understand a few advanced technologies (HTTP request/response, job queues/polling, and long-lived services) to get it right. At any rate, why don't you start by running through the Bulk Export steps with Postman?  That doesn't take any code at all, and if you can't make it make sense manually in Postman you couldn't write the code to automate it.

                   

                  You can also try the shrink-wrapped CData Marketo SSIS component, but don't expect too much. I tested it in the past and found it unacceptable for all but the tiniest databases (< 100000 leads). I also had to hack around some of its mistakes w/the Marketo API, but maybe they've fixed it since then. There's probably no stable, scaleable, and shrink-wrapped solution for you at this point, unfortunately: all the good work in this area is custom work.