internal static void ExecuteStoredProcedure(IntegrationServices store, string schema, string sprocName, SqlParameter[] parameters, string databaseName)
{
try
{
SqlConnection sqlConnection = SqlHelper.GetSqlConnection(store.Connection);
SqlCommand sqlCommand = sqlConnection.CreateCommand();
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandTimeout = 600000;
sqlCommand.CommandText = string.Format(CultureInfo.InvariantCulture, "[{0}].[{1}].[{2}]", new object[]
{
Helpers.GetEscapedName(databaseName),
string.IsNullOrEmpty(schema) ? "dbo" : schema,
sprocName
});
if (parameters != null && parameters.Length > 0)
{
sqlCommand.Parameters.AddRange(parameters);
}
sqlCommand.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw new IntegrationServicesException($"StoredProcedureException: {sprocName}, {ex.Message}");
}
}
private void CreateEnvironmentButton_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"];
EnvironmentInfo env = new EnvironmentInfo(folder, "Environment1", "Description of Environment1");
env.Create();
env.Variables.Add("var1", TypeCode.Int32, 1, false, "Var1 Description");
env.Variables.Add("sensitiveVar2", TypeCode.String, "secure value", true, "");
env.Alter();
//ProjectInfo p = folder.Projects["ProSSIS"];
//p.References.Add("Environment1", folder.Name);
//p.Alter();
MessageBox.Show("Environment Created");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
static void Main(string[] args)
{
string targetServerName = "localhost";
string folderName = "Permanencia";
string projectName = "Permanencia";
string packageName = "Package1.dtsx";
// Conexion
string sqlConnectionString = "Data Source=" + targetServerName +
";Initial Catalog=master;Integrated Security=SSPI;";
SqlConnection sqlConnection = new SqlConnection(sqlConnectionString);
// Integration Services obj
IntegrationServices integrationServices = new IntegrationServices(sqlConnection);
// Integration Services catalog
Catalog catalog = integrationServices.Catalogs["SSISDB"];
// Carpeta
CatalogFolder folder = catalog.Folders[folderName];
// Proyecto
ProjectInfo project = folder.Projects[projectName];
// Package
PackageInfo package = project.Packages[packageName];
// Run run package
package.Execute(false, null);
}
private string GetPackageStatus()
{
Server server = new Server(_server);
IntegrationServices service = new IntegrationServices(server);
Catalog catalog = service.Catalogs[_catalog];
foreach (ExecutionOperation exec in catalog.Executions)
{
if (exec.FolderName == _folder && exec.PackageName == _packageinfo.Name)
{
catalog.Executions.Refresh();
if (!exec.Completed)
{
return("Executing");
}
}
}
return("Sleeping");
//RunningPackages rps = dtsapp.GetRunningPackages(_server);
//foreach (RunningPackage rp in rps)
//{
// if (rp.PackageID == new Guid(_packageinfo.PackageGuid))
// {
// return "Executing";
// }
//}
//return "Sleeping";
}
private void TransferPackageButton_Click(object sender, EventArgs e)
{
try
{
Server server = new Server(_server);
IntegrationServices isServer = new IntegrationServices(server);
Catalog catalog = isServer.Catalogs["SSISDB"];
CatalogFolder folder = new CatalogFolder(catalog, "Test", "Test description");// catalog.Folders["ProSSIS"];
folder.Create();
Project localProject = CreateNewProject("newproject.ispac", "New Project");
folder.DeployProject("New Project", System.IO.File.ReadAllBytes("newprojects.ispac"));
folder.Alter();
ProjectInfo newProject = folder.Projects["New Project"];
newProject.Move("ProSSIS");
newProject.Alter();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
} //END Create web method.
//+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
#endregion
#region Private Methods.
// =====================================================================================
// -------------------------------------------------------------------------------------
// =====================================================================================
/// <summary>
/// This method updates the TokenUserProfile object in the database.
/// </summary>
/// <param name="UserProfile">EusTokenUserProfile object</param>
/// <returns>EvEventCodes enumerated value.</returns>
// -------------------------------------------------------------------------------------
private EvEventCodes UpdateTokenUserProfile(EusTokenUserProfile UserProfile)
{
this.LogMethod("UpdateTokenUserProfile");
//
// initialise the methods variables and objects.
//
EvUserProfileBase serviceUserProfile = new EvUserProfileBase( );
serviceUserProfile.UserId = "TokenService";
serviceUserProfile.CommonName = "Token Service";
//
// Set the device client service session state.
//
IntegrationServices integrationServices = new IntegrationServices(
Global.ServiceVersion,
Global.GlobalObjectList,
Global.ApplicationPath,
serviceUserProfile,
Global.UniForm_BinaryFilePath,
Global.UniForm_BinaryServiceUrl);
integrationServices.LoggingLevel = 5;
integrationServices.EventLogSource = Global.EventLogSource;
//
// Update the token user profile.
//
EvEventCodes result = integrationServices.UpdateTokenUserProfile(UserProfile);
this.LogClass(integrationServices.Log);
this.LogMethodEnd("UpdateTokenUserProfile");
return(result);
}
private void ShowRunningPackagesButton_Click(object sender, EventArgs e)
{
Server server = new Server(_server);
try
{
IntegrationServices isServer = new IntegrationServices(server);
Catalog catalog = isServer.Catalogs["SSISDB"];
StringBuilder messages = new StringBuilder();
foreach (ExecutionOperation exec in catalog.Executions)
{
catalog.Executions.Refresh();
if (!exec.Completed)
{
messages.AppendLine("Package " + exec.PackageName + " is running. The project is " + exec.ProjectName + ". It started running at " + exec.StartTime.ToString());
}
}
LogFileTextbox.Text = messages.ToString();
//MessageBox.Show("Package executed");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
static void Main(string[] args)
{
// Variables
string targetServerName = @"192.168.2.111\SQL17ENTERPRISE,1435";
string folderName = "SSISPackageItems";
string projectName = "SSISPackageRunInSQLJob";
string packageName = "Package.dtsx";
// Create a connection to the server
string sqlConnectionString = "Data Source=" + targetServerName + ";Initial Catalog=master;Integrated Security=SSPI;";
SqlConnection sqlConnection = new SqlConnection(sqlConnectionString);
// Create the Integration Services object
IntegrationServices integrationServices = new IntegrationServices(sqlConnection);
// Get the Integration Services catalog
Catalog catalog = integrationServices.Catalogs["SSISDB"];
// Get the folder
CatalogFolder folder = catalog.Folders[folderName];
// Get the project
ProjectInfo project = folder.Projects[projectName];
// Get the package
PackageInfo package = project.Packages[packageName];
// Run the package
package.Execute(false, null);
}
public void CreateProjectAndDeployTest2()
{
string serverName = ConfigurationManager.ConnectionStrings["CHEF"].ConnectionString.Split(';')[1];
int index = serverName.IndexOf('=');
serverName = serverName.Substring(index + 1);
Server server = new Server(serverName);
IntegrationServices integrationServices = new IntegrationServices(server);
Catalog catalog = integrationServices.Catalogs["SSISDB"];
CatalogFolder catalogFolder = catalog.Folders["NotTheCHEFFolder"];
if (catalogFolder == null)
{
catalogFolder = new CatalogFolder(catalog, "NotTheCHEFFolder", "Not the CHEF folder.");
catalogFolder.Create();
}
string strProjectLocation = @"C:\Program Files\Microsoft\CHEF\Temp";
Package package = null;
Application app = new Application();
package = app.LoadPackage(@"C:\Users\t-satsen\Documents\Visual Studio 2010\Projects\Package_DatabaseToDatabase\BasicFeaturesPackage\bin\Debug\TableToTableDataTransferPkg.dtsx", null);
Program_Accessor.processID = "100";
Program_Accessor.processName = "TestTask";
if (catalogFolder.Projects[Program_Accessor.processID + "_" + Program_Accessor.processName] != null)
{
catalogFolder.Projects[Program_Accessor.processID + "_" + Program_Accessor.processName].Drop();
}
Program_Accessor.CreateProjectAndDeploy(catalogFolder, strProjectLocation, package);
Assert.IsNull(catalogFolder.Projects[Program_Accessor.processID + "_" + Program_Accessor.processName]);
}
public override IExecutionResult Run()
{
var connection = new SqlConnection(string.Format(@"Data Source={0};Initial Catalog=master;Integrated Security=SSPI;", Etl.Server));
var integrationServices = new IntegrationServices(connection);
var catalog = integrationServices.Catalogs[Etl.Catalog];
var folder = catalog.Folders[Etl.Folder];
var project = folder.Projects[Etl.Project];
var package = project.Packages[Etl.Name];
var setValueParameters = new Collection <PackageInfo.ExecutionValueParameterSet>();
setValueParameters.Add(new PackageInfo.ExecutionValueParameterSet
{
ObjectType = 50,
ParameterName = "SYNCHRONIZED",
ParameterValue = 1
});
long executionIdentifier = package.Execute(Etl.Is32Bits, null, setValueParameters);
var execution = catalog.Executions[executionIdentifier];
var result = EtlRunResult.Build(
execution.Status
, execution.Messages.Where(m => m.MessageType == 120 || m.MessageType == 110).Select(m => m.Message)
, execution.StartTime
, execution.EndTime);
return(result);
}
static void Main(string[] args)
{
string targetFolderName = "dailyClosedJobs";
string projectName = "dailyClosedJobs.dtsx";
string projectFilePath = @"" + ConfigurationManager.AppSettings["FolderPath"];
try
{
// Create a connection to the server// Create the Integration Services object
string conStr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
SqlConnection sqlConnection = new SqlConnection(conStr);
using (var con = sqlConnection)
{
IntegrationServices integrationServices = new IntegrationServices(con);
// Get the Integration Services catalog
Catalog catalog = integrationServices.Catalogs["SSISDB"];
// Create the target folder
CatalogFolder folder = new CatalogFolder(catalog,
targetFolderName, "Folder description");
Console.WriteLine("Deploying " + projectName + " project.");
byte[] projectFile = File.ReadAllBytes(projectFilePath);
folder.DeployProject(projectName, projectFile);
Console.WriteLine("Done.");
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
private PackageInfo GetPackage(IntegrationServices integrationServices)
{
if (!integrationServices.Catalogs.Contains(Etl.Catalog))
{
var names = String.Join(", ", integrationServices.Catalogs.Select(c => c.Name));
throw new ArgumentOutOfRangeException("Catalog", String.Format("The catalog named '{0}' hasn't been found on the server '{1}'. List of existing catalogs: {2}.", Etl.Catalog, Etl.Server, names));
}
var catalog = integrationServices.Catalogs[Etl.Catalog];
if (!catalog.Folders.Contains(Etl.Folder))
{
var names = String.Join(", ", catalog.Folders.Select(f => f.Name));
throw new ArgumentOutOfRangeException("Folder", String.Format("The folder named '{0}' hasn't been found on the catalog '{1}'. List of existing folders: {2}.", Etl.Folder, Etl.Catalog, names));
}
var folder = catalog.Folders[Etl.Folder];
if (!folder.Projects.Contains(Etl.Project))
{
var names = String.Join(", ", folder.Projects.Select(p => p.Name));
throw new ArgumentOutOfRangeException("Project", String.Format("The project named '{0}' hasn't been found on the catalog '{1}'. List of existing projects: {2}.", Etl.Project, Etl.Folder, names));
}
var project = folder.Projects[Etl.Project];
if (!project.Packages.Contains(Etl.Name))
{
var names = String.Join(", ", project.Packages.Select(p => p.Name));
throw new ArgumentOutOfRangeException("Name", String.Format("The package named '{0}' hasn't been found on the project '{1}'. List of existing packages: {2}.", Etl.Name, Etl.Project, names));
}
var package = project.Packages[Etl.Name];
return(package);
}
private string GetMessages(IntegrationServices integrationServices, long executionId, bool failOnWarning)
{
StringBuilder messages = new StringBuilder();
foreach (var message in integrationServices.Catalogs[Catalog].Executions[executionId].Messages)
{
if (message.MessageType == 120)
{
messages.AppendLine($"{message.MessageTime} - Error: {message.Message}");
this.HasFailed = true;
}
if (failOnWarning && message.MessageType == 110 && !message.Message.Contains("global shared memory"))
{
messages.AppendLine($"{message.MessageTime} - Warning: {message.Message}");
this.HasFailed = true;
}
if (message.MessageType == 130)
{
messages.AppendLine($"{message.MessageTime} - Task Failed: {message.Message}");
}
}
return(messages.ToString());
}
protected PackageGroupCollection BuildPackageGroupCollection(string pathToProject)
{
string[] path = pathToProject.Split('/');
string catalog = path[0];
string folder = path[1];
string project = path[2];
Server server = new Server(ssisServer);
IntegrationServices service = new IntegrationServices(server);
Catalog catalogObject = service.Catalogs[catalog];
CatalogFolder folderObject = catalogObject.Folders[folder];
ProjectInfo projectObject = folderObject.Projects[project];
PackageGroupCollection collection = new PackageGroupCollection();
foreach (Microsoft.SqlServer.Management.IntegrationServices.PackageInfo p in projectObject.Packages)
{
PackageGroup g = new PackageGroup(p, server.Name, catalog, folder, project);
collection.Add(g);
}
return(collection);
}
public override IExecutionResult Run()
{
var connection = new SqlConnection(string.Format(@"Data Source={0};Initial Catalog=master;Integrated Security=SSPI;", Etl.Server));
var integrationServices = new IntegrationServices(connection);
var catalog = integrationServices.Catalogs[Etl.Catalog];
var folder = catalog.Folders[Etl.Folder];
var project = folder.Projects[Etl.Project];
var package = project.Packages[Etl.Name];
var setValueParameters = new Collection<PackageInfo.ExecutionValueParameterSet>();
setValueParameters.Add(new PackageInfo.ExecutionValueParameterSet
{
ObjectType = 50,
ParameterName = "SYNCHRONIZED",
ParameterValue = 1
});
long executionIdentifier = package.Execute(Etl.Is32Bits, null, setValueParameters);
var execution = catalog.Executions[executionIdentifier];
var result = EtlRunResult.Build(
execution.Status
, execution.Messages.Where(m => m.MessageType == 120 || m.MessageType == 110).Select(m => m.Message)
, execution.StartTime
, execution.EndTime);
return result;
}
//MyCode: Check if catalog exists and if folder exists; create if not.
private static CatalogFolder CheckCatalogFolder(string folderName)
{
string serverName = ConfigurationManager.ConnectionStrings["CHEF"].ConnectionString.Split(';')[1];
int index = serverName.IndexOf('=');
serverName = serverName.Substring(index + 1);
Server server = new Server(serverName);
IntegrationServices integrationServices = new IntegrationServices(server);
Catalog catalog = integrationServices.Catalogs["SSISDB"];
CatalogFolder catalogFolder = null;
if (catalog == null)
{
//Throw exception if catalog doesn't exist
throw new Exception("Catalog not found. Please create the SSISDB catalog and try again.");
}
else
{
catalogFolder = catalog.Folders[folderName];
if (catalogFolder == null)
{
//Create catalog folder if it doesn't exist
catalogFolder = new CatalogFolder(catalog, folderName, "This is the folder which contains all projects generated through CHEF");
catalogFolder.Create();
}
}
return(catalogFolder);
}
internal static bool IsSysAdmin(IntegrationServices store)
{
string cmdText = "SELECT ISNULL(IS_SRVROLEMEMBER ('sysadmin'), 0)";
object value = SqlHelper.ExecuteSQLCommand(store.Connection, CommandType.Text, cmdText, null, ExecuteType.ExecuteScalar);
int num = Convert.ToInt32(value, CultureInfo.InvariantCulture);
return(num == 1);
}
// GET api/values/5
public string Get(int id)
{
string folderName = "ConArchETL";
string environmentName = "";
string cloudDatabaseHost = ".";
//string cloudDatabaseUser = "******";
//string cloudDatabasePassword = "******";
string settings = string.Copy(ConfigurationManager.ConnectionStrings["ManagementContext"].ConnectionString);
string ADO_Evasys_ConnectionString = ".";
// Create a connection to the server
List <string> connectionConfigs = settings.Split(';').Where(c => !string.IsNullOrEmpty(c)).ToList();
Dictionary <string, string> connectionValues = connectionConfigs.Select(item => item.Split('=')).ToDictionary(s => s[0], s => s[1]);
connectionValues.TryGetValue("Data Source", out cloudDatabaseHost);
string sqlConnectionString = "Data Source=" + cloudDatabaseHost +
";Initial Catalog=master;Integrated Security=SSPI;";
SqlConnection sqlConnection = new SqlConnection(sqlConnectionString);
// Create the Integration Services object
IntegrationServices integrationServices = new IntegrationServices(sqlConnection);
// Get the Integration Services catalog
Catalog catalog = integrationServices.Catalogs["SSISDB"];
// Get the folder
CatalogFolder folder = catalog.Folders[folderName];
// Get Environment
environmentName = String.Format("Environment_{0}", folder.Environments.ToList().Count *2);
EnvironmentInfo environmentInfo = folder.Environments[environmentName];
if (environmentInfo != null)
{
environmentInfo.Drop();
}
environmentInfo = new EnvironmentInfo(folder, environmentName, environmentName);
environmentInfo.Create();
if (null == environmentInfo.Variables["CM.ADO_Evasys.ConnectionString"])
{
environmentInfo.Variables.Add("CM.ADO_Evasys.ConnectionString", TypeCode.String, ADO_Evasys_ConnectionString, false, "ConnectionString");
}
else
{
environmentInfo.Variables["CM.ADO_Evasys.ConnectionString"].Value = ADO_Evasys_ConnectionString;
}
environmentInfo.Alter();
return(cloudDatabaseHost);
}
public IntegrationPackageResult Execute(string packageName, SsisParameter[] parameters)
{
SqlConnection ssisConnection = new SqlConnection(SqlConnectionString);
IntegrationServices ssisServer = new IntegrationServices(ssisConnection);
// The reference to the package which you want to execute
PackageInfo ssisPackage = ssisServer.Catalogs[CashDiscipline.Common.Constants.SsisCatalog].Folders[ssisFolderName].Projects[catalogName].Packages[packageName];
// Add execution parameter to override the default asynchronized execution. If you leave this out the package is executed asynchronized
Collection <PackageInfo.ExecutionValueParameterSet> executionParameter = new Collection <PackageInfo.ExecutionValueParameterSet>();
executionParameter.Add(new PackageInfo.ExecutionValueParameterSet {
ObjectType = 50, ParameterName = "SYNCHRONIZED", ParameterValue = 1
});
// Modify package parameter
foreach (var svcParam in parameters)
{
ParameterInfo pInfo = null;
var pKey = new ParameterInfo.Key(svcParam.ParameterName);
if (!ssisPackage.Parameters.TryGetValue(pKey, out pInfo))
{
throw new InvalidOperationException(string.Format("Parameter name {0} does not exist in package.", pKey.Name));
}
pInfo.Set(ParameterInfo.ParameterValueType.Literal, svcParam.ParameterValue);
}
ssisPackage.Alter();
// Get the identifier of the execution to get the log
long executionIdentifier = ssisPackage.Execute(false, null, executionParameter);
// Loop through the log and add the messages to the listbox
SsisMessages = new List <SsisMessage>();
var execution = ssisServer.Catalogs["SSISDB"].Executions[executionIdentifier];
foreach (OperationMessage message in ssisServer.Catalogs["SSISDB"].Executions[executionIdentifier].Messages)
{
SsisMessages.Add(new SsisMessage()
{
MessageSourceType = message.MessageSourceType,
Message = message.Message,
MessageType = message.MessageType
});
}
// Update result
PackageResult = new IntegrationPackageResult();
PackageResult.SsisMessages = this.SsisMessages;
PackageResult.ExecutionIdentifer = executionIdentifier;
PackageResult.PackageName = packageName;
PackageResult.OperationStatus = (SsisOperationStatus)execution.Status;
return(PackageResult);
}
public void CheckCatalogFolderTest1()
{
string folderName = "CHEFFolder"; // TODO: Initialize to an appropriate value
Server server = new Server("localhost");
IntegrationServices integrationServices = new IntegrationServices(server);
Catalog catalog = integrationServices.Catalogs["SSISDB"];
CatalogFolder expected = catalog.Folders[folderName]; // TODO: Initialize to an appropriate value
CatalogFolder actual;
actual = Program_Accessor.CheckCatalogFolder(folderName);
Assert.AreEqual(expected.FolderId, actual.FolderId);
}
internal static object GetPrincipalIdByName(IntegrationServices store, string databaseName, string principalName)
{
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@name", principalName)
};
string cmdText = string.Format(CultureInfo.InvariantCulture, "SELECT [principal_id] from [{0}].[internal].[get_database_principals]() where name = @name", new object[]
{
databaseName
});
return(SqlHelper.ExecuteSQLCommand(store.Connection, CommandType.Text, cmdText, parameters, ExecuteType.ExecuteScalar));
}
private void DropFolderButton_Click(object sender, EventArgs e)
{
try
{
Server server = new Server(_server);
IntegrationServices isServer = new IntegrationServices(server);
Catalog catalog = isServer.Catalogs["SSISDB"];
CatalogFolder folder = new CatalogFolder(catalog, "Test", "Test description");// catalog.Folders["ProSSIS"];
folder.Create();
EnvironmentInfo newEnv = new EnvironmentInfo(folder, "Environment1", "Description of Environment1");
newEnv.Create();
newEnv.Variables.Add("var1", TypeCode.Int32, 1, false, "Var1 Description");
newEnv.Variables.Add("sensitiveVar2", TypeCode.String, "secure value", true, "");
newEnv.Alter();
// this will fail because there is an environment under the folder
try
{
folder.Drop();
}
catch
{
}
foreach (EnvironmentInfo env in folder.Environments.ToArray())
{
env.Drop();
}
foreach (ProjectInfo p in folder.Projects.ToArray())
{
p.Drop();
}
// this will succeed now that everything has been removed.
folder.Drop();
MessageBox.Show("Folder removed");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void GetPackage(IntegrationServices integrationServices, out Catalog catalog, out PackageInfo package)
{
if (integrationServices.Catalogs.Contains(Etl.Catalog))
{
catalog = integrationServices.Catalogs[Etl.Catalog];
}
else
{
var names = String.Join(", ", integrationServices.Catalogs.Select(c => c.Name));
throw new ArgumentOutOfRangeException("Catalog", String.Format("The catalog named '{0}' hasn't been found on the server '{1}'. List of existing catalogs: {2}.", Etl.Catalog, Etl.Server, names));
}
CatalogFolder folder;
if (catalog.Folders.Contains(Etl.Folder))
{
folder = catalog.Folders[Etl.Folder];
}
else
{
var names = String.Join(", ", catalog.Folders.Select(f => f.Name));
throw new ArgumentOutOfRangeException("Folder", String.Format("The folder named '{0}' hasn't been found on the catalog '{1}'. List of existing folders: {2}.", Etl.Folder, Etl.Catalog, names));
}
ProjectInfo project;
if (folder.Projects.Contains(Etl.Project))
{
project = folder.Projects[Etl.Project];
}
else
{
var names = String.Join(", ", folder.Projects.Select(p => p.Name));
throw new ArgumentOutOfRangeException("Project", String.Format("The project named '{0}' hasn't been found on the catalog '{1}'. List of existing projects: {2}.", Etl.Project, Etl.Folder, names));
}
if (project.Packages.Contains(Etl.Name))
{
package = project.Packages[Etl.Name];
}
else
{
var names = String.Join(", ", project.Packages.Select(p => p.Name));
throw new ArgumentOutOfRangeException("Name", String.Format("The package named '{0}' hasn't been found on the project '{1}'. List of existing packages: {2}.", Etl.Name, Etl.Project, names));
}
}
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 IExecutionResult Run()
{
var connection = new SqlConnection(string.Format(@"Data Source={0};Initial Catalog=master;Integrated Security=SSPI;", Etl.Server));
var integrationServices = new IntegrationServices(connection);
Catalog catalog;
PackageInfo package;
GetPackage(integrationServices, out catalog, out package);
var setValueParameters = new Collection <PackageInfo.ExecutionValueParameterSet>();
setValueParameters.Add(new PackageInfo.ExecutionValueParameterSet
{
ObjectType = 50,
ParameterName = "SYNCHRONIZED",
ParameterValue = 1
});
var parameters = Parameterize(Etl.Parameters, package.Parameters, package.Name);
parameters.ToList().ForEach(p => setValueParameters.Add(p));
long executionIdentifier = -1;
if (Etl.Timeout == 0)
{
executionIdentifier = package.Execute(Etl.Is32Bits, null, setValueParameters);
}
else
{
executionIdentifier = package.Execute(Etl.Is32Bits, null, setValueParameters, Etl.Timeout);
}
var execution = catalog.Executions[executionIdentifier];
var etlRunResultFactory = new EtlRunResultFactory();
var result = etlRunResultFactory.Instantiate(
execution.Status
, execution.Messages.Where(m => m.MessageType == 120 || m.MessageType == 110).Select(m => m.Message)
, execution.StartTime
, execution.EndTime);
return(result);
}
private void ShowOperationMessagesButton_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"];
catalog.Operations.Refresh();
StringBuilder messages = new StringBuilder();
foreach (ExecutionOperation exec in catalog.Executions)
{
if (exec.Completed)
{
messages.AppendLine(exec.PackageName + " completed " + exec.EndTime.ToString());
var ops = from a in catalog.Operations where a.Id == exec.Id select a;
foreach (Operation op in ops)
{
op.Refresh();
foreach (OperationMessage msg in op.Messages)
{
messages.AppendLine("\t" + msg.Message);
}
}
}
}
LogFileTextbox.Text = 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 ExecutePackage(string packageName, bool failOnWarning = true)
{
try
{
IntegrationServices integrationServices = new IntegrationServices(this.SsisServer);
PackageInfo package = GetPackage(integrationServices, packageName);
EnvironmentReference env = null;
if (this.UseEnvironment)
{
env = GetEnvironment(package);
}
long executionId = package.Execute(false, env);
var execution = integrationServices.Catalogs[Catalog].Executions[executionId];
while (!execution.Completed)
{
System.Threading.Thread.Sleep(1000);
execution.Refresh();
}
string errorsAndWarnings = GetMessages(integrationServices, executionId, failOnWarning);
var status = integrationServices.Catalogs[Catalog].Executions[executionId].Status;
if (this.HasFailed || status != Operation.ServerOperationStatus.Success)
{
throw new Exception($"The package '{packageName}' has failed with the following warnings and errors: {errorsAndWarnings}");
}
}
catch (Exception e)
{
StringBuilder errorMessage = new StringBuilder();
errorMessage.AppendLine($"An error was thrown while executing a package:");
errorMessage.AppendLine($"Error: {e.Message}");
errorMessage.AppendLine($"The following parameters were used:");
errorMessage.AppendLine($"Folder: {this.PackageFolder}");
errorMessage.AppendLine($"Project: {this.ProjectName}");
errorMessage.AppendLine($"Package: {packageName}");
errorMessage.AppendLine($"Environment Folder: {this.EnvironmentFolder}");
errorMessage.AppendLine($"Environment: {this.EnvironmentName}");
errorMessage.AppendLine($"Fail on Warning: {failOnWarning}");
throw new Exception(errorMessage.ToString());
}
}
public void ExecPackage()
{
Server server = new Server(_server);
IntegrationServices service = new IntegrationServices(server);
Catalog catalogObject = service.Catalogs[_catalog];
CatalogFolder folderObject = catalogObject.Folders[_folder];
ProjectInfo projectObject = folderObject.Projects[_project];
PackageInfo p = projectObject.Packages[_packageinfo.Name];
p.Execute(false, null);
//Package p = dtsapp.LoadFromSqlServer(string.Concat(_packageinfo.Folder +
// "\\" + _packageinfo.Name), _server, null, null, null);
//p.Execute();
}
private void Create_Click(object sender, EventArgs e)
{
Server server = new Server(_server);
try
{
IntegrationServices isServer = new IntegrationServices(server);
Catalog catalog = isServer.Catalogs["SSISDB"];
CatalogFolder folder = new CatalogFolder(catalog, "ProSSIS", "New Folder");
folder.Create();
MessageBox.Show("Folder Created");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
internal static string GetPlatform(IntegrationServices store)
{
if (store == null)
{
return(null);
}
string cmdText = "EXEC xp_msver platform";
object obj = SqlHelper.ExecuteSQLCommand(store.Connection, CommandType.Text, cmdText, null, ExecuteType.ExecuteReader);
using (SqlDataReader sqlDataReader = (SqlDataReader)obj)
{
if (sqlDataReader.Read())
{
return(sqlDataReader.GetString(3));
}
}
return(null);
}
public override IExecutionResult Run()
{
var connection = new SqlConnection(string.Format(@"Data Source={0};Initial Catalog=master;Integrated Security=SSPI;", Etl.Server));
var integrationServices = new IntegrationServices(connection);
Catalog catalog;
PackageInfo package;
GetPackage(integrationServices, out catalog, out package);
var setValueParameters = new Collection<PackageInfo.ExecutionValueParameterSet>();
setValueParameters.Add(new PackageInfo.ExecutionValueParameterSet
{
ObjectType = 50,
ParameterName = "SYNCHRONIZED",
ParameterValue = 1
});
var parameters = Parameterize(Etl.Parameters, package.Parameters, package.Name);
parameters.ToList().ForEach(p => setValueParameters.Add(p));
long executionIdentifier = -1;
if (Etl.Timeout==0)
executionIdentifier = package.Execute(Etl.Is32Bits, null, setValueParameters);
else
executionIdentifier = package.Execute(Etl.Is32Bits, null, setValueParameters, Etl.Timeout);
var execution = catalog.Executions[executionIdentifier];
var etlRunResultFactory = new EtlRunResultFactory();
var result = etlRunResultFactory.Instantiate(
execution.Status
, execution.Messages.Where(m => m.MessageType == 120 || m.MessageType == 110).Select(m => m.Message)
, execution.StartTime
, execution.EndTime);
return result;
}
private void GetPackage(IntegrationServices integrationServices, out Catalog catalog, out PackageInfo package)
{
if (integrationServices.Catalogs.Contains(Etl.Catalog))
catalog = integrationServices.Catalogs[Etl.Catalog];
else
{
var names = String.Join(", ",integrationServices.Catalogs.Select(c => c.Name));
throw new ArgumentOutOfRangeException("Catalog", String.Format("The catalog named '{0}' hasn't been found on the server '{1}'. List of existing catalogs: {2}.", Etl.Catalog, Etl.Server, names));
}
CatalogFolder folder;
if (catalog.Folders.Contains(Etl.Folder))
folder = catalog.Folders[Etl.Folder];
else
{
var names = String.Join(", ", catalog.Folders.Select(f => f.Name));
throw new ArgumentOutOfRangeException("Folder", String.Format("The folder named '{0}' hasn't been found on the catalog '{1}'. List of existing folders: {2}.", Etl.Folder, Etl.Catalog, names));
}
ProjectInfo project;
if (folder.Projects.Contains(Etl.Project))
project = folder.Projects[Etl.Project];
else
{
var names = String.Join(", ", folder.Projects.Select(p => p.Name));
throw new ArgumentOutOfRangeException("Project", String.Format("The project named '{0}' hasn't been found on the catalog '{1}'. List of existing projects: {2}.", Etl.Project, Etl.Folder, names));
}
if (project.Packages.Contains(Etl.Name))
package = project.Packages[Etl.Name];
else
{
var names = String.Join(", ", project.Packages.Select(p => p.Name));
throw new ArgumentOutOfRangeException("Name", String.Format("The package named '{0}' hasn't been found on the project '{1}'. List of existing packages: {2}.", Etl.Name, Etl.Project, names));
}
}
public async Task<IHttpActionResult> RunAsync(ODataActionParameters parameters)
{
if (!ModelState.IsValid)
{
return BadRequest(ModelState);
}
var name = (string)parameters["name"];
var variables = (IEnumerable<JobParameter>)parameters["parameters"];
var descriptor = SsisServiceHelper.SsisJobDescriptors[name];
{
var connection = default(SqlConnection);
try
{
connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["MasterConnection"].ConnectionString);
var services = new IntegrationServices(connection);
var environment = services.Catalogs["SSISDB"].Folders["Diamond"].Environments[descriptor.EnvironmentName];
foreach (var variable in variables)
{
environment.Variables[variable.Name].Value = SsisServiceHelper.Convert(variable.Value, variable.Type);
}
environment.Alter();
}
finally
{
if (connection != null) connection.Dispose();
}
}
{
var connection = default(SqlConnection);
var command = default(SqlCommand);
try
{
connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["MSDBConnection"].ConnectionString);
command = connection.CreateCommand();
command.CommandText = "sp_start_job";
command.CommandType = CommandType.StoredProcedure;
{
var parameter = command.CreateParameter();
parameter.ParameterName = "@return_value";
parameter.SqlDbType = SqlDbType.Int;
parameter.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(parameter);
}
{
var parameter = command.CreateParameter();
parameter.ParameterName = "@job_name";
parameter.Direction = ParameterDirection.Input;
parameter.SqlDbType = SqlDbType.VarChar;
parameter.Value = descriptor.JobName;
command.Parameters.Add(parameter);
}
await connection.OpenAsync();
await command.ExecuteNonQueryAsync();
var result = (int)command.Parameters["@return_value"].Value;
if (result != 0)
{
throw new InvalidOperationException(string.Format("SQL Server Agent job, {0}, failed to start.", descriptor.JobName));
}
return Ok();
}
finally
{
if (command != null) command.Dispose();
if (connection != null) connection.Dispose();
}
}
}
