use script component to extract url parameter value during ETL process

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        int name_length_limit = 255;
        int value_length_limit = 2000;
        string name, value;
        string[] arr = BlobToString(Row.parameter).Split('&');
        foreach (string str in arr)
        {
            if (str.Trim().Length > 0)
            {
                ResultBuffer.AddRow();
                ResultBuffer.id = Row.id;
                ResultBuffer.ts = Row.ts;

                string[] values = str.Trim().Split('=');
                name = decode(values[0]);
                if(name.Length > name_length_limit)
                    name = name.Substring(0,name_length_limit);

                ResultBuffer.parametername = name;

                if (values.Length > 1)
                {
                    value = decode(values[1]);
                    if(value.Length > value_length_limit)
                        value = value.Substring(0,value_length_limit);
                    ResultBuffer.parametervalue = value;
                }
            }
        }
    }

    private string BlobToString(BlobColumn blob)
    {
        string result = "";
        try
        {
            if (blob != null)
            {
                result = System.Text.Encoding.ASCII.GetString(blob.GetBlobData(0, Convert.ToInt32(blob.Length)));
            }
        }
        catch (Exception ex)
        {
            result = ex.Message;
        }
        return result;
    }

    public string decode(string s)
    {
        int charValue;
        int b1, b2, b3;
        Decoder utf8Decoder = Encoding.UTF8.GetDecoder();
        Char[] chars = new Char[1];
        s = s.Replace("+", " ");
        Byte[] threeByte = new Byte[] { 99, 99, 99 };
        for (int i = 0; i < s.Length; i++)
        {
            if (s[i] == '%' && s.Length >= i+3 )
            {
                b1 = int.Parse(s.Substring(i + 1, 2), System.Globalization.NumberStyles.HexNumber);
                if (b1 < 127)
                {
                    s = s.Remove(i, 3);
                    s = s.Insert(i, Convert.ToChar(b1).ToString());
                }
                if ((b1 > 193) && (b1 < 224))
                {
                    threeByte[0] = byte.Parse(b1.ToString());
                    s = s.Remove(i, 3);
                    b2 = int.Parse(s.Substring(i + 1, 2), System.Globalization.NumberStyles.HexNumber);
                    threeByte[1] = byte.Parse(b2.ToString());
                    s = s.Remove(i, 3);
                    charValue = utf8Decoder.GetChars(threeByte, 0, 2, chars, 0);
                    s = s.Insert(i, chars[0].ToString());

                }
                if ((b1 > 223) && (b1 < 240))
                {
                    threeByte[0] = byte.Parse(b1.ToString());
                    s = s.Remove(i, 3);
                    b2 = int.Parse(s.Substring(i + 1, 2), System.Globalization.NumberStyles.HexNumber);
                    threeByte[1] = byte.Parse(b2.ToString());
                    s = s.Remove(i, 3);
                    b3 = int.Parse(s.Substring(i + 1, 2), System.Globalization.NumberStyles.HexNumber);
                    threeByte[2] = byte.Parse(b3.ToString());
                    s = s.Remove(i, 3);
                    charValue = utf8Decoder.GetChars(threeByte, 0, 3, chars, 0);
                    s = s.Insert(i, chars[0].ToString());
                }
            }
        }
        return s.Trim();
    }

database file usage

SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files

Get data size of all tables in Sql server database

CREATE
TABLE #temp (    
[table_name] NVARCHAR(128),  
[rows] CHAR(11),   
reserved VARCHAR(18),    
data VARCHAR(18),    
index_size VARCHAR(18),   
unused VARCHAR(18))

INSERT #temp EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

Using C# to export Reporting Services report to Excel

code copied from: http://msdn.microsoft.com/en-us/library/reportexecution2005.reportexecutionservice.render(v=sql.100).aspx

Add Reference: Microsoft.ReportViewer.WebForms

Using Microsoft.Reporting.WebForms.Internal.Soap.ReportingServices2005.Execution;

