THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is CSO of Linchpin People and SQLPeople, an SSIS Trainer, Consultant, and developer; a Business Intelligence Markup Language (Biml) developer; SQL Server database and data warehouse developer, community mentor, engineer, and farmer. He is a co-author of SQL Server 2012 Integration Services Design Patterns. His background includes web application architecture and development, VB, and ASP. Andy loves the SQL Server Community!
Note: Comments are moderated. Spam shall not pass! </GandalfVoice>

SSIS Expression Language and Dynamic Property Expressions

Introduction

This post is part of a series titled An Introduction to the SSIS Expression Language.

In this post, I demonstrate the SSIS Expression Language and Dynamic Property Expressions.

To build the demo project described in this article you will need SSIS 2005 and the AdventureWorks sample database (which can be downloaded at http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004).

Build The Demo Project

If you have not already done so, create an SSIS project named ExpressionLanguageDemo.

Once the project is created, open Solution Explorer and rename Package.dtsx - change the name to DynamicProperties.dtsx. If you've already created the project, right-click the SSIS Packages folder in Solution Explorer and click New Package - and rename that package to DynamicProperties.dtsx. When prompted, rename the package object as well. (I think you should always do this - I always answer this message box by clicking the Yes button.)

A New Source

Before we begin connect to your instance of SQL Server - I'm using (local) - and execute the following T-SQL statement:

use master
go

if
not exists(select name
              from sys.databases
              where name='TestSource')
 Create Database TestSource
go

use
TestSource
go

if not exists(select name
              from sys.schemas
              where name = 'Person')
 begin
  declare @Sql varchar(100)
  set @Sql = 'create schema Person'
  exec(@Sql)
 end
go

if
not exists(select name
              from sys.tables
              where name='Contact')
 begin
  select top 1000
   ContactID
  ,Title
  ,FirstName
  ,
MiddleName
  ,LastName
  ,EmailAddress
  into
Person.Contact
  from AdventureWorks.Person.Contact
 end
else
 begin
  truncate table Person.Contact
  insert into Person.Contact
  select top 1000 
  
ContactID
  ,Title
  ,FirstName
  ,
MiddleName
  ,LastName
  ,EmailAddress
  from AdventureWorks.Person.Contact
 end

This script creates a new database with a table named dbo.Contact, which it populates from the AdventureWorks database. We'll use this other data source to demonstrate a cool use of dynamic property expressions.

Manage the Connection

Rename the (local).AdventureWorks Connection Manager to MySource:

Click MySource and press the F4 key to display Properties. Highlight the ConnectionString property value and copy it to the clipboard.

Right-click anywhere in the white-space of the Control Flow and click Variables. Create a new variable named ConStr. Check to make sure the Scope of the Varibale is DynamicProperties (the package) and set the Data Type to String. In the Value textbox, paste the contents of the clipboard (the connection string of the MySource connection manager).

Back To Our Regularly Scheduled Flow... 

Drag an Execute SQL Task onto the Control Flow and double-click the Execute SQL Task to open the editor. Drag an OLE DB Source onto the canvas. Double-click the OLE DB Source to open the editor. Set the Connection property to MySource. 

Set the Data Access Mode to SQL Command and enter the following T-SQL statement into the SQL Command Text textbox:

Select Count(*)
From Person.Contact

Set the ResultSet property to Single Row:

Click the Result Set page and then click the Add button. Set the Result Name to 0. Drop down the Variable Name column and click <New Variable>:

When the Add Variable form displays, set the Name to CountVal, Value Type to Int32, and the Value to 0:

Click OK to close the Add Variable form:

 

Click OK again to close the editor.

Follow the Script

Drag a Script Task onto the Control Flow and connect a precedence constraint (green arrow) from the Execute SQL Task to the Script Task. Double-click the Script Task to open the editor.Click the Script page and set the ReadOnlyVariables property to CountVal. Set the ReadWriteVariables property to ConStr: 

 

Click the Design Script button to open the Visual Studio for Applications (VSA) editor.

