CISCO VPN Error 442 and Windows 8.1

After upgrading to Windows 8.1 I had issues running Cisco VPN software. When attempting to run I got an error 442. (Note this applies to Cisco VPN, not Cisco AnyConnect.) As a first step in troubleshooting I ensured that I was on the latest version, 5.0.07.0440.

As I was already on the latest version, I began to do some web searching. Likely you, as I did, found many blog posts referring to a fix for the registry. In case you haven’t seen it, the basic instructions are:

1. Open RegEdit.

2. Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\CVirtA

3. If the DisplayName does not already read:

Cisco Systems VPN Adapter for 64 bit Windows

Change it so matches what you see above. Some installs have some “gibberish” on the front, this should be removed. In my case it was already set to what you see above, so it was on to the next step. After some more searching and experimentation, I finally came upon a solution that worked for me.

Open Windows Explorer, and navigate to C:\Program Files (x86)\Cisco Systems\VPN Client. Right click on the first .exe you find, in my case cisco_cert_mgr.exe and pick Properties from the menu. Switch to the Compatibility page.

Check on “Run this program in compatibility mode for:” and pick Windows 7. Then at the bottom, check on “Run this program as an administrator. Then click on OK.

image

 

Repeat this for every exe in the folder.

image

Unfortunately you can’t apply these in mass, you have to do these one at a time. There’s only a handful though so it shouldn’t take long.

After you are done, reboot. Before you run the VPN software, verify the Cisco network connector is not active by going to Control Panel, Network and Internet, Network Connections and validate the Cisco Systems VPN Adapter for 64 bit Windows is Disabled.

Note after you connect this will become Enabled, you just want to be sure it is disabled before you connect. If you see multiple connections for the Cisco adapter, you will need to delete the excess ones, I’ve also seen suggestions to uninstall the Cisco VPN, make sure all the Cisco connections are deleted, then reinstall. I didn’t have to go through this though, so your mileage may vary.

Also note that after you reboot, you may see a prompt asking if you want to run the Cisco service as Administrator, naturally you’ll say Yes. Then go run the Cisco VPN client again, confirming you want to run as an Admin, and you should be good to go!

Column Cut Copy Paste in VS SSMS and PowerShell

Did you know it’s possible to do Column based cut, copy and paste in Visual Studio, SQL Server Management Studio, and PowerShell v3? Not many people do. Even less people know that with VS 2010 and SSMS 2012 you get a little “extra” functionality. Watch the video to find out all the juicy details.

 

Using TFS2010 with Visual Studio / BIDS 2008 and SQL Server Management Studio

When I come to a customer site, I often have to help them get setup with TFS (Team Foundation Server) 2010, Microsoft’s source code control / ALM (application lifecycle management) system. This is so they can work with their BIDS (Business Intelligence Developer Studio) projects as a team, giving the added benefit of source code control. I’ve had to do this often enough I wanted to record the steps for my own use, and hopefully others too.

Installing the TFS 2010 tools for Visual Studio / BIDS 2008

First off, thanks to Derek Miller for covering most of the steps involved in his blog post http://derekjmiller62.wordpress.com/2010/10/19/using-tfs-2010-with-bids-2008/. I won’t go into the detail he did, but will summarize into these basic steps.

1. If you haven’t installed Visual Studio 2008 Service Pack 1, do so by downloading it and installing.

2. Next, you will need to install the Visual Studio 2008 Team Explorer.

3. After installing Team Explorer, you will have to go back and reinstall VS SP1 (from step 1). Don’t skip this step! Team explorer has some older components that overwrite the SP1 components, and you will have reinstall them.

