C#: Generate Scripts for database for SQL Server

— Generate Scripts for database objects with SMO for SQL Server

Generate Scripts for database objects with SMO for SQL Server

https://www.mssqltips.com/sqlservertip/1826/getting-started-with-sql-server-management-objects-smo/

 

/*working code*/

public void Main()public void Main()

{

ETLControlDetails etl =GetETLControlDetails();

GenerateDropTableScript(etl);

GenerateCreateTableScript(etl);

MessageBox.Show(sb.ToString());

}

/*working code*/

StringBuilder sb = new StringBuilder();

#region public struct ETLControlDetails
public struct ETLControlDetails
{
public string Server { get; set; }
public string SourceServer { get; set; }
public string SourceInstance { get; set; }
public string SourceDatabase { get; set; }
public string SourceSchema { get; set; }
public string SourceTable { get; set; }
}
#endregion

 

#region public ETLControlDetails GetETLControlDetails()
public ETLControlDetails GetETLControlDetails()
{

ETLControlDetails etl = new ETLControlDetails();
etl.Server = Dts.Variables[“User::SourceServer”].Value.ToString() + “\\” + Dts.Variables[“User::SourceInstance”].Value.ToString();
etl.SourceServer = Dts.Variables[“User::SourceServer”].Value.ToString();
etl.SourceInstance = Dts.Variables[“User::SourceInstance”].Value.ToString();
etl.SourceDatabase = Dts.Variables[“User::SourceDatabase”].Value.ToString();
etl.SourceSchema = Dts.Variables[“User::SourceSchema”].Value.ToString();
etl.SourceTable = Dts.Variables[“User::SourceTable”].Value.ToString();
return etl;
}
#endregion

#region public void ConnectToServer(string server)
public Server ConnectToServer(string server)
{
Server myServer = new Server(server);
try
{
/*Using windows authentication*/
myServer.ConnectionContext.LoginSecure = true;
myServer.ConnectionContext.Connect();
////
//Do your work
////
if (myServer.ConnectionContext.IsOpen)
myServer.ConnectionContext.Disconnect();
///*Using SQL Server authentication*/
//myServer.ConnectionContext.LoginSecure = false;
//myServer.ConnectionContext.Login = “SQLLogin”;
//myServer.ConnectionContext.Password = “entry@2008”;
LogInformation(“ConnectToServer”, String.Format(“Connected to the server {0}”, server));
}
catch (Exception e)
{
LogError(“ConnectToServer – Failed connecting to the server”, e);
}
return myServer;
}
#endregion

 

#region public void GenerateCreateTableScript(ETLControlDetails etl)
public void GenerateCreateTableScript(ETLControlDetails etl)
{
/*generate create table script for a individual table */
Server server = new Server(etl.Server);
Database db = server.Databases[etl.SourceDatabase.ToString()];
List<Urn> list = new List<Urn>();
DataTable dataTable = db.EnumObjects(DatabaseObjectTypes.Table);

foreach (DataRow row in dataTable.Rows)
{
list.Add(new Urn((string)row[“Urn”]));
}
Scripter scripter = new Scripter();
scripter.Server = server;
scripter.Options.IncludeHeaders = true;
scripter.Options.IncludeIfNotExists = true;
scripter.Options.SchemaQualify = true;
scripter.Options.SchemaQualifyForeignKeysReferences = true;
scripter.Options.NoCollation = true;
scripter.Options.DriAllConstraints = true;
scripter.Options.DriAll = true;
scripter.Options.DriAllKeys = true;
scripter.Options.DriIndexes = true;
scripter.Options.ClusteredIndexes = true;
scripter.Options.NonClusteredIndexes = true;
scripter.Options.ToFileOnly = true;
//scripter.Options.FileName = @”C:\Download\script_custom.sql”;
/*get the all the tables for a database*/
//scripter.Script(list.ToArray());

/*get a particular table script only*/
Table myTable = db.Tables[etl.SourceTable, etl.SourceSchema];
//scripter.Script(new Urn[] { myTable.Urn});

StringCollection sc = scripter.Script(new Urn[] { myTable.Urn });
foreach (string script in sc)
{
sb.AppendLine();
sb.AppendLine(“–create table”);
sb.Append(script + “;”);
}
}
#endregion

 

#region public void GenerateCreateTableScript_V2(ETLControlDetails etl)
public void GenerateCreateTableScript_V2(ETLControlDetails etl)
{
//=================================================
Server myServer = ConnectToServer(etl.Server);

Scripter scripter = new Scripter(myServer);
Database db = myServer.Databases[etl.SourceDatabase.ToString()];

Table myTable = db.Tables[etl.SourceTable, etl.SourceSchema];
/* With ScriptingOptions you can specify different scripting
* options, for example to include IF NOT EXISTS, DROP
* statements, output location etc*/
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.IncludeHeaders = true;
scriptOptions.ScriptDrops = true;
scriptOptions.IncludeIfNotExists = true;
scriptOptions.WithDependencies = true;
scriptOptions.DriAll = true;
/* You can optionally choose each DRI object separately as given below */
scriptOptions.DriAllConstraints = true;
scriptOptions.DriAllKeys = true;
scriptOptions.DriChecks = true;
scriptOptions.DriClustered = true;
scriptOptions.DriDefaults = true;
scriptOptions.DriForeignKeys = true;
scriptOptions.DriIndexes = true;
scriptOptions.DriNonClustered = true;
scriptOptions.DriPrimaryKey = true;
scriptOptions.DriUniqueKeys = true;
scriptOptions.FileName = @”C:\Download\script_custom.sql”;

/* Generating IF EXISTS and DROP command for tables */
StringCollection tableScripts = myTable.Script(scriptOptions);
foreach (string script in tableScripts)
{
sb.AppendLine();
sb.AppendLine(“–create table”);
sb.Append(script + “;”);
//MessageBox.Show(script);
}
/* Generating CREATE TABLE command */
tableScripts = myTable.Script();
foreach (string script in tableScripts)
{
sb.AppendLine();
sb.AppendLine(“–create table”);
sb.Append(script + “;”);
//MessageBox.Show(script);
}
}
#endregion

 

#region public void GenerateDropTableScript(ETLControlDetails etl)
public void GenerateDropTableScript(ETLControlDetails etl)
{
/*generate drop table script for a individual table*/
Server server = new Server(etl.Server);
Database db = server.Databases[etl.SourceDatabase.ToString()];
List<Urn> list = new List<Urn>();
DataTable dataTable = db.EnumObjects(DatabaseObjectTypes.Table);

foreach (DataRow row in dataTable.Rows)
{
list.Add(new Urn((string)row[“Urn”]));
}
Scripter scripter = new Scripter();
scripter.Server = server;
scripter.Options.IncludeHeaders = true;
scripter.Options.IncludeIfNotExists = true;
scripter.Options.ScriptDrops = true;
scripter.Options.SchemaQualify = true;
//scripter.Options.FileName = @”C:\Download\script_custom.sql”;
/*get the all the tables for a database*/
//scripter.Script(list.ToArray());

/*get a particular table script only*/
Table myTable = db.Tables[etl.SourceTable, etl.SourceSchema];
//scripter.Script(new Urn[] { myTable.Urn });

StringCollection sc = scripter.Script(new Urn[] { myTable.Urn });
foreach (string script in sc)
{
sb.AppendLine();
sb.AppendLine(“–script option”);
sb.Append(script + “;”);
}

}
#endregion

 

Source from online:

Problem
In my last article, Getting started with SQL Server Management Objects (SMO), I discussed what SMO is, how you can start working with SMO, how to connect to SQL server, how to enumerate through the different SQL objects, create a database, create a table etc, all programmatically.

In this tip I would like to take you on an SMO ride to generate SQL object scripts programmatically. Though you can do this through SQL Server Management Studio (SSMS) there might be times (more details on usage scenarios given below) when you would need to create SQL scripts automatically.

