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?
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?
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.
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)
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.
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
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:
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.
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) -->
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?
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.
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!
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
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
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
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.
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
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
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
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
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.
Hope this helps you,
Richard
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
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
is working on a reply...