Customer Engagement & Dynamics CRM Forum

Expand all | Collapse all

Automatic Export of Marketing List to Excel to FTP site

  • 1.  Automatic Export of Marketing List to Excel to FTP site

    TOP CONTRIBUTOR
    Posted Jul 31, 2019 05:25 PM
    Edited by Tim Dailey Jul 31, 2019 05:25 PM
    We would like to automate nightly export from a specific marketing list to Excel, and to have that file added to an FTP site for our print vendor. We can't figure out how to automate the exporting part. Any ideas?

    We are on 2015 On-Premise and can't use Flow yet.

    Thank you in advance!


    ------------------------------
    Tim Dailey
    Sr Marketing CRM Administrator
    Starkey Hearing Technologies
    The CRM Dude (freelance consultant)
    Eden Prairie MN
    ------------------------------
    Conference-CRMUG_200x200


  • 2.  RE: Automatic Export of Marketing List to Excel to FTP site

    SILVER CONTRIBUTOR
    Posted Aug 01, 2019 03:46 AM
    Hi Tim,

    I think you could do it by creating an SSRS report, scheduled to run.  But the part to put it on an FTP site, I think would have to be scripted.  I can't help with the details of "how to" unfortunately.

    Stewart

    ------------------------------
    Stewart Tranter
    Business Systems Manager
    TCL Group
    Derbyshire, UK
    ------------------------------

    Conference-CRMUG_200x200


  • 3.  RE: Automatic Export of Marketing List to Excel to FTP site

    TOP CONTRIBUTOR
    Posted Aug 01, 2019 05:37 AM
    This is a perfect use case for SSIS. Checkout the toolkit from Kingswaysoft that you can use to connect to your instance, export the file, and then upload it to an FTP server.

    Good luck!

    ------------------------------
    Chris Harrington
    Solutions Architect
    PowerObjects, an HCL Company
    ------------------------------

    Conference-CRMUG_200x200


  • 4.  RE: Automatic Export of Marketing List to Excel to FTP site

    Posted Aug 01, 2019 07:36 AM

    There are some 3rd party tools available that can assist with scheduled exports of reports (and reports can be indicated as Excel, word, or pdf) like Zap Report scheduler.  The Appsource has a number of these programs and most of them are very economical in annual cost compared to the time wasted trying to solve for them.

     

    Bill Suycott

    Vice President,  FIS Sales Tools, Infrastructure

    4900 West Brown Deer Road, Brown Deer, WI  53223

    T: 414-357-3082

    C: 414-651-7886

    FIS | Empowering the Financial World image005.png@01D11265.2C7F1D60image006.png@01D11265.2C7F1D60image007.png@01D11265.2C7F1D60

     

    The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you.



    Conference-CRMUG_200x200


  • 5.  RE: Automatic Export of Marketing List to Excel to FTP site

    MICROSOFT MVP
    Posted Aug 01, 2019 07:55 AM
    Tim,

    You could do this with Microsoft Flow by using the create csv file action to create the file and then use the ftp connector to upload it.

    ------------------------------
    Jerry Weinstock
    Business Technology Strategist, MVP
    CRM Innovation
    Lenexa KS
    https://mvp.microsoft.com/en-us/PublicProfile/4029113?fullName=Jerry%20%20Weinstock
    ------------------------------

    Conference-CRMUG_200x200


  • 6.  RE: Automatic Export of Marketing List to Excel to FTP site

    Posted Aug 01, 2019 09:22 AM
    Hi Tim

    +1 vote for SSIS/Kingswaysoft approach.

    Since you are on-premise, another approach would be to use the Flow on-premise gateway, connect to SQL and use all the MS Flow goodness for the Excel/FTP site.  This might take a bit of work, but I also think that if you got it working and wrote a blog post/presentation on the process, you would get a lot of interest.  I would read it. :)

    https://docs.microsoft.com/en-us/flow/gateway-reference

    Cheers
    Nick

    ------------------------------
    Nick Doelman
    Microsoft MVP
    Dynamics 365 Specialist
    Nick Doelman Co.
    ottawa ON
    ------------------------------

    Conference-CRMUG_200x200


  • 7.  RE: Automatic Export of Marketing List to Excel to FTP site

    GOLD CONTRIBUTOR
    Posted Aug 01, 2019 09:58 AM
    Tim,
    While I'm super-intrigued now at the Flow on-prem gateway, which I hadn't heard of before, one slightly-more-lame idea is an old tool called the MSCRMToolkit that provided a way to automate running a FetchXml query and exported it to a CSV.  Not sure if that's sufficient since you mentioned exporting to Excel, so if you actually need .xlsx then it won't help you.  But if it's just CSV, then we've used it to do that in the past and then used Powershell to move the file wherever it needs to go (FTP, S3, etc).  We started using it a couple years ago (we're on-prem too) and it's worked fine for us.  Worth noting that I don't think the MSCRMToolkit is being supported anymore, but the latest version does work with IFD.  The latest version is 0.6.6 and it's located here: https://github.com/zoranivanov/MSCRMToolKittest

    ------------------------------
    Mike Power
    Senior CRM Solutions Developer
    American University
    Washington DC
    ------------------------------

    Conference-CRMUG_200x200


  • 8.  RE: Automatic Export of Marketing List to Excel to FTP site

    SILVER CONTRIBUTOR
    Posted Aug 01, 2019 10:00 AM
    Edited by Fabien Bernard Aug 01, 2019 10:02 AM
    Hi Tim,

    Another option if you have access to a C# developer:

    I would develop a small Windows executable that can then be triggered with a Windows schedule task (Or a Windows Service but you will have to add the schedule feature in the application).
    This program would reference Crm sdk libraries that would allow you to call exportToExcelRequest message (Seems like this message is not documented, so it may not be suitable for future Crm upgrades - So you may prefer generate the Excel file using a .net library after querying data with ExecuteByIdSavedQueryRequest for instance).
    I found a sample code for exportToExcelRequest here:

    https://dreamingincrm.com/2016/11/10/export-to-excel-using-dynamics-365-sdk/
    From a C# program, it should not be difficult to use a FTP library to push the generated file in.

    May sounds more complicated than it actually is, should take less than one day for an experienced C# / CRM developer

    ------------------------------
    Fabien Bernard
    CRM Developer
    Montréal QC
    ------------------------------

    Conference-CRMUG_200x200


  • 9.  RE: Automatic Export of Marketing List to Excel to FTP site

    TOP CONTRIBUTOR
    Posted Aug 01, 2019 06:53 PM
    Hi Tim,

    +1 Vote to KingswaySoft and SSIS. We use it for quite a few of our file based integrations and the toolset is just awesome.

    ------------------------------
    Todd Mercer
    Dynamics CRM Technical Lead
    MD Financial Management
    Ottawa ON
    ------------------------------

    Conference-CRMUG_200x200


If you've found this thread useful, dive deeper into User Group community content by role