Database

static void Main(string[] args)
{
    Application app = new Application();
    Package loadedPkg = app.LoadPackage(@"\bsc\cscm\edh_imdb\DataExchange\ShieldLink\NewFolder\Brandon\TestSSISPackage\SgLgReporting_SLNewBusinessInProgress.dtsx", null);

    // Save the package to SQL Server.
    //app.SaveToSqlServer(loadedPkg, null, "WSQL569S,50101", null, null);
    app.SaveToSqlServer(loadedPkg, null, "WSQL569S,50101", null, null);

    ////Application integrationServices = new Application();
    //Package myPackage = app.LoadFromSqlServer(@"\MSDB\Data Collector\SAW\Load_MEET_Errors\SgLgReporting_SLNewBusinessInProgress.dtsx", "WSQL569S,50101", String.Empty, String.Empty, null);

    var temp = LaunchPackage("sql", string.Empty, "SgLgReporting_SLNewBusinessInProgress.dtsx");

    //myPackage.Execute();
}

public static int LaunchPackage(string sourceType, string sourceLocation, string packageName)
{

    string packagePath;
    Package myPackage;
    Application integrationServices = new Application();

    // Combine path and file name.<br />
    packagePath = Path.Combine(sourceLocation, packageName);

    switch (sourceType)
    {
        case "file":
            // Package is stored as a file.<br />
            // Add extension if not present.<br />
            if (String.IsNullOrEmpty(Path.GetExtension(packagePath)))
            {
                packagePath = String.Concat(packagePath, ".dtsx");
            }
            if (File.Exists(packagePath))
            {
                myPackage = integrationServices.LoadPackage(packagePath, null);
            }
            else
            {
                throw new ApplicationException("Invalid file location: " + packagePath);
            }
            break;
        case "sql":
            // Package is stored in MSDB.<br />
            // Combine logical path and package name.<br />
            if (integrationServices.ExistsOnSqlServer("SgLgReporting_SLNewBusinessInProgress", "WSQL569S,50101", String.Empty, String.Empty))
            {
                myPackage = integrationServices.LoadFromSqlServer("SgLgReporting_SLNewBusinessInProgress", "WSQL569S,50101", String.Empty, String.Empty, null);
            }
            else
            {
                throw new ApplicationException("Invalid package name or location: " + packagePath);
            }
            break;
        case "dts":
            // Package is managed by SSIS Package Store.<br />
            // Default logical paths are File System and MSDB.<br />
            if (integrationServices.ExistsOnDtsServer(packagePath, "."))
            {
                myPackage = integrationServices.LoadFromDtsServer(packagePath, "localhost", null);
            }
            else
            {
                throw new ApplicationException("Invalid package name or location: " + packagePath);
            }
            break;
        default:
            throw new ApplicationException("Invalid sourceType argument: valid values are 'file', 'sql', and 'dts'.");
    }

    return (Int32)myPackage.Execute();

}

1. Checked the SQL Server Management Studio for SQL Server version, it is 12.0 and I am also running Sql Server Data Tools Version 12.0.
2. Checked the SQL Server Integration Services running on the Server, yes it is 11.0 version is running on the Server.
3. Checked various different tools on the Server, they are as below

1. I have Sql Server Client Version installed on my machine, but Server version is not installed, hence I don't have SSIS tools on my local machine from where I am running the Console app to execute SSIS Package. Could it be the problem? Can I just install SSIS tools.

private static void OnCreated(object source, FileSystemEventArgs e)
{
    // Specify what is done when a file is changed, created, or deleted.
    // get the file's extension
    string strFileExt = (Path.GetExtension(e.FullPath) ?? string.Empty).ToLower();
    string directoryFullPath = Path.GetDirectoryName(e.FullPath);

    // filter file types
    if (Regex.IsMatch(strFileExt, @".txt|.csv", RegexOptions.IgnoreCase))
    {
        Package _package = null;
        Application app = new Application();
        string dtsPackPath = ConfigurationManager.AppSettings["SSISPackagePath"];
        _package = app.LoadPackage(dtsPackPath, null);

        _package.Connections["Flat File Connection Manager"].ConnectionString = e.FullPath;
        _package.Connections["Flat File Connection Manager 1"].ConnectionString = directoryFullPath + @"\Test" + DateTime.Now.Year.ToString()
            + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString()
                    + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString()
                    + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString() + ".txt";

        //_package.Variables["User::SourceFileName"].Value = e.FullPath;

        //_package.Variables["User::FileName"].Value = directoryFullPath + @"\Test" + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString()
        //            + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString()
        //            + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString() + ".txt";

        var res = _package.Execute();

        if (res == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success)
        {
            string message = "Package Executed Successfully....";
        }
    }

    while (Console.Read() != 'q') ;
}

