Changing a Connection Manager’s Conenction String using C# – SSIS 2008

A SSIS package can be run using C#, or basically any CLR language. The piece of code depicted in the following image calls a package, tries to execute it, and prints any error or exception that occurs dutring execution of the package to output:

Simple and easy!

Now suppose our package has 2 connection managers: one named “OLTP Source” and another one named “DW Destination”. And let’s suppose that OLTP Source connection manager is created prior to DW Destination, and therefore it is listed first in the Connection Managers pane of BIDS.

The connection managers in a package are accessible in C# code using Connections collection. They are accessed using an index to enumerate in the collection. For example, Pkg.Connections[0] points to the first connection manager listed in the Connection Managers pane. And the connection string of each connection manager can be set by editing connection manager’s ConnectionString property.

The following line of code sets the connection string of the second connection manager in the package (in our example, DW Destination):

pkg.Connections[1].ConnectionString = “Data Source=localhost;Initial Catalog=DW;Integrated Security=SSPI”;

This scenario can be used to update any property of any object in the package when the package is going to be run using a CLR language.

Search for Objects in an instance of SQL Server

There are cases when a developer needs to look for a specific object (table, Column, etc.) inside a database. A good example of such a scenario is when you have a database that has no diagrams or PK-FK constraints assigned, and you want to find out the PK-FK relationships and how tables in the database are connected together. This happens regularly in developing a Data Warehouse and designing an OLAP solution, when you want to transfer all the tables required to the Data Warehouse. And when the database is huge in terms of number of tables and views and the naming convention is not good and understandable, this becomes crazy!

SQL Server Management Studio (SSMS) gives you the ability to export the script used to do any operation on any type of object accessible through SSMS. For example, to get the T-SQL script for creating a table you can right-click on the table, select Script Table AS -> CREATE TO -> New Query Editor Window.

You may have guessed that we can follow almost the same procedure to export all the objects in a database to a query window and look for an object, e.g. a column, using the its name. To do this follow these steps:

1- After connecting to your instance of SQL Server, right-click on the database you are going to search in, navigate to Tasks, and then Generate Scripts… . The Script Wizard will launch:

2- Click Next in the Welcome window. You will see the Select Database window. This is where you can select the database you want to create the script for and search in:

3- In the next page, you can select the options for the objects you want to script. If you are going to get the script of the database just for searching an object in it, leave the options as they are:

4- Choose Object Types page is where you can select the types of objects to script. Note that you can select only the object types of which you have at least one instance in your database. For example, you won’t see the option to script user-defined functions if there are no functions defined by users in the database:

5- Based on the selections you have made in the previous page, you will get one or more pages to select the objects to script. If you have chosen to script only tables, you will see a page to select the tables to script:

6- In the output option, you will get the options for the script mode. You can the destinations of type File, Clipboard, or Query Window. I suggest to script the definition of your objects to a Query Window, because it is more readable in SSMS:

7- Check objects listed in the Script Wizard Summary and click Finish. The Generate Script Progress is done, a new window containing the generated script in SSMS will open.You can look for the objects in here by pressing Crtl+F:

P.S. : I owe a special thanks to Hendra, a very good friend and colleague of mine. Thanks mate. 🙂