Solution
As I discussed in my last tip, SQL Server objects are represented as object hierarchies inside SMO, for example a Server object is a collection of Database objects. A Database object is a collection of a Table (though there are couple of other collection inside the Database object as well such as Stored Procedure, Views, User-defined Functions etc).  A Table  is a collection of a Column and so on.

Every object in this hierarchy has a method called a script, which returns a string collection of scripts. Apart from that, SMO provides a utility class, Scripter, which generates the script in a more efficient way. For example, the Scripter class can discover the relationships between objects and can provide scripts for dependencies as well and it can respond to Progress and Error events.

Usage Scenario

As I said, SQL Server Management Studio (SSMS) provides a wizard type interface to script out all or selected objects, but there might be some scenarios, some of them are discussed below, where you would consider the use of SMO instead of SSMS.

  • You want to create a tool which will automatically set up (or sync) an environment for dev or test which resembles the production environment.
  • You have a data warehouse database, in this database data is not that important (or even the data size is so big, taking a backup of the entire database would not be feasible) as data can again be pulled from the source systems but you want to make sure the schema objects are scripted and backed up automatically so that you can re-create the database in case of a disaster.
  • Programmatically you want to control the backup and restore process of database administration (I will cover this in more detail in another tip “Backup and Restore Programmatically with SMO”).
  • Programmatically you want to transfer a database schema and data to another instance of SQL Server. (I will cover this in more detail in another tip “Transferring schema objects and data programmatically with SMO”).

Example

Before you start writing your code using SMO, you need to take reference of several assemblies which contain different namespaces to work with SMO. For more details on what these assemblies are and how to reference them in your code, refer to my tip Getting started with SQL Server Management Objects (SMO).

C# Code Block 1 – Here I am using the Scripter utility class to generate the script for two selected databases. Two Databaseobjects are created first; one of them refers to the AdventureWorks database and another one refers to AdventureWorksDW.  The script method of the scripter object is called which takes database object URN (Unique Resource Name) as input and returns a string collection of scripts. URN is a new concept in SMO (this was not available in SQL-DMO) which provides similar notation like XPath to denote object hierarchy.

C# Code Block 1 – Generating Database Script

Scripter scripter = new Scripter(myServer);
Database myAdventureWorks = myServer.Databases[“AdventureWorks”];
Database myAdventureWorksDW = myServer.Databases[“AdventureWorksDW”];
Urn[] DatabaseURNs = new Urn[] { myAdventureWorks.Urn, myAdventureWorksDW.Urn };
StringCollection scriptCollection = scripter.Script(DatabaseURNs);
foreach (string script in scriptCollection)
Console.WriteLine(script);

C# Code Block 2 – In this code block I am generating CREATE TABLE scripts for all the tables in the AdventureWorks database. As said before, a database is a collection of tables, so I am enumerating through the table collection of the database to generate a script for each table. Along with that I am also using the ScriptOptions class to specify the different scripting options, for example in this code I am scripting IF NOT EXISTS and DROP TABLE scripts as well.

C# Code Block 2 – Generating scripts for table collection

Scripter scripter = new Scripter(myServer);

Database myAdventureWorks = myServer.Databases[“AdventureWorks”];
/* With ScriptingOptions you can specify different scripting
* options, for example to include IF NOT EXISTS, DROP
* statements, output location etc*/
scriptOptions = new ScriptingOptions();
scriptOptions.ScriptDrops = true;
scriptOptions.IncludeIfNotExists = true;
foreach (Table myTable in myAdventureWorks.Tables)
{
/* Generating IF EXISTS and DROP command for tables */
StringCollection tableScripts = myTable.Script(scriptOptions);
foreach (string script in tableScripts)
Console.WriteLine(script);

/* Generating CREATE TABLE command */
tableScripts = myTable.Script();
foreach (string script in tableScripts)
Console.WriteLine(script);
}

C# Code Block 3 – This code block further extends the use of the Scripter and ScriptOptions classes to generate a script for a table (HumanResources.EmployeeAddress) along with all the other objects on which this table depends on. The ScriptOptionsclass also provides several properties for DRI (Declarative Referential Integrity) objects. You can either select individual DRI objects or all to script out along with the main object script.

C# Code Block 3
Generating table script with all dependencies and DRI objects

Scripter scripter = new Scripter(myServer);
Database myAdventureWorks = myServer.Databases[“AdventureWorks”];
Table myTable = myAdventureWorks.Tables[“EmployeeAddress”, “HumanResources”];/* Generate Scripts of table along with for all
* objects on which this table depends on */
ScriptingOptions scriptOptionsForDependendencies = new ScriptingOptions();
scriptOptionsForDependendencies.WithDependencies = true;
/* DriAll will include all DRI objects in the generated script. */
scriptOptionsForDependendencies.DriAll = true;
/* You can optionally choose each DRI object separately as given below */
//scriptOptionsForDependendencies.DriAllConstraints = true;
//scriptOptionsForDependendencies.DriAllKeys = true;
//scriptOptionsForDependendencies.DriChecks = true;
//scriptOptionsForDependendencies.DriClustered = true;
//scriptOptionsForDependendencies.DriDefaults = true;
//scriptOptionsForDependendencies.DriForeignKeys = true;
//scriptOptionsForDependendencies.DriIndexes = true;
//scriptOptionsForDependendencies.DriNonClustered = true;
//scriptOptionsForDependendencies.DriPrimaryKey = true;
//scriptOptionsForDependendencies.DriUniqueKeys = true;/* If you can use FileName to output generated script in a file
* Note : You need to have access on the specified location*/
scriptOptionsForDependendencies.FileName = @”D:\TableScriptWithDependencies.sql”;
StringCollection tableScripts = myTable.Script(scriptOptionsForDependendencies);
foreach (string script in tableScripts)
Console.WriteLine(script);

C# Code Block 4 – This code block provides CREATE TABLE scripts for all the AdventureWorks’ tables along with all the indexes of each table. Here I am using IndexCollection class to enumerate through index collection of the table to generate CREATE INDEX scripts for all the indexes on the given table.

C# Code Block 4
Generating scripts for table collection along with all the indexes on the table

Scripter scripter = new Scripter(myServer);
Database myAdventureWorks = myServer.Databases[“AdventureWorks”];
/* With ScriptingOptions you can specify different scripting
* options, for example to include IF NOT EXISTS, DROP
* statements, output location etc*/
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.ScriptDrops = true;
scriptOptions.IncludeIfNotExists = true;foreach (Table myTable in myAdventureWorks.Tables)
{
/* Generating IF EXISTS and DROP command for tables */
StringCollection tableScripts = myTable.Script(scriptOptions);
foreach (string script in tableScripts)
Console.WriteLine(script);/* Generating CREATE TABLE command */
tableScripts = myTable.Script();
foreach (string script in tableScripts)
Console.WriteLine(script);IndexCollection indexCol = myTable.Indexes;
foreach (Index myIndex in myTable.Indexes)
{
/* Generating IF EXISTS and DROP command for table indexes */
StringCollection indexScripts = myIndex.Script(scriptOptions);
foreach (string script in indexScripts)
Console.WriteLine(script);

/* Generating CREATE INDEX command for table indexes */
indexScripts = myIndex.Script();
foreach (string script in indexScripts)
Console.WriteLine(script);
}
}

C# Code Block 5 – In my last tip on SMO, I showed you how you can use SMO to create a database and a table on the server programmatically. In this code block, I am using the same code, but this time before creating the objects on the server I am generating database and table scripts. This means it is not required to create objects on the server in order to generate scripts, if you have objects in memory you can generate scripts for these objects as well even without creating on the server.

 

C# Code Block 5
Generating Script for objects in memory even before creating on the server

