Find Jobs
Hire Freelancers

Mass XML to database ingest / update / manage expertise - SQL or ETL

$250-750 USD

Closed
Posted about 9 years ago

$250-750 USD

Paid on delivery
Hi there - We have a situation where we get new ZIP files from a service daily. The zip files include hundreds or thousands in some cases of product XML. We have to import these files into a database while placing parts of data in specific tables (product specs in the products table, overview in the overview table, etc etc). Additionally, if the product is already in the table, we need to update it and all of the data attached instead of inserting duplicates. We already have a solution in place, somewhat, using Pentaho Kettle. Unfortunately, our team isn't much familiar with the automation of that system or editing it (or even validating that it is getting the data pulled in correctly in its entirety for that matter)...so while we may end up landing there...we're looking for subject matter experts on 1) what is the best, most efficient way to import these files, 2) how do we schedule it do run daily in a stable manner and 3) how do we edit the deployment. We're pretty open, but we'd like to stick to either a stored procedure of sorts or something SQL side...or something that will run natively in our environment...I don't like installing a bunch of new stuff because we have to migrate servers frequently or bring up testing environments and that becomes a pain. We're a Windows 2008r2 / SQL Server shop. If you do integrate with a coding language, please use Coldfusion. Thanks much! PLEASE PROPOSE A SOLUTION IN YOUR RESPONSE. Responses without a "this is what I plan to do and why" in the description will be ignored. Thanks!
Project ID: 7073482

About the project

29 proposals
Remote project
Active 9 yrs ago

Looking to make some money?

Benefits of bidding on Freelancer

