Database Export / Auto Upload

[Vodo4321]

Ok so here's my situation.

At work we use a software package that covers everything from our work order scheduling to our invoice creation. The program runs off a Sequel Server database and in the database there's the Product table that holds all the info for our sellable products.

On my website I'm building a quick little php lookup form so whichever technician is on overtime on a given night can check the product list and get pricing information. My web host has support for MYSQL databases.

So what I need is an application or something that'll take the info from the Sequel database on my internal server convert it if necessary and upload it (nightly?) to the MYSQL database on the offsite web server.

Right now I've got an application called FlySpeed data exporter to get the Sequel database into excel or other formats I may need, but unfortunately it dosent do remote uploads that I've seen.

So is what I'm after possible? And if so any application reccomendations?

[lendarios]

SSIS package

Create a package and connect directly to your MYSQL using ODBC and export the data directly.
Depends on the complexity of your data it may be trivial or not.
if all you are doing is reading from table and coping to other table then the package itself is not complicated.

[Nazbuk]

Or if your web server isn't in the DMZ you can link directly into the table through some ASP or .NET code.

Or you can write an API to tie into that data.

[lendarios]

The problem of doing it ona .net code is the scheduler, He wants the item to be run on nightly basis. I would say on that sense dont use a webform for the schedule but a windows app/linux application to run the elements nightly.

If you go the SSIS route then you set the schedule no your sql server and then no need to baby sit it.

[Vodo4321]

Ok I may be retarted, but for all the SSIS package tutorials they're saying I need SQL Server Business Intelligence Development Studio which looks like it comes with Sequel Server 2005 (whcih im running), but didnt get installed on my server and I'm unable to find it on any of the installation cds.

[lendarios]

second CD.
=)
Its under workstation components

[Fog]

Excuse me, but what you're doing and the advice you're getting makes no sense. If the data is in a database on your network at work, why wouldn't you connect to that database directly?

Either expose the server to the outside world (with appropriate security set up), connect in with a VPN, or host the website from within your work network. Any of those will work. Trying to cook up an automated backup-to-file, send-the-backup-outside, restore-the-backup-over-here process between SQL Server 05 and MySQL makes no sense unless there's something you're not telling us.

EDIT: Also, I hope you have managed to make the connection between "Sequel Server 2005", "Sequel databases", "MySQL", and "SQL Server Business Intelligence Development Studio."

[Vodo4321]

Originally Posted by lendarios

second CD.
=)
Its under workstation components

Danke, Ill hop into work for a few hours tomorrow and try it out.

Excuse me, but what you're doing and the advice you're getting makes no sense. If the data is in a database on your network at work, why wouldn't you connect to that database directly?

Either expose the server to the outside world (with appropriate security set up), connect in with a VPN, or host the website from within your work network and until the local phone co / cable co decide to run some better shit into the area theres nothing I can do about it. Any of those will work. Trying to cook up an automated backup-to-file, send-the-backup-outside, restore-the-backup-over-here process between SQL Server 05 and MySQL makes no sense unless there's something you're not telling us.

EDIT: Also, I hope you have managed to make the connection between "Sequel Server 2005", "Sequel databases", "MySQL", and "SQL Server Business Intelligence Development Studio."

1) Wow no need to be such a condescending douchebag. I'm quite new to all this shit and came to the development forum to ask some advice / help. If you want to help, act like Lendarios and help, otherwise go away.
2) I cant do a direct connection because our business is in an ancient old commercial building into which all of the data (phone lines, no coaxial) suck giant donkey balls. The internal network is fine, but any traffic going in and out is somewhere between dialup and shitty dsl. There's NO way we could handle any kind of direct connection, whereas a nightly dump to the website isnt as big of a deal.

[lendarios]

For the direct DTS packages the servers have to be able to see eachother.
If not you can still use DTS to create your daily data export but then you will have to handle the task of uploading it to your site and inserting it into mysql.

[Vodo4321]

They should be able to see eachother fine. Like I said it's just that its a really shitty connection speed wise, so it might take a bit every night, but I'll just schedule it at like 1am or something when it dosent really matter.

[Sabolin]

For importing into MySQL I recommend using LOAD DATA LOCAL INFILE, it is many times faster than looping through insert statements. Basically you dump your table data into a file with any format you want (CSV is most common) and then you define in the SQL statement what characters terminate the fields (, for CSV) and lines (\r\n for windows), and then MySQL Server just reads the file directly into the table. If you have commas in your table data, then you can use ; or any other character you want. Your data in the file should match the fields in your MySQL table (ie, if you have 10 columns in your MySQL table, you want 10 columns in your file separated by commas and in the same order as the MySQL columns). If it doesn't, then the SQL statement will get much more complicated requiring variables and assigning variables to each field.

link-
MySQL :: MySQL 5.1 Reference Manual :: 12.2.6 LOAD DATA INFILE Syntax