Corey Roth [MVP]

You might have already seen this through MSDN, but AppDev is giving a way a free Atlas training CD. This one might actually be worth using, who knows? The CD is free, but be warned that someone from AppDev sales will definitely call you if you give them your number. If anyone tries it out, let me know.

Free Atlas Training CD

Ok, this is pretty cool. I saw this on Scott's blog and had to try it since I am doing so much SQL Sever work lately. RedGate released a free program that enables IntelliSense when working with SQL Queries. What's cool is it works in everything from SQL Server Management Studio, Visual Studio 2005/2003 and even Enterprise Manager. Once you get it installed activate it by pressing Ctrl+Space. So far from what I have seen it works great.

SQLPrompt

Don't feel like installing Vista yet, but you want to check out IIS7? Well no problem. At the new site IIS.net, they have virtual labs where you can spend 90 minutes messing around with the new IIS7. Definitely worth taking a look.

IIS7 Virtual Labs

Also of note, IIS.net, has a ton of information on the new IIS7.

IIS.NET

By far one of the most hatet things by developers in Windows XP is the fact that the made it so that only one instance of a web site could be in IIS. This made developing multiple sites quite a pain in the ass. I got Windows Vista Beta 2 installed tonight (which btw is a lot more stable than the last CTP I tried) and I ran IIS and I was able to create multiple web sites. On a side note IIS7 is a ton different, and I will have to go into whats new in a future post.

There are actually quite a few ways to deploy an SSIS package to a SQL Server. The first is to change the CreateDeploymentUtility property to true on the properties of your SSIS project. By changing this setting and then doing a built it copies files to the bin\deployment folder (by default) and creates a manifest file.

You then copy these files over to the SQL Server and execute the manifest file on the SQL Server itself. This launches a wizard that guides you through the installation of your SSIS packages. It will first prompt you if you want to install the packages on the file system or on the SQL Server. If you choose filesystem, the dtsx files (the extension of an SSIS package) can be put pretty much anywhere and the SQL Server can use them. If you choose SQL Server, the packages will be stored in the MSDB database.

If your packages are not encrypted, you can check the option to validate the packages after installation. This is useful to verify that the package will work, but unfortunately does not work when you encrypt the package. The validation process will always return an error (even though the package installed fine).

Once you have done this once, you will notice the process kind of takes a while because it has to open every package in your project (and it will give you the error I mentioned yesterday on everyone). Luckily the process is actually really simple. Instead of rebuilding every time, simply copy the dtsx file from your project directory to the SQL Server and run the manifest file.

To speed up the installation process you can modify your manifest file or create a new one (it is just XML). Here it the format.

<DTSDeploymentManifest 
GeneratedBy="DOMAIN\user" GeneratedFromProjectName="Project Name" 
GeneratedDate="2006-05-22T14:15:43.2745185-05:00" 
AllowConfigurationChanges="true">
  <Package>MyPackage1.dtsx</Package>
  <Package>MyPackage2.dtsx</Package>
</DTSDeploymentManifest>

As you can see it is pretty simple. A few other things to note. First, remember that SSIS packages are not visible in SQL Server Management Studio by just connecting to the Database Services instance of a SQL Server. You have to choose Integration Service in the dropdownlist before you connect. Second, supposedly you can import a package using Management Studio, but everytime I try it, I get an error. Maybe it will work better in a different environment, I don't know.

This by far is one of the most annoying things that occurs when working with a package. When opening a package that you have been working with for a while, you will most likely start getting this error. Although, not critical, it can make working with packages and building deployment packages take a while.

So what causes it? It happens when you start using Script Tasks. These script tasks are by default compiled into binaries which effectively get stored in the .dtsx file. You can turn set the PreCompile property to false on your script components, but that of course affects runtime performance. So basically, we are stuck with this error message for now.

At some point you may be assigning permissions to execute a stored procedure or something and notice that there is a new column labeled WITH GRANT. As you GRANT gives that user or role permission to perform that activity (i.e.: execute a stored procedure). WITH GRANT gives that user or role permission to GRANT other users or roles that same permission.

