ssis package to import date from LINUX Box to SQL Server2008
The MySQL Database is available on Linux Server and in MySQL i have two databases from which i need to import the data to SQL Server 2008 which is on Windows 2003 Server. I need to Schedule a Job for this. How can i achieve this by using SSIS Package. Any help Please.....
You can use SQL Agent on the SQL Server 2008 server to schedule a job. Connect to the SQL Server 2008 instance using SSMS. In Object Explorer, expand SQL Server Agent. Right-click Jobs and create a new job. Name the job and click the Steps page. Click the New button to add a new step. Name it and set the type to Integration Services. Configure the items on the General tab, aiming them at your SSIS package. Click the Schedules page and set up a schedule to execute the job periodically. Hope this helps, Andy
If you are new to SSIS then check [SSIS tutorials]. If your concerns are regarding Automating SSIS - Assuming you have deployed the SSIS package to the package store or file system, you can automate the package by using SQL Server Agent Jobs. To call SSIS package on SQL Jobs you should must create a Proxy Account and the Credential. Note the Credential should have the necessary privilege on both Servers and DBs. Refer [Scheduling SSIS Package with SQL Server Agent] for more details regarding Scheduling. :
Hi as @Cyborg wrote, check the SSIS Tutorials. But anyway to transfer any kind of data you use a Data Flow Task. Inside the Data Flow Task put a OLE DB Source for connecting to the MySQL using OLE DB Driver or ADO NET Source if connecting to MySQL using ODBC drivers. As a target for connecting to the SQL Server 2008 Instance use OLE DB Destination Of course, you need to install appropriate drivers for MySQL. Eg. OLE DB Drivers and/or at least ODBC Driver, configure ODBC to use it. The drivers needs to be installed on the machine on which the SSIS package will be executed. Also you have to remember potential issues if you will be running the packages on x64 machine. Then you need x64 drivers or run the packages in x32 mode.
Another solution would be to use [MySQL Compare from Redgate] to copy the schema across, you could then dump the table contents from MySQL to CSV files and use SSIS to read the files into the newly created tables in SQL Server. That shouldn't be too difficult. :