private static void OnCreated(object source, FileSystemEventArgs e)
{
    // Specify what is done when a file is changed, created, or deleted.
    // get the file's extension
    string strFileExt = (Path.GetExtension(e.FullPath) ?? string.Empty).ToLower();
    string directoryFullPath = Path.GetDirectoryName(e.FullPath);

    if (Regex.IsMatch(strFileExt, @".txt|.csv", RegexOptions.IgnoreCase))
    {
        Package _package = null;
        Application app = new Application();
        string dtsPackPath = ConfigurationManager.AppSettings["SSISPackagePath"];
        string sqlServerConnectionString = ConfigurationManager.AppSettings["SqlServerConnectionString"];
        _package = app.LoadPackage(dtsPackPath, null);

        _package.Connections["Flat File Connection Manager"].ConnectionString = e.FullPath;

        //_package.Variables["User::SourceFileName"].Value = e.FullPath;

        //_package.Variables["User::FileName"].Value = directoryFullPath + @"\Test" + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString()
        //    + DateTime.Now.Day.ToString()
        //    + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString()
        //    + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString() + ".txt";

        _package.Connections["BSCWD725128.DTS_Testing"].ConnectionString = sqlServerConnectionString;

        var res = _package.Execute();

        if (res == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success)
        {
            string message = "Package Executed Successfully....";
        }

        //_package.Connections["Flat File Connection Manager 1"].ConnectionString = directoryFullPath + @"\Test" + DateTime.Now.Year.ToString()
        //    + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString()
        //            + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString()
        //            + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString() + ".txt";
    }

    while (Console.Read() != 'q') ;
}

ALTER PROCEDURE [EventPortion].[24Start]
 AS  

IF object_id('[IntegratedTest1].[EventPortion].[244Q1]') is not null 
DROP TABLE [IntegratedTest1].[EventPortion].[244Q1]

IF object_id('[IntegratedTest1].[EventPortion].[244Q2]') is not null 
DROP TABLE [IntegratedTest1].[EventPortion].[244Q2]

IF object_id('[IntegratedTest1].[EventPortion].[244Q3]') is not null 
DROP TABLE [IntegratedTest1].[EventPortion].[244Q3]


SELECT * Into [IntegratedTest1].[EventPortion].[244Q1] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q1

While (object_id('[IntegratedTest1].[EventPortion].[244Q1]') is null)
Begin
WAITFOR DELAY '00:00:00.001';
End

Begin
WAITFOR DELAY '00:00:01.000';
End

SELECT * Into [IntegratedTest1].[EventPortion].[244Q2] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q2


While (object_id('[IntegratedTest1].[EventPortion].[244Q2]') is null)
Begin
WAITFOR DELAY '00:00:00.001';
End
SELECT * Into [IntegratedTest1].[EventPortion].[244Q3] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q3

ALTER PROCEDURE [EventPortion].[24Start]
 AS
DROP TABLE [IntegratedTest1].[EventPortion].[244Q1]
DROP TABLE [IntegratedTest1].[EventPortion].[244Q2]
DROP TABLE [IntegratedTest1].[EventPortion].[244Q3]
SELECT * Into [IntegratedTest1].[EventPortion].[244Q1] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q1
SELECT * Into [IntegratedTest1].[EventPortion].[244Q2] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q2
SELECT * Into [IntegratedTest1].[EventPortion].[244Q3] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q3
DROP TABLE [IntegratedTest1].[EventPortion].[245Q1]
DROP TABLE [IntegratedTest1].[EventPortion].[245Q2]
DROP TABLE [IntegratedTest1].[EventPortion].[245Q3]
SELECT * Into [IntegratedTest1].[EventPortion].[245Q1] FROM IntegratedTest1.Event.MOE_2_4_DE_5_Q1
SELECT * Into [IntegratedTest1].[EventPortion].[245Q2] FROM IntegratedTest1.Event.MOE_2_4_DE_5_Q2
SELECT * Into [IntegratedTest1].[EventPortion].[245Q3] FROM IntegratedTest1.Event.MOE_2_4_DE_5_Q3

private void ParseStartProcedure(string spText)
        {            
            spText = spText.Replace("\r", " ");            
            spText = spText.Replace("\t", " ");           
            spText = spText.Trim();

            // Take everything after the first "AS"
            string[] queryPieces = Regex.Split(spText.ToLower(), " as ");
            string[] queryLines = Regex.Split(queryPieces[1], "\n"); //split on the new line character

            for (int i = 0; i < queryLines.Length; i++)
            {
                if (queryLines[i] != "")
                {
                    string query = queryLines[i];
                    SqlCommand cmd = new SqlCommand(query, m_dbSettings.sqlMetadataDbConn);
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (SqlException ex)
                    {
                        //TODO: Add more robust error handling to handle errors other than "Failed to drop, etc.
                        string message = ex.Message;
                    }
                }
            }            
        }