Increase the Execution time or Connection Time out property of SSIS package from C# code - Database Discussion Boards

When you create the table, you are creating an index. Creating is not the same as rebuilding.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

According to this KB article[^], SQL 2012 should be able to rebuild the index online.

Try installing SP3[^], possibly followed by the latest cumulative update[^].

Or, if you've got SP2, install CU4[^] or one of the later cumulative updates[^].

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Hi,

I am executing an SSIS package using my C# code, its saying package executing successfully but unfortunately when I checking in the database, it is not putting the values into that Server, in between the execution
I am getting a Warning that "Configuration from a parent variable "ServerName" did not occur because there was no parent variable collection.", if really package is having issues in connections and variables, how can I set the variable values differently than below, below is the code how I am setting the Package connections and Variables.

It seems that package is using some configurations, is there any way I can modify the existing package configurations at run time using C# code? I don't want to add new configurations to package but want to edit the existing ones to match my needs.

Here is the code, any help would be very very helpful for me thanks in advance my friends.

public void SetRetainSameConnection()
{
    try
    {
        if (_Status == DTSPackageStatus.Loaded)
        {
            Connections connections = _Pkg.Connections;

            if (connections != null)
            {
                for (int Idex = 0; Idex < connections.Count; Idex++)
                {
                    ConnectionManager connection = connections[Idex];

                    if (connection.Properties.Contains("RetainSameConnection"))
                        connection.Properties["RetainSameConnection"].SetValue(connection, true);
                }
            }
        }
    }
    catch (Exception ex)
    {
        _Status = DTSPackageStatus.LoadFailed;
        throw ex;
    }
}
public void SetPakageConnections(System.Collections.Hashtable ConnectionCollection)
{
    try
    {
        if (_Status == DTSPackageStatus.Loaded)
        {
            Connections connections = _Pkg.Connections;

            if ((ConnectionCollection != null) && (connections != null))
            {
                for (int Idex = 0; Idex < connections.Count; Idex++)
                {
                    ConnectionManager connection = connections[Idex];

                    //connection.Properties["RetainSameConnection"].SetValue(connection, true);

                    string ConName = connection.Name;

                    if (ConnectionCollection.Contains(ConName))
                    {
                        connection.ConnectionString =
                            ConnectionCollection[ConName].ToString();
                    }
                }
            }
        }
    }
    catch (Exception ex)
    {
        _Status = DTSPackageStatus.LoadFailed;

        throw ex;
    }
}

public void SetPakageVariables(System.Collections.Hashtable VariableCollection)
{
    try
    {
        if (_Status == DTSPackageStatus.Loaded)
        {
            Variables variables = _Pkg.Variables;
            if ((VariableCollection != null) && (variables != null))
            {
                for (int Idex = 0; Idex < variables.Count; Idex++)
                {
                    Variable variable = variables[Idex];
                    string VarName = variable.Name;

                    if (VariableCollection.Contains(VarName))
                    {
                        bool IsReadOnly = (bool)variable.Properties["ReadOnly"].GetValue(variable);
                        if (!IsReadOnly)
                            variable.Value =
                                VariableCollection[VarName].ToString();
                    }
                }
            }
        }
    }
    catch (Exception ex)
    {
        _Status = DTSPackageStatus.LoadFailed;
        throw ex;
    }
}

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

modified 21-Oct-16 20:20pm.

We'd like to start using Database Projects to manage our SQL Server code in the same structured way we do the other code. We have a separate domain for development, and we also pass any changes to production to a third party.

Is there a good resource (book, blog etc) on the day-to-day usage of these?

One issue I've got at the moment is that the comparison result script is very protective (which is good). In my case its warning about tables that are being altered could contain data. Now I know that it doesn't matter as these are working tables, so it doesn't matter if they 'loose' data.

Should I be taking the script and then altering it to suit the situation, or have I missed something earlier that would have made VS generate a better script?

Also would people say that the comparison script is the best way to bring a database to the right version or is there a better way? Note the implementers have little to no understanding of the purpose of the database.