Set your budget and timeframe
Get paid for your work
Outline your proposal
It's free to sign up and bid on jobs
29 freelancers are bidding on average $681 USD for this job
User Avatar
Dear Sir/Madam, Is the structure of these XML files same? Are their structures different? Can we see their structures? We suggest to program a background software with multiple threads to import these XML files paralelly (improve the performance of importing…). And program a store procedure in SQL server to check the duplicate data. Best regards, Lee.
$789 USD in 10 days
4.9 (10 reviews)
6.2
6.2
User Avatar
"this is what I plan to do and why" My solution will be for this is to generate and console application (will do in C# .Net but you mentioned to use coldfusion for this) which will pull the data from sepcific service(website) and will parse it accordingly to rules which we will setup or can define the rules which will push the data to database. We will schedule it using SQL Server Agent Services (Job). Regarding Deployment we have to create a job scheduler in SQL Ajent and need to specify the path of executable. Dear Sir, This is Sikander. I am having 12+ years of experience in software development and I am ready to start this job. I will provide you best services in development. Please have a look at my Profile, Ratings and feedback. I am working in Microsoft technologies and you can find the details below. My Skills includes: 1) .Net (Web/Windows) asp.net 2) MVC 3) Entity Framework 4) WPF/WinForms 5) MSSQL Server 6) SQL Server Reporting Services 7) Javascript/JQuery 8) WebServices/WCF Services 9) MySQL 10) Hosting Sites 11) Mailchimp 12) Recently Worked on Vending Machine solution with Monetary apparatus and Shipping Goods motor also worked on Kisok applicaiton. many more...... I have no fancy talks, no false promises and nothing to hide but honesty and a great will to expand our work experience. I am willing to expand my work experience to different clients and in this order I apply for this job in the hope to hear some positive response from you. I hope you wil
$750 USD in 12 days
5.0 (9 reviews)
4.6
4.6
User Avatar
Hello, My proposed method (in order of execution): 1. An Agent Job will run an Integration Services (SSIS) Package (comes with SQL Server, you edit the schedule as per your requirements) 2. The SSIS Package will unzip the file(s) (will need an unzip program for this like 7zip) 3. The package will import the raw XML data from the file(s) into staging tables in SQL Server 4. The package will then run an stored procedure inside SQL Server to shred the XML data as per your business requirements from the staging tables into your live tables. Note: if the business requires changes in the future only the stored proc needs to be changed, not the package. 5. The package reports as complete and successful The most complex part is actually how the XML maps to the business logic. Could you please provide an example of one of your XML files? A bit about me: I am a Microsoft Certified Solutions Master: SQL Server. I am a SQL Server Consultant working as a Developer and DBA, I have had 14 years experience (SQL Server 2005 - 2014). My website is: http://www.nicholsonsql.com. I have experience with many companies (particularly large government departments with 100,000+ users). Please note my bid is low because I am doing this to improve my rank rather than to gain money. If you have any questions let me know.
$500 USD in 10 days
5.0 (2 reviews)
3.9
3.9
User Avatar
This is what I plan to do and why I need to check the working first and I do have the solution which updates / insert the data into various tables depending from XML file. I have expertise to get the work done but before hand I want to see the format of XML file before proceeding.
$555 USD in 10 days
5.0 (9 reviews)
3.0
3.0
User Avatar
Hi, I am DB developer with data migration/data loading experience with Pentaho kettle. I propose to work with you to suitably modify and stabilize your existing Pentaho kettle based system to load xml files. Advantages of Pentaho Kettle is that 1. Being GUI based system, modifications of loading process should be easy. 2. Well tested system which gives features like parallelism and high volume data handling inherently 3. Open source tool with no licence fee. Before assigning the job, let us agree on verifiable milestones (max 5 numbers) of work delivery and payments, so that it is comfortable both sides. You have any queries, please let me know. Regards Sankar
$555 USD in 10 days
5.0 (2 reviews)
2.6
2.6
User Avatar
Dear My proposal is as follows: If you have SQL Server (Standart - Enterprise - Developer), I can design a SSIS package to take de XML files and import data into SQL database. The process is simple and can be automated execution from SQL agent. The most complicated is mapping the XMl files with SQL tables. A warning is that Zip file can not be taken by Integration Services. The files must be unzip. I need a XML file example to make a test accord with standart XML that files use. The cost of the project can be modified accord with the quantity of XML files, cause for one file correspond one ETL process. I make the solution in my development environment, or I can work remotely on your servers. I prefer the first option for security issues. You have to indicate the version and edition of your database. (e.g. SQL Server Enterprise 2008 R2). If you have questiosn, please contact me. Thanks. Regards!
$544 USD in 11 days
5.0 (4 reviews)
2.4
2.4
User Avatar
Hi, STEP 1. Your requirement can be achieved only by using SQL Server SSIS services. We can write one DTS job which will read the zip file and if found extract the files in Zip. Start Reading each and every xml file and map it to respective table with data validation ie if record exitst then update or Insert. STEP2: I can provide one service will be run on your server as watch dog. It will look for the folder where Zip file is supposed to be available and then does the rest of thing same as above using SQL server Stored procedure. Detailed discussion can be done over the chat to make you more clear on this line. I request if u can provide me the sample Zip file containing XML and backup of table structure. and mapping details of XML VS SQL TABLE. then i can show you one demo of the same. Regards! DNG TEAM
$833 USD in 20 days
5.0 (1 review)
2.0
2.0
User Avatar
Hi, greetings, This is Raj, we can develop a standalone app using Java, as Java is open source and you don't have to install anything else, we will share the executable file with you and then that file will run in any windows platform, the file will help you to extract data from xml file and store the data to a database, we will connect the app to H2 database, and then you can export that data to your desired SQL Server database or any other db. Please contact us for any further queries or suggestions Have a great day Thanks and Regards Raj
$500 USD in 5 days
0.0 (0 reviews)
0.0
0.0
User Avatar
Hello, We can offer a Windows application. It will be most reliable in your case. The application will track the emergence of new XML files to specify the location of the file (optional). 1) what is the best, most efficient way to import these files Then (automated): 1. Creates a Backup of the database 2. Data files are synchronized with the database SQL 3. On the screen will show a log of all processes 4. The ability of the process to roll back if something goes wrong More details can be discussed when you will see what data is sent and to what extent. Perhaps it would be better to write ETL, using the same technology. 2) how do we schedule it do run daily in a stable manner 1. When the files at the specified address, the application automatically starts working with them. 2. Schedule. How much or how long the period of time the application will check for new files 3. Both the choice Use: C#, SQL Experience more than 9 years Best regards
$777 USD in 14 days
0.0 (0 reviews)
0.0
0.0
User Avatar
Hello, I have 2 years of experience in Sql Server. 1) Why do you not use SSIS or DTSX packages? 2) I can use XP_CMDSHELL which can manupulate with files and load them with OPENROWSET
$555 USD in 10 days
0.0 (0 reviews)
0.0
0.0
User Avatar
The specific details of your situation could influence these thoughts: Step 1) automate the import of the zipped xml files into SQL Server (SQL Server can store native xml (it is important to assume that the xml is well-formed...)) Step 2) using stored procedures move the xml data into appropriate tables, logging all activity in sql tables Step 3) create SQL Server job to automate the above on fixed schedule with email output of results to project managers and logging to sql tables of each task and outcome I'm a physician software developer and have done similar tasks. All doable, but the specific details have to be carefully thought out. (A clear map of input xml to target sql tables) Input xml in sql server can take up a lot of space, a decision would have to be made whether to keep these files or only use sql server xml tables as a staging platform and delete when complete. If interested, would need a better feel of the number and size of the input files, and the capacity of the sql server...
$1,111 USD in 7 days
0.0 (0 reviews)
0.0
0.0
User Avatar
Purveyor of competitive intelligence and holistic, timely analyses of Big Data made possible by the successful installation, configuration and administration of Hadoop ecosystem components and architecture. Two years’ experience installing, configuring, testing Hadoop ecosystem components. Capable of processing large sets of structured, semi-structured and unstructured data and supporting systems application architecture. Able to assess business rules, collaborate with stakeholders and perform source-to-target data mapping, design and review. Familiar with data architecture including data ingestion pipeline design, Hadoop information architecture, data modeling and data mining, machine learning and advanced data processing. Experience optimizing ETL workflows. Hortonworks Certified Hadoop Developer, Cloudera Certified Hadoop Developer and Certified Hadoop Administrator. Areas of Expertise: Big Data Ecosystems: Hadoop, MapReduce, HDFS, HBase, Zookeeper, Hive, Pig, Sqoop, Cassandra, Oozie, Flume, Chukwa, Pentaho Kettle and Talend Programming Languages: Java, C/C++, eVB, Assembly Language (8085/8086) Scripting Languages: JSP & Servlets, PHP, JavaScript, XML, HTML, Python and Bash Databases: NoSQL, Oracle UNIX Tools: Apache, Yum, RPM Tools: Eclipse, JDeveloper, JProbe, CVS, Ant, MS Visual Studio Platforms: Windows(2000/XP), Linux, Solaris, AIX, HPUX Application Servers: Apache Tomcat 5.x 6.0, Jboss 4.0 Testing Tools: NetB
$555 USD in 9 days
0.0 (0 reviews)
0.0
0.0
User Avatar
I suggest to create a Windows Service (preferably in C#) to import XML files to SQL DB paddingpadding
$733 USD in 10 days
0.0 (0 reviews)
0.0
0.0
User Avatar
Hi I'm SQL developer from many years and I have been doing imports like this many times. I prefer to use SSIS packages for SQL server. With that solution you will not need to install any additional software. All what is needed is in SQL server. I want to build ETL package that will download zip file, extract it and then load to tmp table (XML to SQL). After i will have it inside the SQL table I will start inserting the dimensions and then facts tables with your products. To increase solution stability i propose to add some checking procedures before loading fact tables. To accomplish my work I will need your database structure schema (it could be mdf file with empty tables) and mappings for data from XML file. I will also need propositions for data integrity checks. Whole solution will be runned as standard SQL server job that will have SSIS packages as steps. After correct or not data load I suggest to send an e-mail to some e-mail group with information about finished or not import proces. In error message there should be also some error messages from data checkers. I'm waiting for your response. Best regards Daniel Stawicki
$750 USD in 14 days
0.0 (0 reviews)
0.0
0.0
User Avatar
Dear Sir/ Madam, My name is Henry from PHAMTECH Co. My team is really interested in this project as it is exactly within our scope of expertise: We are web/ app design & development experts. Please kindly visit PhamTech to learn more about us and our works similar to this type of projects. Working with a team who has both in-house DESIGNERS and PROGRAMMERS means that you can take advantage of both: Our designers could help revise the design even in coding process, which is a GREAT benefit for any ongoing project. + We plan and build. We don't use prebuilt templates which produce conflicting codes and bugs and hurt the system in the long term. + Our team is 100% in-house. We don't outsource projects elsewhere. So you pay for the real service - not marketing fee. In short, if you need an experienced team who offers *real English* communication, beautiful design & optimized code, premium after-service, in time and to budget, then we are the choice. If you need something based on solely ONE of the above criteria, then we're still happy if you decide to give another freelancer a chance. Just bookmark our website and come back in case the selected freelancer can't meet your demands. * Even if you don't go with us, avoid fast food services. Chances are they omit the software planning & testing phases for the sake of reducing time & cost, which produces unsustainable codes behind and creates more distrust in this market! Best wishes, Henry.G
$1,030 USD in 10 days
0.0 (0 reviews)
0.0
0.0
User Avatar
By making use of SSIS, we can import the XML files and interrogate the date in-flight. SSIS component will allow logic to be written to check for existing data and either update or import new data. SSIS package can be automated and configured for any modifications thereafter. I will provide the SQL coding and SSIS creation for this solution, along with documentation on how to administer the solution thereafter.
$555 USD in 10 days
0.0 (0 reviews)
0.0
0.0
User Avatar
-I am develop a BI system for a retail business where the largest table had 60 million records .. The 1st version of ETL was kettle-mysql InfiniDB and the second version was kettle-IBM Netezza -The analysis tool was pentaho version 4.8 -The analysis tool was spago-bi 4.2 I suggest the following: 1) what is the best, Most efficient way to import files in Original -Read Each script file using linux (bash) and get new files where the field delimiter is ";" and row "\ n" -Upload These new files using some resemblance to "cimport InfiniDB-mysql" command or "nzload Netezza-ibm" where you can climb million records in just a few minutes: for the first case the increase was delayed 45 million and 6 minutes the second one minutes -The Kettle just would execute the process "sh" of generation of new files and there's another process "sh" upload files to the database 2) how do we do run daily schedule it in a stable Manner -By A process manager cron on linux or window or by calling an "sh" script from your ERP system 3) how do we edit the deployment. -The Kettle generates files that are kjb script that can modify and call seamlessly from files "sh"
$1,666 USD in 10 days
0.0 (0 reviews)
0.0
0.0
User Avatar
I used to perform ETL jobs for the US Navy for reporting budgetary information. The first approach that comes to mind is to use SSIS, which is SQL Server Integration Services. It is meant for this very thing, and if you have a SQL Server license already , my understanding is that this falls under that license for no extra cost. If that is not desirable for any reason, it is a good approach to write a batch script that will unzip everything and subsequently use sqlcmd utility to import the XML using Transact-SQL statements. I am not sure the complexity of the mapping, but these are the two approaches that make it simplest to manage. Thank you for your consideration.
$721 USD in 10 days
0.0 (0 reviews)
0.0
0.0
User Avatar
I work on similar projects where HL7 messages data comes in XML format. After reading the requirements my proposal is below. 1) I will use simple SSIS ETL that comes free with Sql 2008R2 so no new purchase is required. 2) A simple step in SSIS to unzip files I can write in VB.net or C sharp. 3) using SQL XML queries I will parse the XML using SQL and load into staging tables. 4) Any filtering will done in Staging table 5) Once the filtering is done we can load the data into the main tables. 6) Scheduling of the job will be done by SQL Agent. Pros: 1) No new installation is required. 2) Simple SQL maintenance is good enough. 3) Simple to migrate the package and schedule on other server(s) if needed. 4) We can setup an alert as needed for the Job fails. 5) No complex store proc or Programming coding is required..
$555 USD in 12 days
0.0 (0 reviews)
0.0
0.0
User Avatar
Hi, I have worked with MS SQL 2008 daily with data integrations and reporting. To use the native MS SQL 2008 to achieve this project, we will need the SSIS packages for following: Phase 1: Use SSIS to FTP download the ZIP files to a folder location. Unzip the files of product XML files. Archive original files into separate folder. Phase 2: Looping through the XML files to stored into a stage database and tables as raw XML files/data using SSIS and stored procedures calls to save the data into a table for reading. Phase 3: Use stored procedure to parse the raw XML data from the stage table to read the XML data elements for insert/update records. Phase 4: Truncate the staging tables (initially or after) daily. Phase 5: Schedule / configure alert settings once deployed the SSIS package into SSIS server. All the phases can be designed in a single SSIS package or as separate ones.
$722 USD in 15 days
0.0 (0 reviews)
0.0
0.0

About the client

Flag of UNITED STATES
Denver, United States
4.9
46
Payment method verified
Member since Sep 21, 2012

Client Verification

Thanks! We’ve emailed you a link to claim your free credit.
Something went wrong while sending your email. Please try again.
Registered Users Total Jobs Posted
Freelancer ® is a registered Trademark of Freelancer Technology Pty Limited (ACN 142 189 759)
Copyright © 2024 Freelancer Technology Pty Limited (ACN 142 189 759)
Loading preview
Permission granted for Geolocation.
Your login session has expired and you have been logged out. Please log in again.