2015-06-26

SSRS Replace Embedded Image Dynamically Part 2 - Do the Replacement

Continue to the part 1 of this topic, we are ready to start the coding task. But before start the coding task, we need at least a report for our job.

As you can see, I create a simple report in report server
When open the report from Report Builder, you can see nothing but a google image in the report. This is the report will be used for demonstration purpose



We need another image to do the replacement job, so I just grab a random image and put it into a temporary folder.



So everthing is ready, time to go back to the Visual Studio project and then start the coding.

Firstly, in the project I create the winform like below.



Controls used in the form are:
  • txtServer: the textbox to capture reporting server name
  • txtReportPath: the textbox to capture SSRS report path
  • btnSearch: the button to pull the image definition from the report
  • lstImages: the listbox control to hold images obtained from btnSearch
  • txtImagePath: the textbox to input the replace image path
  • btnReplace: the replace button to replace the image
The beginning of the coding area is like this, private variables to hold report definition and image index of the report

     Report report;  
     RS2010.ReportingService2010 reportservice;  
     int imgindex;  

No we can start to code the search button click event. The basic concept is to get the report definition from user input, then drill down until we find what we need. To demostrate what we need:



First of the first is to grab the report deinition, in my code I created a method to load report definition into previously declared report class variable:

     private void LoadReport(string path)  
     {  
       byte[] bytes = reportservice.GetItemDefinition(path);  
       if (bytes != null)  
       {  
         XmlSerializer serializer = new XmlSerializer(typeof(Report));  
         using (MemoryStream stream = new MemoryStream(bytes))  
         {  
           report = (Report)serializer.Deserialize(stream);  
         }  
       }  
     }  


Now we need to ind embedded image in the report definition. To do that, I started to work on the button click task:

 private void button1_Click(object sender, EventArgs e)  
     {  
       string servername = txtServer.Text;  
       reportservice = new ReportingService2010();  
       reportservice.Credentials = System.Net.CredentialCache.DefaultCredentials;  
       reportservice.Url = "http://" + servername + "/reportserver/reportservice2010.asmx";  
       string ReportPath = txtReportPath.Text;  
       LoadReport(ReportPath);  
       if (report == null)  
       {  
         MessageBox.Show("error, cannot load report definition");  
         return;  
       }  
       List<ItemsChoiceType118> reportItems = new List<ItemsChoiceType118>(report.ItemsElementName);  
       imgindex = reportItems.IndexOf(ItemsChoiceType118.EmbeddedImages);  
       if (imgindex < 1)  
       {  
         MessageBox.Show("cannot find image");  
         return;  
       }  
       EmbeddedImagesType imgs = (EmbeddedImagesType)report.Items[imgindex];  
       lstImages.DataSource = imgs.EmbeddedImage;  
       lstImages.DisplayMember = "Name";  
     }  

At this stage, if we try to debug the project by pressing F5, we should be able to get the image listed in the listbox, as shown below




Now stop the debug mode and return to the project. Time to code the replace event:

  private void btnReplace_Click(object sender, EventArgs e)  
     {  
       if (report == null || imgindex == 0)  
       {  
         return;  
       }  
       string base64;  
       Image replacement = Image.FromFile(txtImagePath.Text);  
       MemoryStream ms = new MemoryStream();  
       replacement.Save(ms, ImageFormat.Png);  
       base64 = Convert.ToBase64String(ms.ToArray());  
       ms.Close();  
       ms = null;  
       foreach (var item in ((EmbeddedImagesType)report.Items[imgindex]).EmbeddedImage)  
       {  
         item.Items[1] = base64;  
       }  
       XmlSerializer serializer = new XmlSerializer(typeof(Report));  
       using (MemoryStream stream = new MemoryStream())  
       {  
         serializer.Serialize(stream, report);  
         stream.Position = 0;  
         byte[] bytes = stream.ToArray();  
         Warning[] warnings = reportservice.SetItemDefinition(txtReportPath.Text, bytes, null);  
       }  
     }  

After we finish above code, it is ready to do the replacement:

The original report looks like this:



Now find the image embedded in this report, input the replacement image path in the textbox, and click replace button



 Go back to the report and refersh it, the image gets replaced :D




