Applied Visual Studio Team System

SQL Server Integration Services Conditional Precendence

SQL Server Integration Services offers vast functional enhancements over DTS. One impressive change is the flexibility of new Precendence Constraints.

Right-clicking a precedence constraint reveals a context menu:

Clicking Edit reveals most of the new functionality. To demonstrate, let's walk through a simple example.

First, add a new variable named Now as shown below:

Drag an Execute SQL Task onto the Control Flow workspace:

Double-click the task to open the editor. Enter some generic SQL in the task as shown:

Change the Resultset fron "None" to "Single row": 

Click the Result Set navigation link. Add "Now" as the Result Name and "User::Now" as the Variable Name. This maps the single row returned by the query to the user variable name "Now" - which assigns the current server time to the [User::Now] variable:

Test the package by clicking the Run button (or F5). The package fails with the following error:

Clicking the Execution Results tab reveals the error to be related to the Result Binding:

  To correct the error, add an alias ("Now") to the query:

The package now succeeds:

Add two more Execute SQL Tasks. Configure them as shown below. Change the Name property of the second task to "Execute SQL Task 31 - 60":

Drag a new precendence constraint from the first task to the second. Right-click the precedence constraint and select Edit:

Change the Expression operation from "Constraint" to "Expression and Constraint":

Leave the Value set to Success. Enter "DatePart("s", @[User::Now])<=30" in the Expression textbox. Leave the Multiple Constraints option set to Logical AND - this means the previous task must succeed (the Success value must be satisfied) and the Expression must evaluate to True (the seconds of the current minute must be between 0 and 30 inclusive) for this precedence constraint to execute. Click OK to close the precedence constraint editor:

The precendence constraint now displays an fx symbol to indicate it is controlled by an Expression:

Drag another precendence constraint from the first Execute SQL Task to the third (31-60) task. As before, enter "DatePart("s", @[User::Now])>30" in the Expression textbox. Leave the Multiple Constraints option set to Logical AND - this means the previous task must succeed (the Success value must be satisfied) and the Expression must evaluate to True (the seconds of the current minute must be between 31 and 59 inclusive) for this precedence constraint to execute. Click OK to close the precedence constraint editor:

The second precendence constraint now indicates an Expression. Click the Play (F5) button to start execution. Depending on which half of the current minute the execution occurs, one of the two images below will display indicating that precedence constraint's Expression to evaluates True. Clicking Play 30 seconds later will cause the other precedence constraint's Expression to evaluate True:

Precedence Constraints in SSIS provide lots more functionality than their predecessors (no pun intended!).