We use them where I work to version stored procedures, permissions and other objects in source control. We also use the generated script(s) to deploy changes. The build process includes a task to compile the db project(s) and generate a diff script for the target environment, and then all the application files, SQL scripts, etc, are pushed to a staging folder on the network, ready for deployment. I personally alter the generated scripts when I come across the warnings of existing data being lost. However, I don't know if that's standard/best practice or not - that's just how I've always done it.

djj55: Nice but may have a permission problem
Pete O'Hanlon: He has my permission to run it.

Thanks for that, this is what I'm thinking will be the case too.

VS can't *know* everything, so it makes sense that manual alteration of the diff scripts is required.

Right. It can be a bit tedious. I've been working on a project for a few months, an existing application. The new enhancements/features required that we split some existing tables out (moving a field from one table to a new table). When setting up the deployment, I had to alter the generated diff scripts quite a bit.

djj55: Nice but may have a permission problem
Pete O'Hanlon: He has my permission to run it.

Hi,

I have a Console Application that is executing an SSIS package, as the SSIS package is taking too long to execute either I want to set time out property max or more execution time.
Currently I am using RetainSameConnection to true on Connection, but if I can set Time out property that would be better either for the Execution or Connection, anything is good.

Can anybody please help me in that regards? Any help either code snippet, a link or even a suggestion would be very helpful - thanks in advance.

Here is my code:

For loading and executing SSIS Pakcage I am using a class