/* Create database called, “MyNewDatabase” */
Database myDatabase = new Database(myServer, “MyNewDatabase”);/* Output the database script on the console */
StringCollection DBScripts = myDatabase.Script();
foreach (string script in DBScripts)
Console.WriteLine(script);/* Create a table instance */
Table myEmpTable = new Table(myDatabase, “MyEmpTable”);
/* Add [EmpID] column to created table instance */
Column empID = new Column(myEmpTable, “EmpID”, DataType.Int);
empID.Identity = true;
myEmpTable.Columns.Add(empID);
/* Add another column [EmpName] to created table instance */
Column empName = new Column(myEmpTable, “EmpName”, DataType.VarChar(200));
empName.Nullable = true;
myEmpTable.Columns.Add(empName);
/* Add third column [DOJ] to created table instance with default constraint */
Column DOJ = new Column(myEmpTable, “DOJ”, DataType.DateTime);
DOJ.AddDefaultConstraint(); // you can specify constraint name here as well
DOJ.DefaultConstraint.Text = “GETDATE()”;
myEmpTable.Columns.Add(DOJ);
/* Add primary key index to the table */
Index primaryKeyIndex = new Index(myEmpTable, “PK_MyEmpTable”);
primaryKeyIndex.IndexKeyType = IndexKeyType.DriPrimaryKey;
primaryKeyIndex.IndexedColumns.Add(new IndexedColumn(primaryKeyIndex, “EmpID”));
myEmpTable.Indexes.Add(primaryKeyIndex);/* Output the table script on the console */
StringCollection TableScripts = myEmpTable.Script();
foreach (string script in TableScripts)
Console.WriteLine(script);

/* If you want to create objects on the server you need call
* create method or else objects will not be created on the server */
myDatabase.Create();
myEmpTable.Create();

Output:

The complete code listing (created on SQL Server 2008 and Visual Studio 2008, though there is not much difference if you are using it on SQL Server 2005 and Visual Studio 2005) can be found in the below text box.

SSIS C#: Create Excel file, worksheet, header row dynamically

I need to

  1. create a excel file,
  2. which will multiple worksheets.
  3. every worksheet will have a header row.

Excel file name, worksheet name, header row column names will be provided dynamically.

ScriptMain.cs file

 

namespace ST_50e9d5ec3f17426585feb2e6ab8d41bb{  [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute] public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase {        public static StringBuilder informationLog = new StringBuilder();        //Log log;        public void Main() {
//Dts.TaskResult = (int)ScriptResults.Success;            try            {                SqlConnection sqlConnection = new SqlConnection();                sqlConnection = ConnectToDataSource(“ADONET_IntegratedCare”);
DataTable dtControl = new DataTable();                dtControl =GetControlInfo(sqlConnection);
ClearFolder(dtControl);                SQLToExcel(sqlConnection, dtControl);
//MoveFile(dtControl);                sqlConnection.Close();                dtControl.Clear();            }            catch(Exception x)            {                StringBuilder errorMessage = new StringBuilder();                Exception e = x;                while (e != null)                {                    errorMessage.AppendFormat(“Exception occured [{0}]\n”, e.Source, e.Message);                    errorMessage.AppendFormat(“Stack trace: \n{0}\n”, e.StackTrace);                    errorMessage.AppendLine();                    e = e.InnerException;                }                //log.Error(“Adhoc – exception”, errorMessage.ToString());            }        } } }

ScriptMainDataAccess.cs

using Microsoft.SqlServer.Dts.Runtime;
using System;
using System.Data;
using System.Data.SqlClient;

namespace ST_50e9d5ec3f17426585feb2e6ab8d41bb
{
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region private SqlConnection ConnectToDataSource(SqlConnection sqlConnection)
private SqlConnection ConnectToDataSource(String dataSource)
{
SqlConnection sqlConnection = new SqlConnection();
try
{
using (ConnectionManager adonetConnection = (ConnectionManager)Dts.Connections[dataSource])
{
sqlConnection = (SqlConnection)adonetConnection.AcquireConnection(Dts.Transaction);
if (sqlConnection.State == ConnectionState.Open)
{
sqlConnection.Close();
}
sqlConnection.Open();
//Log.Information(“Adhoc”, String.Format(“Connect to the data source: {0}.{1}”, sqlConnection.DataSource, sqlConnection.Database));
};
}
catch (Exception e)
{
throw e;
}
return sqlConnection;
}
#endregion

#region private DataTable GetControlInfo(SqlConnection sqlConnection)
private DataTable GetControlInfo(SqlConnection sqlConnection)
{
SqlDataAdapter da = new SqlDataAdapter();
DataSet dsControl = new DataSet();
DataTable dtControl = new DataTable();
SqlCommand sqlCommand;
try
{

String controlStatement = Dts.Variables[“$Package::ControlStatement”].Value.ToString();
String dataType = Dts.Variables[“$Package::DataType”].Value.ToString();

sqlCommand = new SqlCommand(controlStatement, sqlConnection);
da.SelectCommand = sqlCommand;
da.Fill(dsControl, “Adhoc_DataTable”);
dtControl = dsControl.Tables[“Adhoc_DataTable”];
//log.Information(“Adhoc – Information”, “Get control information from the package parameter: $Package::ControlStatement”);
}
catch (Exception e)
{
throw e;
}
return dtControl;
}
#endregion
}
}

ScriptMainFile.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;

namespace ST_50e9d5ec3f17426585feb2e6ab8d41bb
{
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region private void ClearFolder(DataTable dtControl)
private void ClearFolder(DataTable dtControl)
{
//delete all the files from a directory
try
{
foreach (DataRow dr in dtControl.Rows)
{
String path = dr[“DataProcessingDirectory”].ToString();
if (Directory.Exists(path))
{
DirectoryInfo di = new DirectoryInfo(path);
foreach (FileInfo file in di.GetFiles())
{
file.Delete();
//log.Information(“Adhoc-information”, string.Format(“Deleted file {0} “, path+file));
}
}
else
{
Directory.CreateDirectory(path);
};
}
}
catch (Exception e)
{
throw e;
}
}
#endregion

#region private void MoveFile(DataTable dtControl)
private void MoveFile(DataTable dtControl)
{
try
{
foreach (DataRow dr in dtControl.Rows)
{
String sourcePath = dr[“DataProcessingDirectory”].ToString();
String destinationPath = dr[“FilePath”].ToString();
if (Directory.Exists(destinationPath) == false)
{
Directory.CreateDirectory(destinationPath);
}
DirectoryInfo di = new DirectoryInfo(sourcePath);
foreach (FileInfo fileName in di.GetFiles())
{
if (File.Exists(destinationPath + fileName))
{
File.Delete(destinationPath + fileName);
}
fileName.MoveTo(destinationPath + fileName);
//log.Information(“Adhoc-information”, string.Format(“Move the file from {0} to {1}”, sourcePath + fileName, destinationPath + fileName));
}
}
}
catch (Exception e)
{
throw e;
}

}
#endregion
}
}

ScriptMainExcel.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Linq;
using System.Windows.Forms;
using System.Collections.Generic;
using System.Runtime.InteropServices;

using Excel = Microsoft.Office.Interop.Excel;
using X14 = DocumentFormat.OpenXml.Office2010.Excel;
using X15 = DocumentFormat.OpenXml.Office2013.Excel;