So you created this great new SSIS package in Business Intelligence Development Studio and now you want to deploy it to the SQL Server. You build a manifest (I'll actually explain how to do this in the next post), and copy it to the SQL Server and run it and validation starts. Then you notice that you are getting login failures using the credentials you specify in the connection. You go back and test them and they are correct. You try again and it still doesn't work. This is caused by the default protection level of an SSIS package.

In SSIS packages there is a property called ProtectionLevel. This property by default has a value of EncryptSensitiveWithUserKey. What this basically means is that all passwords and other data SSIS deems sensitive (i.e.: passwords) will be encrypted using a UserKey (based off the account you created the package with. This works well, but once you transfer it to a SQL Server, you are more than likely not using the same account to run the package.

To get the package to work you have to change the protection level to either EncryptSensitiveWithPassword or EncryptAllWithPassword. The difference between the two is that EncryptSensitive just encrypts the sensitive data and EncryptAll encrypts the entire package. You are required to specify a password to encrypt the password with. This same password is required to install the package and execute it.

Note, I have tried using EncryptSensitive and I have still had trouble with it storing password for connections. Therefore, you might have to use EncryptAll.

There is also another protection level called ServerStorage. When you are deploying a package into MSDB, it will make use of database security. However, I have not figured out how to use this yet since Business Intelligence Development Studio will not let you select it when saving an SSIS package to the file system. If anyone figures it out, please let me know.

I have always wondered what the Clean menu item did in the Build Menu, so I finally bothered to look it up. Basically, it goes in and deletes all intermediate and output files created by previous builds. Effectively, it cleans your project directory and leaves you with nothing but the code and what you have in your project.

I have been messing around quite a bit with SSIS lately and couldn't find an abundance of information on how to read or write to a variable from a Script Component. It actually differs from accessing a variable from a Script Task.

Too get access to the variables collection, basically you create an instance of the IDTSVariables90 object (no idea why it is called that). Then you use the VariableDispense to lock it for reading or writing using the LockOneForRead or LockOneForWrite methods and passing it the name of the IDTSVariables90 object you just created. There is also a LockForRead and LockForWrite method which I believe are used when you want to lock multiple variables at the same time. Once you lock the variable, to read it all you have to do is access it via an indexer and use the Value property. You will of course have to cast it to whatever type you need. Once you are done be sure and call the Unlock method to release the variable.

The following code shows the procedure for reading a variable. Since SSIS script components currently only support support Visual Basic.NET and Marcus loves it so much, the code below is in VB.

Dim dtsVariables As IDTSVariables90
Me.VariableDispenser.LockOneForRead("VariableName", dtsVariables)
Dim MyInteger = CType(dtsVariables("VariableName").Value, Integer)
dtsVariables.Unlock()

Writing to a DTS variable is pretty similar.

Dim dtsVariables As IDTSVariables90
Me.VariableDispenser.LockOneForWrite("VariableName", dtsVariables)
dtsVariables("VariableName").Value = myInteger
dtsVariables.Unlock()

I hope this helps if you have to write a package someday.

The final version of the web application project template has finally been released. This gives you a web project that is pretty much similar to that of Visual Studio 2003. This makes conversion from 2003 require almost no code changes, but you do do lose some of the benefits of the traditional web project in 2.0. For example, since web application projects have a project file, you still have to check it out and in to add a new file to the project. It also compiles everything into a single DLL (although this may be configurable, I'm not sure yet). It does add one cool thing which is edit and continue support in both C# and VB.

If you have a situation in which this style of project makes more sense, go check it out.

Web Application Projects 1.0

If you haven't bothered installing SQL Server Management Studio because you either don't have the disc or didn't want to deal with SQL Server 2005, you are missing out. Microsoft has now released SQL Server Management Studio Express as a free seperate download.

Although SQL Server Mangement Studio isn't perfect for working on everything on existing SQL Server 2000 servers, it is great for doing most things such as looking at data and creating stored procedures. Download it today and check it out. If it doesn't do everything you need, you can always use Enterprise Manager for those tasks.

SQL Server Management Studio Express

On another note, have you tried out using SQL Server Express. It is surprising powerful and Microsoft has now provided what they call advanced services which includes Reporting Services and Full-Text Search. Although, you can't host a large enterprise application with it, it is great if you need a simple database for your website, or need a place to store data with a Windows Forms application (it can even be bootstrapped to a Click Once Install). Try it out today. Note, if you want to set the instance name or enable mixed mode authentication, I recommend doing a custom install.

SQL Server 2005 Express

For those of us that weren't lucky enough to get to go to MIX06, you can view the sessions online at the link below.

MIX06 Sessions

Microsoft has released the source code for the built in ASP.NET 2.0 providers (i.e.: Session, Profile, Membership, Role Management, Web Events, etc.). This is cool because it will make it really easy to customize existing providers into the schema or whatever that you need.

Here is the link.

Provider Source Code

Chances are there is something out there that you need to create reports on. Whether it is transactions, reservations, members, change orders, or bandwidth utilization, SQL Server Reporting Services is an easy way to build nice looking reports.

Let me start by clearing up any miconceptions about it that might be viewed as hurdles.

1. You do not need a server running SQL Server 2005 to develop Reporting Services.
2. You can query against any normal data source (i.e.: SQL Server 2000), so again you don't need to be runing SQL Server 2005.

SQL Server Reporting Services is developed using the Business Intelligence Development Studio (it's the Visual Studio 2005 IDE). All you need to do is install the client tools for SQL Server 2005 on your local machine. Basically all this does is install the project templates needed to create a Reporting Services project.

Creating a new report is easy with the report wizard. It will first ask you for a SQL Server connection, then it will ask you for your query. You can use any valid ad-hoc or stored procedure you want. Then it will ask you for a tabular layout or matrix layout. Unless you know how to do real data mining type stuff, you will probably typically go with the matrix layout for most simple reports. Last, you just select the columns you want and a layout and you will have the start of a basic report.

Typically with most reports, you will have the need for date parameters. You will also usually want to specify default dates for these. There are a couple of things you have to do to make this happen. First, add the parameters, by clicking on the layout tab, right clicking anywhere and selecting report parameters. Here you can add parameters in various types, but typically the most common is DateTime. Add the parameters you need and I'll explain how to set default dates down below.

After you add your parameters, click back on the data tab, so we can add them to the query. The syntax for using a parameter is Parameters!ParameterName.Value for example Parameters!StartDateTime.Value. Unfortunately, you have to change the query syntax slightly to make this happen. You have to convert the string to an excel expression.

For example if you had the following query:

SELECT Field1, Field2 FROM MyTable WHERE
TransactionDate > '4/1/2006'

It would have to be changed to the following to use a new parameter called StartDateTime:

="SELECT Field1, Field2 FROM MyTable WHERE " &
"TransactionDate > '" & Parameters!StartDateTime.Value & "'"

Note, once you make this change, it won't let you run the SQL Query any more with by clicking the ! button. You always have to click on the Preview tab at this point to view the report.

Now that you have a parameterized query built, you might want to set a default value. You can do this by either hard coding it (huh huh), executing another query, or using an Excel expression. Typically, I just use excel expressions. Here are a couple common ones that you might find useful. For dates, remember that you have to cast whatever you build back to a Date object.

• =Now (Obviously now)
• =CDate("1/1/2006") (Start of the year)
• =DateAdd(DateInterval.Day, -7, Today) (7 days ago)
• =CDate(Month(Now).ToString() & "/1/" & Year(Now).ToString()) (First Day of the Current Month)
• =IIf(Weekday(Today) = 4 or Weekday(Today) = 6, DateAdd(DateInterval.Day, -2, Today), DateAdd(DateInterval.Day, -3, Today)) (3 days ago from Mondays, 2 days ago from Wednesdays and Fridays)

As you can see you can get as elaborate as you want with the expressions. Once you are done with development, you will want to deploy the reports to an instance of SQL Server 2005 with Reporting Services installed. Reporting Services can be installed to the same server running SQL Server. However, typically you will probably want it installed somwehere else (although that does require another SQL Server license). Again you do not have to have a full instance of SQL Server 2005 installed anywhere to install Reporting Services.

Once you have a Reporting Server to use, it is easy to deploy your reports from Visual Studio. Once deployed, you will be able to take advantage of execution snapshots and subscriptions. For example, you can create a subscription to have the report executed every other Wednesday and E-mail a PDF, Excel document, XML file, or whatever to someone.

I'll talk more in the future about what you can do on the server side of things. I hope this is enough to get you started though with this powerful and easy to use tool.