Now this next part I really haven’t seen anywhere else and was a real pain to find, and thus is the main reason for this post. During the SP1 install, we often see “Visual Studio SP1 Installation Failed”. Checking the error log, buried deep you will find “Returning IDOK. INSTALLMESSAGE_ERROR [Error 2902. An internal error has occurred. …”

When you see this, go to your Control Panel, and then to Add Remove Programs. Look for a program called “Microsoft Visual Studio Web Authoring Component” and uninstall it. This is actually installed as part of the Office suite, and you don’t really need it since you likely have much more powerful web authoring tools, or since you are doing BI development won’t be doing an web development in Microsoft Office.

After uninstalling it, SP1 should then install, and you are ready for step 4.

4. Install the Visual Studio Team System 2008 SP1 Forward Compatibility Update for Team Foundation Server 2010. That probably took you longer to read than it actually will to install. After installing, it may prompt you to reboot. Even if it doesn’t ask you should reboot anyway, we’ve seen a few times when we weren’t able to connect until we rebooted.

After that you should be able to go into Visual Studio and go to Tools, Connect to Team Foundation Server. If you still have problems connecting, I will refer you to Derek’s post where he describes some registry entries you can try. So far we haven’t found them necessary, but you may.

Installing the TFS 2010 Tools

Note that there is one big limitation to using TFS 2010 with VS2008. You can connect to a TFS site and upload your solutions and projects, but you can’t create a new team site with VS2008. To do so, you will need the VS2010 shell with the TFS components, a free download.

Installing TFS 2010 for SQL Server Management Studio (SSMS)

Now that you have BIDS all setup to work with TFS, it only makes sense to make your SQL Server Management Studio (SSMS) also work with TFS. Joseph Jun has a great blog post that goes into all the nitty gritty of how to do this. The short version though, is after you install the TFS 2010 tools in the step above (and they are a prerequisite) you need to install the Team Foundation Server MSSCCI Provider 2010.

After the install, you should see a new Source Control menu option under the File menu in SSMS. From here you can launch the TFS 2010 management shell or open an existing SSMS project / solution. If you have a solution you need to add, simply right click on the solution in the Solution Explorer window and pick Add to Source Control.

Visual Studio Database Projects

Note that if you are using Visual Studio Database Projects, any SQL Server 2008R2 development must be done in Visual Studio 2010. VS2010 is already setup to talk to TFS 2010. If you are using VS 2008 database projects to build a SQL Server 2008 (non-R2) database, then with the steps above you should be good to go for checking in your database project into TFS.

And away we go!

And with that you should be setup to manage your BI Development in Team Foundation Server 2010. It’s a lot of work, but well worth the effort. Using TFS will let your BI staff work as a team to develop projects. Additionally you have the benefit of source code control, something invaluable in the case of package corruptions or needing to track history.

Deep Fried Arcane

At TechEd last year I was interviewed by the Deep Fried Bytes guys, along with another great SQL guy Denny Cherry. The topic of our interview was What Should Developers Know About SQL Server. (Click the link for the show.)

In the interview we cover SQL Server Full Text Search, SQL Server Service Broker, and SQL Server Integration Services. And if you listen, you’ll hear about my favorite deep fried food!

Unmixing the MIX 10 Content

Every year Microsoft puts on a big conference called MIX, this year was MIX 10. It’s a big developers conference focused mostly on Web development, although this year also had a fair amount of information on the new Windows Phone 7 platform. Microsoft is also kind enough to make the sessions available for free download to anyone interested after the conference is over. As of this blog post the average quality WMV recordings are up, and the high quality ones should be up soon. You can download them at http://live.visitmix.com/Videos .

To make downloading easy I suggest using FireFox with an add-in called Down-Them-All. Yes, I know, you’re probably a die hard Microsoft guy and use IE all the time, but you probably have FireFox installed for testing purposes. Go grab the Down-Them-All add-in. Then go to the Mix website (link above), right click anywhere in the webpage, and pick “DownThemALL!”. In the dialog that pops up it will show all the downloadable content on the page. Set the directory to “Save files in:” in the lower part of the dialog (for the average quality WMV files you’ll need about 12.4 gig), then under filters you can pick Videos to get all the WMVs. You could also go to the fast filtering area and set something like *.pptx to download all the slides. Click “Start!” and the downloads will begin. Then go get coffee. And lunch. And probably dinner as well, as this will take a while.

Once you’ve got it all downloaded you’re left with a lot of files with the session names as the file names. CL01.wmv, CL02.wmv, etc. I find it much easier if the file names also contain the text of the session description. That way when I’m looking over the media inside my Zune library or even just browsing in Windows explorer it’s easy to pick something to watch. So using the column mode capabilities plus a simple macro in a text editor called UltraEdit (easily the best software purchase I’ve ever made in my life) I quickly put together a batch file to rename all the files for me. Here is the contents of it:

rename CL01.wmv "CL01 – Changing our Game – an Introduction to Windows Phone 7 Series.wmv"
rename CL02.wmv "CL02 – Authoring for Windows Phone Silverlight 4 and WPF 4 with Expression Blend.wmv"
rename CL03.wmv "CL03 – Prototyping Rich Microsoft Silverlight Applications with Expression SketchFlow.wmv"
rename CL06.wmv "CL06 – Designing Bing – Heart and Science.wmv"
rename CL07.wmv "CL07 – Microsoft Silverlight 4 Overview – What’s in Store for Silverlight 4?.wmv"
rename CL08.wmv "CL08 – Microsoft Silverlight 4 Business Applications.wmv"
rename CL09.wmv "CL09 – Developing with WCF RIA Services Quickly and Effectively.wmv"
rename CL10.wmv "CL10 – Stepping Outside the Browser with Microsoft Silverlight 4.wmv"
rename CL13.wmv "CL13 – Overview of the Windows Phone 7 Series Application Platform.wmv"
rename CL14.wmv "CL14 – Windows Phone UI and Design Language.wmv"
rename CL15.wmv "CL15 – An Introduction to Developing Applications for Microsoft Silverlight.wmv"
rename CL16.wmv "CL16 – Building Windows Phone Applications with Silverlight Part 1.wmv"
rename CL17.wmv "CL17 – Building Windows Phone Applications with Silverlight Part 2.wmv"
rename CL18.wmv "CL18 – Windows Phone Application Platform Architecture.wmv"
rename CL19.wmv "CL19 – Development and Debugging Tools for Building XNA Games for Windows Phone.wmv"
rename CL20.wmv "CL20 – Distributing and Monetizing Windows Phone Applications and Games.wmv"
rename CL21.wmv "CL21 – Building Windows Phone Games.wmv"
rename CL22.wmv "CL22 – Building a High Performance 3D Game for Windows Phone.wmv"
rename CL23.wmv "CL23 – Designing and Developing for the Rich Mobile Web.wmv"
rename CL24.wmv "CL24 – The Microsoft Silverlight Analytics Framework.wmv"
rename CL25.wmv "CL25 – Microsoft Silverlight Media  – Moving at 60fps.wmv"
rename CL26.wmv "CL26 – Introducing the Silverlight Rough Cut Editor.wmv"
rename CL27.wmv "CL27 – HTML5 – Cross-Browser Best Practices.wmv"
rename CL28.wmv "CL28 – In-Depth Look at Internet Explorer 9.wmv"
rename CL29.wmv "CL29 – HTML5 – High-Performance Best Practices for Web Sites.wmv"
rename CL30.wmv "CL30 – Building Innovative Windows Client Software.wmv"
rename CL50.wmv "CL50 – Search Engine Optimization for Microsoft Silverlight.wmv"
rename CL51.wmv "CL51 – Building an Accessible Microsoft Silverlight Experience.wmv"
rename CL52.wmv "CL52 – Microsoft Silverlight Optimization and Extensibility with MEF.wmv"
rename CL53.wmv "CL53 – Flash Skills Applied to Microsoft Silverlight Design and Development.wmv"
rename CL54.wmv "CL54 – Software and Web Entrepreneurs – Go Big with BizSpark and WebsiteSpark.wmv"
rename CL55.wmv "CL55 – Dynamic Layout and Transitions for Microsoft Silverlight 4 with Microsoft Expression Blend.wmv"
rename CL56.wmv "CL56 – A Case Study – Rapid WordPress Design and Prototyping with Expression Web 3.wmv"
rename CL58.wmv "CL58 – Accessing Web Services in Microsoft Silverlight.wmv"
rename CL59.wmv "CL59 – Unit Testing Silverlight and Windows Phone Applications.wmv"
rename CL60.wmv "CL60 – Silverlight Performance on Windows Phone.wmv"
rename DS01.wmv "DS01 – The Laws of User Experience.wmv"
rename DS02.wmv "DS02 – Treat Your Content Right.wmv"
rename DS03.wmv "DS03 – Running with Wireframes – Taking Information Architecture (IA) into Design.wmv"
rename DS04.wmv "DS04 – Lifecycle of a Wireframe.wmv"
rename DS05.wmv "DS05 – Total Experience Design.wmv"
rename DS06.wmv "DS06 – Touch in Public – Multi-touch Interaction Design for Kiosks and Architectural Experiences.wmv"
rename DS07.wmv "DS07 – The Art Technology and Science of Reading.wmv"
rename DS08.wmv "DS08 – Creating Great Experiences through Collaboration.wmv"
rename DS09.wmv "DS09 – Peanut Butter and Jelly – Putting ‘Content Management’ Back into Context.wmv"
rename DS10.wmv "DS10 – Service Design Goes Social.wmv"
rename DS11.wmv "DS11 – Great User Experiences – Seamlessly Blending Technology and Design.wmv"
rename DS12.wmv "DS12 – Total Experience – A Design Methodology for Agencies.wmv"
rename DS13.wmv "DS13 – The Elephant in the Room.wmv"
rename DS14.wmv "DS14 – The Democratization of the Design Industry.wmv"
rename DS15.wmv "DS15 – The Type We Want.wmv"
rename DS16.wmv "DS16 – An Hour With Bill Buxton.wmv"
rename EX01.wmv "EX01 – Cloud Computing Economies of Scale.wmv"
rename EX02.wmv "EX02 – The Mono Project.wmv"
rename EX03.wmv "EX03 – Modern Web Form Design.wmv"
rename EX04.wmv "EX04 – Robots at MySpace – Massive Scaling a .NET Website with the Microsoft Robotic Studio.wmv"
rename EX06.wmv "EX06 – 10 Ways to Attack a Design Problem and Come Out Winning.wmv"
rename EX07.wmv "EX07 – Principles of Microsoft Silverlight Graphics and Animation.wmv"
rename EX10.wmv "EX10 – Building a Next-Generation Web Application with Microsoft ASP.NET MVC 2 and jQuery.wmv"
rename EX11.wmv "EX11 – Using Storage in the Windows Azure Platform.wmv"
rename EX12.wmv "EX12 – Creating Effective Info Viz in Microsoft Silverlight.wmv"
rename EX13.wmv "EX13 – Microsoft Surface Goes Social – Research Project at the University of Linz.wmv"
rename EX14.wmv "EX14 – Understanding the Model-View-ViewModel Pattern.wmv"
rename EX15.wmv "EX15 – Build Your Own MVVM Framework.wmv"
rename EX16.wmv "EX16 – Securing Microsoft Silverlight Applications.wmv"
rename EX17.wmv "EX17 – IronRuby for the .NET Developer.wmv"
rename EX18.wmv "EX18 – Developing Natural User Interfaces with Microsoft Silverlight and WPF 4 Touch.wmv"
rename EX19.wmv "EX19 – The OpenRasta Framework for Building RESTful Applications.wmv"
rename EX20.wmv "EX20 – Building Great Standards-Based Websites for the Big Wide World with Microsoft ASP.NET 4.wmv"
rename EX21.wmv "EX21 – Syncing Audio Video and Animations in Microsoft Silverlight Applications.wmv"
rename EX22.wmv "EX22 – Six Things Every jQuery Developer Must Know.wmv"
rename EX23.wmv "EX23 – Building Facebook Apps with Microsoft .NET and Deploying to Windows Azure.wmv"
rename EX25.wmv "EX25 – Design the Ordinary Like the Fixie.wmv"
rename EX26.wmv "EX26 – From Comp to Code – A Design Communion.wmv"
rename EX27.wmv "EX27 – Do You Speak My Language? Microsoft Translator and the Power of Collaboration.wmv"
rename EX28.wmv "EX28 – Building Rich and Interactive User Experiences in SharePoint.wmv"
rename EX29.wmv "EX29 – Building Platforms and Applications for the Real-Time Web.wmv"
rename EX30.wmv "EX30 – SVG – The Past Present and Future of Vector Graphics for the Web.wmv"
rename EX31.wmv "EX31 – Developing Multiplayer Games with Microsoft Silverlight 4.wmv"
rename EX32.wmv "EX32 – Smooth Streaming Live in HD – From Camera to Screen.wmv"
rename EX33.wmv "EX33 – Smooth Streaming Live in HD – 2010 Olympic Winter Games.wmv"
rename EX34.wmv "EX34 – Participating in the Web of Data with Open Standards.wmv"
rename EX35.wmv "EX35 – Opening Up Opportunity with Twitter.wmv"
rename EX36.wmv "EX36 – How jQuery Makes Hard Things Simple.wmv"
rename EX37.wmv "EX37 – Adding the Where to the When of Social Applications.wmv"
rename EX38.wmv "EX38 – Building Large-Scale Data-Centric Applications with Silverlight.wmv"
rename EX39.wmv "EX39 – The Tale of JavaScript. I Mean ECMAScript..wmv"
rename EX50.wmv "EX50 – Debugging Microsoft Silverlight Applications.wmv"
rename EX51.wmv "EX51 – Building Finance Applications with Microsoft Silverlight 4.wmv"
rename EX52.wmv "EX52 – Copyright – A Cloudy Subject.wmv"
rename EX53.wmv "EX53 – Storm Clouds – What to Consider About Privacy Before Writing a Line of Code.wmv"
rename EX55.wmv "EX55 – Building the eBay Simple Lister with Silverlight.wmv"
rename EX56.wmv "EX56 – Designing Rich Experiences for Data-Centric Applications.wmv"
rename FT03.wmv "FT03 – Tips and Tricks for Making Web Forms Shine with Microsoft ASP.NET 4.wmv"
rename FT04.wmv "FT04 – What’s New in Microsoft ASP.NET MVC 2.wmv"
rename FT05.wmv "FT05 – The HaaHa Show – Microsoft ASP.NET MVC Security with Haack and Hanselman.wmv"
rename FT06.wmv "FT06 – Deep Dive into Orchard Extensibility for CMS Developers.wmv"
rename FT07.wmv "FT07 – Beyond File – New Company – From Cheesy Sample to Social Platform.wmv"
rename FT08.wmv "FT08 – Improving Software Quality for the Modern Web.wmv"
rename FT09.wmv "FT09 – Pumping Iron on the Web – IronRuby and IronPython.wmv"
rename FT10.wmv "FT10 – Driving Experiences via Services Using the Microsoft .NET Framework.wmv"
rename FT11.wmv "FT11 – Designing and Delivering Scalable and Resilient Web Services.wmv"
rename FT12.wmv "FT12 – OData – There’s a Feed for That.wmv"
rename FT13.wmv "FT13 – Implementing OData – How to Create a Feed for That.wmv"
rename FT14.wmv "FT14 – Web Deployment Made Awesome – If You’re Using XCopy You’re Doing It Wrong.wmv"
rename FT15.wmv "FT15 – Accessing Data in a Microsoft .NET-Connected Web Application.wmv"
rename FT50.wmv "FT50 – Advanced Web Debugging with Fiddler.wmv"
rename FT51.wmv "FT51 – Internet Explorer Developer Tools.wmv"
rename FTL01.wmv "FTL01 – Reactive Extensions for JavaScript.wmv"
rename FTL02.wmv "FTL02 – Building Pivot Collections.wmv"
rename FTL03.wmv "FTL03 – Unlocking Audio-Video Content with Speech Recognition.wmv"
rename FTL50.wmv "FTL50 – Incarnate – Behind the Scenes.wmv"
rename FTL51.wmv "FTL51 – Quickly Implementing New Cross-Browser Features with Ruby and Python.wmv"
rename KEY01.wmv "KEY01 – Keynote Day 1.wmv"
rename KEY02.wmv "KEY02 – Keynote Day 2.wmv"
rename PR01.wmv "PR01 – Designing Corporate Web Sites using SharePoint 2010.wmv"
rename PR02.wmv "PR02 – Designing an Internet-Facing Web Site Using SharePoint 2010.wmv"
rename SVC01.wmv "SVC01 – Using Windows Identity Foundation for Creating Identity-Driven Experiences in Microsoft Silverlight.wmv"
rename SVC02.wmv "SVC02 – Microsoft Project Code Name Dallas – Data For Your Apps.wmv"
rename SVC03.wmv "SVC03 – Using Ruby on Rails to Build Windows Azure Applications.wmv"
rename SVC04.wmv "SVC04 – Lap around the Windows Azure Platform.wmv"
rename SVC05.wmv "SVC05 – Building Web Applications with Windows Azure Storage.wmv"
rename SVC06.wmv "SVC06 – Microsoft Silverlight and Windows Azure – A Match Made for the Web.wmv"
rename SVC07.wmv "SVC07 – Building Web Applications with Microsoft SQL Azure.wmv"
rename SVC08.wmv "SVC08 – Connecting Your Applications in the Cloud with Windows Azure AppFabric.wmv"
rename SVC09.wmv "SVC09 – Building and Deploying Windows Azure-Based Applications with Microsoft Visual Studio 2010.wmv"
rename SVC10.wmv "SVC10 – Building Offline Web Apps Using Microsoft Sync Framework.wmv"
rename SVC12.wmv "SVC12 – Building PHP Applications using the Windows Azure Platform.wmv"
rename SVC50.wmv "SVC50 – Improving the Usability and Security of OpenID.wmv"

Simply take the above content, copy into notepad and save it in the same folder as all the WMV files. (I saved it as WmvRename.bat). Then just run it and your Mix10 filenames will no longer be all MIXed up!

TechMixer University – SSIS for Developers

In addition to help recruit speakers, I also had the privilege of speaking at TechMixer University 2009.

The slide deck and main demo can be found at my Code Gallery site:

https://code.msdn.microsoft.com/Release/ProjectReleases.aspx?ProjectName=SSISForDevs&ReleaseId=2883

The calling of SSIS from .Net demo can be found at:

http://code.msdn.microsoft.com/ssisfromnet

Thanks to everyone who attended TechMixer University. I look forward to seeing you next year!

Calling SSIS from .Net

In a recent DotNetRocks show, episode 483, Kent Tegels was discussing SQL Server Integration Services and how it can be useful to both the BI Developer as well as the traditional application developer. While today I am a SQL Server BI guy, I come from a long developer background and could not agree more. SSIS is a very powerful tool that could benefit many developers even those not on Business Intelligence projects. It was a great episode, and I high encourage everyone to listen.

There is one point though that was not made very clear, but I think is tremendously important. It is indeed possible to invoke an SSIS package from a .Net application if that SSIS package has been deployed to the SQL Server itself. This article will give an overview of how to do just that. All of the sample code here will also be made available in download form from the companion Code Gallery site, http://code.msdn.microsoft.com/ssisfromnet .

In this article, I do assume a few prerequisites. First, you have a SQL Server with SSIS installed, even if it’s just your local development box with SQL Server Developer Edition installed. Second, I don’t get into much detail on how SSIS works, the package is very easy to understand. However you may wish to have a reference handy. You may also need the assistance of your friendly neighborhood DBA in setting up the SQL job used in the process.

Summary

While the technique is straightforward, there are a fair number of detailed steps involved. For those of you just wanting the overview, we need to start with some tables (or other data) we want to work with. After that we’ll write the SSIS package to manipulate that data.

Once the package is created it must be deployed to the SQL Server so it will know about it. This deploy can be to the file system or to SQL Server.

Once deployed, a SQL Server Job must be created that executes the deployed SSIS package.

Finally, you can execute the job from your .Net application via ADO.NET and a call to the sp_start_job stored procedure built into the msdb system database.

OK, let’s get to coding!

Setup the Tables

First we need some data to work with. What better than a listing of previous Dot Net Rocks episodes? I simply went to the Previous Shows page, highlighted the three columns of show number, show name, and date, and saved them to a text file. (Available on the Code Gallery site.)

Next we need a place to hold data so SSIS can work with it. I created a database and named it ArcaneCode, however any database should work. Next we’ll create a table to hold “staging” DNR Show data.

CREATE TABLE [dbo].[staging_DNRShows](
  [ShowData] [varchar](250) NOT NULL
) ON [PRIMARY]

This table will hold the raw data from the text file, each line in the text file becoming one row here. Next we want a table to hold the final results.

CREATE TABLE [dbo].[DNRShows](
  [ShowNumber] [int] NOT NULL,
  [ShowName] [varchar](250) NULL,
  [ShowDate] [datetime] NULL,
  CONSTRAINT [PK_DNRShows] PRIMARY KEY CLUSTERED
  (
  [ShowNumber] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY]

The job of the SSIS package will be to read each row in the staging table and split it into 3 columns, the show’s number, name, and date, then place those three columns into the DNRShows table above.

The SSIS Package

The next step is to create the SSIS package itself. Opening up Visual Studio / BIDS, create a new Business Intelligence SQL Server Integration Services project. First let’s setup a shared Data Source to the local server, using the ArcaneCode database as our source.

The default package name of “Package.dtsx” isn’t very informative, so let’s rename it ”LoadDNRShows.dtsx”. Start by adding a reference to the shared data source in the Connection Managers area, taking the default. Then in the Control Flow surface add 3 tasks, as seen here:

clip_image001

The first task is an Execute SQL Task that simply runs a “DELETE FROM dbo.DNRShows” command to wipe out what was already there. Of course in a true application we’d be checking for existing records in the data flow and doing updates or inserts, but for simplicity in this example we’ll just wipe and reload each time.

The final task is also an Execute SQL Task, after we have processed the data we no longer need it in the staging table, so we’ll issue a “DELETE FROM dbo.staging_DNRShows” to remove it.

The middle item is our Data Flow Task. This is what does the heavy lifting of moving the staging data to the main table. Here is a snapshot of what it looks like:

clip_image002

The first task is our OLEDB Source, it references the staging_DNRShows table. Next is what’s called a Derived Column Transformation. This will allow you to add new calculated columns to the flow, or add columns from variables. In this case we want to add three new columns, based on the single column coming from the staging table.

clip_image003

As you can see in under Columns in the upper left, we have one column in our source, ShowData. In the lower half we need to add three new columns, ShowNumber, ShowDate, and ShowName. Here are the expressions for each:

ShowNumber
    (DT_I4)SUBSTRING(ShowData,1,FINDSTRING(ShowData,"\t",1))

ShowDate
    (DT_DBDATE)SUBSTRING(ShowData,FINDSTRING(ShowData,"\t",2) + 1,LEN(ShowData) – FINDSTRING(ShowData,"\t",2))

ShowName
    (DT_STR,250,1252)SUBSTRING(ShowData,FINDSTRING(ShowData,"\t",1) + 1,FINDSTRING(ShowData,"\t",2) – FINDSTRING(ShowData,"\t",1) – 1)

The syntax is an odd blend of VB and C#. Each one starts with a “(DT_”, these are type casts, converting the result of the rest of the expression to what we need. For example, (DT_I4) converts to a four byte integer, which we need because in our database the ShowNumber column was defined as an integer. You will see SUBSTRING and LEN which work like their VB counterparts. FINDSTRING works like the old POS statement, it finds the location of the text and returns that number. The “\t” represents the tab character, here the C# fans win out as the Expression editor uses C# like escapes for special characters. \t for tab, \b for backspace, etc.

Finally we need to write out the data. For this simply add an OLEDB Destination and set it to the target table of dbo.DNRShows. On the mappings tab make sure our three new columns map correctly to the columns in our target table.

Deploy the Package

This completes the coding for the package, but there is one final step we need to do. First, in the solution explorer right click on the project (not the solution, the project as highlighted below) and pick properties.

clip_image004

In the properties dialog, change the “CreateDeploymentUtility” option from false (the default) to True.

clip_image006

Now click the Build, Build Solution menu item. If all went well you should see the build was successful. It’s now time to deploy the package to the server. Navigate to the folder where your project is stored, under it you will find a bin folder, and in it a Deployment folder. In there you should find a file with a “.SSISDeploymentManifest” extension. Double click on this file to launch the Package Installation Wizard.

When the wizard appears there are two choices, File system deployment and SQL Server deployment. For our purposes we can use either one, there are pros and cons to each and many companies generally pick one or the other. In this example we’ll pick SQL Server deployment, but again know that I’ve tested this both ways and either method will work.

Once you pick SQL Server deployment, just click Next. Now it asks you for the server name, I’ve left it at (local) since I’m working with this on a development box; likewise I’ve left “Use Windows Authentication”. Finally I need the package path, I can select this by clicking the ellipse (the …) to the right of the text box. This brings up a dialog where I can select where to install.

clip_image007

In a real world production scenario we’d likely have branches created for each of our projects, but for this simple demo we’ll just leave it in the root and click OK.

Once your form is filled out as below, click Next.

clip_image008

We are next queried to what our installation folder should be. This is where SSIS will cache package dependencies. Your DBA may have a special spot setup for these, if not just click next to continue.

Finally we are asked to confirm we know what we are doing. Just click Next. If all went well, the install wizard shows us it’s happy with a report, and we can click Finish to exit.

Setup the SQL Server Job

We’ve come a long way and we’re almost to the finish line, just one last major step. We will need to setup a SQL Server Job which will launch the SSIS package for us. In SQL Server Management Studio, navigate to the “SQL Server Agent” in your Object Explorer. If it’s not running, right click and pick “Start”. Once it’s started, navigate to the Jobs branch. Right click and pick “New Job”.

When the dialog opens, start by giving your job a name. As you can see below I used LoadDNRShows. I also entered a description.

clip_image010

Now click on the Jobs page over on the left “Select a page” menu. At the bottom click “New” to add a new job step.

In the job step properties dialog, let’s begin by naming the step “Run the SSIS package”. Change the Type to “SQL Server Integration Services Package”. When you do, the dialog will update to give options for SSIS. Note the Run As drop down, this specifies the account to run under. For this demo we’ll leave it as the SQL Server Agent Service Account, check with your DBA as he or she may have other instructions.

In the tabbed area the General tab first allows us to pick the package source. Since we deployed to SQL Server we’ll leave it at the default, however if you had deployed to the file system this is where you’d need to change it to pick your package.

At the bottom we can use the ellipse to pick our package from a list. That done your screen should look something like:

clip_image011

For this demo that’s all we need to set, I do want to take a second to encourage you to browse through the other tabs. Through these tabs you can set many options related to the package. For example you could alter the data sources, allowing you to use one package with multiple databases.

Click OK to close the job step, then OK again to close the Job Properties window. Your job is now setup!

Calling from .Net

The finish line is in sight! Our last step is to call the job from .Net. To make it a useful example, I also wanted the .Net application to upload the data the SSIS package will manipulate. For simplicity I created a WinForms app, but this could easily be done in any environment. I also went with C#, again the VB.Net code is almost identical.

I started by creating a simple WinForm with two buttons and one label. (Again the full project will be on the Code Gallery site).

clip_image012

In the code, first be sure to add two using statements to the standard list:

using System.Data.SqlClient;

using System.IO;

Behind the top button we’ll put the code to copy the data from the text file we created from the DNR website to the staging table.

    private void btnLoadToStaging_Click(object sender, EventArgs e)

    {

      /* This method takes the data in the DNRShows.txt file and uploads them to a staging table */

      /* The routine is nothing magical, standard stuff to read as Text file and upload it to a  */

      /* table via ADO.NET                                                                      */

 

      // Note, be sure to change to your correct path

      string filename = @"D:\Presentations\SQL Server\Calling SSIS From Stored Proc\DNRShows.txt";

      string line;

 

      // If you used a different db than ArcaneCode be sure to set it here

      string connect = "server=localhost;Initial Catalog=ArcaneCode;Integrated Security=SSPI;";

      SqlConnection connection = new SqlConnection(connect);

      connection.Open();

 

      SqlCommand cmd = connection.CreateCommand();

 

      // Wipe out previous data in case of a crash

      string sql = "DELETE FROM dbo.staging_DNRShows";

      cmd.CommandText = sql;

      cmd.ExecuteNonQuery();

 

      // Now setup for new inserts

      sql = "INSERT INTO dbo.staging_DNRShows (ShowData) VALUES (@myShowData)";

 

      cmd.CommandText = sql;

      cmd.Parameters.Add("@myShowData", SqlDbType.VarChar, 255);

 

      StreamReader sr = null;

 

      // Loop thru text file, insert each line to staging table

      try

      {

        sr = new StreamReader(filename);

        line = sr.ReadLine();

        while (line != null)

        {

          cmd.Parameters["@myShowData"].Value = line;

          cmd.ExecuteNonQuery();

          lblProgress.Text = line;

          line = sr.ReadLine();

        }

      }

      finally

      {

        if (sr != null)

          sr.Close();

        connection.Close();

        lblProgress.Text = "Data has been loaded";

      }

 

Before you ask, yes I could have used any number of data access technologies, such as LINQ. I went with ADO.NET for simplicity and believing most developers are familiar with it due to its longevity. Do be sure and update the database name and path to the file in both this and the next example when you run the code.

This code really does nothing special, just loops through the text file and uploads each line as a row in the staging table. It does however serve as a realistic example of something you’d do in this scenario, upload some data, then let SSIS manipulate it on the server.

Once the data is there, it’s finally time for the grand finale. The code behind the second button, Execute SSIS, does just what it says; it calls the job, which invokes our SSIS package.

    private void btnRunSSIS_Click(object sender, EventArgs e)

    {

      string connect = "server=localhost;Initial Catalog=ArcaneCode;Integrated Security=SSPI;";

      SqlConnection connection = new SqlConnection(connect);

      connection.Open();

 

      SqlCommand cmd = connection.CreateCommand();

 

      // Wipe out previous data in case of a crash

      string sql = "exec msdb.dbo.sp_start_job N’LoadDNRShows’";

      cmd.CommandText = sql;

      cmd.ExecuteNonQuery();

      connection.Close();

      lblProgress.Text = "SSIS Package has been executed";

 

    }

The key is this sql command:

exec msdb.dbo.sp_start_job N’LoadDNRShows’

“exec” is the T-SQL command to execute a stored procedure. “sp_start_job” is the stored procedure that ships with SQL Server in the MSDB system database. This stored procedure will invoke any job stored on the server. In this case, it invokes the job “LoadDNRShows”, which as we setup will run an SSIS package.

Launch the application, and click the first button. Now jump over to SQL Server Management Studio and run this query:

select * from dbo.staging_DNRShows;

select * from dbo.DNRShows;

You should see the first query bring back rows, while the second has nothing. Now return to the app and click the “Execute SSIS” button. If all went well running the query again should now show no rows in our first query, but many nicely processed rows in the second. Success!

A few thoughts about xp_cmdshell

In researching this article I saw many references suggesting writing a stored procedure that uses xp_cmdshell to invoke dtexec. DTEXEC is the command line utility that you can use to launch SSIS Packages. Through it you can override many settings in the package, such as connection strings or variables.

xp_cmdshell is a utility built into SQL Server. Through it you can invoke any “DOS” command. Thus you could dynamically generate a dtexec command, and invoke it via xp_cmdshell.

The problem with xp_cmdshell is you can use it to invoke ANY “DOS” command. Any of them. Such as oh let’s say “DEL *.*” ? xp_cmdshell can be a security hole, for that reason it is turned off by default on SQL Server, and many DBA’s leave it turned off and are not likely to turn it on.

The techniques I’ve demonstrated here do not rely on xp_cmdshell. In fact, all of my testing has been done on my server with the xp_cmdshell turned off. Even though it can be a bit of extra work, setting up the job, etc., I still advise it over the xp_cmdshell method for security and the ability to use it on any server regardless of its setting.

In Closing

That seemed like a lot of effort, but can lead to some very powerful solutions. SSIS is a very powerful tool designed for processing large amounts of data and transforming it. In addition developing under SSIS can be very fast due to its declarative nature. The sample package from this article took the author less than fifteen minutes to code and test.

When faced with a similar task, consider allowing SSIS to handle the bulk work and just having your .Net application invoke your SSIS package. Once you do, there are no ends to the uses you’ll find for SQL Server Integration Services.

Welcome to COMFRAME

I admit to being remiss lately, my poor blog has been neglected for these past few weeks. I can only plead mea culpa and explain.

A few weeks ago I had an opportunity placed before me that I simply could not refuse. I’d been happy at my old job and wasn’t looking, but a good friend of mine works for a great company called COMFRAME. They are a consulting firm that does a variety of things, including Enterprise Project Management, .Net and Java development projects, SOA, and most important to me, Business Intelligence.

To make a long story short my friend took a lesson from the Godfather movies and “made me an offer I couldn’t refuse”. I am now a COMFRAME employee! The work is very exciting, I’ll be an architect on a BI project that is using Silverlight 3 for it’s front end. We are working with data from Microsoft Project, not only that but it’s the world’s biggest implementation of Project Server, so I’ll get to work with the fine folks at Microsoft even more closely. We’re also a Microsoft Partner, which will give me new avenues for relationships that will compliment my MVP.

I got to meet the customer this week, although brief they seemed very easy to work with, and nice as well. I also got to meet the development team I’ll be working with, I’m impressed with the work they’ve done so far and can’t wait to roll up my sleeves and dive in.

I’ve had a crazy time wrapping up my old job and starting my new one, hopefully I can get back to regular blogging soon. I’ve been doing a lot with SSIS and SSAS which will give me lots of good material to talk about, not to mention any Silverlight 3 work I get to explore.

SSIS For Developers at CodeStock 2009

At the 2009 CodeStock event I am presenting SQL Server Integration Services for Developers. This class will demonstrate tasks commonly done by VB.Net or C# developers within SQL Server Integration Services.

The sample project and documentation for the lab can be found on the code gallery site at http://code.msdn.microsoft.com/SSISForDevs .

BSDA Presentation on Visual Studio Database Edition

Last week I did a presentation at the Birmingham Software Developers Association on generating sample data using Visual Studio Database Edition, often called by it’s code name of “Data Dude” for short.  You can find my original posting, which has links to the code gallery site at http://arcanecode.com/2009/04/02/sql-server-sample-data-the-sql-name-game/ .

During my presentation I was using Visual Studio Team System 2008 Database Edition GDR R2, which you can find here: http://www.microsoft.com/downloads/details.aspx?FamilyID=bb3ad767-5f69-4db9-b1c9-8f55759846ed&displaylang=en 

This update assumes you have Visual Studio Database Edition installed. Most developers with an MSDN license have the Development Edition installed on their PC. When Microsoft announced the Database and Development products would merge in the Visual Studio 2010 product, they made the Development Editions of Visual Studio 2005 and 2008 available via MSDN. Go check your MSDN, and see if you have “Data Dude”. If so download and install it, then download and install the GDR R2 update from the link above. These will add new menus and tools to your Visual Studio environment.

Most notably you’ll look at the Data menu. there are menu options for Schema Compare and Data Compare. These will allow you to setup comparisons between a source and target for schemas or data.

Big Thinkers – Andy Warren

image

I’m devoting this week to “Big Thinkers”. I want to highlight individuals who challenge my thought processes and cause me to think about my profession, my methodologies, and force me to reflect on my skills. Some of these individuals I have the privilege of knowing personally, others I have only known via Podcasts or Twitter. I’m hoping that by highlighting these Big Thinkers you too will be challenged to grow and evolve in your craft. Last week was focused on individuals in the development community, this week will focus on the SQL Server realm.

Rather than “Big Thinker”, I think the label “Big Do-er” may be more accurate when it comes to today’s selection. Andy Warren maintains a blog at SQL Server Central (and was also one of its founders), and runs End to End Training out of Orlando FL. He also had a vision for training videos that were short in duration (roughly five minutes) and very focused on a single subject, hence he created JumpstartTV.

His biggest contribution to the community perhaps centers around SQL Saturday. Andy saw the success around code camps, events where developers could congregate on a Saturday and take free community based training. At the same time he recognized some of the difficulties around them. They tended to be hard to find, without a standard look and feel to their websites. There was also a hurdle for people wanting to put on a code camp for the first time. Andy decided to act.

He created SQL Saturday.com, a centralized website where anyone wishing to put on a SQL Saturday could advertise their event, handle registrations, schedules, and speakers. He created a guide for event planners, to give them a checklist for their event. Speaking from personal experience, I know we followed the guide closely and found it very valuable when our group held SQL Saturday 7 recently. Finally Andy throws himself into the event as well, appearing personally at as many SQL Saturdays as humanly possible.

Truly Andy is the shining example of “one man can make a difference” and I can but hope my own contributions will come anywhere close to Andy’s.

Big Thinkers – Brent Ozar

image

I’m devoting this week to “Big Thinkers”. I want to highlight individuals who challenge my thought processes and cause me to think about my profession, my methodologies, and force me to reflect on my skills. Some of these individuals I have the privilege of knowing personally, others I have only known via Podcasts or Twitter. I’m hoping that by highlighting these Big Thinkers you too will be challenged to grow and evolve in your craft. Last week was focused on individuals in the development community, this week will focus on the SQL Server realm.

Brent Ozar is an active blogger and Twitterer, in addition to being Editor-in-chief at SQL Serverpedia. The site hosts many video tutorials, many of which are created by Brent. I very much like his style, it is relaxed, easy going, but informative and right to the point. I find it very easy to learn from these videos, thus enhancing my skills greatly. I find Brent to be a good role model for my public speaking, and I think you will too.

Big Thinkers – Pinal Dave

image I’m devoting this week to “Big Thinkers”. I want to highlight individuals who challenge my thought processes and cause me to think about my profession, my methodologies, and force me to reflect on my skills. Some of these individuals I have the privilege of knowing personally, others I have only known via Podcasts or Twitter. I’m hoping that by highlighting these Big Thinkers you too will be challenged to grow and evolve in your craft. Last week was focused on individuals in the development community, this week will focus on the SQL Server realm. “

Pinal Dave reminds me of that chef who goes “bang” all the time. I first got to know him when I was working on a SQL Server project and doing some things that were new to me. I’d do a web search and “bang”, there came the answer on his blog. Another search and “bang” there was his blog in the top 10 results again. Over and over that day I’d search and “bang” there would be the answer, right on his blog in an easy to read and understand format.

Pinal has to be one of the most prolific writers I’ve seen, his blog SQL Authority is filled with informative, easy to understand articles. I also had the privilege of meeting him at the MVP summit earlier this year, and he has got to be the nicest guy in SQL Server you’ll ever meet. He is also a frequent poster on Twitter at http://twitter.com/pinaldave. To me he is the embodiment of helpful service, and reminds me to remain humble as I work in the SQL community.

Go ahead, give his blog a try. By the end of the day you too may be thinking “Hey, who needs Books on Line when you have Pinal Dave?”

Big Thinkers – Kimberly Tripp and Paul Randal

image image

I’m devoting this week to “Big Thinkers”. I want to highlight individuals who challenge my thought processes and cause me to think about my profession, my methodologies, and force me to reflect on my skills. Some of these individuals I have the privilege of knowing personally, others I have only known via Podcasts or Twitter. I’m hoping that by highlighting these Big Thinkers you too will be challenged to grow and evolve in your craft. Last week was focused on individuals in the development community, this week will focus on the SQL Server realm.

Today’s pick is actually a two for one special. Perhaps not fair since individually either of them is outstanding in the SQL Server field and have appeared on more podcasts and events than I can count, but since they got married they have become an unstoppable, inseparable duo. I speak of course of Paul Randal and Kimberly Tripp. While most couples argue over paint color, they argue over indexing strategies. As I said they’ve been on more podcasts than I can count, some of my favorites though were Dot Net Rocks Episodes 178, 110, 74, 217, plus RunAsRadio shows 104, 76, 74, 72, and my favorite Episode 36. In addition to podcasts I’ve seen them present live at TechEd.

I like Paul and Kim because they make SQL Server fun. Yes, I said fun. During one of their presentations I feel like a kid being shown a toy catalog a page at a time. When its over I can’t wait to get my hands on the geeky SQL Server toys I’ve just been shown. Take a listen, I believe you’ll find their fun infectious and will soon be ‘playing’ with a new toy called SQL Server.

(And just for the record, I don’t care what Carl Franklin says, Kimberly is the cuter one of the two. )

Big Thinkers – Steve Jones

image

I’m devoting this week to “Big Thinkers”. I want to highlight individuals who challenge my thought processes and cause me to think about my profession, my methodologies, and force me to reflect on my skills. Some of these individuals I have the privilege of knowing personally, others I have only known via Podcasts or Twitter. I’m hoping that by highlighting these Big Thinkers you too will be challenged to grow and evolve in your craft. Last week was focused on individuals in the development community, this week will focus on the SQL Server realm.

Steve began working with SQL Server way back in 1991. One of his earliest DBA jobs was on SQL Server version 4.2 running on OS/2 v1.3. To say Steve is prolific is an understatement; he seems to be all over the web. He does a regular column at SQL Server Central, which he cofounded. Steve also does a regular video podcast called “The Voice of the DBA”. Finally he is very active on Twitter, engaging others in regular conversation.

I think that is what I like most about Steve. His style is very conversational, when ever I read his editorials or watch his video podcasts I always feel like I’m right there with him, having a discussion. A frequent closing line to his videocast is “tell me what you think”. Thinking is what Steve inspires, after he throws out a topic I invariably wind up pondering it for a while. I’ve met Steve, and he is just like what you see in the videos, I never walk away from him without having something fun to mull over in the old gray matter. Check out Steve and see if your brain isn’t buzzing afterward.

Follow

Get every new post delivered to your Inbox.

Join 93 other followers