MERGE with SSIS

Use exec sp_executesql N’merge !!!! instead of direct Merge statement in Execute SQL Task

Report Styles

http://www.simple-talk.com/sql/reporting-services/reporting-services-with-style/

Publish multiple .rdl files to Reporting services - First Way Creating MSI for reporting Services

Create a Class library project and add the web references to http://localhost/ReportServer/ReportService2005.asmx.

Add the following code to the Class1.Cs
using System;
using System.Collections.Generic;
using System.Text;
using ClassLibrary1.ReportService2005;
using System.IO;
using System.Configuration.Install;
using System.ComponentModel;

namespace InstallerNameSpace
{

// Set 'RunInstaller' attribute to true.
[RunInstallerAttribute(true)]
public class InstallerClass : System.Configuration.Install.Installer
{
public InstallerClass()
: base()
{
}

// Override the 'Install' method.
// The Installation will call this method to run the Custom Action
public override void Install(System.Collections.IDictionary savedState)
{
base.Install(savedState);


//// get parameters for custom install of data source and report
string dataSourceName = this.Context.Parameters["DSN"];
string dataSourceLocation = this.Context.Parameters["DSL"];
string sqlServerName = this.Context.Parameters["DSSERVERNAME"];
string dbName = this.Context.Parameters["DBNAME"];
string uid = this.Context.Parameters["UID"];
string pwd = this.Context.Parameters["PWD"];

ReportingService2005 rs = new ReportingService2005();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

//Create Folder to drop report in
rs.CreateFolder("MSI Report Demo", "/", null);

//Create Report using RDL doc that was in MSI

string reportPath = @"SampleReport.rdl";
byte[] reportDefinition;
ClassLibrary1.ReportService2005.Warning[] warnings;
FileStream stream = File.OpenRead(@"c:\temp\SampleReport.rdl");
reportDefinition = new Byte[stream.Length];
stream.Read(reportDefinition, 0, (int)stream.Length);
stream.Close();

warnings = (ClassLibrary1.ReportService2005.Warning[])rs.CreateReport(reportPath.Remove(reportPath.Length - 4, 4), @"/MSI Report Demo", true, reportDefinition, null);

//Create DataSource that Report will Use

DataSource dSource = new DataSource();
DataSourceDefinition dDefinition = new DataSourceDefinition();

dSource.Item = dDefinition;
dDefinition.Extension = "OLEDB-MD";
dDefinition.ConnectString = @"Data Source=" + sqlServerName + @";Initial Catalog=" + dbName;
System.Diagnostics.Trace.WriteLine(dDefinition.ConnectString.ToString());
dDefinition.ImpersonateUserSpecified = true;
dDefinition.Prompt = null;
dDefinition.WindowsCredentials = true;
dDefinition.UserName = uid;
dDefinition.Password = pwd;
dDefinition.CredentialRetrieval = CredentialRetrievalEnum.Store;
dSource.Name = dataSourceName;
try
{
rs.CreateDataSource(dataSourceName, dataSourceLocation, false, dDefinition, null);
}

catch (System.Web.Services.Protocols.SoapException ex)
{
Console.WriteLine(ex.Detail.InnerXml.ToString());
}

// Report and Datasource created, now fix up datasource reference to make sure report points at correct ds
System.Diagnostics.Trace.WriteLine("DS Created");
try
{

DataSourceReference reference = new DataSourceReference();
DataSource ds = new DataSource();
reference.Reference = dataSourceLocation + @"/" + dataSourceName;
DataSource[] dsarray = rs.GetItemDataSources(@"/MSI Report Demo/SampleReport");
ds = dsarray[0];
ds.Item = (DataSourceReference)reference;
rs.SetItemDataSources(@"/MSI Report Demo/SampleReport", dsarray);
}
catch (System.Web.Services.Protocols.SoapException ex)
{
Console.WriteLine(ex.Detail.InnerXml.ToString());
}
System.IO.File.Delete(@"c:\temp\Sample Report.rdl");
}




}
}

More Info @ http://blogs.msdn.com/bimusings/archive/2006/03/01/541599.aspx