SSIS package - set oledb connections at runtime

SSIS oledb connection manager does't allow user to connect to different oledb servers. If we store oledb connection sting in Config file then it will accepts server name, InitialCatalog and username. But it wont accept password. The is because oledb connection manager has not exposed the oledb password property. To over come this problem we have to override the OLEDB connection manager itself. So that it accepts connections to different oledb servers.

Public Sub Main()
Dim vars As Variables

Dim oledbConnectionManager As ConnectionManager
oledbConnectionManager = Dts.Connections("<oledb connection name>")

' ADDED TO MAKE oledb CONNECTION STRING DYNAMIC
Dim oServerName As String
Dim oUserName As String
Dim oInitialCatalog As String
Dim oPassword As String

Dts.VariableDispenser.LockOneForWrite("oServerName", vars)
oServerName = vars("oServerName").Value.ToString().Trim()
vars.Unlock()

Dts.VariableDispenser.LockOneForWrite("oInitialCatalog", vars)
oInitialCatalog = vars("oInitialCatalog").Value.ToString().Trim())
vars.Unlock()

Dts.VariableDispenser.LockOneForWrite("oUserName", vars)
oUserName = vars("oUserName").Value.ToString().Trim())
vars.Unlock()

Dts.VariableDispenser.LockOneForWrite("oPassword", vars)
oPassword = vars("oPassword").Value.ToString().Trim()) '
vars.Unlock()

Dts.Connections("<oledb connection name>").Properties("ServerName").SetValue(oledbConnectionManager, oServerName)
Dts.Connections("<oledb connection name>").Properties("InitialCatalog").SetValue(oledbConnectionManager, oInitialCatalog)
Dts.Connections("<oledb connection name>").Properties("UserName").SetValue(oledbConnectionManager, oUserName)
Dts.Connections("<oledb connection name>").Properties("Password").SetValue(oledbConnectionManager, oPassword )

Dts.TaskResult = Dts.Results.Success

End Sub

Comments

C# Generic Factory

Implement Factory pattern using generics public interface IDoWork { string DoWork(); } Declare an Interface first by abstracting the common functionality Here I am taking the example of DoWork public class Manager : IDoWork { public string DoWork() { return "Manager Manages School" ; } } Implement the IDoWork in concrete classes as shown public class Teacher : IDoWork { public string DoWork() { return "Teacher teaches student in school" ; } } public class Student : IDoWork { public string DoWork() { return "Study in school" ; } } This class will actually create an instance of the concrete class so that you can work on that object further public class FactoryDemo { public T Create

How to enable windows authentication in PostgreSQL

1. Steps to create user or role in PostgreSQL · Open pgAdmin III · Login to PostgreSQL database · Select “Login Roles” and right click on mouse to view the context menu · Select “New Login Role” from context menu · Enter desired user name in “Role name” text box · Select “Definition” tab in “New Login Role” window · Enter desired Password in the given text box · Select “Role privileges” tab in “New Login Role” window · Select the privileges for the entered user or role · Select “SQL” tab in “New Login Role” window · This will display the auto generated script · Review the script and click on “OK” button · Now in the “Login Roles” the newly created role or user “newUser” is displayed · Now in pgAdmin III object browser select the database to which the newly created user should be ma

UML - Association, Aggregation, Composition, Generalization, Specialization, Realization and Dependency

Association Association is a simple relationship between two classes. For example A relationship between Professor Class and Student class is known as Association. Both Classes can exist without each other, so Professor and Student are two independent classes. In this kind of relationships there will not be any owner class. Both classes have their own life cycle. UML Notation: Aggregation Aggregation is a special type of Association. It is known as “Has-A” relationship. For example A Department class can contain Professor Class. Here Department class is owner class. Here in this relationship even after deleting Department class, Professor Class can exits. UML Notation: Composition Composition is a special type of Aggregation. It is known as “Is-A” relationship. For example A University Class has many Department class. Here University and Department objects are dependent on each other. If we delete University cl

Prime Instance

Search This Blog