2015-06-18

SSRS Replace Embedded Image Dynamically Part 1 - Prepare the Project

Back to few months ago, one of our clients had a re-brand campaign. As a result, all of its related reports needs to be modified, because of the logo change.

As you know, in the native reporting service, there are three ways to hold an image on the report:
  • Embedded Image saved in the report
  • Database Stored Image saved in the database
  • External Image can be in a network shared folder or a web site hosted image

In my case, if the logo was created by using the 2nd way or the 3rd way, it is simple to change: just replace the referenced target. But there were some reports are using embedded image. It means we have to open the report in edit mode, and verify the image storage type, and then change it if applicable. Time consuming, isn't it?


If you google the question, an old post (here) gives a way to extract embedded images from the RS report. However I could not find the source code though author mentioned "you can download the source code using the link at the top" :(

But at least it gives me some thoughts. So I am going to show you how to dynamically replace the embedded images of the RS report through C# code.

Firstly, let's create the project and add references we need.



Now to simplify our job, we need to grab the report definition from Microsoft. You can find the definition here. Open this xsd in the web broswer and save this into the project folder.


Time to generate class definition from our downloaded XSD. Open Visual Studio command prompt window, navigate to the project folder, and type

xsd /c /n:SampleRDLSchema ReportDefinition.xsd

The cs file should be generated within the project folder.


Now we add this newly created class file into our project, in my project, it is ReportDefinition.cs


Next step is to add web service reference to our reporting service. In the project, right click Service References folder and select "add service references"


On the pop up window, click "Advanced..." button located at the bottom right corner.


now we get the 2nd pop up window, at the bottom of this window, click button "Add Web References"


Finally we reach the window we want. in this window, input our reporting service address, it should be http://<server>/reportserver/reportservice2010.asmx. As you can see in above window I named it as RS2010.

After click "Add Reference" button, your project should look like below


Before we do some coding jobs, a finaly step is to add using clauses we need:

using System.IO;
using SSRS_ReplaceImages.RS2010;
using System.Xml.Serialization;
using System.Drawing.Imaging;


After you add these using clauses, we are ready to replace the image in the report, which will be covered in my next post. :P






2015-06-03

Create SSIS Custom Control Flow Task Part 4 - Something You Need To Know

Today I am going to put an end to this custom control flow task topic. In my previous posts (Part 1 (Project Creation), Part 2(Configuration), Part3 (Coding)) I have presented you a basic solution template. Now I want to talk about some "advanced" topic

List variables on the task UI

This is the most common requirement when designing a SSIS component. Doesn't matter it is a connection manager, a control flow task, a log provider, or a data flow task, you want to give developers some kinds of flexibilities to config the task dynamically.

The variables of the SSIS package are stored in the TaskHost class, so to list variables on your task UI, we can use below codes:

       foreach (Variable var in TaskHostValue.Variables)  
       {  
         if (!var.SystemVariable)  
         {  
           cbx.Items.Add(var.QualifiedName);  
         }  
       }  

It will retrieve all variables stored in the TaskHost class, and if the variable is not a system variable, add it into a combo box (cbx).

Read variable value during the execution phase

Once we done the configuration through our custom UI, certainly we need to get the stored value of the variable during the task execution phase. It actually follows the same way when we do the script task:

     public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)  
 public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)  
     {                
       Variables varFileFormat = null;  
       variableDispenser.LockOneForRead(FileFormat, ref varFileFormat);         
     }  


In codes listed above, we locked the variable FileFormat stored in VariableDispenser and then read the value from it.

Pass complex configuration setting

Sometimes, actually I think most times, the configuration on the custom UI could be quite complex. To pass the configuration setting to the custom task, you may want to use some easy ways to do it, for example, a data table. Then in the execution phase, we simply need to read row by row to get the setting as a whole.

However bear in mind, firstly only simple types can be passed into the task. Here the simple types refer to string type and numeric type.

Now a bit explanation: SSIS package actually will be saved in XML format. So if you store the configuration setting in a complex object type, SSIS won't know how to save it in XML format. Therefore only simple types can be passed into the custom task.