namespace ST_50e9d5ec3f17426585feb2e6ab8d41bb
{
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
//MessageBox.Show(fileExtension);

 

#region private void SQLToExcel(SqlConnection sqlConnection, DataTable dtControl)
private void SQLToExcel(SqlConnection sqlConnection, DataTable dtControl)
{
try
{
foreach (DataRow dr in dtControl.Rows)
{
String filePath = dr[“DataProcessingDirectory”].ToString();
String workbookNamePostfix = dr[“WorkbookNamePostfix”].ToString();
String fileExtension = dr[“FileExtension”].ToString().ToLower();
String worksheetName = dr[“WorksheetName”].ToString();
String sqlStatement = dr[“SqlStatement”].ToString();
String fileName = filePath
+ workbookNamePostfix
+ DateTime.Now.ToString(“yyyyMMdd”)
+ fileExtension
;
SqlDataAdapter da = new SqlDataAdapter();
DataSet dsResult = new DataSet();
DataTable dtResult = new DataTable();
SqlCommand sqlCommand = new SqlCommand(sqlStatement, sqlConnection);
da.SelectCommand = sqlCommand;
da.Fill(dsResult, “Adhoc_Result”);
dtResult = dsResult.Tables[“Adhoc_Result”];
if (fileExtension == “.xlsx”)
{
if (File.Exists(fileName))//if file already exist
{
OpenExcelFile_OpenXml(dtResult, fileName, worksheetName);
}
else//if file not exist
{
CreateExcelFile_OpenXml(dtResult, fileName, worksheetName);
}
/*The auto-fit logic is something which is implemented by Microsoft Excel, and is not a part of the OpenXML spreadsheet format.
* Auto-fit involves measuring the width (or height) of the value in each cell and finding the maximum value.
* In order to implement auto-fit in your own code, you will have to manually measure the text.*/
ExcelStyle_InteropExcel(fileName, worksheetName);
}

}
}
catch (Exception e)
{
throw e;
}
}
#endregion

#region private void CreateExcelFile_OpenXml(DataTable dtResult, string fileName, string worksheetName)
private void CreateExcelFile_OpenXml(DataTable dtResult, string fileName, string worksheetName)
{
try
{
SpreadsheetDocument spreadsheetDocument;
WorkbookPart workbookPart;
WorksheetPart worksheetPart;
WorkbookStylesPart wbsp;
Sheets sheets;
Sheet sheet;
int rowNumber = dtResult.Rows.Count;
int columNumber = dtResult.Columns.Count;

 

// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
spreadsheetDocument = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook);

// Add a WorkbookPart to the document.
workbookPart = spreadsheetDocument.AddWorkbookPart();
workbookPart.Workbook = new Workbook();

// add styles to sheet
wbsp = workbookPart.AddNewPart<WorkbookStylesPart>();
wbsp.Stylesheet = GenerateWorkbookStylesPart1Content();

// generate rows
SheetData sheetData = CreateSheetData_OpenXml(dtResult);

// Add a WorksheetPart to the WorkbookPart.
worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
Worksheet worksheet = new Worksheet();
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;

wbsp.Stylesheet.Save();

/*set a auto filter*/
AutoFilter autoFilter = new AutoFilter() { Reference = (“A1:” + ExcelColumnNumberToName(columNumber.ToString()) + “1”) };
worksheet.Append(autoFilter);

// Add Sheets to the Workbook.
sheets = workbookPart.Workbook.AppendChild(new Sheets());

// Append a new worksheet and associate it with the workbook.
sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = worksheetName };

sheets.Append(sheet);
workbookPart.Workbook.Save();

//string cellReference = GetCellReference(@”c:\temp\test.xlsx”, “Sheet1”, 1, “Quantity”);
//Console.WriteLine(GetColumnName(cellReference)); //prints D for your example

 

// Close the document.
//spreadsheetDocument.WorkbookPart.Workbook.Save();
spreadsheetDocument.Close();

}// try

catch (Exception e)
{
throw e;
}
}
#endregion

#region private void OpenExcelFile_OpenXml(DataTable dtResult, string fileName, string worksheetName)
private void OpenExcelFile_OpenXml(DataTable dtResult, string fileName, string worksheetName)
{
try
{
SpreadsheetDocument spreadsheetDocument;
WorksheetPart worksheetPart;
Sheets sheets;
Sheet sheet;
int rowNumber = dtResult.Rows.Count;
int columNumber = dtResult.Columns.Count;

// open a spreadsheet document to modify
spreadsheetDocument = SpreadsheetDocument.Open(fileName, true);

// generate rows
SheetData sheetData = CreateSheetData_OpenXml(dtResult);

// Add a WorksheetPart to the WorkbookPart.
worksheetPart = spreadsheetDocument.WorkbookPart.AddNewPart<WorksheetPart>();
Worksheet worksheet = new Worksheet();
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;

/*set a auto filter*/
AutoFilter autoFilter = new AutoFilter() { Reference = (“A1:” + ExcelColumnNumberToName(columNumber.ToString()) + “1”) };
worksheet.Append(autoFilter);

// Add Sheets to the Workbook.
sheets = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>();
string relationshipId = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart);

// Get a unique ID for the new worksheet.
uint sheetId = 1;
if (sheets.Elements<Sheet>().Count() > 0)
{
sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}

// Append the new worksheet and associate it with the workbook.
sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = worksheetName };

sheets.Append(sheet);
//worksheetPart.Worksheet.Save();

// Close the document.
//spreadsheetDocument.WorkbookPart.Workbook.Save();
spreadsheetDocument.Close();

}// try

catch (Exception e)
{
throw e;
}
}
#endregion

#region private SheetData CreateSheetData_OpenXml(DataTable dtResult)
private SheetData CreateSheetData_OpenXml(DataTable dtResult)
{
SheetData sheetData = new SheetData();
//worksheetPart.Worksheet = new Worksheet(sheetData);

Row headerRow = new Row();

List<String> columns = new List<string>();
foreach (System.Data.DataColumn column in dtResult.Columns)
{
columns.Add(column.ColumnName);
Cell cell = new Cell() { StyleIndex = (UInt32Value)2U };
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(column.ColumnName);
//MessageBox.Show(“”);
headerRow.AppendChild(cell);
}

sheetData.AppendChild(headerRow);

foreach (DataRow dsrow in dtResult.Rows)
{
Row newRow = new Row();
foreach (String col in columns)
{
//Cell cell = new Cell();
Cell cell = new Cell() { StyleIndex = (UInt32Value)1U };
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(dsrow[col].ToString());
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
return sheetData;
}
#endregion

#region private Stylesheet GenerateWorkbookStylesPart1Content()
// Generates content of workbookStylesPart1.
private Stylesheet GenerateWorkbookStylesPart1Content()
{
Stylesheet stylesheet1 = new Stylesheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = “x14ac” } };
stylesheet1.AddNamespaceDeclaration(“mc”, “http://schemas.openxmlformats.org/markup-compatibility/2006&#8221;);
stylesheet1.AddNamespaceDeclaration(“x14ac”, “http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac&#8221;);

Fonts fonts1 = new Fonts() { Count = (UInt32Value)2U, KnownFonts = true };

Font font1 = new Font();
FontSize fontSize1 = new FontSize() { Val = 11D };
Color color1 = new Color() { Theme = (UInt32Value)1U };
FontName fontName1 = new FontName() { Val = “Calibri” };
FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() { Val = 2 };
FontScheme fontScheme1 = new FontScheme() { Val = FontSchemeValues.Minor };

font1.Append(fontSize1);
font1.Append(color1);
font1.Append(fontName1);
font1.Append(fontFamilyNumbering1);
font1.Append(fontScheme1);

Font font2 = new Font();
Bold bold1 = new Bold();
FontSize fontSize2 = new FontSize() { Val = 11D };
Color color2 = new Color() { Theme = (UInt32Value)1U };
FontName fontName2 = new FontName() { Val = “Calibri” };
FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering() { Val = 2 };
FontScheme fontScheme2 = new FontScheme() { Val = FontSchemeValues.Minor };

font2.Append(bold1);
font2.Append(fontSize2);
font2.Append(color2);
font2.Append(fontName2);
font2.Append(fontFamilyNumbering2);
font2.Append(fontScheme2);

fonts1.Append(font1);
fonts1.Append(font2);

Fills fills1 = new Fills() { Count = (UInt32Value)4U };

Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill() { PatternType = PatternValues.None };

fill1.Append(patternFill1);

Fill fill2 = new Fill();
PatternFill patternFill2 = new PatternFill() { PatternType = PatternValues.Gray125 };

fill2.Append(patternFill2);

Fill fill3 = new Fill();

PatternFill patternFill3 = new PatternFill() { PatternType = PatternValues.Solid };
ForegroundColor foregroundColor1 = new ForegroundColor() { Theme = (UInt32Value)0U, Tint = -0.499984740745262D };
BackgroundColor backgroundColor1 = new BackgroundColor() { Indexed = (UInt32Value)64U };

patternFill3.Append(foregroundColor1);
patternFill3.Append(backgroundColor1);

fill3.Append(patternFill3);

Fill fill4 = new Fill();

PatternFill patternFill4 = new PatternFill() { PatternType = PatternValues.Solid };
ForegroundColor foregroundColor2 = new ForegroundColor() { Rgb = “FFFF0000” };
BackgroundColor backgroundColor2 = new BackgroundColor() { Indexed = (UInt32Value)64U };

patternFill4.Append(foregroundColor2);
patternFill4.Append(backgroundColor2);

fill4.Append(patternFill4);

fills1.Append(fill1);
fills1.Append(fill2);
fills1.Append(fill3);
fills1.Append(fill4);

Borders borders1 = new Borders() { Count = (UInt32Value)2U };

Border border1 = new Border();
LeftBorder leftBorder1 = new LeftBorder();
RightBorder rightBorder1 = new RightBorder();
TopBorder topBorder1 = new TopBorder();
BottomBorder bottomBorder1 = new BottomBorder();
DiagonalBorder diagonalBorder1 = new DiagonalBorder();

border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder1);
border1.Append(diagonalBorder1);

Border border2 = new Border();

LeftBorder leftBorder2 = new LeftBorder() { Style = BorderStyleValues.Thin };
Color color3 = new Color() { Indexed = (UInt32Value)64U };

leftBorder2.Append(color3);

RightBorder rightBorder2 = new RightBorder() { Style = BorderStyleValues.Thin };
Color color4 = new Color() { Indexed = (UInt32Value)64U };

rightBorder2.Append(color4);

TopBorder topBorder2 = new TopBorder() { Style = BorderStyleValues.Thin };
Color color5 = new Color() { Indexed = (UInt32Value)64U };

topBorder2.Append(color5);

BottomBorder bottomBorder2 = new BottomBorder() { Style = BorderStyleValues.Thin };
Color color6 = new Color() { Indexed = (UInt32Value)64U };

bottomBorder2.Append(color6);
DiagonalBorder diagonalBorder2 = new DiagonalBorder();

border2.Append(leftBorder2);
border2.Append(rightBorder2);
border2.Append(topBorder2);
border2.Append(bottomBorder2);
border2.Append(diagonalBorder2);

borders1.Append(border1);
borders1.Append(border2);

CellStyleFormats cellStyleFormats1 = new CellStyleFormats() { Count = (UInt32Value)1U };
CellFormat cellFormat1 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };

cellStyleFormats1.Append(cellFormat1);

CellFormats cellFormats1 = new CellFormats() { Count = (UInt32Value)4U };
CellFormat cellFormat2 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
CellFormat cellFormat3 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyBorder = true };
CellFormat cellFormat4 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat5 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyFill = true, ApplyBorder = true };

