Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Jeremy 14 posts 35 karma points
    Sep 14, 2009 @ 19:56
    Jeremy
    0

    Insert data into umbraco v4 with SSIS package

    I am working on a project that requires me to insert data from an .xml file into Umbraco through an automated process.  I am new to Umbraco, so I am going to try to explain what I need to do the best I can...

     

    I have an .xml file that contains data.  This .xml file is retrieved from an FTP site (there will be a new file every week).  The data in the file represents, for example, people.  I have created a new document type in Umbraco, called Person.  I have added attributes to this document type (first name, last name, address, etc.).  For each person record in the .xml file, I need to add a new Person record in Umbraco.

     

    I have been looking through the Umbraco database structure, and it seems the cmsPropertyData table contains the actual data for a document type.  I would like to insert the data directly into this table through an SSIS package.  Has anyone done something similar to this?  Is there a simpler way to automatically import data into Umbraco?

     

    Thanks!

  • Dirk De Grave 4541 posts 6021 karma points MVP 3x admin c-trib
    Sep 14, 2009 @ 20:16
    Dirk De Grave
    0

    Yes, there's a much simpler way of importing data. Have a look at Richard's project umbImport, may be exactly what you need!

     

    Cheers,

    /Dirk

  • Jeremy 14 posts 35 karma points
    Sep 14, 2009 @ 20:26
    Jeremy
    0

    Hey Dirk, thanks for the quick reply.

     

    I have looked at the umbImport package.  Unfortunately, I haven't found a way to automate the process.  I basically need to setup a job that will grab an .xml file and insert/update the data into the database on a weekly basis.  UmbImport gives you a nice wizard interface, but it is a manual process.  Is there a way to automate this?

     

    Thanks

  • Dirk De Grave 4541 posts 6021 karma points MVP 3x admin c-trib
    Sep 14, 2009 @ 20:40
    Dirk De Grave
    0

    Don't think that's available in the free version, I've heard rumours about a PRO version which will enable scheduled imports and the like. I'd contact Richard (there's an e-mail link on his website albeit a very small icon...) for more info.

     

    Cheers,

    /Dirk

  • Jeremy 14 posts 35 karma points
    Sep 14, 2009 @ 23:24
    Jeremy
    0

    I sent an email to Richard. 

     

    Do you know if it is possible to automate the execution of a package?  UmbImport is exactly what I need, I just need it to run on its own.

  • Dirk De Grave 4541 posts 6021 karma points MVP 3x admin c-trib
    Sep 15, 2009 @ 08:25
    Dirk De Grave
    0

    Jeremy, that's why I've advised to sent a mail to Richard, as the PRO version will probably include scheduled imports (btw, scheduled tasks are already available oob in umbraco)

     

    Cheers,

    /Dirk

  • Casey Neehouse 1339 posts 483 karma points MVP 2x admin
    Sep 15, 2009 @ 08:51
    Casey Neehouse
    0

    Jeremy,

    You should try to stay away from direct database imports if at all possible.  A simple application can be written to use the umbraco API to insert the data.  You can have this application monitor a specific file for changes, or, even have it retrieve the file, and process it once fully downloaded.

    Umbraco has a 'task manager' to kickoff tasks at certain intervals.  I am sure there are other options as well.

    However, UmbImport may do the job (pro).

    Case

  • Richard Soeteman 4046 posts 12899 karma points MVP 2x
    Sep 15, 2009 @ 10:16
    Richard Soeteman
    0

    Hi Jeremy,

    I've sent you an email also but for others. I'll post some info also. With UmbImport PRO it is indeed possible to schedule imports. Downside is that it is not finished yet, due client projects it's a bit delayed.

    One other thing is that you need to create a custom dataAdapter to  retrieve the xml using FTP. It is really simple just implement two interfaces, one for the logic and one for UI. I've written a blogpost about this a while back. Read it here

    Cheers,

    Richard

  • Jeremy 14 posts 35 karma points
    Sep 15, 2009 @ 21:25
    Jeremy
    0

    Thanks, Richard.  I have sent you an email with a couple of questions in regard to umbImport PRO.  I've also just thought of another question... The xml file that I retrieve looks similar to the following:

     

    <?xml version="1.0" encoding="UTF-8"?>
    <people>

    <person>
    <first_name>Mike</first_name>
    <last_name>Smith</last_name>
    <address>123 Fake Street</address>
    <sibling>Jo Smith</sibling>
    <sibling>Steve Smith</sibling>
    <sibling>Bill Smith</sibling>
    </person>

    </people>

     

    This isn't exactly what the file looks like, but I hope you can see my problem.  There are some nodes that "repeat", that is, the 'sibling' node can appear 1 or many times.  When running through the import wizard, I can't match this up (it doesn't appear in the drop down list).  Is there any way to resolve this?  Unfortunately, this file comes from a third party and the format cannot be changed.

     

    I'm thinking about creating a table that I will use to hold the data from the xml file.  I can use SSIS to get the data to look the way I want.  From there, I can use the import tool to get it into Umbraco.

     

    Casey, can you elaborate on the "task manager"?  I have not seen this used anywhere.

     

    Thanks

  • Richard Soeteman 4046 posts 12899 karma points MVP 2x
    Sep 15, 2009 @ 22:00
    Richard Soeteman
    0

    Hi Jeremey,

     

    I've just replied your questions. I can see what youre problem is and indeed you need to format this to a single string before importing the data using umbImport. Casey is refereing to the ScheduledTask option that you can set in the /Config/UmbracoSettings.Config file. Where you can set the interval as shown in the following example. I'm using it to kick off my scheduled import tasks. 

    <

     

    scheduledTasks>

    <!--

     

    add tasks that should be called with an interval (seconds) -->

    <!--

     

    <task log="true" alias="test60" interval="60" url="http://localhost/umbraco/test.aspx"/>-->

    <

     

    task log="true" alias="umbImportScheduler" interval="60" url="http://umbimportpro.local/handlers/ScheduleTaskHandler.ashx"/>

    </

     

    scheduledTasks>

    Hope this helps you,

    Richard

  • Jeremy 14 posts 35 karma points
    Sep 17, 2009 @ 23:19
    Jeremy
    0

    Alright, I solved the issue with the xml file by using an xsl transorm.  I am trying to figure out how to schedule a package (zipUpload in this case), but I have not been able to figure out how to do it.  I have been googling for what seems like years, and I have yet to come across anything useful.  Does anyone have any insight into how to create a scheduled task that executes a package?

     

    Thanks

  • Richard Soeteman 4046 posts 12899 karma points MVP 2x
    Sep 18, 2009 @ 09:27
    Richard Soeteman
    0

    Hi Jeremy,

    A task is not scheduled for a certain date/time, it's based on an interval. So you need to check yourself if it's the right date/time to fire a task. Furthermore you need to specify an Url that get's executed every interval. I'm using a custom scheduler in UmbImport that checks every minute if there is a task to be executed. I'm planning to extract this functionality and make that available as an open source package.

    Cheers,

    Richard

Please Sign in or register to post replies

Write your reply to:

Draft