public static Microsoft.SqlServer.Management.IntegrationServices.PackageInfo GetPackageFromCatalog(CatalogFolder catalogFolder, string projectName)
{
Microsoft.SqlServer.Management.IntegrationServices.PackageInfo package = null;
ProjectInfo project = catalogFolder.Projects[projectName];
if (project == null)
{
return(package); //returns null
}
package = project.Packages["Package.dtsx"];
return(package);
}
private void ExecutePackageButton_Click(object sender, EventArgs e)
{
try
{
Server server = new Server(_server);
IntegrationServices isServer = new IntegrationServices(server);
Catalog catalog = isServer.Catalogs["SSISDB"];
CatalogFolder folder = catalog.Folders["ProSSIS"];
ProjectInfo p = folder.Projects["My ProSSIS Project"];
Microsoft.SqlServer.Management.IntegrationServices.PackageInfo pkg = p.Packages["package.dtsx"];
EnvironmentReference reference = p.References["Environment1", folder.Name];
reference.Refresh();
long operationId = pkg.Execute(false, reference);
catalog.Operations.Refresh();
StringBuilder messages = new StringBuilder();
foreach (Operation op in catalog.Operations)
{
if (op.Id == operationId)
{
op.Refresh();
foreach (OperationMessage msg in op.Messages)
{
messages.AppendLine(msg.Message);
}
}
}
LogFileTextbox.Text = "Package executed: " + messages.ToString();
//MessageBox.Show("Package executed");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public override DTSExecResult Execute(Connections connections,
VariableDispenser variableDispenser,
IDTSComponentEvents componentEvents,
IDTSLogging log,
object transaction)
{
//return base.Execute(connections, variableDispenser, componentEvents, log, transaction);
Server catalogServer = new Server(ServerName);
IntegrationServices integrationServices = new IntegrationServices(catalogServer);
Catalog catalog = integrationServices.Catalogs[PackageCatalog];
CatalogFolder catalogFolder = catalog.Folders[PackageFolder];
ProjectInfo catalogProject = catalogFolder.Projects[PackageProject];
Microsoft.SqlServer.Management.IntegrationServices.PackageInfo catalogPackage = catalogProject.Packages[PackageName + ".dtsx"];
catalogPackage.Execute(false, null);
return(DTSExecResult.Success);
}
public void TestFlatFileToTableTask()
{
string[] args = { };
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CHEF"].ConnectionString);
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "DELETE FROM [Chef].[Log]"
+ " WHERE [Log].[QueueID] IN"
+ " (SELECT QueueID FROM [CHEF].[RequestQueue]"
+ " WHERE ProcessID = 9500)";
cmd.ExecuteNonQuery();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "DELETE FROM [Chef].[RequestQueue]"
+ " WHERE ProcessID = 9500";
cmd.ExecuteNonQuery();
cmd.CommandText = "DELETE FROM [Chef].[MetaData]"
+ " WHERE ProcessID = 9500";
cmd.ExecuteNonQuery();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "INSERT INTO [CHEF].[Metadata] VALUES "
+ "(9500"
+ ",'FlatFileToTableTaskTest'"
+ ",'<CHEFMetaData ApplicationName=\"TestApplication\">"
+ " <Process ID=\"9500\" Name=\"DemoFlatFileToTable\" DefaultAllowTruncate=\"False\" VerboseLogging=\"False\" ExecuteExistingPackage=\"False\" >"
+ " <ConnectionSet>"
+ " <SQLConnection key=\"SQLConnection\" ServerName=\"(local)\" DatabaseName=\"AdventureWorks2012\" />"
+ " <FlatFileConnection key=\"FlatFileConnection\" FileName=\"C:\\Users\\t-satsen\\Desktop\" />"
+ " </ConnectionSet>"
+ " <Step ID=\"9510\" Name=\"SQL Query To Table Test\" TypeID=\"1\" TypeName=\"Staging\">"
+ " <DataFlowSet Name=\"FlatFileToTable\" SourceConnection=\"FlatFileConnection\" TargetConnection=\"SQLConnection\" SourceType=\"FlatFile\" TargetType=\"Table\" PickColumnsFromTarget=\"True\" ColumnDelimeter=\",\" RowDelimeter=\"{CR}{LF}\" IsColumnNamesInFirstDataRow=\"True\" RunParallel=\"True\" AllowFlatFileTruncate=\"False\" TruncateOrDeleteBeforeInsert=\"Truncate\" DeleteFilterClause=\"\" >"
+ " <DataFlow Name=\"TestFile1\" SourceName=\"MyFlatFile.txt\" TargetName=\"Sales.CustomerCOPY\" />"
+ " </DataFlowSet>"
+ " </Step>"
+ " </Process>"
+ "</CHEFMetaData>'"
+ ",0"
+ ",SUSER_SNAME()"
+ ",SYSDATETIME()"
+ ",SUSER_SNAME()"
+ ",SYSDATETIME())";
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ProcessID", 9500);
cmd.Parameters.AddWithValue("@StartStepID", 9510);
cmd.Parameters.AddWithValue("@RequestStatus", 1);
cmd.CommandText = "CHEF.InsertRequestQueue";
cmd.ExecuteNonQuery();
Program_Accessor.WraperCreateSSISPkg(args);
cmd.Parameters.Clear();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "DELETE FROM [CHEF].[RequestQueue] WHERE [QueueID] = (SELECT MAX(QueueID) FROM [CHEF].[RequestQueue] WHERE [ProcessID] = 9500)";
cmd.ExecuteNonQuery();
string serverName = ConfigurationManager.ConnectionStrings["CHEF"].ConnectionString.Split(';')[1];
int index = serverName.IndexOf('=');
int finalStatusID = 4;
bool testPass = true;
serverName = serverName.Substring(index + 1);
Server server = new Server(serverName);
IntegrationServices integrationServices = new IntegrationServices(server);
Microsoft.SqlServer.Management.IntegrationServices.PackageInfo packageInfo = null;
ProjectInfo projectInfo = integrationServices.Catalogs["SSISDB"].Folders["CHEFFolder"].Projects["9500_FlatFileToTableTaskTest"];
Assert.IsNotNull(projectInfo);
packageInfo = projectInfo.Packages["Package.dtsx"];
Assert.IsNotNull(packageInfo);
packageInfo.Execute(false, null);
Thread.Sleep(1000);
cmd.Parameters.Clear();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "SELECT MAX([StatusID])"
+ " FROM [CHEF].[Log]"
+ " WHERE [QueueID] ="
+ " (SELECT [QueueID]"
+ " FROM [CHEF].[RequestQueue]"
+ " WHERE [ProcessID] = 9500)";
SqlDataReader sqlDataReader = cmd.ExecuteReader();
if (sqlDataReader.HasRows)
{
sqlDataReader.Read();
finalStatusID = Convert.ToInt32(sqlDataReader[0].ToString());
}
if (finalStatusID == 4)
{
testPass = false;
}
sqlDataReader.Close();
if (testPass)
{
cmd.Parameters.Clear();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "UPDATE [CHEF].[RequestQueue]"
+ " SET [RequestStatus] = 2"
+ " WHERE [ProcessID] = 9500";
cmd.ExecuteNonQuery();
}
else
{
cmd.Parameters.Clear();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "UPDATE [CHEF].[RequestQueue]"
+ " SET [RequestStatus] = 4"
+ " WHERE [ProcessID] = 9500";
cmd.ExecuteNonQuery();
}
Assert.IsTrue(testPass);
}
public void TestMailTask()
{
string[] args = { };
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CHEF"].ConnectionString);
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "DELETE FROM [Chef].[Log]"
+ " WHERE [Log].[QueueID] IN"
+ " (SELECT QueueID FROM [CHEF].[RequestQueue]"
+ " WHERE ProcessID = 9200)";
cmd.ExecuteNonQuery();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "DELETE FROM [Chef].[RequestQueue]"
+ " WHERE ProcessID = 9200";
cmd.ExecuteNonQuery();
cmd.CommandText = "DELETE FROM [Chef].[MetaData]"
+ " WHERE ProcessID = 9200";
cmd.ExecuteNonQuery();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "INSERT INTO [CHEF].[Metadata] VALUES "
+ "(9200"
+ ",'MailTaskTest'"
+ ",'<CHEFMetaData ApplicationName=\"TestApplication\">"
+ " <Process ID=\"9200\" Name=\"DemoMailTask\" DefaultAllowTruncate=\"False\" VerboseLogging=\"False\" ExecuteExistingPackage=\"False\" >"
+ " <ConnectionSet>"
+ " <SMTPConnection key=\"SMTPConnection\" SmtpServer=\"mail.messaging.microsoft.com\" UseWindowsAuthentication=\"True\" EnableSsl=\"False\" />"
+ " </ConnectionSet>"
+ " <Step ID=\"9210\" Name=\"SendMailTask Test\" TypeID=\"1\" TypeName=\"Staging\">"
+ " <SendMailTask Name=\"SendMail\" SMTPServer=\"SMTPConnection\" From=\"[email protected]\" To=\"[email protected]\" CC=\"[email protected]\" BCC=\"[email protected]\" Subject=\"Test Mail: Send Mail Task\" Priority=\"Normal\" MessageSourceType=\"DirectInput\" MessageSource=\"Hi, this is mail task test. Please ignore.\">"
+ " <DataFlow Name=\"Populate Sales Currency\" SourceName=\"[Sales].[Currency]\" TargetName=\"[Sales].[Currency_Copy]\" />"
+ " <Attachments FileName=\"C:\\Users\\t-satsen\\Desktop\\Note.txt\" />"
+ " </SendMailTask>"
+ " </Step>"
+ " </Process>"
+ "</CHEFMetaData>'"
+ ",0"
+ ",SUSER_SNAME()"
+ ",SYSDATETIME()"
+ ",SUSER_SNAME()"
+ ",SYSDATETIME())";
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ProcessID", 9200);
cmd.Parameters.AddWithValue("@StartStepID", 9210);
cmd.Parameters.AddWithValue("@RequestStatus", 1);
cmd.CommandText = "CHEF.InsertRequestQueue";
cmd.ExecuteNonQuery();
Program_Accessor.WraperCreateSSISPkg(args);
cmd.Parameters.Clear();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "DELETE FROM [CHEF].[RequestQueue] WHERE [QueueID] = (SELECT MAX(QueueID) FROM [CHEF].[RequestQueue] WHERE [ProcessID] = 9200)";
cmd.ExecuteNonQuery();
string serverName = ConfigurationManager.ConnectionStrings["CHEF"].ConnectionString.Split(';')[1];
int index = serverName.IndexOf('=');
int finalStatusID = 4;
bool testPass = true;
serverName = serverName.Substring(index + 1);
Server server = new Server(serverName);
IntegrationServices integrationServices = new IntegrationServices(server);
Microsoft.SqlServer.Management.IntegrationServices.PackageInfo packageInfo = null;
ProjectInfo projectInfo = integrationServices.Catalogs["SSISDB"].Folders["CHEFFolder"].Projects["9200_MailTaskTest"];
Assert.IsNotNull(projectInfo);
packageInfo = projectInfo.Packages["Package.dtsx"];
Assert.IsNotNull(packageInfo);
packageInfo.Execute(false, null);
Thread.Sleep(1000);
cmd.Parameters.Clear();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "SELECT MAX([StatusID])"
+ " FROM [CHEF].[Log]"
+ " WHERE [QueueID] ="
+ " (SELECT [QueueID]"
+ " FROM [CHEF].[RequestQueue]"
+ " WHERE [ProcessID] = 9200)";
SqlDataReader sqlDataReader = cmd.ExecuteReader();
if (sqlDataReader.HasRows)
{
sqlDataReader.Read();
finalStatusID = Convert.ToInt32(sqlDataReader[0].ToString());
}
if (finalStatusID == 4)
{
testPass = false;
}
sqlDataReader.Close();
if (testPass)
{
cmd.Parameters.Clear();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "UPDATE [CHEF].[RequestQueue]"
+ " SET [RequestStatus] = 2"
+ " WHERE [ProcessID] = 9200";
cmd.ExecuteNonQuery();
}
else
{
cmd.Parameters.Clear();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "UPDATE [CHEF].[RequestQueue]"
+ " SET [RequestStatus] = 4"
+ " WHERE [ProcessID] = 9200";
cmd.ExecuteNonQuery();
}
Assert.IsTrue(testPass);
}
private void button1_Click(object sender, RoutedEventArgs e)
{
if (comboBoxPackages.SelectedIndex != -1)
{
KeyValuePair maxPages = (KeyValuePair)comboBoxPackages.SelectedItem;
string skey = maxPages.Key.ToString();
string svalue = maxPages.Value.ToString();
string pkgLocation = string.Empty;
string pkgFullName = string.Empty;
int requestStatus = 0;
//Package pkg;
//Microsoft.SqlServer.Dts.Runtime.Application app;
//DTSExecResult pkgResults;
Microsoft.SqlServer.Management.IntegrationServices.PackageInfo packageInfo = null;
try
{
ProgressBarExecutePackage.Visibility = Visibility.Visible;
Mouse.OverrideCursor = Cursors.Wait;
ProcessExecutePackageBar();
// Insert an entry in to Request Queue Table with Request Status 1
SqlConnection sqlConn = new SqlConnection(connectionString);
SqlCommand sqlCommand = new SqlCommand("CHEF.InsertRequestQueue", sqlConn);
sqlCommand.CommandType = CommandType.StoredProcedure;
SqlParameter ProcessID = sqlCommand.Parameters.Add("@ProcessID", SqlDbType.Int, 5);
ProcessID.Value = skey;
SqlParameter RequestStatus = sqlCommand.Parameters.Add("@RequestStatus", SqlDbType.TinyInt, 1);
RequestStatus.Value = 1;
sqlConn.Open();
int result = sqlCommand.ExecuteNonQuery();
sqlConn.Close();
string selecteSql = "select max(QueueID) as QueueID from chef.RequestQueue" + " where ProcessID= @processId and RequestStatus=1";
SqlCommand selectSqlCommand = new SqlCommand(selecteSql, sqlConn);
selectSqlCommand.Parameters.Add("@processId", SqlDbType.Int, 6, "ProcessID");
selectSqlCommand.Parameters["@processId"].Value = skey;
sqlConn.Open();
SqlDataReader selectDataReader = selectSqlCommand.ExecuteReader();
while (selectDataReader.Read())
{
QueueId = Convert.ToInt32(selectDataReader["QueueID"]);
}
sqlConn.Close();
sqlCommand = new SqlCommand("CHEF.ExecutePackageFromCatalog", sqlConn);
sqlConn.Open();
result = sqlCommand.ExecuteNonQuery();
sqlConn.Close();
if (result != 1)
{
pkgResults = false;
requestStatus = 4;
}
else
{
pkgResults = true;
requestStatus = 2;
}
// Update Request Queue Table with Request Status with result of package execution
SqlConnection updateSqlConnection = new SqlConnection(connectionString);
string updateSql = "UPDATE CHEF.RequestQueue " + "SET RequestStatus = @requestStatus " + "WHERE QueueID = @QueueID";
SqlCommand UpdateSqlCommand = new SqlCommand(updateSql, updateSqlConnection);
UpdateSqlCommand.Parameters.Add("@requestStatus", SqlDbType.Int, 5, "RequestStatus");
UpdateSqlCommand.Parameters.Add("@QueueID", SqlDbType.Int, 6, "QueueID");
UpdateSqlCommand.Parameters["@requestStatus"].Value = requestStatus;
UpdateSqlCommand.Parameters["@QueueID"].Value = QueueId;
updateSqlConnection.Open();
UpdateSqlCommand.ExecuteNonQuery();
if (pkgResults == true)
{
MessageBox.Show("Package in Project " + skey + "_" + svalue + ".ispac Successfully Executed. ", "Success", MessageBoxButton.OK, MessageBoxImage.Information);
}
else
{
MessageBox.Show("Package Execution Failed for Package in Project " + skey + "_" + svalue, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
buttonViewLog.Visibility = Visibility.Visible;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
ProgressBarExecutePackage.Visibility = Visibility.Hidden;
Mouse.OverrideCursor = Cursors.Arrow;
}
}
else
{
MessageBox.Show("Please select the Package", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
public void TestSQLTask()
{
string[] args = { };
string metadata = "<CHEFMetaData ApplicationName=\"TestApplication\">";
metadata += "<Process ID=\"9100\" Name=\"DemoSQL\" DefaultAllowTruncate=\"False\" VerboseLogging=\"False\" ExecuteExistingPackage=\"False\" >";
metadata += "<ConnectionSet>";
metadata += "<SQLConnection key=\"SQLConnection\" ServerName=\"(local)\" DatabaseName=\"AdventureWorks2012\" />";
metadata += "</ConnectionSet>";
metadata += "<Variables>";
metadata += "<Variable Name=\"vTID\" DataType=\"String\" Value=\"0\" />";
metadata += "</Variables>";
metadata += "<SetVariables>";
metadata += "<SetVariable TargetConnection=\"SQLConnection\" SQLStatement= \"SELECT CAST([TerritoryID] AS char(1)) FROM [AdventureWorks2012].[Sales].[SalesTerritory] WHERE [CountryRegionCode] = \'\'CA\'\'\">";
metadata += "<ResultSet VariableName=\"vTID\" Order=\"0\" />";
metadata += "</SetVariable>";
metadata += "</SetVariables>";
metadata += "<Step ID=\"9110\" Name=\"SQLTaskTest\" TypeID=\"1\" TypeName=\"Staging\" Description=\"This is to demo update\">";
metadata += "<SQLTaskSet Name=\"SQLTaskSet\" TargetConnection=\"SQLConnection\" RunParallel=\"False\">";
metadata += "<SQLTask Name=\"SQLTask\" SQLStatement=\"UPDATE [Sales].[SalesPerson] SET Bonus = Bonus+10000 WHERE TerritoryID = CAST(\'\'"+@[CHEF::vTID]+"\'\' as int)\" />";
metadata += "</SQLTaskSet>";
metadata += "</Step>";
metadata += "</Process>";
metadata += "</CHEFMetaData>";
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CHEF"].ConnectionString);
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "DELETE FROM [Chef].[Log]"
+ " WHERE [Log].[QueueID] IN"
+ " (SELECT QueueID FROM [CHEF].[RequestQueue]"
+ " WHERE ProcessID = 9100)";
cmd.ExecuteNonQuery();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "DELETE FROM [Chef].[RequestQueue]"
+ " WHERE ProcessID = 9100";
cmd.ExecuteNonQuery();
cmd.CommandText = "DELETE FROM [Chef].[MetaData]"
+ " WHERE ProcessID = 9100";
cmd.ExecuteNonQuery();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "INSERT INTO [CHEF].[Metadata] VALUES "
+ "(9100"
+ ",'SQLTaskTest'"
+ ",\'" + metadata + "\'"
+ ",0"
+ ",SUSER_SNAME()"
+ ",SYSDATETIME()"
+ ",SUSER_SNAME()"
+ ",SYSDATETIME())";
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ProcessID", 9100);
cmd.Parameters.AddWithValue("@StartStepID", 9110);
cmd.Parameters.AddWithValue("@RequestStatus", 1);
cmd.CommandText = "CHEF.InsertRequestQueue";
cmd.ExecuteNonQuery();
Program_Accessor.WraperCreateSSISPkg(args);
cmd.Parameters.Clear();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "DELETE FROM [CHEF].[RequestQueue] WHERE [QueueID] = (SELECT MAX(QueueID) FROM [CHEF].[RequestQueue] WHERE [ProcessID] = 9100)";
cmd.ExecuteNonQuery();
string serverName = ConfigurationManager.ConnectionStrings["CHEF"].ConnectionString.Split(';')[1];
int index = serverName.IndexOf('=');
int finalStatusID = 4;
bool testPass = true;
serverName = serverName.Substring(index + 1);
Server server = new Server(serverName);
IntegrationServices integrationServices = new IntegrationServices(server);
Microsoft.SqlServer.Management.IntegrationServices.PackageInfo packageInfo = null;
ProjectInfo projectInfo = integrationServices.Catalogs["SSISDB"].Folders["CHEFFolder"].Projects["9100_SQLTaskTest"];
Assert.IsNotNull(projectInfo);
packageInfo = projectInfo.Packages["Package.dtsx"];
Assert.IsNotNull(packageInfo);
packageInfo.Execute(false, null);
Thread.Sleep(1000);
cmd.Parameters.Clear();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "SELECT MAX([StatusID])"
+ " FROM [CHEF].[Log]"
+ " WHERE [QueueID] ="
+ " (SELECT [QueueID]"
+ " FROM [CHEF].[RequestQueue]"
+ " WHERE [ProcessID] = 9100)";
SqlDataReader sqlDataReader = cmd.ExecuteReader();
if (sqlDataReader.HasRows)
{
sqlDataReader.Read();
finalStatusID = Convert.ToInt32(sqlDataReader[0].ToString());
}
if (finalStatusID == 4)
{
testPass = false;
}
sqlDataReader.Close();
if (testPass)
{
cmd.Parameters.Clear();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "UPDATE [CHEF].[RequestQueue]"
+ " SET [RequestStatus] = 2"
+ " WHERE [ProcessID] = 9100";
cmd.ExecuteNonQuery();
}
else
{
cmd.Parameters.Clear();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "UPDATE [CHEF].[RequestQueue]"
+ " SET [RequestStatus] = 4"
+ " WHERE [ProcessID] = 9100";
cmd.ExecuteNonQuery();
}
Assert.IsTrue(testPass);
}
public void TestExecutePackageTask()
{
string[] args = { };
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CHEF"].ConnectionString);
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "DELETE FROM [Chef].[Log]"
+ " WHERE [Log].[QueueID] IN"
+ " (SELECT QueueID FROM [CHEF].[RequestQueue]"
+ " WHERE ProcessID = 9300)";
cmd.ExecuteNonQuery();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "DELETE FROM [Chef].[RequestQueue]"
+ " WHERE ProcessID = 9300";
cmd.ExecuteNonQuery();
cmd.CommandText = "DELETE FROM [Chef].[MetaData]"
+ " WHERE ProcessID = 9300";
cmd.ExecuteNonQuery();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "INSERT INTO [CHEF].[Metadata] VALUES "
+ "(9300"
+ ",'ExecutePackageTaskTest'"
+ ",'<CHEFMetaData ApplicationName=\"TestApplication\">"
+ " <Process ID=\"9300\" Name=\"DemoExecutePackage\" DefaultAllowTruncate=\"False\" VerboseLogging=\"False\" ExecuteExistingPackage=\"False\" >"
+ " <ConnectionSet>"
+ " <FileConnection key=\"dtsxPackageConnection\" FileName=\"C:\\Users\\t-satsen\\Documents\\Visual Studio 2010\\Projects\\Package_DatabaseToDatabase\\BasicFeaturesPackage\\bin\\Debug\\TableToTableDataTransferPkg.dtsx\" />"
+ " </ConnectionSet>"
+ " <Step ID=\"9310\" Name=\"Execute Package Test\" TypeID=\"1\" TypeName=\"Staging\">"
+ " <PackageExecution Name=\"ExecPackageTask\" Connection=\"dtsxPackageConnection\" PackageName=\"DemoPackageTask\" />"
+ " </Step>"
+ " </Process>"
+ "</CHEFMetaData>'"
+ ",0"
+ ",SUSER_SNAME()"
+ ",SYSDATETIME()"
+ ",SUSER_SNAME()"
+ ",SYSDATETIME())";
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ProcessID", 9300);
cmd.Parameters.AddWithValue("@StartStepID", 9310);
cmd.Parameters.AddWithValue("@RequestStatus", 1);
cmd.CommandText = "CHEF.InsertRequestQueue";
cmd.ExecuteNonQuery();
Program_Accessor.WraperCreateSSISPkg(args);
cmd.Parameters.Clear();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "DELETE FROM [CHEF].[RequestQueue] WHERE [QueueID] = (SELECT MAX(QueueID) FROM [CHEF].[RequestQueue] WHERE [ProcessID] = 9300)";
cmd.ExecuteNonQuery();
string serverName = ConfigurationManager.ConnectionStrings["CHEF"].ConnectionString.Split(';')[1];
int index = serverName.IndexOf('=');
int finalStatusID = 4;
bool testPass = true;
serverName = serverName.Substring(index + 1);
Server server = new Server(serverName);
IntegrationServices integrationServices = new IntegrationServices(server);
Microsoft.SqlServer.Management.IntegrationServices.PackageInfo packageInfo = null;
ProjectInfo projectInfo = integrationServices.Catalogs["SSISDB"].Folders["CHEFFolder"].Projects["9300_ExecutePackageTaskTest"];
Assert.IsNotNull(projectInfo);
packageInfo = projectInfo.Packages["Package.dtsx"];
Assert.IsNotNull(packageInfo);
packageInfo.Execute(false, null);
Thread.Sleep(1000);
cmd.Parameters.Clear();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "SELECT MAX([StatusID])"
+ " FROM [CHEF].[Log]"
+ " WHERE [QueueID] ="
+ " (SELECT [QueueID]"
+ " FROM [CHEF].[RequestQueue]"
+ " WHERE [ProcessID] = 9300)";
SqlDataReader sqlDataReader = cmd.ExecuteReader();
if (sqlDataReader.HasRows)
{
sqlDataReader.Read();
finalStatusID = Convert.ToInt32(sqlDataReader[0].ToString());
}
if (finalStatusID == 4)
{
testPass = false;
}
sqlDataReader.Close();
if (testPass)
{
cmd.Parameters.Clear();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "UPDATE [CHEF].[RequestQueue]"
+ " SET [RequestStatus] = 2"
+ " WHERE [ProcessID] = 9300";
cmd.ExecuteNonQuery();
}
else
{
cmd.Parameters.Clear();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "UPDATE [CHEF].[RequestQueue]"
+ " SET [RequestStatus] = 4"
+ " WHERE [ProcessID] = 9300";
cmd.ExecuteNonQuery();
}
Assert.IsTrue(testPass);
}
private static void WraperCreateSSISPkg(string[] args)
{
const string pkgCreationLog = "_PackageCreation";
const string sqlLogConnectionKey = "SQLPreAndPostLogKey007";
const string flatFileConnectionLogKey = "FileVerboseLoggingProviderConnection";
string PreSQL = "Declare @StepName varchar(255)=left('<StepName>',255) EXEC CHEF.InsertLog @StepName," + (int)JobStatus.Started;
string PostSQL = "Declare @StepName varchar(255)=left('<StepName>',255) Exec CHEF.InsertLog @StepName,?";
string QueueNextSQL = "EXEC CHEF.QueueNext";
string strPackageLocation = string.Empty;
string strProjectLocation = string.Empty;
CatalogFolder catalogFolder = null;
bool executeExistingPackage = false;
SqlConnection cn = new SqlConnection(DatabaseConnection.CHEF);
SqlCommand cm = cn.CreateCommand();
try
{
string isMetaDataFileOrDatabase = ConfigurationManager.AppSettings["MetaDataFileOrDatabase"];
//Reading Global Config XML form Database
cn.Open();
cm.Parameters.Clear();
cm.CommandType = CommandType.StoredProcedure;
cm.Parameters.AddWithValue("@MetadataTypeID", MetaDataTypes.GlobalConfig);
cm.CommandText = "CHEF.GetMetaData";
SqlDataReader drGlobalConfig = cm.ExecuteReader();
if (drGlobalConfig.HasRows)
{
drGlobalConfig.Read();
strGlobalConfigXML = drGlobalConfig["MetaData"].ToString();
}
drGlobalConfig.Close();
//Reading MetaDataProccess form Database
cm.Parameters.Clear();
cm.CommandType = CommandType.StoredProcedure;
cm.Parameters.AddWithValue("@MetadataTypeID", MetaDataTypes.Process);
cm.CommandText = "CHEF.GetMetaData";
SqlDataReader drProcess = cm.ExecuteReader();
if (drProcess.HasRows)
{
drProcess.Read();
strMetaDataXML = drProcess["MetaData"].ToString();
processName = drProcess["ProcessName"].ToString();
processID = drProcess["ProcessID"].ToString();
startStepID = Convert.ToInt32(drProcess["StartStepID"].ToString());
processType = (ProcessTypeNames)Enum.Parse(typeof(ProcessTypeNames), drProcess["ProcessTypeName"].ToString());
}
drProcess.Close();
XElement root = XElement.Load(new StringReader(strMetaDataXML));
XElement process = root.Element("Process");
if (process.Attribute("ExecuteExistingPackage") == null)
{
executeExistingPackage = false;
}
else
{
executeExistingPackage = Convert.ToBoolean(process.Attribute("ExecuteExistingPackage").Value);
}
if (executeExistingPackage)
{
Microsoft.SqlServer.Management.IntegrationServices.PackageInfo packageInfo = null;
catalogFolder = CheckCatalogFolder("CHEFFolder");
packageInfo = GetPackageFromCatalog(catalogFolder, processID + "_" + processName);
if (packageInfo != null)
{
return;
}
}
//CHEFMetaData chefMetaData;
CHEFGlobalConfig chefGlobalConfig;
// Package Creating Logging
cm.Parameters.Clear();
cm.CommandType = CommandType.StoredProcedure;
cm.Parameters.AddWithValue("@ProcessStep", processName + pkgCreationLog);
cm.CommandText = "CHEF.InsertLog";
cm.ExecuteNonQuery();
SSISBuilder pkg;
chefGlobalConfig = GetCHEFGlobalConfig <CHEFGlobalConfig>(strGlobalConfigXML);
/* PUTTING THIS PORTION WITHIN COMMENTS; PACKAGE STORAGE LOCATION NO LONGER REQUIRED SINCE IT IS DEPLOYED TO CATALOG */
//if (chefGlobalConfig.GlobalConfiguration.OutputPackageLocation == null)
//{
// throw new Exception("Invalid Output Package Location");
//}
//else
//{
// if (args.Length <= 0)
// strPackageLocation = chefGlobalConfig.GlobalConfiguration.OutputPackageLocation;
// else
// strPackageLocation = args[0].ToString();
// //Removing the last successfully created package
// if (File.Exists(strPackageLocation)) File.Delete(strPackageLocation);
//}
//chefMetaData = GetCHEFMetaData<CHEFMetaData>(strMetaDataXML);
//Linq
pkg = new SSISBuilder(processName, processName);
//pkg.VerboseLogging = Convert.ToBoolean(chefMetaData.Process.VerboseLogging);
// Bad Rows error rediction
chefGlobalConfig.GlobalConfiguration.BadRowFolderLocation = chefGlobalConfig.GlobalConfiguration.BadRowFolderLocation + @"\" + DateTime.Now.ToString().Replace(':', '-');
if (!System.IO.Directory.Exists(chefGlobalConfig.GlobalConfiguration.BadRowFolderLocation))
{
System.IO.Directory.CreateDirectory(chefGlobalConfig.GlobalConfiguration.BadRowFolderLocation);
}
pkg.BadRowsFolderLocation = chefGlobalConfig.GlobalConfiguration.BadRowFolderLocation;
// SQL Connection for Log
pkg.AddConnection(new Connection()
{
Key = sqlLogConnectionKey, ConnectionString = cn.ConnectionString + ";Provider=SQLNCLI11.1;", ConnectionType = ConnectionTypes.OleDBConnection
});
//File Verbose Logging Provider Connection
pkg.AddConnection(new Connection()
{
Key = flatFileConnectionLogKey, ConnectionString = chefGlobalConfig.GlobalConfiguration.LogLocation, ConnectionType = ConnectionTypes.FileConnection
});
//Package Start Execuation Logging
Executable sqlTaskPre = pkg.AddExecutableSQLTask(PreSQL.Replace("<StepName>", processName + "_PackageExecution"), sqlLogConnectionKey, "Pre-" + processName + "_PackageExecution", "PackageFailSuccess");
pkg.AddPrecedenceConstraints(sqlTaskPre);
//Package Level Variables
pkg.SetVariableValue("PackageFailSuccess", 2, "Int32");
PackageCreation(strMetaDataXML, pkg, processType.ToString());
// Process Finished Package Execution Logging
Executable sqlTaskQueueNext = pkg.AddExecutableSQLTask(QueueNextSQL, sqlLogConnectionKey, "QueueNextETL");
pkg.AddPrecedenceConstraints(sqlTaskQueueNext);
// Process Finished Package Execution Logging
Executable sqlTaskPost = pkg.AddExecutableSQLTask(PostSQL.Replace("<StepName>", processName + "_PackageExecution"), sqlLogConnectionKey, "Post-" + processName + "_PackageExecution", "PackageFailSuccess");
pkg.AddPrecedenceConstraints(sqlTaskPost);
/* PUTTING THIS PORTION WITHIN COMMENTS; PACKAGE ARCHIVE LOCATION NO LONGER REQUIRED */
bool DumpPackageFileOnDisk = false;
if (ConfigurationManager.AppSettings["DumpPackageFileOnDisk"] == null)
{
DumpPackageFileOnDisk = false;
}
else if (ConfigurationManager.AppSettings["DumpPackageFileOnDisk"].ToString() == "True")
{
DumpPackageFileOnDisk = true;
}
if (System.IO.Directory.Exists(chefGlobalConfig.GlobalConfiguration.OutputPackageLocation))
{
System.IO.Directory.Delete(chefGlobalConfig.GlobalConfiguration.OutputPackageLocation);
}
if (DumpPackageFileOnDisk)
{
strPackageLocation = chefGlobalConfig.GlobalConfiguration.OutputPackageLocation;
pkg.SaveAsPackage(strPackageLocation);
}
strProjectLocation = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + @"\Temp";
catalogFolder = CheckCatalogFolder("CHEFFolder");
CreateProjectAndDeploy(catalogFolder, strProjectLocation, pkg.GetCurrentPackage());
// Logging successful creation of Package
cm.Parameters.Clear();
cm.CommandText = "CHEF.InsertLog";
cm.CommandType = CommandType.StoredProcedure;
cm.Parameters.AddWithValue("@ProcessStep", processName + pkgCreationLog);
cm.Parameters.AddWithValue("@StatusID", 2);
cm.ExecuteNonQuery();
cm.Parameters.Clear();
}
catch (Exception exx)
{
string errorMessage = exx.Message;
if (errorMessage.Length > 500)
{
errorMessage = errorMessage.Substring(0, 499);
}
cm.CommandText = "CHEF.InsertLog";
cm.Parameters.Clear();
cm.CommandType = CommandType.StoredProcedure;
cm.Parameters.AddWithValue("@ProcessStep", processName + pkgCreationLog);
cm.Parameters.AddWithValue("@Description", errorMessage);
cm.Parameters.AddWithValue("@StatusID", JobStatus.Failed);
cm.ExecuteNonQuery();
//throw (exx);
}
finally
{
cn.Close();
cm.Dispose();
}
}