Check OLDEDB database Connection using script task in SSIS.

Question

Hi all,
How can i check or test OLEDB connection in SSIS using script component before going to use the connection.
All i need is to check whether OLEDB connection is successful if "Yes" got next step if "No" then send mail or write to log table.

I went online but couldn't get right code or info since am not very good at C# or VB.
Please help me how to do this.

Answer 1

Hi @Gopinath Desai ,

We can use the following C# code in Script Task to check the OLEDB Connection.

public void Main()  
		{  
            try  
            {  
                ConnectionManager myOLEDBConnection1 = Dts.Connections["Test OLEDB Connection1"];  
                MessageBox.Show(myOLEDBConnection1.ConnectionString, "Test OLEDB Connection1 Successfully!");  
                Dts.Variables["Result"].Value = 1;  
            }  
            catch (Exception ex)  
            {  
                Debug.WriteLine("Exception Message: " + ex.Message);  
                MessageBox.Show("Exception Message: " + ex.Message);  
                Dts.Variables["Result"].Value = 0;  
            }  
  
            Dts.TaskResult = (int)ScriptResults.Success;  
		}  

Please refer to Connecting to Data Sources in the Script Task.

Best Regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues in November--What can I do if my transaction log is full?
Hot issues in November--How to convert Profiler trace into a SQL Server table?

Answer 2

It is relatively easy to implement a connection testing by calling the AcquireConnection() method of a connection manager.

Here is the link to the official documentation: connecting-to-data-sources-in-the-script-task

And obviously never use MessageBoxes in the production code.