public class DTSPackage
    {
        private string _PkgLocation;
        private DTSPackageStatus _Status;
        private Package _Pkg;
        private Microsoft.SqlServer.Dts.Runtime.Application _app;

        public string PkgLocation
        {
            get { return _PkgLocation; }
        }

        public DTSPackageStatus PackageStatus
        {
            get { return _Status; }
        }

        public string SsisPackageToBeLoadedFrom { get; set; }

        public DTSPackage()
        {
            _PkgLocation = null;
            _Status = DTSPackageStatus.Empty;
            _Pkg = null;
            _app = new Microsoft.SqlServer.Dts.Runtime.Application();
        }

        private void DisposePackage()
        {
            try
            {
                if (_Pkg != null)
                {
                    _Pkg.Dispose();
                    _Pkg = null;
                }
            }
            catch (Exception ex)
            {
                _Status = DTSPackageStatus.LoadFailed;
            }
        }

        public void ClearPackage()
        {
            _PkgLocation = null;
            _Status = DTSPackageStatus.Empty;
            DisposePackage();
        }

        public void LoadPackage(string SsisPackageToBeLoadedFrom = "File System", string PackageName = "", string TargetServer = "WSQL569S,50101", string PackFileSystemLocation = "", string PkgMSDBLocation = "", string SSISPackageStoreLocation = "")
        {
            /PkgLocation = PkgLocation;/
            _Pkg = null;
            _PkgLocation = null;

            //if (PkgLocation != null)
            //{
            DisposePackage();
            try
            {
                string packageFullPhysicalPath = Path.Combine(PackFileSystemLocation, PackageName);

                if (String.IsNullOrEmpty(Path.GetExtension(PkgLocation)))
                {
                    packageFullPhysicalPath = String.Concat(packageFullPhysicalPath, ".dtsx");
                }

                switch (SsisPackageToBeLoadedFrom)
                {
                    case "File System":
                        if (File.Exists(packageFullPhysicalPath))
                        {
                            _Pkg = _app.LoadPackage(packageFullPhysicalPath, null);
                            _PkgLocation = packageFullPhysicalPath;
                            _Status = DTSPackageStatus.Loaded;
                        }
                        else
                        {
                            throw new ApplicationException("Invalid file location: " + packageFullPhysicalPath);
                        }
                        break;
                    case "MSDB":
                        // Package is stored in MSDB.<br />
                        // Combine logical path and package name.

                        _PkgLocation = Path.Combine(PkgMSDBLocation, PackageName);
                        //TargetServer = "WSQL569S";
                        if (!_app.ExistsOnSqlServer(_PkgLocation, TargetServer, String.Empty, String.Empty))
                        {
                            _Pkg = _app.LoadPackage(packageFullPhysicalPath, null);
                            _app.SaveToSqlServer(_Pkg, null, TargetServer, null, null);
                            _Status = DTSPackageStatus.Loaded;
                        }

                        if (_app.ExistsOnSqlServer(PkgLocation, TargetServer, String.Empty, String.Empty))
                        {
                            _Pkg = _app.LoadFromSqlServer(PkgLocation, TargetServer, String.Empty, String.Empty, null);
                            _Status = DTSPackageStatus.Loaded;
                        }
                        else
                        {
                            throw new ApplicationException("Invalid package name or location: " + PkgLocation);
                        }
                        break;
                    case "DTS":
                        // Package is managed by SSIS Package Store.<br />
                        // Default logical paths are File System and MSDB.
                        _PkgLocation = Path.Combine(SSISPackageStoreLocation, PackageName);

                        //blnReturn = _app.ExistsOnDtsServer(@"\MSDB" + _PkgLocation, TargetServer)

                        if (!_app.ExistsOnDtsServer(@"\Stored Packages\MSDB" + _PkgLocation, TargetServer))
                        {
                            _Pkg = _app.LoadPackage(packageFullPhysicalPath, null);
                            _app.SaveToDtsServer(_Pkg, null, SSISPackageStoreLocation, TargetServer);
                            _Status = DTSPackageStatus.Loaded;
                        }

                        if (_app.ExistsOnDtsServer(_PkgLocation, TargetServer))
                        {
                            _Pkg = _app.LoadFromDtsServer(_PkgLocation, TargetServer, null);
                            _Status = DTSPackageStatus.Loaded;
                        }
                        else
                        {
                            throw new ApplicationException("Invalid package name or location: " + _PkgLocation);
                        }
                        break;
                    default:
                        throw new ApplicationException("Invalid sourceType argument: valid values are 'file', 'sql', and 'dts'.");
                }

            }
            catch (Exception ex)
            {
                _Status = DTSPackageStatus.LoadFailed;
            }
            //}<br />
        }

        public void SetRetainSameConnection()
        {
            try
            {
                if (_Status == DTSPackageStatus.Loaded)
                {
                    Connections connections = _Pkg.Connections;

                    if  (connections != null)
                    {
                        for (int Idex = 0; Idex < connections.Count; Idex++)
                        {
                            ConnectionManager connection = connections[Idex];

                            connection.Properties["RetainSameConnection"].SetValue(connection, true);<br />
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                _Status = DTSPackageStatus.LoadFailed;
            }
        }
        public void SetPakageConnections(System.Collections.Hashtable ConnectionCollection)
        {
            try
            {
                if (_Status == DTSPackageStatus.Loaded)
                {
                    Connections connections = _Pkg.Connections;

                    if ((ConnectionCollection != null) && (connections != null))
                    {
                        for (int Idex = 0; Idex < connections.Count; Idex++)
                        {
                            ConnectionManager connection = connections[Idex];

                            connection.Properties["RetainSameConnection"].SetValue(connection, true);

                            string ConName = connection.Name;

                            if (ConnectionCollection.Contains(ConName))
                            {
                                connection.ConnectionString =
                                    ConnectionCollection[ConName].ToString();
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                _Status = DTSPackageStatus.LoadFailed;
            }
        }

        public System.Collections.Hashtable GetPackageConnections()
        {
            System.Collections.Hashtable ConnectionCollection =
                               new System.Collections.Hashtable();

            try
            {
                if (_Status == DTSPackageStatus.Loaded)
                {
                    Connections connections = _Pkg.Connections;
                    if (connections != null)
                    {
                        for (int Idex = 0; Idex < connections.Count; Idex++)
                        {
                            ConnectionManager connection = connections[Idex];
                            string ConName = connection.Name;
                            string ConStr = connection.ConnectionString;

                            ConnectionCollection.Add(ConName, ConStr);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                _Status = DTSPackageStatus.LoadFailed;
            }
            return ConnectionCollection;
        }

        public void SetPakageVariables(System.Collections.Hashtable VariableCollection)
        {
            try
            {
                if (_Status == DTSPackageStatus.Loaded)
                {
                    Variables variables = _Pkg.Variables;
                    if ((VariableCollection != null) && (variables != null))
                    {
                        for (int Idex = 0; Idex < variables.Count; Idex++)
                        {
                            Variable variable = variables[Idex];
                            string VarName = variable.Name;

                            if (VariableCollection.Contains(VarName))
                            {
                                variable.Value =
                                    VariableCollection[VarName].ToString();
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                _Status = DTSPackageStatus.LoadFailed;
            }
        }

        public System.Collections.Hashtable GetPackageVariables()
        {
            System.Collections.Hashtable VariablesCollection =
                               new System.Collections.Hashtable();

            try
            {

                if (_Status == DTSPackageStatus.Loaded)
                {
                    Variables variables = _Pkg.Variables;
                    if (variables != null)
                    {
                        for (int Idex = 0; Idex < variables.Count; Idex++)
                        {
                            Variable variable = variables[Idex];
                            string VarName = variable.Name;
                            object VarVal = variable.Value;

                            VariablesCollection.Add(VarName, VarVal);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                _Status = DTSPackageStatus.LoadFailed;
            }

            return VariablesCollection;
        }

        public DTSExecResult Execute()
        {
            return _Pkg.Execute();
        }

        public DTSExecResult Execute(SSISEventListener Listerner)
        {<br />
            return _Pkg.Execute(null, null, Listerner, null, null);
        }

        ~DTSPackage()
        {
            DisposePackage();
        }
    }

"From if ((connectionValuesFromConfig != null) && (connectionValuesFromConfig.Count > 0)) this statement I using it in the main method."

 if ((connectionValuesFromConfig != null) && (connectionValuesFromConfig.Count > 0))
                        {
                            foreach (DictionaryEntry entry in existingConnections)
                            {
                                tempString = (string)connectionValuesFromConfig[entry.Key.ToString().Replace(" ", string.Empty)];

                                if (!string.IsNullOrEmpty(tempString) && !string.IsNullOrEmpty(tempString))
                                    toBeSetConnections.Add(entry.Key, tempString);
                            }
                            _package.SetPakageConnections(toBeSetConnections);
                        }

                        _package.SetRetainSameConnection();                       

                        Hashtable existingVariables = _package.GetPackageVariables();
                        Hashtable toBeSetVariables = new Hashtable();
                        IDictionary variableValuesFromConfig = WatcherDB.CallGetDtsVariables(ImportMetaDataId);

                        if ((variableValuesFromConfig != null) && (variableValuesFromConfig.Count > 0))
                        {
                            foreach (DictionaryEntry entry in existingVariables)
                            {
                                string SourceFileVariableManagerName = string.Empty;

                                if ((variableValuesFromConfig["SourceFileVariableManagerName"] != null) && !DBNull.Value.Equals(variableValuesFromConfig["SourceFileVariableManagerName"]))
                                {
                                    SourceFileVariableManagerName = (string)variableValuesFromConfig["SourceFileVariableManagerName"];
                                }

                                if (entry.Key.ToString() == SourceFileVariableManagerName)
                                {
                                    toBeSetVariables.Add(entry.Key, fileFullPath);
                                }
                                else
                                {
                                    tempString = (string)variableValuesFromConfig[entry.Key.ToString().Replace(" ", string.Empty)];

                                    if (!string.IsNullOrEmpty(tempString) && !string.IsNullOrEmpty(tempString))
                                        toBeSetVariables.Add(entry.Key, tempString);
                                }
                            }

                            _package.SetPakageVariables(toBeSetVariables);
                        }

                        SSISEventListener Listerner = new SSISEventListener();

                        DTSExecResult PkgResult = _package.Execute(Listerner);

                        if (PkgResult.ToString() == "Success")
                        {
                            errorDescription = Listerner.ErrorDescription;
                            Logger.Log(dtsPackPath, "", "FileWatcherScheduleApp", "WatcherScheduler", "ExecutePackage", PkgResult.ToString(), PkgResult.ToString(), (int)Priority.Debug, PriorityTobeLogged);

                            WatcherDB.UpdateImportedFileDetails(Id);

                            return true;
                        }
                        else
                        {
                            errorDescription = Listerner.ErrorDescription;
                            Logger.Log(dtsPackPath, "", "FileWatcherScheduleApp", "WatcherScheduler", "ExecutePackage", "Error", errorDescription, (int)Priority.LogicalError, PriorityTobeLogged);
                        }

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

Hi,

I have been going through a tough time in resolving this issue, I am getting the following error when I am trying to execute a package that's on the MSDB database, it says "Source : Microsoft.SqlServer.Dts.Runtime.TaskHost, ErrorCode : -1073679321, Description : To run a SSIS package outside of SQL Server Data Tools you must install Populate FileCollection of Integration Services or higher."

My code is as below:

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();

}

The things I have tried are

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.
Checked the SQL Server Integration Services running on the Server, yes it is 11.0 version is running on the Server.
Checked various different tools on the Server, they are as below

Microsoft SQL Server Management Studio: 11.0.6020.0
Microsoft Analysis Services Client Tools: 11.0.6020.0
Microsoft Data Access Components (MDAC): 6.3.9600.17415
Microsoft MSXML: 3.0 6.0
Microsoft Internet Explorer: 9.11.9600.18450
Microsoft .NET Framework: 4.0.30319.34014
Operating System: 6.3.9600

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.

Here is my machine Configuration:

Microsoft SQL Server Management Studio: 11.0.3000.0
Microsoft Analysis Services Client Tools: 11.0.3000.0
Microsoft Data Access Components (MDAC): 6.1.7601.17514
Microsoft MSXML: 3.0 5.0 6.0
Microsoft Internet Explorer: 9.11.9600.18449
Microsoft .NET Framework: 4.0.30319.42000
Operating System: 6.1.7601

Is there anything that I am missing?

I have been searching and googling the whole day to resolve this issue. Any kind help is much appreciated. Thanks in advance my friends.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

I needed to have SSIS run time on my machine where I am executing the Console app, or reinstall SQL Server along with Integration Services.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

I have an MS Access db that needs to export to Sql Server on daily basis. The access database contains numerous tables and views associated with it. Everyday basis, this gets updated and it's ok to replace the database on daily basis. Sometimes the structure remains the same, but the data in many of the tables will be more on each day. Modifications to existing structure is also possible sometimes.

So, rather than appending data to the existing tables, I would like to replace the whole database on daily basis in Sql Server and perform operations on it.And it should be scheduled at a particular time. So I am looking out which will be the best option to do the same. Will the SSIS packages helpful? Please suggest possible ways.

meeram39 wrote:
Please suggest possible ways.

Create a linked server in SQL Server, point to your Access-database, SELECT INTO to where you need them.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Hi,

I am getting the following error,while creating identity column in sql ???

This is the code:

Create Table Myschema.Employee_Demo
(
Emp_ID int constraint pk_con Primary Key Identity(1,1),
Emp_Name varchar(55),
Emp_Sal decimal(10,2)
)
Insert Myschema.Employee_Demo(Emp_Name,Emp_Sal)values('Puja',20,000),('Tira',10,000),('Serrma',15,000),('Airrwari',12,000),('Shyyjina',13,000)

ERROR:

Msg 110, Level 15, State 1, Line 1

There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

SO,

how to solve this error???Please help.......

Thanks...

Take the delimiter out of the monetary walues - these are for display only, not to be stored in the database. The SQL parser sees the comma in the figures as a column delimiter

SQL

Insert Myschema.Employee_Demo(Emp_Name,Emp_Sal)values('Puja',20000),('Tira',10000),('Serrma',15000),('Airrwari',12000),('Shyyjina',13000)

=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================

When you are inserting a record, The value for Emp_Sal should not contain a comma(,).
That is ('Puja',~~20,000~~) should be written as ('Puja',20000)

Hello all. I am writing a C# program. The program downloads an rdl report and then examines the SharedDataSetReference tag of the XML.

From this information, I can get the server path to the shared dataset. What I need to do is to use the shared dataset name to get the name of the stored procedure that the dataset gets it's information from. I assume that I would query the Report Server Database. The dataset uses a shared datasource as well. I have been trying to figure out how to do this for a while now, and I cannot.

I am not giving up though. If I figure it out, I will come back and post the solution, but I would appreciate any help I can get.

Thanks,
David

From reading this[^] the shared datasource may have nothing to do with a database but is a file on the REPORT server, its source may be anything, Excel, CSV, or a web service query.

Never underestimate the power of human stupidity
RAH

Thank you for your reply. All of our shared datasets point to stored procedures.

I think I just figured out a solution. I have done this before. I don't know why I didn't think of it before. I am going to have my program download the shared dataset as an .rds file and then look at the xml tag CommandText in the rds file.

Hi All,

I am executing a dtsx package from C# code, what dtsx package does is, it simply reads data from .txt file and writes into another .txt file. I have two connections "Flat File Connection Manager" and "Flat File Connection Manager 1", I am calling that package with FileWatcher application which invokes when file changes or created etc. But when I am trying to execute the package, its giving me Success message but its not able to create the destination file. But package is able to create the destination file when its executed from SSDT (BIDS) using start debugging but the same package when its called from the C# code, its not generating the destination flat file.

I am not understanding is it a permission issue or something I am missing in my Code?

Can anybody please help me I am searching in google and trying with different combinations, any kind of help would be greatly helpful - thanks in advance.

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') ;
}

Now I did try it by putting OLEDB connection for destination, still when package runs from Visual Studio yields o/p but when run from the Console app, it doesn't give me any o/p neither in the o/p file nor in the Database table

Here is the code for writing into Database table

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') ;
}

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

modified 28-Sep-16 14:15pm.

I got it resolved my friends, no worries right now Smile | :)

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

we have been working on application which use ..mdb file to store the data ...

now if any one copies the database from c drive opens it which is password protected make changes and overwrite it back on program files ... is there any way we can find this out ... if someone has touched the db ...

file is password protected but in case person knows the password ...

like if we made some mistake in some entry and we open the db correct the entry and save it and then overwrite it in program files ... the application keeps working normally after that ...

Please let me know if there is any software or method to find that out .

Well for a start .mdb indicates you are using Access which is a single user tool. Why is someone able to copy the file from your machine! Oh wait you probably have it on a server and a number of clients use it - WRONG TOOL for the job. Move to SQL Server.

Password protection is supposed to PROTECT your file, you are not managing your passwords well enough!

You can make your folder readonly so they can copy but not return the file. Not sure if this will stop you writing to the database - test it.

It seem you are using the wrong tool in a badly managed environment, it is unlikely this will have a happy ending.

Never underestimate the power of human stupidity
RAH

Mycroft Holmes wrote:
You can make your folder readonly so they can copy but not return the file. Not sure if this will stop you writing to the database - test it.

It will.

Making the folder read-only would even stop you reading the database, because this is MS Access. In order to open the database, it has to create a lock file in the same folder.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

It's been 15 years since I touched Access, does that represent short memory loss I wonder.

While I think Access has it's place, the moment it moves to a server or shared drive it becomes the wrong tool for the job.

Never underestimate the power of human stupidity
RAH

Maybe this discussion could help you:
oledb - Detect time of last change on a Microsoft Access database table - Stack Overflow[^]