Click here to Skip to main content
14,773,509 members
Home » Discussions » Database
   

Database

 
GeneralRe: Why SQL server maintenance plan Cannot rebuild clustered index offline? Pin
Eddy Vluggen25-Oct-16 23:17
professionalEddy Vluggen25-Oct-16 23:17 
AnswerRe: Why SQL server maintenance plan Cannot rebuild clustered index offline? Pin
Richard Deeming26-Oct-16 4:23
mveRichard Deeming26-Oct-16 4:23 
QuestionConfiguration from a parent variable "ServerName" did not occur because there was no parent variable collection or Edit the existing Package Configurations using C# Pin
indian14321-Oct-16 13:12
Memberindian14321-Oct-16 13:12 
QuestionVisual Studio Database Projects and General Use Pin
cjb11018-Oct-16 3:38
Membercjb11018-Oct-16 3:38 
AnswerRe: Visual Studio Database Projects and General Use Pin
Matt U.18-Oct-16 11:12
MemberMatt U.18-Oct-16 11:12 
GeneralRe: Visual Studio Database Projects and General Use Pin
cjb11019-Oct-16 21:56
Membercjb11019-Oct-16 21:56 
GeneralRe: Visual Studio Database Projects and General Use Pin
Matt U.20-Oct-16 6:01
MemberMatt U.20-Oct-16 6:01 
SuggestionIncrease the Execution time or Connection Time out property of SSIS package from C# code Pin
indian14312-Oct-16 14:40
Memberindian14312-Oct-16 14:40 
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."

QuestionTo run a SSIS package outside of SQL server data tools you must install populate filecollection of integration services or higher Pin
indian14310-Oct-16 7:39
Memberindian14310-Oct-16 7:39 
AnswerRe: To run a SSIS package outside of SQL server data tools you must install populate filecollection of integration services or higher - resolved Pin
indian14312-Oct-16 13:58
Memberindian14312-Oct-16 13:58 
QuestionExport Access db to Sql Server db everyday Pin
meeram396-Oct-16 16:09
professionalmeeram396-Oct-16 16:09 
AnswerRe: Export Access db to Sql Server db everyday Pin
Eddy Vluggen10-Oct-16 7:58
professionalEddy Vluggen10-Oct-16 7:58 
QuestionIdentity_Column Pin
Member 111616253-Oct-16 20:43
MemberMember 111616253-Oct-16 20:43 
AnswerRe: Identity_Column Pin
Chris Quinn4-Oct-16 0:11
MemberChris Quinn4-Oct-16 0:11 
AnswerRe: Identity_Column Pin
Swinkaran4-Oct-16 2:17
professionalSwinkaran4-Oct-16 2:17 
QuestionSQL Server Reporting Services Query to Get Stored Procedure Name for Shared Dataset Pin
David_4129-Sep-16 12:48
MemberDavid_4129-Sep-16 12:48 
AnswerRe: SQL Server Reporting Services Query to Get Stored Procedure Name for Shared Dataset Pin
Mycroft Holmes29-Sep-16 13:43
professionalMycroft Holmes29-Sep-16 13:43 
GeneralRe: SQL Server Reporting Services Query to Get Stored Procedure Name for Shared Dataset Pin
David_4129-Sep-16 13:51
MemberDavid_4129-Sep-16 13:51 
QuestionExecuting SSIS Package from C# Console App Pin
indian14328-Sep-16 5:51
Memberindian14328-Sep-16 5:51 
AnswerRe: Executing SSIS Package from C# Console App - resolved Pin
indian14328-Sep-16 13:09
Memberindian14328-Sep-16 13:09 
Question.mdb file problem Pin
Member 1275753624-Sep-16 7:19
MemberMember 1275753624-Sep-16 7:19 
AnswerRe: .mdb file problem Pin
Mycroft Holmes24-Sep-16 15:49
professionalMycroft Holmes24-Sep-16 15:49 
GeneralRe: .mdb file problem Pin
Richard Deeming26-Sep-16 3:12
mveRichard Deeming26-Sep-16 3:12 
GeneralRe: .mdb file problem Pin
Mycroft Holmes26-Sep-16 13:33
professionalMycroft Holmes26-Sep-16 13:33 
AnswerRe: .mdb file problem Pin
Victor Nijegorodov25-Sep-16 4:31
MemberVictor Nijegorodov25-Sep-16 4:31 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.