locked
Import sql reports to table in new database RRS feed

  • Question

  • we are currently migrating from a system we use to a similar product from a different company. Before we start using the new product, we need to migrate the data from the old server to the new one. This is fine, but obviously the column names, table names, and data types are all different. What the old server does have on it, is SSRS. We have configured a few reports to extract the data and present it in table format with the column names in the right order and with the right names. The question is, is there an easy way to import these into a database without downloading each report as a csv and importing them one by one. Not only is this tedious due to the large amount of tables, but when using this method, I get all sorts of errors where the data type conversion has failed. I will buy cake for whoever solves this for me!
    Thursday, November 5, 2015 2:19 PM

Answers

  • Then you get the error because of the usage of different data types in source and destination database; you have to convert the data within a SSIS package.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, November 5, 2015 3:36 PM

All replies

  • If you have the original Visual Studio Project files to hand then you should be able to use these to publish the Reports to the new Server after a few tweaks: -

    1. Copy the Project(s) to the new Server.
    2. Open each one in turn.
    3. Append the dataset settings to connect to the correct Server(s)
    4. Alter the publication path
    5. Publish the Reports.

    If you do not have the Project Files to hand then you can: -


    Please click "Mark As Answer" if my post helped. Tony C.

    • Proposed as answer by Ice Fan Tuesday, November 17, 2015 5:26 AM
    Thursday, November 5, 2015 2:43 PM
  • Hello,

    With reports you can not copy data to a SQL Server data base ... or only with a lot of manually work.

    Why don't you use SSIS or Import/Export Wizard for this; in both you can mapp the source to destination columns?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, November 5, 2015 2:48 PM
  • sorry, will this migrate the reports or will it migrate the data produced by the report?
    Thursday, November 5, 2015 3:27 PM
  • The issue I'm having with this, is the fact that it keeps erroring out. I'm getting errors because of truncation and conversion errors amongst others.
    Thursday, November 5, 2015 3:30 PM
  • Then you get the error because of the usage of different data types in source and destination database; you have to convert the data within a SSIS package.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, November 5, 2015 3:36 PM