ReportExecutionService rs = new ReportExecutionService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
rs.Url = "http://servername/reportserver/ReportExecution2005.asmx";
 
// Render arguments
byte[] result = null;
string reportPath = "/folder/reportname";
string format = "EXCEL";
string historyID = null;
string devInfo = @"False";
 
// Prepare report parameter.
ParameterValue[] parameters = new ParameterValue[1];
parameters[0] = new ParameterValue();
parameters[0].Name = "Date";
parameters[0].Value = "2012-05-07";
 
DataSourceCredentials[] credentials = null;
string showHideToggle = null;
string encoding;
string mimeType;
string extension;
Warning[] warnings = null;
ParameterValue[] reportHistoryParameters = null;
string[] streamIDs = null;
 
ExecutionInfo execInfo = new ExecutionInfo();
ExecutionHeader execHeader = new ExecutionHeader();
 
rs.ExecutionHeaderValue = execHeader;
 
execInfo = rs.LoadReport(reportPath, historyID);
 
rs.SetExecutionParameters(parameters, "en-us");

String SessionId = rs.ExecutionHeaderValue.ExecutionID;
 
Console.WriteLine("SessionID: {0}", rs.ExecutionHeaderValue.ExecutionID);
 
 
try
{
    result = rs.Render(format, devInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);
 
    execInfo = rs.GetExecutionInfo();
 
    Console.WriteLine("Execution date and time: {0}", execInfo.ExecutionDateTime);
 
 
}
catch (SoapException e)
{
    Console.WriteLine(e.Detail.OuterXml);
}
// Write the contents of the report to an MHTML file.
try
{
    FileStream stream = File.Create("report.xls", result.Length);
    Console.WriteLine("File created.");
    stream.Write(result, 0, result.Length);
    Console.WriteLine("Result written to the file.");
    stream.Close();
}
catch (Exception e)
{
    Console.WriteLine(e.Message);
}

How to delete duplicate rows in SQL Server

set rowcount 1
delete from table_name
where id = 1

SSIS from mysql to SQL Server, create query to handle zero datetime

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC etl.get_create_select_query (@schema varchar(MAX), @table varchar(MAX))
AS BEGIN
DECLARE @sql VARCHAR(max)
DECLARE @max_id BIGINT
DECLARE @tbl TABLE (id BIGINT)
INSERT INTO @tbl
EXEC ('select isnull(max('+@table+'_id),0) from '+@schema+'.'+@table)
SELECT @max_id = id FROM @tbl
SET @sql =
'SELECT group_concat(CONCAT(
CASE WHEN ordinal_position = 1 THEN ''SELECT '' ELSE '', '' END
, CASE Data_Type WHEN ''date'' THEN CONCAT(''CASE '',column_name,'' WHEN ''''0000-00-00'''' THEN NULL else '' ,column_name,'' END as '',column_name)
WHEN ''datetime'' THEN CONCAT(''CASE '', column_name,'' WHEN ''''0000-00-00 00:00:00'''' THEN NULL ELSE '', column_name,'' END as '',column_name)
WHEN ''time'' THEN CONCAT(''CAST('',column_name,'' AS CHAR(8) ) as '',column_name)
ELSE column_name END
, CASE WHEN Ordinal_Position <> MaxOrd THEN '''' ELSE CONCAT('' FROM '+@schema+'.'', c.table_name, '' where '+@table+'_id > '+CAST(@max_id AS VARCHAR(MAX))+''') END
) SEPARATOR '' '') select_query
from information_schema.columns as c
JOIN (
select table_name
, MAX(ordinal_position) as MaxOrd
from information_schema.columns
WHERE Table_Schema = '''+@schema+'''
and table_name = '''+@table+'''
GROUP BY Table_Name
) as t1
ON c.Table_Name = t1.Table_Name
where table_schema = '''+@schema+'''
order by c.table_name, ordinal_position'
SELECT @sql
END