:{> Andy

Technorati Tags: SSIS Sql Server Precendence Constraint

Using ODBC and ADO.Net to Connect SSIS to DB2

I needed to connect SSIS to a DB2 database via ODBC today and learned something worth blogging about: There are different ways to use ODBC connections in SSIS. It seems I learn some cool, new, flexible function about SSIS every time I take it out for a spin!

I had already installed the IBM DB2 client for Windows and tested the ODBC connection it created, so I knew I had good credentials and could access the data I wanted. It's important to check this before proceeding.

I created a new project to test different connection flavors.

I right-clicked in the Connection Managers workspace and selected "Create Connection."

 

Next, I selected ODBC as the type of connection:

I then clicked the New button on the "Configure ODBC Connection Manager":

The Connection Manager opened and I entered the name of the test server's data source name; followed by my login credentials:

I then clicked the "Test Connection" button. It failed because I mistyped my password, displaying the following dialog:

When I corrected my password, the test succeeded:

I clicked OK to close the Connection Manager...

...and the clicked OK again to close the "Configure ODBC Connection Manager" dialog. A new connection appeared in the connection managers workspace:

I like to rename the connection to something more descriptive, so I appended a dot and the connection type (i.e. ".ODBC") to the connection name:

Next I created a new ADO.Net connection:

When the dialog appeared, I selected the "Odbc Data Provider" provider type: 

I then supplied the DSN and credentials as before:

After configuring the connection, I clicked OK and dragged a Data Flow onto the workspace:

I next opened the Data Flow and dragged a DataReader Source onto the data flow:

I double-clicked the DataReader to edit it, and the Advanced Editor. I then toggled between the two connections, hitting the Refresh button to update the feedback:

When set to the ODBC connection, the DataReader cannot acquire a managed connection. I could not make the DataReader work under these conditions:

When I changed the connection to the Ado.net connection and clicked the Refresh button, the feedback vanished, indicating the DataReader is prepared to use this connection object:

That's it! In sum, I was able to use the ODBC connection - but only through the ADO.Net connection.

:{> Andy

Technorati Tags: SSIS Sql Server DB2 Developer Community Richmond Virginia

 

SQL Server 2005 SP1 Installation Issues, Part 2

As described in Part 1, I ran into a couple issues installing SQL Server 2005 SP1.

The second issue took the form of a "The following files are currently locked" error in a "Locked Files Found" dialog:

To correct this issue I followed this procedure.

1. Open Services and stop the "SQL Server FullText Search" service (right-click the service and then click Stop):

2. Stop the "SQL Server Integration Services" service in similar fashion:

3. After about 20 minutes of processing, the service pack installation completed successfully.

4. I had to reboot the server to get the SQL Server Integration Services service to start again.

:{> Andy

Technorati Tags: SSIS Sql Server SP1 Developer Community Richmond Virginia

SQL Server 2005 SP1 Installation Issues, Part 1

I ran into a handful of issues applying SQL Server 2005 SP1 to my Development SQL Server.

The first issue was an "Unable to install Windows Installer MSP file" error:

After some searching, I found  that helped with this error. Here are the steps I took to address it with screenshots.

1. Open RegEdit and navigate to the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Bootstrap\MSIRefCount key:

2. Select the MSIRefCount key. Click File, then Export:

3. Select a Path and Filename to store the contents of this key and click the Save button:

4. On the SQL Server 2005 installation media, locate the SQLSupport.msi file. RIght-click the file and click Uninstall:

NOTE: There is no indication the uninstallation succeeds. You may or may not see a brief dialog with a progress bar, but there is no status message at the end of the uninstall.

5. Re-install the SQLSupport.msi file by again right-clicking the file. This time, click Install to install the file:

6. Return to RegEdit. Click File, then Import:

7. Navigate to the file you exported earlier:

8. Click Open. If successful, a dialog will display status:

This got me over the first hurdle, but I hit another... more in Part 2.

:{> Andy

Technorati Tags: SSIS Sql Server SP1 Developer Community Richmond Virginia

 

Using Google Caching to Rescue a Lost Community Server Blog

I recently needed to move some blog entries from one site (where they were previously posted) to a new blog site (this one, in fact). Both sites utilize Community Server, but the old site was offline - hence the need to relocate the files. 

As I had been blogging at this site for nine months, there were quite a few entries there. And, because things had changed during the past nine months, I knew some of the material would be difficult to reproduce.

I realized I could get to Google's cached version of some of the entries, but I didn't know how to get to them all. After some casting about, I hit upon a method for retrieving the information I sought.

Here's what I did:

1. Browse to Google.com and search for some text in your blog, preferably an entry title:

2. When you locate an entry from your old blog in the search results, right-click the entry and select "Copy Shortcut":

3. Click "Advanced Search" beside the main textbox on the Google results page:

4. On the Advanced Search page, paste the contents of the clipboard (the copied shortcut to your old blog entry) in the Domain textbox and click the "Google Search" button:

5. The Google advanced search will likely only return one result - the desired page:

6. Click on the "Cached" link below the description to view Google's cached version of the page:

7. Heres where it can get tricky. If your Community Server theme includes a sidebar that displays Archives, right-click on the earliest archived month and select "Copy Shortcut" (If Archives is not visible, all is not lost...):

8. Return to Google and again click the "Advanced Search" link. Paste the copied shortcut into the Domain textbox (If you do not have access to the Archived link, Community Server Archive links are fairly standard. They follow the format "/archive/YYYY/MM.aspx") and click the "Google Search" button:

9. The search results should contain the latest cached version of your Community Server's blog entries for the archived month:

10. Click on the "Cached" link to view Google's cache of your blog:

11. You can then copy and paste the results into another blog editor, or save the page - thus rescuing your Community Server blog!

:{> Andy

Technorati Tags: Blog Rescue Blogs Google Cache Google Cache Developer Community

Richmond Code Camp - Success!

The first ever Richmond MSDN Code Camp was held yesterday. The event was an awesome success!

Many people deserve recognition and thanks. Indulge me while I say thanks to:

ECPI, for allowing us to use their Moorefield facilities. The ECPI Moorefield campus was the perfect location for a Code Camp! The administration and staff were more than accomodating and, without their help, there simply would not have been a Richmond Code Camp. Special thanks to Ada Gerard and Matt Keadle for their support!

Microsoft, for providing support, managing registration, buying lunch for all Code Camp attendees and for picking up the tab for the speakers-and-volunteers-night-before-dinner, and for all the cool swag. Special thanks to our Developer Evangelist, Andrew Duthie, for his tireless efforts on our behalf!

MaconIT, for the coolest Code Camp shirts ever!

TekSystems, for the surprise breakfast!

APress, Wrox, and Wiley for lots of books! (I believe every attendee received at least one book.)

 

Thanks to the speakers and volunteers (forgive me if I leave anyone out - and then notify me so I can add you!):

Susan Lennon,
Frank La Vigne,
Greg Postlewait,
Gary Blatt,
Mike Richardson,
Brian C. Lanham,
Kevin S. Goff,
Geoff Switz,
Omri Bahat,
Don Demsak,
Vishwas Lele,
Darrell Norton,
Andrew Duthie,
Jonathan Cogley,
Harold Buckner,
Robin Edwards,
Kevin O'Connor,
Teresa Shi,
Joe Waldin,
Christopher Berg,
and Jason Leonard.

 

Special thanks to the organizing team: Frank La Vigne, ; Darrell Norton, ; Susan Lennon, MCAD,MCSD,MCDBA, MCSE + I, MCT; and Andrew Duthie!

:{> Andy

Technorati Tags: Developer Community Richmond Virginia VA Code Camp

Reposting old blogs

23 Apr 2006

My old technical blog at SQL Server Central has been down for a couple weeks now. I've been (and continue) waiting patiently for SSC to bring their community server instance back online.

Yesterday, I received several comments from regular readers (I have regular readers!) - expressing that they missed my writing. So I have decided to repost all previous posts I can locate here. I am backdating the material (and this post) so it will appear in roughly the same order - with roughly the same date- / time-stamps as the previous material.

Enjoy!

:{> Andy

Last Minute Fill-In Speaking Gig

I had an opportunity to speak to the Richmond .Net Users Group last night as a last-minute fill-in guest speaker.

The folks in attendance were simply awesome! It was an honor (as always) to be able to present to a cool group of folks. Frank LaVigne, MVP (congratulations Frank on being awarded the 21st Tablet PC MVP on the planet!) and I had concerns about presenting a database-centric topic to a developer group. But it went very well. It turns out there are a lot of developers delving into SQL Server 2005 - either by choice or necessity.

"Hey you!
Do you know how to spell SQL?
Good.
You're our new database resource."

You laugh... this is a paraphrase of the discussion that converted Andy from a web developer to a SQL Server DBA...

I'm working on more Team System presentations for upcoming events, like the Richmond Code Camp. I will be presenting "SSIS with Team System" there and at upcoming user group engagements.

Plus, Frank and I are (re-) starting the Richmond SQL Server Users Group. The first meeting is planned for May 11, 2006 and we plan to hold monthly meetings the 2nd Thursday of each month. More details will follow...

:{> Andy