Wednesday, September 3, 2014

In recent past it happened for me to work with SSRS reporting and i had to extensively work with the ReportServer database for getting the details about the reports. But when i started working i came to know there is no proper documentation for the ReportSever database. In the internet some information is available and it is scattered all over. So thought of creating a small documentation for the SSRS ReportServer database tables.

As a first step , i have created SSRS ReportServer database diagram for analyzing the relationship between the tables. You can download the PDF version of the file from the below link.

Tuesday, September 2, 2014

Recently I got a scenario from my friend where she was
trying to migrate the query log from production to development. While trying to
create the aggregation design from usage based optimization wizard it was
showing the message “There are no queries in the log for selected measure
group”. She had already followed the below helpful link by updating the query
log table with proper server path.

With the above query we can
clearly verify the update in the query log is fine or not. In our case the
issue was with the update of the server name.One more point we noticed is the database name and solution name should be the same. The Object path was being sent with the solution name.

Friday, May 30, 2014

Recently i was working on a requirement on exporting the data into excel file. As the data may exceed more than millions the data needs to be split into multiple excel files as row limitation is 1048576 i.e, if the source returns 3 million rows the data needs to be split into 3 destination files.

For this i have used the script component destination in the data flow task to achieve this

Bucket Size - For how many records a new file needs to be created
Filename - Name of the file. Multiple files will be created with _fileno Ex- Sorna_1
FilePath - Path in which file will be created
Header Row - In each file header row needs to be added. For this demo it is hard coded. This also can be achieved dynamically using expressions or other mechanisms

Following namespaces needs to be added in the script component code

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.IO;
using Microsoft.CSharp;

Monday, April 7, 2014

Code: 0x00000001 Exception has been thrown by the target of an invocation error was thrown when I was trying access the excel
files from script task in SSIS. The SSIS package works fine when running from
development tool. But throws an error while called from SQL Server Agent
job. This can be fixed be following steps