locked
Database sinking for Reports RRS feed

  • Question

  • Hi All

    Actully I have Query regarding database sinking...

    Actully i have two database server one is mysql and another is sqlserver..

    Mysql is live database .... and i want to make reports in ssrs and ssas using  sqlserver..

    Now i want that mysql should sink data in sqlserver on daily basis... it should be good in performance wise..

    now i have an option for this..

    1.Linkedserver and make ssispkg

    2.Logshipping

    what option should i choose...

    If I choose first then please guid me what sequence i should follow...

    Kindly Help Friends

    Regards

    BI_Group

    • Moved by Kalman Toth Thursday, December 6, 2012 3:49 PM Not a Database Design question. (From:Database Design)
    Friday, August 10, 2012 10:28 AM

Answers

All replies

  • Hi,

    You should consider using SSIS, as it's designed for the same purpose. You can schedule the package easily and handle the any event/error which can occur and send a notification etc.

    If you use link server to Mysql for reports, the load will still be on MySQL server and purpose if not fulfilled.

    Not sure about secons option, but you can not run report from logshipped sql database as by default its in restoring state.


    - Chintak (My Blog)

    Friday, August 10, 2012 11:27 AM
  • Thanx For the Helpful Reply Chintak Chhapia..

    I create LinkedServer to mysql to Sql Server ...

    Now I'm doing SSIS Pkg for this ... actully this MYSQL is Are calling Database, and i'm little bit afraid for firing query there ...

    because i consider that when i execute query it should not hamper calling..

    next if i'll fire query when calling is stopped then how much time it will take ..?

    Performance should not affected in any of case..

    I do in pkg that

    first time it will create databse wit table and insert data there...using EXECUTESQL Task

    INSERT INTO abc(col1,col2,col3)
    SELECT col1,col2,col3
    FROM OPENQUERY(MYSQL,'SELECT col1,col2,col3 FROM asterisk.tablename c;')...

    Is there any better Solution for this... and how can i measure Performance..

    Kindly Suggest something ..

    Regards

    BI_Group

    Saturday, August 11, 2012 6:38 AM
  • Hi,

    Whatever you have done will work, I feel below will be a better approach to do the same thing.

    You do not need to create a link server MYSQL, you can use connection yo MYSQL inside a SSIS. Kindly have a look at below link to see how to extract data from MYSQL and load it into SQL..

    http://www.mssqltips.com/sqlservertip/2613/export-data-from-mysql-to-sql-server/


    - Chintak (My Blog)

    Saturday, August 11, 2012 6:50 AM
  •  Chintak I tried DSN So many times But unbale to do on my server thats why i go for LinkedServer...

    Saturday, August 11, 2012 7:02 AM
  • Hi,

    Are you geeting any error message beck from DSN?

    See if you have insalled connector

    http://dev.mysql.com/downloads/connector/odbc/


    - Chintak (My Blog)

    Saturday, August 11, 2012 7:11 AM
  • thanx for this Chintak..

    Actully I'm Getting this error during connection..

    TITLE: Connection Manager
    ------------------------------

    Test connection failed because of an error in initializing provider. ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

    ------------------------------
    ------------------------------

    Saturday, August 11, 2012 8:33 AM
  • Is this still an issue?

    Thanks!


    Ed Price (a.k.a User Ed), SQL Server Customer Program Manager (Blog, Twitter, Wiki)

    Wednesday, December 5, 2012 7:22 AM