cellFormats1.Append(cellFormat2);
cellFormats1.Append(cellFormat3);
cellFormats1.Append(cellFormat4);
cellFormats1.Append(cellFormat5);

CellStyles cellStyles1 = new CellStyles() { Count = (UInt32Value)1U };
CellStyle cellStyle1 = new CellStyle() { Name = “Normal”, FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };

cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats() { Count = (UInt32Value)0U };
TableStyles tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = “TableStyleMedium2”, DefaultPivotStyle = “PivotStyleMedium9” };

StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();

StylesheetExtension stylesheetExtension1 = new StylesheetExtension() { Uri = “{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}” };
stylesheetExtension1.AddNamespaceDeclaration(“x14”, “http://schemas.microsoft.com/office/spreadsheetml/2009/9/main&#8221;);
X14.SlicerStyles slicerStyles1 = new X14.SlicerStyles() { DefaultSlicerStyle = “SlicerStyleLight1” };

stylesheetExtension1.Append(slicerStyles1);

StylesheetExtension stylesheetExtension2 = new StylesheetExtension() { Uri = “{9260A510-F301-46a8-8635-F512D64BE5F5}” };
stylesheetExtension2.AddNamespaceDeclaration(“x15”, “http://schemas.microsoft.com/office/spreadsheetml/2010/11/main&#8221;);
X15.TimelineStyles timelineStyles1 = new X15.TimelineStyles() { DefaultTimelineStyle = “TimeSlicerStyleLight1” };

stylesheetExtension2.Append(timelineStyles1);

stylesheetExtensionList1.Append(stylesheetExtension1);
stylesheetExtensionList1.Append(stylesheetExtension2);

stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
stylesheet1.Append(stylesheetExtensionList1);

return stylesheet1;
}

#endregion

#region private void ExcelStyle_InteropExcel(String fileName, String worksheetName)
private void ExcelStyle_InteropExcel(String fileName, String worksheetName)
{
Excel.Application xlApp = null;
Excel.Workbook xlWorkBook = null;
Excel.Sheets xlSheets = null;
Excel.Worksheet xlWorksheet = null;

try
{
FileInfo file = new FileInfo(fileName);
// initialize interop excel objects
xlApp = new Excel.Application();
xlApp.DisplayAlerts = false;
xlApp.Visible = true;
xlWorkBook = xlApp.Workbooks.Open(fileName, ReadOnly: false, Editable: true);
xlSheets = xlWorkBook.Sheets;
xlWorksheet = xlWorkBook.Sheets[worksheetName];

//#################################
//create table
//xlWorksheet.Columns.ClearFormats();
//xlWorksheet.Rows.ClearFormats();
/*get the number of columns and rows in a excel file*/
//int colNumber = xlWorksheet.UsedRange.Columns.Count;
//int rowNumber = xlWorksheet.UsedRange.Rows.Count;
////Add some formatting

//Excel.Range rng1 = xlWorksheet.get_Range((Excel.Range)xlWorksheet.Cells[2, 1], (Excel.Range)xlWorksheet.Cells[1, colNumber]);
//rng1.Font.Bold = true;
//rng1.Font.ColorIndex = 3;
//rng1.Borders.ColorIndex = 1;
xlWorksheet.Columns.AutoFit();

//#################################
xlWorkBook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, false, Excel.XlSaveAsAccessMode.xlNoChange, false, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
xlWorkBook.Close();
xlApp.Quit();
}
catch (Exception e)
{
throw e;
}
finally
{
/*Release the resources. If you do not release the resources properly.
* File will be still opened by the process and it stops the user from modifying and even from opening*/
Marshal.ReleaseComObject(xlWorksheet);
Marshal.ReleaseComObject(xlSheets);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlApp);

xlWorksheet = null;
xlSheets = null;
xlWorkBook = null;
xlApp = null;

GC.WaitForPendingFinalizers();
GC.Collect();
}
}
#endregion

#region public static int ExcelColumnNameToNumber(string input)
public static int ExcelColumnNameToNumber(string input)
{
input = input.ToUpperInvariant();
int sum = 0;
int charExcelNumber;
for (int i = 0; i < input.Length; i++)
{
/*
In excel A=1 and Z=26*. In ASCII value for character A iS 65 and Z is 122. ‘A’ and 65 can be used interchangebly.
If the input is ABC then input[0]=A, input[1]=B, input[3]=C.
Charracter respective Excel column number A=1, B=2, C=3.
The formula to convert: ABC=1*26^2 + 2*26^1 + 3*26^0.
*/
charExcelNumber = ((int)input[i] – ‘A’ + 1);
sum = sum + charExcelNumber * (int)Math.Pow(26, (input.Length – 1 – i));
}
return sum;
}
#endregion

