Selecting data from MySql in MSSQL

Setup Link Server

  • right click to add new linked server. Fill in all the appropriate details accordingly. Follow the database name in mysql to be use as data source over here.
  • Provider string: DRIVER={MySQL ODBC 5.3 ANSI Driver};SERVER=ip address;PORT=port number;DATABASE=mt4_demo;USER=username;PASSWORD=password;Option=3

l2

  • test connection and save it.

l6

 

  • after setup

l4

  • To query the data from the table
select * FROM OPENQUERY(MT4DBDEMONew, 'SELECT * FROM mt4_trades')

 

 

 

Advertisements

Running SSIS package in a database scheduler

Copy excel file/ csv file into the table daily repetitiously.

Below is the guide to set the SSIS and use the generate package file in the job scheduler in MSSQL.

SSIS Setup

  •  Tasks -> Import data

s1

  • client ‘Next’

s2

  • select ‘Flat file source’

s3

  • Select the file, change text qualifier to ‘ ” ‘, uncheck the check box if first row is data

s4

  • click ‘Next’

s5

  • Specify the database server name, type of authentication and database name

s6

  • assign the destination table where data copy into. Click ‘Edit Mapping’ to set the mapping column

s7.png

  • map the column in csv with the column in the table

s8

  • check and click ‘Next’

s12

  • Only Enterprise version allow to save package (Express version cannot). check the Save SSIS package.

s9

  • click ‘Next’

s10

  • specify the package name and click ‘Next’

s11

  • click ‘Finish’

s13

Database Job Scheduler

  • Create a new job under SQL Server Agent

s14

  • Set the step to execute the package

s15

  • set the package file to be executed

s16

  • set the scheduler time

s17

Job done!! you can test it before leave it for everyday process.

Web Programming guide – Inserting data using SQL data source controller

In developing web base system, retrieving and inserting data is a basic thing. As in ASP.NET, it makes development easy. As we’re going to explore one of the control-SqlDataSource control which I have tested it out with a simple web application form name ‘NewContract.aspx’ in SCM test n error 2 project.

NewContract.aspx
NewContract.aspx

Continue reading