So to allow the setting, for example, a data table, can be saved, at the Task project, we need to implement IDTSComponentPersist interface. Under this interface, we have SaveToXml and LoadFromXML methods, which will tell the package how to save the setting and how to read the setting.

However, to implement IDTSComponentPersist interface, you cannot use "short hand" method to write class properties, for example

     //below is not allowed  
     public string Server { get; set; }  

This style, if I am correct, was first introduced in C#3.0. Basically when we write code in this style, the backend compiler handles the property, create a temp object to automate the property implementation , etc. etc. (Well, I am not professional in programming... :( So correct me if I am wrong)

So you might understand now, because backend compiler will create the temp object, the task cannot really identify the relationship between the temp object and the actual property. So to implement the interface, we need to have the getter and setter implemented explictly.

To save your time, I put one of my implementations below.

SaveToXML
It reads a datatable object DtParameters and then create XML tree nodes based on data rows.

     //save into package  
     public void SaveToXML(System.Xml.XmlDocument doc, IDTSInfoEvents infoEvents)  
     {  
       XmlElement elementRoot;  
       XmlNode propertyNode;  
       elementRoot = doc.CreateElement(string.Empty, "DtParameters", string.Empty);  
       if (DtParameters != null)  
       {  
         if (DtParameters.Rows.Count > 0)  
         {  
           //  
           foreach (DataRow row in DtParameters.Rows)  
           {  
             propertyNode = doc.CreateNode(XmlNodeType.Element, "Parameter", string.Empty);  
             propertyNode.InnerText = row["ParameterValue"].ToString();  
             XmlAttribute attrParameterName = doc.CreateAttribute("ParameterName");  
             attrParameterName.Value = row["ParameterName"].ToString();  
             propertyNode.Attributes.Append(attrParameterName);  
             XmlAttribute attrRequired = doc.CreateAttribute("Required");  
             attrRequired.Value = row["Required"].ToString();  
             propertyNode.Attributes.Append(attrRequired);  
             elementRoot.AppendChild(propertyNode);  
           }  
         }  
       }        
       propertyNode = doc.CreateNode(XmlNodeType.Element, "Server", string.Empty);  
       propertyNode.InnerText = Server;  
       elementRoot.AppendChild(propertyNode);  
       propertyNode = doc.CreateNode(XmlNodeType.Element, "SelectedReport", string.Empty);  
       propertyNode.InnerText = SelectedReport;  
       elementRoot.AppendChild(propertyNode);  
       propertyNode = doc.CreateNode(XmlNodeType.Element, "FileName", string.Empty);  
       propertyNode.InnerText = FileName;  
       elementRoot.AppendChild(propertyNode);  
       propertyNode = doc.CreateNode(XmlNodeType.Element, "FileFormat", string.Empty);  
       propertyNode.InnerText = FileFormat;  
       elementRoot.AppendChild(propertyNode);  
       doc.AppendChild(elementRoot);  
     }  

LoadFromXML
It read the package XML node and get the saved parameter values into the datatable _dtparameters

     //load from package  
     public void LoadFromXML(System.Xml.XmlElement node, IDTSInfoEvents infoEvents)  
     {  
       _dtparameters = new DataTable();  
       _dtparameters.Columns.Add("ParameterName", typeof(string));  
       _dtparameters.Columns.Add("Required", typeof(bool));  
       _dtparameters.Columns.Add("ParameterValue", typeof(string));  
       foreach (XmlNode n in node.ChildNodes)  
       {  
         if (n.Name == "Server")  
         {  
           _server = n.InnerText;  
         }  
         if (n.Name == "SelectedReport")  
         {  
           _selectedreport = n.InnerText;  
         }  
         if (n.Name == "FileName")  
         {  
           _filename = n.InnerText;  
         }  
         if (n.Name == "FileFormat")  
         {  
           _fileformat = n.InnerText;  
         }  
         if (n.Name == "Parameter")  
         {  
           string parametername = n.Attributes.GetNamedItem("ParameterName").Value;  
           bool required = Convert.ToBoolean(n.Attributes.GetNamedItem("Required").Value);  
           string parametervalue = n.InnerText;            
           _dtparameters.Rows.Add(parametername, required, parametervalue);  
         }  
       }  
     }  

So now this topic is done! :D