#region private static String ExcelColumnNumberToName(String columnNumber)
private static String ExcelColumnNumberToName(String columnNumber)
{
int colNum;
Int32.TryParse(columnNumber, out colNum);
int dividend = colNum;
string columnName = String.Empty;
int modulo;

while (dividend > 0)
{
/*
In excel A=1 and Z=26*. In ASCII value for character A iS 65 and Z is 122. ‘A’ and 65 can be used interchangebly.
If the input is ABC then input[0]=A, input[1]=B, input[3]=C.
Charracter respective Excel column number A=1, B=2, C=3.
The formula to convert: ABC=1*26^2 + 2*26^1 + 3*26^0.
*/
modulo = (dividend) % 26;
columnName = Convert.ToChar(64 + modulo).ToString() + columnName;
dividend = (int)((dividend – modulo) / 26);
Console.WriteLine(modulo.ToString());
Console.WriteLine(dividend.ToString());
}
return columnName;
}
#endregion
}//class
}//namespace

 

C# SQL: Query to find a specific column in each tables and all databases.

Scenario:

In the CustomerID there is some Customer Account information which we do not want. So we have to find the CustomerID column that is used almost in all databases and all the tables and replace the CustomerID value with CustomerIDPseudonymisedNumber.

Plan:

  1. SQL Update query: Write a query which will look CustomerID in a table if CustomerID column exist then it will return information: DatabaseName, SchemaName, TableName, ColumnName. It will also return a dynamically build sql update query. We want to to store the result the query into a table
  2. ETL Toool: Now we want to create an ETL tool which will get the update query from the table and execute it. We also want to log the result of the execute query if it has successfully completed the query or failed. If failed also log the error message

 

Steps 1 – SQL Update query:

USE SUS_SEM;
GO

DECLARE
@RETURN_VALUE INT
,@command1 nvarchar(MAX)

IF OBJECT_ID(‘tempdb..#temp’, ‘U’) IS NOT NULL DROP TABLE tempdb..#temp
IF OBJECT_ID(‘tempdb..#temp2’, ‘U’) IS NOT NULL DROP TABLE tempdb..#temp2

CREATE TABLE #temp
(
ServerName nvarchar(100)
,Table_Catalog nvarchar(256)
,Table_Schema nvarchar(256)
,Table_Name SYSNAME
,Column_Name SYSNAME
,Row_Count int
,Table_Size_MB decimal(10,2)
,Table_Size_GB decimal(10,2)
)

SET @command1 = ‘
USE [?];
DECLARE @dbName nvarchar(100);
SET @dbName=”?”;
INSERT INTO #temp
SELECT
@@SERVERNAME AS ServerName
,@dbName AS DatabaseName
,s.Name AS SchemaName
,t.NAME AS TableName
,c.name AS ColumnName
,p.rows AS RowCounts
,CONVERT(decimal(10, 2), (SUM(a.used_pages) * 8))/(1024) AS SizeInMB
,CONVERT(decimal(10, 2), (SUM(a.used_pages) * 8))/(1024*1024) AS SizeInGB
FROM
sys.tables t
INNER JOIN sys.columns AS c
ON t.object_id=c.object_id
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE ”dt%”
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
AND @dbName NOT IN (”RefDB”, ”master”, ”msdb”, ”temp”, ”model”, ”distribution”)
AND @dbName IN (””)
AND t.NAME NOT IN (””)
–AND t.NAME IN(””)
AND t.NAME IN(””)
AND c.name IN ( ”Number”, ”No”, ”Num”, ” Number”, ”_Number”, ”_No”, ”_Num”, ” No#”, ”xNumber”)
AND p.rows>0
GROUP BY
t.Name, s.Name, p.Rows, c.name
ORDER BY
t.Name’

/*EXECUTE master.sys.sp_MSforeachdb ‘USE [?]; EXEC sp_spaceused’
Notice that [?] is used as a placeholder for the unspecified database name.

As another example, you can execute sp_helpfile on each database by running the following command.
EXECUTE master.sys.sp_MSforeachdb ‘USE [?]; EXEC sp_helpfile’*/

EXEC @RETURN_VALUE = sp_MSforeachdb @command1
— = @command1
–SELECT * FROM #temp

CREATE TABLE #temp2
(
TableID INT IDENTITY
,ServerName nvarchar(100)
,Table_Catalog nvarchar(256)
,Table_Schema nvarchar(256)
,Table_Name SYSNAME
,Column_Name SYSNAME
,Row_Count int
,Table_Size_MB decimal(10,2)
,Table_Size_GB decimal(10,2)
,TableFullName nvarchar(200)
,SQLStatement nvarchar(1000)
,InsertColumn_Statement nvarchar(4000)
,UpdateColumn_Statement nvarchar(4000)
,IsNulled_Statement nvarchar(4000)
,IsNulled_Result nvarchar(100)
)

INSERT INTO #temp2 (ServerName, Table_Catalog, Table_Schema, Table_Name, Column_Name, TableFullName, [Row_Count], Table_Size_MB, Table_Size_GB, SQLStatement, InsertColumn_Statement, UpdateColumn_Statement, IsNulled_Statement, IsNulled_Result)
SELECT
ServerName
,Table_Catalog
,Table_Schema
,Table_Name
,Column_Name
,'[‘ + Table_Catalog + ‘].’ + ‘[‘ + Table_Schema + ‘].’ + ‘[‘ + Table_Name + ‘]’
,[Row_Count]
,[Table_Size_MB]
,[Table_Size_GB]
,’SELECT TOP 10 [‘+Column_Name + ‘] From [‘ + Table_Catalog+’].[‘+Table_Schema+’].[‘+Table_Name+’] WHERE [‘+Column_Name+’] IS NOT NULL;’

,’BEGIN TRY ALTER TABLE [‘ + Table_Catalog + ‘].’ + ‘[‘ + Table_Schema + ‘].’ + ‘[‘ + Table_Name + ‘]’ + ‘ ADD SK_CustomerID int; RAISERROR(”SK_CustomerID Column ADDED”, 0, 1); END TRY BEGIN CATCH RAISERROR(”SK_CustomerID Column already exist”, 0, 1); END CATCH;’ AS SK_PaitentID_Statement

/*,’UPDATE target
SET target. [‘ + Column_Name + ‘]=[RefDBTable].[SK_CustomerID]
FROM [‘ + Table_Catalog + ‘].[‘ + Table_Schema + ‘].[‘ + Table_Name + ‘] AS target
INNER JOIN [SUS_SEM].[dbo].[RefDBTable] AS RefDBTable
ON target.[‘ + Column_Name + ‘]=RefDBTable.Number;’ AS UpdateColumn_Statement
*/
/*,’UPDATE target
SET target.[‘ + Column_Name + ‘]=ISNULL([Customer].[SK_CustomerID], 1)
FROM [‘ + Table_Catalog + ‘].[‘ + Table_Schema + ‘].[‘ + Table_Name + ‘] AS target
LEFT JOIN [RefDB].[dbo].[Customer] AS Customer
ON target.[‘ + Column_Name + ‘]=Customer.SK_CustomerID;’ AS UpdateColumn_Statement
*/
,’UPDATE target
SET target.[‘ + Column_Name + ‘]=[Customer].Number
FROM [‘ + Table_Catalog + ‘].[‘ + Table_Schema + ‘].[‘ + Table_Name + ‘] AS target
INNER JOIN [RefDB].[dbo].[Customer] AS Customer
ON target.[‘ + Column_Name + ‘]=convert(varchar(100), Customer.SK_CustomerID);’ AS UpdateColumn_Statement

,’SELECT CASE WHEN COUNT(*)=0 THEN ”NULLED (Number)” ELSE ”EXIST (Number)” END AS IsNulled_Statement
FROM (SELECT TOP 10 [‘+Column_Name + ‘] From [‘ + Table_Catalog+’].[‘+Table_Schema+’].[‘+Table_Name+’] WHERE [‘+Column_Name+’] IS NOT NULL) AS C’
,” AS IsNulled_Result
FROM #temp as t

IF OBJECT_ID(‘[temp].[Statement]’, ‘U’) IS NOT NULL DROP TABLE [temp].[Statement];

SELECT distinct ServerName, Table_Catalog, Table_Schema, Table_Name, Column_Name
,TableFullName
,[Row_Count]
,[Table_Size_MB]
,[Table_Size_GB]
–,SQLStatement
–,InsertColumn_Statement
,UpdateColumn_Statement
–,IsNulled_Statement
–,IsNulled_Result
INTO [temp].[Statement]
FROM #temp2 order by 2, 3, 4
;

IF OBJECT_ID(‘[temp].previewStatement’, ‘U’) IS NOT NULL DROP TABLE [temp].previewStatement;
SELECT *
INTO [temp].previewStatement
FROM [temp].[Statement]
WHERE 1=2
;
SELECT * FROM [temp].[Statement]
;

 

Steps 2 – ETL Toool:

ETL tool will have 3 elements:

  1. Execute SQL: Execute the sql query written in steps 1
  2. Data flow task – select * from result table and flow to another table. Enable data viewer. The purpose of this step is just to see what table and databases will be affected.
  3. Script task:
    1. Read the update query from the table
    2. Execute the update query
    3. Log the execution time, status, table name, table row count, table size

public void Main()
{
/*Name of the log file name*/
String fileName = “H:\\Log\\Log_NHSNumber.txt”;
String rowCount = “”, tableSizeInMB = “”, tableFullName = “”, status = “”;
DateTime today;
ConnectionManager cm;
SqlConnection sqlConn;
//SqlCommand sqlComm, sqlCommCreateStatement, sqlCommExecuteStatement;

/*connect to the DataSources*/
cm = Dts.Connections[“ADONetSUS_SEM”];
sqlConn = (SqlConnection)cm.AcquireConnection(Dts.Transaction);
if (sqlConn.State == ConnectionState.Open)
{
sqlConn.Close();
sqlConn.Open();
}

/*get the statement from the tempStatement table*/
var da = new SqlDataAdapter();
var ds = new DataSet();
var dtStatement= new DataTable();
String serverName = “”;
/*get select statement based on the server name*/
serverName = sqlConn.DataSource.ToString();
if (serverName == “PSFADWHPIT01\\nelcsu”)
{
da.SelectCommand = new SqlCommand(“SELECT [Row_Count], [Table_Size_MB], [TableFullName], [UpdateColumn_Statement] FROM [Unify2].[temp].[Statement]”, sqlConn);
}
else
da.SelectCommand = new SqlCommand(“SELECT [Row_Count], [Table_Size_MB], [TableFullName], [UpdateColumn_Statement] FROM [Unify2.1].[temp].[Statement]”, sqlConn);
da.Fill(ds, “dsTableStatement”);
dtStatement = ds.Tables[“dsTableStatement”];

String statement = “”;
foreach(DataRow dr in dtStatement.Rows)
{
rowCount = dr[0].ToString();
tableSizeInMB = dr[1].ToString();
tableFullName = dr[2].ToString();
statement = dr[3].ToString();
var sqlComm = new SqlCommand(statement, sqlConn);
sqlComm.CommandTimeout = 60000;
String line = “”;
try
{
today = DateTime.Now;
{
status = “Started”;
line = string.Format(“Status: {0}, Time: {1}, Table: {2}, RowCount: {3}, TableSizeInMB: {4}”, status, today, tableFullName, rowCount, tableSizeInMB);
/*append the time, table name, status to the log file*/
if (File.Exists(fileName))
{
using (StreamWriter writer = File.AppendText(fileName))
{
writer.WriteLine(line);
}
}
}
/*Execute the query here*/
sqlComm.ExecuteNonQuery();
}
catch (Exception e)
{
/*log the time, error message*/
today = DateTime.Now;
{
status = “Failed”;
line = string.Format(“Status: {0}, Time: {1}, Table: {2}, RowCount: {3}, TableSizeInMB: {4}, Message:{5}”, status, today, tableFullName, rowCount, tableSizeInMB, e.Message);
}
throw e;
}
finally
{
sqlComm.Dispose();
today = DateTime.Now;
/*log the time, error message*/
if (status != “Failed”)
{
status = “Completed”;
line = string.Format(“Status: {0}, Time: {1}, Table: {2}, RowCount: {3}, TableSizeInMB: {4}”, status, today, tableFullName, rowCount, tableSizeInMB);
}
/*append the time, table name, status to the log file*/
if (File.Exists(fileName))
{
using (StreamWriter writer = File.AppendText(fileName))
{
writer.WriteLine(line);
}
}
}
}

da.Dispose();
ds.Dispose();
}

 

 

C#: Execute SQL server stored procedure with parameters and return value

Scenario:

I want to execute SQL store procedure from the .net c# page, pass parameter, and return output parameter

Steps:

  1. Add namespaces
  2. Build a connection string
  3. Connect to the database c#
  4. Specify the stored procedure
  5. Specify the input parameters
  6. Specify the output parameter

Code:

Add namespaces:

Add the following namespaces in the top

using System.Data.SqlClient;

Build a connection string (Instruction only for Microsoft Visual Studio Products – ASP.NET, SSIS):

This is the cruicial part of the program where people make more mistakes. In order the get the correct connection string. Folowing the following procedure

  1. Open the application you will be using to connect to the database
  2. Add the Database Connection using the visual studio graphics toolbox.Say for example: Visual Studio 2013 ASP.NET Project
    1. From the toolbox–>Data–>Add SqlDataSource
    2. Click on the Configure Data Source
    3. Click on the button New Connection
      1. Select Microsoft SQL Server and
      2. Provider drop down box select .NET Framework data provider for SQL Server
    4. Add connection window:
      1. Type server name
      2. Authentication: You can choose either Windows or SQL Server authentication
      3. Type the database name
      4. Select the table name you want to select then click Finish
    5. Right click on the SqlDataSource–>Properties–>ConnectionString: Copy the connection string. A sample of connection string: <add name=”SimpleTopupConnection” connectionString=”Server=.\SQLExpress; Initial Catalog=simpletopup; Integrated Security=true;” providerName=”System.Data.SqlClient”/>

Connect to the database c#:

 

 

 

 

C# SSIS: Get list of all Directory and sub-directories and delete files older than retention period

Scenario:

I want to read all the directory and sub-directories on certain directory.

Note: You cannot read objVariable using c# code – OleDbDataAdapter if it is not a ADO.NET record. Which means if SQL result set was not written into the object variable.

Plan:

  1. Read all directories and assign it to a Object variable “objDir”
  2. Using Foreach Loop Container, loop through Object variable, read a directory and assign to a string variable “dirName”
  3. Inside the Foreach Loop Container, using a script task show the value of the dirName to check we are reading all the directories correctly

Now follow the steps:

o1

o2

o3

  1. We have declared two variables:
    1. objDir=Object type
    2. dirName=String type
  2. Add a script task:
    1. ReadOnlyVariables: rootDirectory
    2. ReadWriteVariables:objDir
    3. Edit script:
      public void Main()
      {
                 Dts.Variables[“User::objDir”].Value = System.IO.Directory.GetDirectories(Dts.Variables[“User::rootDirectory”].Value.ToString(), “*”,   SearchOption.AllDirectories);
      /*Dts.Variables[“User::objDir”].Value = System.IO.Directory.GetDirectories(Dts.Variables[“User::rootDirectory”].Value.ToString(), “*”, SearchOption.TopDirectoryOnly);*/
      }
  3. Add a “Foreach Loop Container”
    1. In the “Collection” tab
      1. Enumerator option–>Select  “Foreach From Variable Enumerator”
      2. In the Enumerator configuration–>From the variable drop down list–>Select the Object variable you will read”objDir”
    2. In the Variable Mappings tab
      1. After reading a record from the objDir variable we need to write the value into a variable. We need to add this variable here.

 

Delete files and folder older than retention period:

Rootdirectory–>TopDirectory–>SubDirectory–>Files.csvo4

Plan:

  1. Delete the folder contents first then
  2. Delete the folder

C# Code:

public void Main()
{
String subDirName=Dts.Variables[“User::subDirName”].Value.ToString();
int retentionPeriod=(Int32)Dts.Variables[“User::retentionDays”].Value;
DateTime creationDate= Directory.GetCreationTime(subDirName);
DirectoryInfo di = new DirectoryInfo(subDirName);
String[] oldFiles = Directory.GetFiles(subDirName, “*”);
if (creationDate < DateTime.Now.AddDays(-retentionPeriod))
{

//MessageBox.Show(subDirName + ” folder will be deleted.”);
foreach (String curFile in oldFiles)
{
FileInfo fi = new FileInfo(curFile);
fi.Delete();
}
di.Delete();
}
}

C# SSIS: Delete files older than retention period

--// You need to apply below one line in "namespaces" region.
using System.IO;
--//You need to apply below lines inplace of  // TODO: Add your code here
int RetentionPeriod = Convert.ToInt32(Dts.Variables["User::Period"].Value.ToString());
string directoryPath = Dts.Variables["User::BackupFolder"].Value.ToString();
string[] oldFiles = System.IO.Directory.GetFiles(directoryPath, "*.*");
foreach (string currFile in oldFiles)
  {
         FileInfo currFileInfo = new FileInfo(currFile);
              
            if (currFileInfo.LastWriteTime < (DateTime.Now.AddDays(-RetentionPeriod)))
                {
                    currFileInfo.Delete();
                }
  }

C# SSIS: Skip first and last row in a flat file conneciton

There are many situations which you can use power of scripting in SSIS data flow. Today I encounter a situation here , there is source flat file here with header and trailer records which have different structure from data rows. Data rows delimited by vertical line ( | ) , but header and trailer not. Also Trailer rows information needs to import to another destination.

So we have a situation to read both data rows delimited and trailer row which isn’t delimited ( it’s fixed length on each value )

This is one of the situations which Power of script component makes sense.

In this sample I used a Script Component as Source.

Problem:

This is content of source flat file:

HVWBB630 201012302010123005473333
Iseyapcm            |88071523|E|Y|N|N|N|Y|
Anitakaul1          |88072163|E|Y|N|N|N|Y|
efarrellssb         |88072233|E|Y|N|N|N|Y|
markghouse          |88072450|E|Y|N|N|N|Y|
dechopra            |88074988|E|Y|N|N|N|Y|
TVWBB630 00000005122010123020101230TRADE PROFILE

first row is header row which should be skipped so we don’t consider this .

and last row is trailer which have useful information for us, like:

Program Name : VWBB630

Record Count: 512

Date:  20101230

Data rows should import into a data table, and trailer information should import in another destination table.

Solution:

1- Add a Data flow task.

2- Add a Script Component Transformation as SOURCE .


3- Double click on script component, and in General tab, set language as Visual Basic


4- Go to Inputs and Outputs Tab,

rename the existing Output0 to OutputMainRows

and add another output named OutputTrailerRow


Under OutputMainRows add 8 columns with names : Column 0 , Column 1 , … , Column 7 and set data type of them as DT_STR

( this is because we have 8 data columns in this source file sample )

Under OutputTrailerRow add 3 Columns as below:

Column name                      data type

————————————–

ProgramName                      DT_STR

RecordCount                       DT_I4

Date                                  DT_DATE


4- Go back to general tab, and click on Edit Script,

Here is where we should read data from source file, in this sample I used System.IO.StreamReader .NET class which get us the ability to read data from file.

First of all , in the class definition, declare a variable of this type:

Dim sr As System.IO.StreamReader

the StreamReader class should be instantiated once the script component is in pre execute step, so I write this line in the PreExecute() method as below:

Public Overrides Sub PreExecute()
MyBase.PreExecute()
sr = New System.IO.StreamReader(“D:\SSIS\ScriptComponentAsSource\sourcefile.txt”)
End Sub


Now we opened the file and we can read it with .ReadLine() method line by line, this is where we process source data and redirect each data to appropriate output.

We should use the CreateNewOutputRows method, and write our main code there, also note that for adding row to every output we should use this structure<OUTPUTNAME>Buffer.AddRow()

This is the code which should be written in CreateNewOutputRows() :

Public Overrides Sub CreateNewOutputRows()
Dim lineIndex As Integer = 0

While (Not sr.EndOfStream)
Dim line As String = sr.ReadLine()
If (lineIndex <> 0) Then ‘remove header row
Dim columnArray As String() = line.Split(Convert.ToChar(“|”))
If (columnArray.Length > 1) Then
‘main rows
OutputMainRowsBuffer.AddRow()
OutputMainRowsBuffer.Column0 = columnArray(0)
OutputMainRowsBuffer.Column1 = columnArray(1)
OutputMainRowsBuffer.Column2 = columnArray(2)
OutputMainRowsBuffer.Column3 = columnArray(3)
OutputMainRowsBuffer.Column4 = columnArray(4)
OutputMainRowsBuffer.Column5 = columnArray(5)
OutputMainRowsBuffer.Column6 = columnArray(6)
OutputMainRowsBuffer.Column7 = columnArray(7)
Else
‘trailer row
OutputTrailerRowBuffer.AddRow()
OutputTrailerRowBuffer.ProgramName = line.Substring(1, 7)
OutputTrailerRowBuffer.RecordCount = line.Substring(9, 10)
OutputTrailerRowBuffer.Date = DateTime.ParseExact(line.Substring(19, 8), “yyyyMMdd”, New   System.Globalization.CultureInfo(“en-US”))
End If
End If
lineIndex = lineIndex + 1
End While

End Sub


When reading data completed we should close the StreamReader , the PostExecute() method is the best place to close connections.

Public Overrides Sub PostExecute()
MyBase.PostExecute()
sr.Close()
End Sub


This is whole code in this script component:

‘ Microsoft SQL Server Integration Services Script Component
‘ Write scripts using Microsoft Visual Basic 2008.
‘ ScriptMain is the entry point class of the script.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute()> _
<CLSCompliant(False)> _
Public Class ScriptMain
Inherits UserComponent
Dim sr As System.IO.StreamReader

Public Overrides Sub PreExecute()
MyBase.PreExecute()
sr = New System.IO.StreamReader(“D:\SSIS\ScriptComponentAsSource\sourcefile.txt”)
End Sub

Public Overrides Sub PostExecute()
MyBase.PostExecute()
sr.Close()
End Sub

Public Overrides Sub CreateNewOutputRows()
Dim lineIndex As Integer = 0

While (Not sr.EndOfStream)
Dim line As String = sr.ReadLine()
If (lineIndex <> 0) Then ‘remove header row
Dim columnArray As String() = line.Split(Convert.ToChar(“|”))
If (columnArray.Length > 1) Then
‘main rows
OutputMainRowsBuffer.AddRow()
OutputMainRowsBuffer.Column0 = columnArray(0)
OutputMainRowsBuffer.Column1 = columnArray(1)
OutputMainRowsBuffer.Column2 = columnArray(2)
OutputMainRowsBuffer.Column3 = columnArray(3)
OutputMainRowsBuffer.Column4 = columnArray(4)
OutputMainRowsBuffer.Column5 = columnArray(5)
OutputMainRowsBuffer.Column6 = columnArray(6)
OutputMainRowsBuffer.Column7 = columnArray(7)
Else
‘trailer row
OutputTrailerRowBuffer.AddRow()
OutputTrailerRowBuffer.ProgramName = line.Substring(1, 7)
OutputTrailerRowBuffer.RecordCount = line.Substring(9, 10)
OutputTrailerRowBuffer.Date = DateTime.ParseExact(line.Substring(19, 8), “yyyyMMdd”, New System.Globalization.CultureInfo(“en-US”))
End If
End If
lineIndex = lineIndex + 1
End While

End Sub

End Class

Save and close the script editor.

5- Now you have two outputs in script component. connect OutputMainRows to the table which you want to insert data rows.


and connect OutputTrailerRow to the destination table which should store trailer information.


6- Now is the time to run package, This is output showed in the OutputMainRows:


and this is output in the OutputTrailerRow:


Hope this be helpful post.