SQL Query: Schedule multiple jobs to run over night

I had about 40 sql agent jobs. I had to schedule them to run one time only over night. It was a bit of hectic to open every job, change the ownership, modify the schedule time, then change the ownership back to the service account. In order to minimize the work effort, I have written a script based on CURSOR. Where I will have a control table, it will define the job name, schedule name, schedule start date, schedule start time. I will read this from the control table and the change the schedule accordingly.

 

USE msdb;USE msdb;go
/*create the control table and insert some dummy data.In order to make this script work, there has to be job exist defined in the control table.*/
CREATE TABLE [ETL].[SqlJobControl]( [SqlJobControlID] [int] IDENTITY(1,1) NOT NULL, [job_id] [int] NULL, [job_name] [nvarchar](200) NULL, [schedule_name] [nvarchar](50) NULL, [job_description] [nvarchar](100) NULL, [active_start_date] [int] NULL, [active_start_time] [int] NULL, [owner_login_name_user] [nvarchar](50) NULL, [owner_login_name_service_account] [nvarchar](50) NULL, [enabled] [bit] NULL, CONSTRAINT [PK__SqlJobCo__D32BB6AA03DE9823] PRIMARY KEY CLUSTERED ( [SqlJobControlID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
GOSET IDENTITY_INSERT [ETL].[SqlJobControl] ON
INSERT [ETL].[SqlJobControl] ([SqlJobControlID], [job_id], [job_name], [schedule_name], [job_description], [active_start_date], [active_start_time], [owner_login_name_user], [owner_login_name_service_account], [enabled]) VALUES (1, NULL, N’Developer_Test_Job1′, N’OneOff’, N’Dummy job’, 20170613, 175500, N’blue\ahmedma’, N’ELC\srvc.DWHSQLAgent’, 1)INSERT [ETL].[SqlJobControl] ([SqlJobControlID], [job_id], [job_name], [schedule_name], [job_description], [active_start_date], [active_start_time], [owner_login_name_user], [owner_login_name_service_account], [enabled]) VALUES (2, NULL, N’Developer_Test_Job2′, N’OneOff’, N’Dummy job’, 20170614, 180000, N’blue\ahmedma’, N’ELC\srvc.DWHSQLAgent’, 1)SET IDENTITY_INSERT [ETL].[SqlJobControl] OFF;
DECLARE  @job_id int, @job_name nvarchar(200), @schedule_name nvarchar(50), @job_description nvarchar(100), @active_start_date int, @active_start_time int, @owner_login_name_user nvarchar(50), @owner_login_name_service_account nvarchar(50), @enabled bit
;
DECLARE @oneoff_sqljob_cursor AS CURSOR;
SET @oneoff_sqljob_cursor = CURSOR FAST_FORWARD FOR SELECT  [job_id] ,[job_name] ,[schedule_name] ,[job_description] ,[active_start_date] ,[active_start_time] ,[owner_login_name_user] ,[owner_login_name_service_account] ,[enabled] FROM [UNIFY2].[ETL].[SqlJobControl] WHERE [enabled]=1;
OPEN @oneoff_sqljob_cursor;FETCH NEXT FROM @oneoff_sqljob_cursor INTO  @job_id ,@job_name ,@schedule_name ,@job_description ,@active_start_date ,@active_start_time ,@owner_login_name_user ,@owner_login_name_service_account ,@enabled ;
WHILE @@FETCH_STATUS = 0BEGIN EXEC [dbo].[sp_update_job_for_developers]   @job_name=@job_name,  @owner_login_name=@owner_login_name_user; EXEC msdb.dbo.sp_detach_schedule  @job_name=@job_name,  @schedule_name=@schedule_name, @delete_unused_schedule=1 ;
EXEC msdb.dbo.sp_add_jobschedule  @job_name=@job_name,  @name=@schedule_name,  @enabled=1,  @freq_type=1,  @freq_interval=1,  @freq_subday_type=0,  @freq_subday_interval=0,  @freq_relative_interval=0,  @freq_recurrence_factor=1,  @active_start_date=@active_start_date,  @active_start_time=@active_start_time ; EXEC [dbo].[sp_update_job_for_developers]   @job_name=@job_name,  @owner_login_name=@owner_login_name_service_account; FETCH NEXT FROM @oneoff_sqljob_cursor INTO  @job_id ,@job_name ,@schedule_name ,@job_description ,@active_start_date ,@active_start_time ,@owner_login_name_user ,@owner_login_name_service_account ,@enabled ;END

Advertisements

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

 

SSIS Project: Data migration from Text file to SQL database

Project architecture: Sales force migration project – SSIS process

Folder name Package name ID Archive folder name
A Apkg 1 Achive
B Bpkg 2 Bchive

CTRLConfig

 

Poll

Folder A

A20150814.csv

A20150814_2.csv

Folder B

B20150814.csv

CTRL_001_Polling

ID folderID (FK) File name date
101 1 A20150814.csv
102 2 B20150814.csv
103 1 A20150814_2.csv

 

Import

101 -> 1 (APKG) – Invoke [APKG] to import [A20150814.csv] -> rename and archive
102 -> 2 (BPKG) – Invoke [BPKG] to import [B20150814.csv] -> rename and archive
103 -> 1 (APKG) – Invoke [APKG] to import [A20150814_2.csv] -> rename and archive

 

 

Steps for next week.

  • Store packages on SLQ server
  • Set up tables if not done so – see above
  • Build routines to dynamically invoke package and alert
  • Migration_IMPORTS.DBO.Migration_Table01 blah blah (or similar)
  • Copy this and create a version with only nvarchar datatypes try and match naming convention IMP_003_Hyperactive_raw
  • Add additional field for staging ID
  • Create audit tables one for load events (package failure) -> data source to raw table link to CTRL_001_Polling.ID
  • Create an audit table for record validation and move to hyperactive table -> raw to datatyped table Success (moved) Failure (datatype mismatch) Link to the staging ID
  • Run a sample package found in D:\Projects\Salesforce\SF2015_Staging_area\Integration Services Project1 to load data from file in to hyperactive raw table
  • Create validation for data types that are not nvarchar in Migration_Table02 which is the same as hyperactive raw but with the real datatypes _ Compare the 2 to determine the correct datatypes for the fields and validate against those that are not straightforward text
  • Write all events to audit tables

I have created three packages to achieve the above goal:

c1

u2

c3 m1 m2

 

u5 u4

 

 

Scenario:

I have to create a SSIS solution. Among many packages there will be one master package which will call all the other packages. All the operations in the master package will be dynamic. Colleagues will upload or create packages almost every week, so we have to create a kind of system which will pick up the new packages every day and execute it automatically. Functional requirements are listed below:

  1. Person A (my colleague) will
    1. receive files by email, post, ftp server.
    2. convert those files into text format
    3. create SSIS child packages to import data from those files into SQL server and save them into certain location.
  2. I have to create Master package will
    1. scan into that certain location where person A has saved the SSIS child packages and save the package location, package name, is enabled, read the connection string of the child packages and get the file name, file location information into a SQL table
    2. call the child packages and import the associated files data into designated database and table.
    3. save the package info, associated file name, associated file location, is imported successfully, date imported information into a second SQL table
    4. read the second SQL table info and archive the files into certain location

The SSIS tasks list for this project:

  1. Design SQL tables
    1. LookupDirectory table: file lookup source directory, archive directory, file extension, modified date
    2. FileInfo table: inserted file information for example: file name, source directory, archive directory, modified date
    3. Control table: Will hold the information about the packages. Package name, package status, modified date.
  2. Archiving files
    1. Execute SQL task: Initialize a object variable value from a SQL table
    2. Object variable: How to
    3. Script task: Assign values to other variable from
    4. Loop over files: Loop over the files in the specific folder with specific extension
      1. Execute SQL task: Insert the file name, source directory, archive directory into another SQL table
      2. File system task: Move the files into specific archive folder
  3. Connecting to Data Sources in the Script task
  4. Conditional split
  5. Calling a child packages from the parent package.
    1. Execute package task
      1. Dynamically set the package name in the package execute task
    2. Loading and Running a Local Package Programmatically
  6. Dynamically call a package from another package
    1. Execute package task
  7. Dynamically Run SSIS Child Packages With a Script Task
    1. Execute SQL task: Get package name, package status from the SQL table
    2. Script task:
      1. Assign the value to DataTable
      2. Script Code to conditionally load  the packages