SQL: Generating Unique Identifier Keys

Create Unique Identifier column and add newid (NEWID()):

UPDATE [dbo].[DimManagement]
    SET [ManagementUniqueKey] = NEWID()
    ,[CartesisUniqueKey] = NEWID()

SSRS: IIS 7 and Reportviewer

In IIS 7, we need to add Reserved.ReportViewerWebControl.axd httpHandler.

Resolution:
Open Internet Information Services (IIS) Manager and select your Web application.
Under IIS area, double-click on Handler Mappings icon.
At the Action pane on your right, click on Add Managed Handler.
At the Add Managed Handler dialog, enter the following:Request path: Reserved.ReportViewerWebControl.axdType: Microsoft.Reporting.WebForms.HttpHandlerName: Reserved-ReportViewerWebControl-axd
Click OK.

Reserved-ReportViewerWebControl-axd handler is now added to your Handler Mappings list. Notice that the following line has also been added to your Web.config file under the system.webserver's handler section:


For more information, see http://otkfounder.blogspot.com/2007/11/solving-reportviewer-rendering-issue-on.html

http://forums.asp.net/p/1360494/2814290.aspx#2814290

Password Generator

http://www.OnlinePasswordGenerator.com

SSRS: Performance Tuning

1. Fix problem with slow booting up of report server:
- Open the report config file
- modify RecycleTime parameter from the default 720 to 28800.

SSIS: Logging Table

SELECT top 1000 *
FROM [dbo].[sysssislog]
order by starttime desc

SSIS: VBScript for Excel inserting 0 and get columns

/*
Microsoft SQL Server Integration Services Script Task
Write scripts using Microsoft Visual C# 2008.
The ScriptMain is the entry point class of the script.
*/
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Diagnostics;
namespace ST_f1f4f90e7d8e4a25b9330e1d946d8c95.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
/*
The execution engine calls this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples.
To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call Dts.Log("This is my log text", 999, null);
To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);
To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
To open Help, press F1.
*/
public void Main()
{
int error_flag = 0;
Dts.Variables["ErrorFileName"].Value = Dts.Variables["FileName"].Value.ToString().Replace(".xls", "_errors.xls");
//SET NUMERICS IN EXCEL FILE TO ZERO TO AVOID CRAPPY JET DRIVER INCORRECTLY SETTING COLUMN TYPES AT RUNTIME
string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=" + Dts.Variables["FileName"].Value.ToString() + @";
Extended Properties=
""Excel 8.0;HDR=YES;""";
System.Data.OleDb.OleDbConnection ExcelConnection =
new System.Data.OleDb.OleDbConnection
(ConnectionString);
string ExcelQuery;
List numericExcelColumns = new List{
//"[AnnualSalary]",
"[Salaries]",
"[Overtime]",
"[Pay In Lieu Of Holiday]",
"[Severances]",
"[Employers Social Taxes]",
"[SPOT Bonus]",
"[STIP Bonus]",
"[Contractual STIP]",
"[Earnout STIP]",
"[Sales Commision Bonus]",
"[Taxable Value Of PSA Payout]",
"[Taxable Value Of Other Restricted Payout]",
"[Taxable Value Of LEAP Payout]",
"[Taxable Value Of Worldwide Ownership Plan On Exercise Payout]",
"[Taxable Value Of Executive Option Plan On Exercise Payout]",
"[Pensions - Defined Contribution Schemes]",
"[Pensions - Defined Benefit Schemes]",
"[Medical Insurance]",
"[Life Assurance]",
"[PHI/LTD]",
"[Critical Illness]",
"[Car - Lease Or Depreciations]",
"[Car Allowance]",
"[Other Car]",
"[Housing]",
"[School Fees]",
"[COLA]",
"[Home Leave]",
"[Club Membership]",
"[Relocation Expenses]"};
try
{
System.Data.OleDb.OleDbCommand ExcelCommand;
//UPDATE
ExcelConnection.Open();
ExcelCommand = new System.Data.OleDb.OleDbCommand();
ExcelCommand.Connection = ExcelConnection;
int nocols = numericExcelColumns.Count;
//Fill DataTable with columns information
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", ExcelConnection);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet, "ExcelInfo");
//DataGrid1.DataSource = myDataSet.Tables["ExcelInfo"].DefaultView;
//DataGrid1.DataBind();
DataTable myDataTable = ExcelConnection.GetSchema("Columns");//, new string[] { "Test", "Tables" });
string columnName;
foreach (string excelColumn in numericExcelColumns)
{
//For every Column in the table
for (int i = 0; i < myDataSet.Tables[0].Columns.Count; i++)
{
//Get column name and type
columnName = myDataSet.Tables[0].Columns[i].ToString();
if ("[" + columnName + "]" == excelColumn)
{
error_flag = error_flag + 1;
break;
}
}
ExcelQuery = string.Format("update [Sheet1$] set {0} = '0' where {0} is null and EmployeeID is not null", excelColumn);
ExcelCommand.CommandText = ExcelQuery;
ExcelCommand.ExecuteNonQuery();
}
//ExcelQuery = "update [Sheet1$] set [SPOT Bonus] = '1' where len([SPOT Bonus]) = 0";//works
//ExcelQuery = "update [Sheet1$] set [SPOT Bonus] = '9' where [SPOT Bonus] = 1";//works
//ExcelQuery = "update [Sheet1$] set [SPOT Bonus] = '9' where [SPOT Bonus] is null";//works
//ExcelQuery = "update [Sheet1$] set [Overtime] = '0' where [Overtime] is null";//works
ExcelCommand = null;
ExcelConnection.Close();
}
catch (Exception e)
{
Debug.WriteLine(e.Message);
}
if (error_flag == 30)
{
Dts.TaskResult = (int)ScriptResults.Success;
}
else
{
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
}
}

ASP. NET: Retrieve Excel file columns

protected void btnImportExcelFile_Click(object sender, EventArgs e) {
string strConn; strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=C:\\exceltest.xls;" + "Extended Properties=Excel 8.0;"; //You must use the $ after the object you reference in the spreadsheet OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Tabelle1$]", strConn);
DataSet myDataSet = new DataSet(); myCommand.Fill(myDataSet, "ExcelInfo"); DataGrid1.DataSource = myDataSet.Tables["ExcelInfo"].DefaultView; DataGrid1.DataBind();
}