Replace the code with the following:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

 Public
Sub Main()

  Dim sConStr As String = Dts.Variables("ConStr").Value.ToString
  Dim iCountVal As Integer = Convert.ToInt32(Dts.Variables("CountVal").Value)
  Dim sMsg As String

  sMsg = "ConnectionString: " & sConStr
  sMsg = sMsg & vbCrLf &
"Count: " & iCountVal.ToString

  MsgBox(sMsg)

  Dts.Variables(
"ConStr").Value = Strings.Replace(sConStr, "AdventureWorks", "TestSource")

  Dts.TaskResult = Dts.Results.Success

 End
Sub

End
Class

This script reads the value of the ConStr and CountVal variables, then displays them in a message box. Finally, the script changes the value of the ConStr variable, replacing the AdventureWorks database with the TestSource database. We'll use this change in a bit. 

Close the VSA editor and click the OK button to close the Script Task editor.

Go Back, Jack, Do It Again...

Copy the Execute SQL Task and paste it in the Control Flow. Connect the Script Task to the pasted Execute SQL Task (Execute SQL Task 1).

Drag another Script Task onto the Control Flow. Connect a precedence constraint from Execute SQL Task 1 to Script Task 1 and double-click it to open the editor. As before, add ConStr to the ReadOnlyVariables property and CountVal to the ReadWriteVariables property. Click the Design Script button and paste the same code as before into this Script Task.

I can hear you thinking: "Andy, why not just copy and paste the Script Task?" That's an excellent question! Script Tasks are very fickle on the clipboard. On 32-bit systems, you get better results. On x64, it gets ugly. The task fails with the error:

Error: Precompiled script failed to load. The computer that is running the Script Task does not have a compiler capable of recompiling the script. To recompile the script, see the Microsoft Knowledge Base article, KB931846 (http://go.microsoft.com/fwlink/?LinkId=81885).

Ugly.

One Last Thing

Ok. We're counting the rows in the AdventureWorks.Person.Contact table and displaying that value in a message box. Then, we're altering the connection string contained in the ConStr variable to point from AdventureWorks to TestSource, and then re-executing the Count query. Then we're displaying the ConStr variable value and the Count value again.

We're almost done. Here's where we use dynamic property expressions.

Click the MySource Connection Manager and press the F4 key to display the properties. Click the the Expressions property, then the ellipsis (...) button in the value textbox. The Property Expressions Editor form displays. Click the Property dropdown and select the ConnectionString property. In the Expression textbox, type @ConStr.

This maps the value contained in the ConStr variable into the ConnectionString property of the MySource Connection Manager - dynamically. Change the variable value and the ConnectionString changes. It's that simple - and that cool.

Test It!

Execute the package in debug mode to view the results:

Conclusion

Changing a connection string dynamically is but one use of Dynamic Property Expressions, albeit a very powerful use.

:{> Andy

Published Sunday, February 22, 2009 1:23 AM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Jimmy Dean said:

Thank you. Finding a useful example like this proved more difficult than I thought. This post has ended many useless hours.

March 5, 2009 2:03 PM
 

Jan Masopust said:

Thanks Andy. Brief note for SQL2K8: to change the initial catalog in the connection manager, the manager's InitialCatalog rather than its ConnectionString property shall be updated.

October 30, 2010 5:52 AM
 

Ed said:

So, you are a Jedi...

April 14, 2011 5:22 PM
 

Andriy said:

Thanks a lot Andy! a very nice and helpfull set of articles.

January 16, 2012 11:42 AM
 

Nate said:

Great articles!  One thing that caught my attention though. Under the "Go Back, Jack, Do It Again" section it states: "As before, add ConStr to the ReadOnlyVariables property and CountVal to the ReadWriteVariables property."  However, the first time these properties were set, they were opposite; not the way you state them here.

June 10, 2013 9:55 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

My Company


Other Blog

Check out my personal blog...
http://andyleonard.me

Contact Me

Twitter: @AndyLeonard
Email: andy.leonard@gmail.com

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement