Random Neural Firings

Multi Row Inserts in T-SQL

2011-12-02T11:14:00.000-05:00

A new feature in SQL Server 2008 is the ability to insert multiple rows with one insert statement!

In previous versions, anytime you wanted to insert multiple rows into a table with a T-SQL script, you would have done something like this:

Insert into MyTable (Field1, Field2)
Values ('ABC',123)
Insert into MyTable (Field1, Field2)
Values ('DEF',456)

However, with SQL Server 2008, you can now do this:

Insert into MyTable (Field1, Field2)
Values ('ABC',123),('DEF',456)

Working with a growing data mart, I do a lot of scripted inserts, so this definitely makes my life a little simpler!

peace

Unattended Visual Source Safe 2005 installation

2011-10-18T13:38:00.000-04:00

To install Visual Source Safe 2005 silently, you must first create an install ini file using Setup.exe.
The setup in the root of the CD will not do this, you have to navigate to the setup folder on the CD.
If you are using a download from MSDN, there is only one setup.exe and it’s the one to use.
The machine that you use to create this ini will be the template for all installations. You cannot create the ini file from a machine that already has VSS installed. You must create it from a machine that does not yet have VSS installed.

From a command line, execute the following:

\\(servername)\(sharename)\(VSS Path)\setup.exe /CreateUnattend \\(servername)\(sharename)\(Path to INI)\filename.ini

I create my ini file in the same folder as the setup process.
For example, my command would look like this:

\\VSSServer\VSS\setup.exe /CreateUnattend \\VSSServer\VSS\FullInstall.ini

Again, the machine that you are running this process on should not have VSS installed yet. If it does have VSS installed, the ini file will be used to upgrade or add/remove features and will not install VSS on the target machine.

To install on the target machine, run this command:

\\(servername)\(sharename)\(VSS Path)\setup.exe /UnattendFile \\(servername)\(sharename)\(Path to INI)\filename.ini

Repeat Table Header on Each Page in SSRS 2008R2

2011-09-13T10:16:00.000-04:00

I ran into an issue with the tablix in SSRS 2008R2 where I set the tablix property “RepeatColumnHeaders” to True. However, this did not cause the table header to repeat.

After a little experimentation, and a little help from Paul Turley, I figured out how to consistently get the table header to repeat on every page.

The setting of the “RepeatColumnHeaders” property on the tablix seems to have no effect on this behavior. Instead, we have to set it on a static member of the row groups.

To see the static members of the row groups, click on the small drop down arrow on the far right of the row groups/columns groups header. Then select Advanced Mode.

With the static members of the row group shown, select the first one in the list. The top left cell of the tablix should be selected in the designer.

Now open the property pane and find the property “RepeatOnNewPage” and set it to True.

Then find the property “KeepWithGroup” and make sure its set to After. If its not, set it to After.

Now your table header will repeat on every page.

BTW, check out Paul’s book “Microsoft SQL Server Reporting Services Recipes: for Designing Expert Reports” It has been a good resource for SSRS.
peace

SSIS 2008 / SQL Server 2008 Connectivity Issue

2010-09-23T15:29:00.000-04:00

We recently upgraded to SSIS 2008 and SQL Server 2008 r2. This helped the performance of a large package that contained 27 lookups for a fact table. It used an OLEDB destination component, set to Table – Fast Load. It would process a million rows in less than 2 minutes.

This worked well, until I added a multicast and an OLE Command component to the data flow. What I wanted was to check the values passed to the stored procedure and add the existing row to history if there was a change. The OLE Command used the same connection manager as the main data insert, since the history and the current tables were in the same database.

When running it, I would get to about 100,000 rows, and then SSIS would begin to slow down and finally it would lose network connectivity to the SQL Server. (We have a separate SSIS server) This is the error that was written to the logs:

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.
Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0"
Hresult: 0x80004005 Description: "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0"
Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0"
Hresult: 0x80004005 Description: "Named Pipes Provider: No process is on the other end of the pipe."

I began by troubleshooting the network, looking for errors on the switch and both servers. This yielded nothing, there were no errors logged. So I turned to SSIS, figuring that it somehow was the culprit since everything worked until I added the multicast.

I removed the multicast and the OLE Command components and tested again. The package processed a million rows in less than 2 minutes.

I added the multicast back in, without the OLE Command and tested. Again it processed a million rows in less than 2 minutes.

I added the OLE Command component back in and tested. It failed at 49,000 records.

I added another connection manager, set the same properties and then pointed the OLE Command to the new manager. When I tested, I was surprised to see it complete successfully!

It seems that the one connection manager was not able to handle the multiple destinations simultaneously. I have no way to confirm this at this time, but since I have multiple connection managers for the same database, I have not experienced this issue at all.

SSRS Fix for FireFox/Chrome Report Size

2010-08-04T12:43:00.000-04:00

If you have ever used Chrome or FireFox with SSRS, you know the report window can show up very small. This is due to the way that SSRS implements the IFRAME.

A quick fix for this is to add the following entry to the ReportingServices.css file.

.DocMapAndReportFrame
{
min-height: 800px;
min-width: 800px;
}

You can usually find the ReportingServices.css file at: C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager\Styles, however your location may be different depending on your installation.

peace

Using Maximum Insert Commit Size with FAST LOAD on the OLE DB Connector, and a Warning

2010-07-23T11:29:00.000-04:00

I have been working on some new ETL recently, which of course has me optimizing the ETL. One of the things that I look at while optimizing is the Maximum Insert Commit Size (MICS) one the OLEDB connector when using the Table – FAST LOAD option.

This setting controls how many records are sent and committed to a table at a time. For example, if you have a 12,000 record source, and you set MICS to 5,000, you will have 3 bulk insert statements sent to the server: two with 5,000 records and 1 with 2,000 records.

The beauty of doing this is that it helps keep the transaction log small if you use simple logging. (On staging databases, I routinely use simple logging since there is no need to have full logging). It can also increase performance since the server is dealing with smaller chunks of data at one time.

But, like all good things, there is something you should watch out for. If you set up redirect row on error in the OLEDB Destination and one record in a batch fails, that one record will be redirected to the error output, but the entire batch will not be inserted into the database! The only indication you will get is that one record failure.

I have tried to set the MICS to 1 for safety, but that ends up being slower than the normal Table Load.
What I wound up doing is using the normal table load on smaller tables, and removing the redirect row error handling on larger tables. I then put in a derived column step that clean the data before inserting it. This allows me to load a 46 million row table in a few minutes, with a transaction log that doesn’t get above 10 Megs.

So, definitely work with the MICS setting to further optimize your ETL, but if you are using error handling to redirect rows, be aware that you will lose data each time a row is redirected.

Setting a NULL value in SSIS derived column

2010-07-14T13:55:00.004-04:00

Ran into an interesting error while trying to set a null value to a column in a derived column task in SSIS.
Here is my statement:

[CustNum]=="" ? NULL(DT_STR, 4, 1252) : [CustNum]

And the error thrown:

For operands of the conditional operator, the data type DT_STR is supported only for input and cast operations.

The correct syntax for this is:

[CustNum]=="" ? (DT_STR, 4, 1252)NULL(DT_STR, 4, 1252) : [CustNum]

Not sure why this requires the explicit cast since you include the type in the null statement.

peace

Deploying SSIS Packages with a Batch File

2010-07-08T11:17:00.001-04:00

This is a handy little batch file that I have been using for years to deploy multiple SSIS packages to a SQL Server. It is pretty basic; it copies all of the *.dtsx files in a specified folder to the SQL Server.
It first removes the packages from the SQL Server, deletes the folder, and then copies the new files to the server. One limitation is that it only deletes the packages that are in the source folder. So if you have extra packages deployed, you will need to remove them manually.

This batch files uses DTUTIL so it must be run on a machine that has SSIS installed.

The syntax for running the file is: DeploySSISPackages.cmd (SQL Server name) (Folder containing packages to deploy) (Folder in MSDB to deploy to)

If any of the 3 parameters contain spaces, enclose them in quotes.

Examples:

DeploySSISPackages.cmd SQLServer001 “C:\My Projects\SSIS\ETL” “ETL SSIS”

DeploySSISPackages.cmd SQLServer001 C:\Projects\SSIS\ETL ETL

Enjoy!

@Echo Off

Echo.
Echo.
Echo SSIS Package Installation Script
Echo Written by Bob Pearson
Echo 7/6/07
Echo.

if %1a == a goto Error
if %2a == a goto Error
if %3a == a goto Error

Echo.
Echo.
Echo Deployment Server: %1
Echo -----------------------------------------------------
Echo --This will delete any %3 data mart files
Echo --on the server, and reinstall from the local machine
Echo -----------------------------------------------------
Pause
REM Goto Out

REM Remove Existing files and directory on Server
for %%f in (%2"\*.dtsx") do (
Echo Now Removing: %%~nf
dtutil /Q /SourceS %1 /SQL "\%3\\%%~nf" /Del
)

dtutil /Q /SourceS %1 /FDe "SQL;\;%3"

:Create

Echo.
Echo Preparing to create folder
Echo.
pause

REM Create the Directory
dtutil /Q /SourceS %1 /FC "SQL;\;%3"
if errorlevel 1 goto End
Echo.
Echo Preparing to Copy Files to Server
Echo.
pause

:Out
REM copy the SSIS Packages to the server
for %%f in (%2"\*.dtsx") do (
Echo Now Copying: %%~nf
dtutil /Q /DestS %1 /Fi "%%f" /C "SQL;\%3\\%%~nf"
)

Echo.
Echo.
Echo Installation Complete!
Echo.
Echo.
Pause
Goto End

:Error
Echo.
Echo.
Echo Missing Servername!
Echo Syntax: Deploy SSIS Packages [servername] [Source File Path] [MSDB Deploy Folder]
Echo.
Echo.

Pause

:End

Pulling Data from DB2 into SQL Server using SSIS

2010-07-02T15:05:00.001-04:00

Well, I am finally back! I am at a new location, in a new industry, and working with new data! Very exciting times!

One of the new things I am working with is pulling data into SSIS from a DB2 database running on an AS/400. The first thing I did was scour the net for information on which provider I should use to pull data from DB2 to SQL Server. I found 4 options:

1. Use a flat file extract from the DB2
2. Use the ODBC provider.
3. Use the iSeries OLEDB provider.
4. Use the Microsoft OLEDB provider.

After some initial testing between the ODBC provider and the iSeries OLEDB, I ruled out the ODBC. It is much too slow. I ran several small tests, but once I got up to 100k records, it was too painful to even complete the ODBC portion of the tests.

I then turned my attention to comparing the Microsoft OLEDB v3.0 provider to the iSeries OLEDB V5R3M0 provider.

I setup an SSIS package with 2 sequence containers: one that contained the flow for the MSOLEDB and one for the iSOLEDB. I setup several SQL SELECT statements from the tables that I will be using in my ETL. I used no WHERE clause, just a basic select. I wanted this test to be as simple as possible.
I then put these 2 containers into a for..next container and set it up to loop 10 times.

Note: I am running on a development server with limited memory (4 gigs, running a SQL server instance and SSIS) and 4 processors.

I ran it on the first SQL statement that would return about 4k rows. The MSOLEDB ran in 298ms, the iSOLEDB in 732ms. I changed the SQL statement to return 400k rows. The MSOLEDB took 83 seconds (1min, 23sec), and the iSOLEDB took 514 seconds (8min, 34sec). I then went for a big test, 1 million rows. The MSOLEDB took 636 seconds (10min, 36sec) and the iSOLEDB took 2008 seconds (33min, 28sec).

So as you can see from these tests, the Microsoft OLEDB for DB2 provider is much faster. However, how would it perform on a multimillion row rowset?

I tested the Microsoft OLEDB provider for DB2 against a table that contained over 12 million rows, with each row being ~500 bytes wide. The flat file import took 15 min, 56sec. The MSOLEDB took 20min, 53sec. However, if I use the OLEDB provider, I do not have to worry about the file not being created, etc. There are less moving parts. So for this example, I am going to use the OLEDB provider. I have several tables that have more than 40 million records. I am going to have to test these out and then decide how much it is worth to not have to rely on external file creation processes.

So, in summary, the new Microsoft OLEDB Provider for DB2 is very speedy. It is a no brainer to use it instead of the iSeries or the ODBC providers. For larger tables however, flat files may still be the way to go.

peace

Wave of the future...

2009-06-30T08:46:00.003-04:00

Here is an interesting demo by Doug Engelbart from 1968, showing how a computer could be used in an office setting...

The quality isn't the best, but the demo is very interesting. This would have been very exciting to work on.

peace

Tab Delimited Renderer in SSRS 2005

2009-06-18T23:25:00.004-04:00

I was recently tasked with coming up with a tab delimited renderer in SSRS 2005.

My first thought was to use the CSV renderer and add a config entry in the ReportServer.config file to use a tab as a field delimiter.
However, in SSRS 2005, Microsoft clears any whitespace when reading the config file for the CSV renderer, meaning that the tab character does not work.

So I set about researching how to create a tab renderer.

The first article I read stated that you would have to visit Redmond, WA and sit with an SSRS developer to even think about developing a rendering extension. I didn't buy this and kept searching.

There isn't a lot out there on rendering extensions, but I did find several references to the following assemblies:

Microsoft.ReportingServices.Interfaces
Microsoft.ReportingServices.ReportRendering

So with that, I started to develop a prototype solution to the tab rendering problem.

My first decision was to only render tables and matrices. The SSRS CSV renderer tries to render textboxes, which only leads to malformed CSV files. And since most of the time the tab delimited export is going to be loaded into Excel, I decided to just focus on the table and the matrix control.

More to come in the next post...

Last Update Dates

2009-03-06T09:24:00.004-05:00

It's been a while... We are well into deployment and finalization of this version of our ETL and reporting system. Things are going well.

Recently I ran into an interesting ETL problem while using a source system "last update" field. Let me give you some background.

We have an ETL process that reads from a source system that was developed in-house. The queries were all based on the last update field in all of the tables.

While in UAT, several reports were reported as missing rows. After investigating, it appeared that the rows had never made it to the data mart. Needless to say, this was very worrisome.

I researched and tried to find out why these rows were excluded. There seemed to be no pattern, just random rows.

While looking at my morning logs, I noticed something strange. The ETL Last Update table showed times from about 5 hours after the ETL had run. It should have been when the ETL had run.

I looked in the source system, and there were 3 rows that had update dates in the future! 5 hours to be exact!

It turns out that under certain circumstances, the source system was using the wrong date/time to update the last update field. And this date was GMT, so it was 5 hours in the "future" when it was applied to the last update field.

The result of this was missed records on the ETL. It would miss 5 hours worth of updates anytime this occured in the source system.

So my recommendation, which I am now kicking myself for not implementing to begin with, is this: Always use a date range, not just a "Greater than" for last update fields. For example:

Where LastUpdate Between '3/5/09 11:00:00' and GetDate()

And the other rule... Never trust the source system to be accurate 100% of the time. Anticipate issues like this.

Anyway, that's all for now.

peace

PerformancePoint Server 2007

2008-12-18T09:32:00.002-05:00

We have begun the task of implementing PerformancePoint Server 2007 (PPS) on top of our existing data marts. Right now we are in the early stages of the requirements gathering. However, I have had a good chance to play with PPS and it is very slick!

I would highly recommend anyone needing a dashboard application to download the evaluation version and spend some time on it. You can download it here.

You will see some more blogs about it in the very near future because some of the stuff this can do is worth writing about. Also, some of the pitfalls that we have hit are interesting as well!

peace

DateTime Columns in Slowly Changing Dimension Component

2008-12-10T11:40:00.005-05:00

We had an interesting error when implementing a slowly changing dimension in SSIS this week.

We had a date column that we were passing thru to the database from the source. However, the source was a script task because it came in from a multi-resultset stored procedure. In other data flows, where we were using an OLE DB destination, we typed datetime columns as DT_DATE. However, when we used DT_DATE with the slowly changing dimension component, it threw an error:

Error at Import Data [Slowly Changing Dimension]:The input column "input column "ALDATE (15157)" cannot be mapped to external column "external column "ALDATE (15160)" because they have different data types. The Slowly Changing Dimension transformation does not allow mapping between column of different types except for DT_STR and DT_WDTR.

After much digging and research, we determined that we had to set the output column type on the script component to DT_DBTIMESTAMP. Once we changed that, the SCD worked just fine!

peace

Importing CSV Files with SSIS

2008-12-05T11:17:00.003-05:00

One of the shortcomings of the comma separated value (CSV) file import in SSIS is the inability for SSIS to determine if there are too few fields in a record.

According to Microsoft, they only check for the end of record indicator (Ususally a CR/LF) when processing the last column in the file. If you are not on the last column, and a CR/LF is encountered, it is ignored and the first field of the new line is added to the last column of the previous line.

Here is an example of what you see:

SSIS is set up to accept 3 columns in a CSV file: Col1, Col2 and Col3.

File comes in like this:

Col1,Col2
A1,A2
B1,B2
C1,C2

When ssis imports this file it produces this result:
Record 1: A1, A2B1, B2
Record 2: C1,C2 Error - Column Delimiter Not Found

Now, when troubleshooting this error, you can be looking in the wrong place, since the error usually shows up only at the end of the file.

So what I have been doing to gracefully catch and report on this is to quickly pre-process the file to count the columns. Since the processes that create the CSV files for my app are automated, I can assume that every record in the file has the same number of fields, and I only need to count 1 row. If you cannot assume this, it would be just as easy to loop thru each row and determine the field count.

I would also like to point out that my code does not yet handle commas embedded within quote delimited fields. For example, if your record looked like this:

1,"this is um, the field", 1,1

I will be posting code for that next week sometime.

Ok, so here is what I am doing.

I set up a script task in the control flow, before I try to open the file with ssis. This is the code I use:

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

Imports System.IO

Public Class ScriptMain

Public Sub Main()

Dim sr As StreamReader
Dim Textline As String
Dim Fields As String()

sr = File.OpenText("C:\FileName.csv")

If Not sr.EndOfStream Then
sr.ReadLine() 'Skip 1 row (Header)
End If

If Not sr.EndOfStream Then
Textline = sr.ReadLine() 'Read in the entire line of text.
Fields = Textline.Split(Convert.ToChar(",")) ' Split the line of text into a string array.
Else
sr.Close()
sr.Dispose()

Dts.TaskResult = Dts.Results.Failure 'There were no records after the header. This might not be a failure in all implementations.
Exit Sub
End If

sr.Close()
sr.Dispose()

If Fields.Length <> 25 Then ' If there are not 25 fields in the record, raise error.
Dts.TaskResult = Dts.Results.Failure
Err.Raise(vbObjectError + 513, Nothing, "Field Count is Invalid! 25 expected, " + Fields.Length.ToString() + " received.")
Exit Sub
End If

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

When this code runs, it generates an error if the field count is not correct.

If you need to check multiple file layouts, you can put the script task into a foreach container in SSIS and use database lookups to get the field length based on the filename, making this code totally reusable!

Also note... I actually converted this code into a C# dll and put it in the GAC on the SSIS server so all I have to do is call that dll in my script task. I will blog about that soon.

Hope this helps!

peace

Using GUID Data Type in SQL Command Task

2008-12-02T16:27:00.003-05:00

I have now run into this problem several times, so I thought I would write it down.

Just had a colleague come to me with an issue of the SQL Command task not returning the data it should have.

Looking at his stored proc, everything seemed fine. The proc took in a UniqueIdentifier and returned a SELECT from a table.

The SQL Command task in SSIS passed a GUID and put the result set into an object variable.

However, by watching Profiler, we captured that the value being sent to the stored procedure parameter from the SQL Command task was not the GUID that was being processed in SSIS.

So on the parameter mapping tab we changed the data type of the parameter from GUID to VARCHAR(70).

After that the SQL Command returned the rows that were expected.

I hope this helps someone out there

Timeouts in SSRS 2005 Web Service and Report Viewer Control

2008-11-10T09:38:00.002-05:00

One of the questions that I get asked a lot is how to prevent timeouts when running or exporting large reports using the SSRS web service or the .NET report viewer control.

With the report viewer control, this is fairly simple; the problem is that the property is hiding in the property tab. In the property tab, expand the ServerReport property, and set the timeout value to a large number or to -1 (infinite timeout)

For the web services, the ReportExecutionService instance contains the property Timeout.

In my previous post showing how to use the web service, I neglected to add the timeout property.

ReportExecutionService re = new ReportExecutionService();
re.Credentials = System.Net.CredentialCache.DefaultCredentials;
re.Timeout = -1;

Also, when working with the web service in SSRS 2005, you have to keep the IIS timeouts in mind. For example, the session timeout by default is 20 minutes. If you have reports taking longer than that, you would need to increase this setting in IIS. However, if your reports are taking that long, I would consider using SSIS to pre-process the report data on a schedule and then run the report against the pre processed data. That is a much more elegant solution.

peace

WANTED: Your Opinion and Thoughts...

2008-11-04T09:31:00.002-05:00

Ok, So I have been a bit slack in the blog department.

I have been finding it hard to break out of the everyday rut of work to post anything.

Well, that has to change. I need to get out of this rut, to challenge myself, to expand my horizons. After all, the project I am working on is quite interesting, why not share?

So I have been thinking... Is anyone out there interested in a daily or weekly email newsletter for BI? Maybe a forum, etc? User donated articles, etc? Sort of like Code Project, except for BI? A place where I can get everyone involved, rather than just myself.

I would like to hear some feedback, as well as thoughts or ideas.

Meanwhile, I have to get back to work. I have an article on the ForEach SSIS Task almost complete. I will try to post it today.

peace

CERN Powers Up The Large Hadron Collider Overnight

2008-09-09T17:30:00.002-04:00

Well... On Wednesday, at 3:30AM EDT, the Large Hadron Collider will be powered up.

I guess we will know shortly if it will unleash many tiny black holes that will consume the Earth.

Or better yet, a time machine :)

http://www.foxnews.com/story/0,2933,419404,00.html

peace

Look out for black holes...

Determining if a File Exists in SSIS Using Wildcards

2008-09-05T14:43:00.000-04:00

In some instances I have had to deal with files that did not have a set name. In these cases, I have a handy script file to determine if the files are there.

(Another way to handle this is to use the ForEach task and use a wildcard in there, but I am going to blog about that next week)

First, I add a variable to my package. I usually call this variable FileExists and set it as a Boolean, with a default value of False.

I then add a script task to my control flow. In the Read/Write variable property, I add User::FileExists.

In the script editor, I use this script:

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

Public Class ScriptMain

Public Sub Main()

Dim di As DirectoryInfo = New DirectoryInfo("c:\")

Dim fi As FileInfo() = di.GetFiles("*.txt")

If fi.Length > 0 Then
Dts.Variables("User::FileExists").Value = True
Else
Dts.Variables("User::FileExists").Value = False
End If

Dts.TaskResult = Dts.Results.Success
End Sub

End Class

This searches c:\ for a file *.txt. The Length property of FileInfo is the number of files found. If this is greater than zero, I set the FileExists variable to True.

After closing the script editor, and clicking OK on the script task property window, I am ready to set up my precedence constraint.

I then add my next task to the control flow and drag the flow control from the script task to the new task. I right click on the constraint and select edit. I select Expression in the Evaluation Operation drop down box. For the expression I use: @[User::FileExists]==true. This way, the only way the next task is executed is if files exist. If the don’t exists, the package ends gracefully.

You could add another task to log the fact that there were no files, connect the script to that task, and set the expression to: @[User::FileExists]==false.

peace

How to Use the SSIS Script Component as a Data Source

2008-09-02T09:32:00.006-04:00

Recently I had the pleasure of working with a data source that returned all of the transactions with 1 stored procedure. However, this meant the stored procedure returned 14 result sets, and an output parameter.

The question was how to get the 15 result sets into SSIS. The answer: Use a Script Component.

Here is the path I took to handle this:

Making the Connection
All connections in SSIS are handled thru connection managers. We will have to have a connection manager to connect to the source database to execute the stored procedure.

In the Connection Manager tab of SSIS, right click and select New ADO.NET Connection…
Setup your connection properties, click on Test Connection. Once you have a good connection, you are ready for the next step.

Setup a Script Component
I added a script component to my data flow task. When asked if it was a Source, Transformation or a Destination, I selected Source.

I then added a total of 15 outputs. (14 for the result sets, 1 for the output parameter) To do this, I clicked on the Inputs and Outputs tab, and clicked on the Add Output button until I have 15 outputs.

Then came the fun part: adding, naming and typing all of the columns for all of the outputs. On the same Inputs and Outputs tab, I selected the first output, renamed it to the result set name. Then I opened up the output in the tree view, and expanded the Output Columns folder. I clicked on the Add Column button until I had as many columns as the first result set.

Once the columns where in the tree view, I selected the first one, changed the name, set the data type and size, and moved onto the next column, until they were complete.

Then I did the same for each output in the component.

The final step here is to configure the script component to use your newly created connection manager. To do this, click on the Connection tab and add a new connection. Set the name, and then in the middle column, choose your connection manager.

Scripting the Outputs
The next step is to tie together the stored procedure and the script component outputs. To do this, click on the script tab and click the Design Script button to open the scripting window.

I added 2 subroutines to handle opening the connection and executing the stored procedure:

Public Class ScriptMain
Inherits UserComponent

Private connMgr As IDTSConnectionManager90
Private Conn As SqlConnection
Private Cmd As SqlCommand
Private sqlReader As SqlDataReader

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

connMgr = Me.Connections.Connection ‘This is the connection to your connection manager.
Conn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)

End Sub

Public Overrides Sub PreExecute()

Dim cmd As New SqlCommand("Declare @SessionID int; Exec spgTransactions @SessionID OUTPUT; Select @SessionID", Conn)
sqlReader = cmd.ExecuteReader

End Sub

The AcquireConnections subroutine is called by SSIS when it is ready to open the database connections. I override it to make sure the database connection is ready to use.

Likewise, the PreExecute is called when it’s time to get the data. (This should clear up some of the long running PreExecute issues out there.) I open our SQL Reader here and execute the source stored procedure.

Now comes the fun part. Linking the result sets to the output columns.

This is done in the CreateNewOutputRows subroutine:

Public Overrides Sub CreateNewOutputRows()

'Invoice Header
Do While sqlReader.Read
With Me.InvoiceHeaderBuffer
.AddRow()
.InvoiceNumber = sqlReader.GetInt32(0)
.InvoiceDate = sqlReader.GetDate(1)
'etc, etc, etc for all columns.
End With
Loop

sqlReader.NextResult()

'Invoice Detail
Do While sqlReader.Read
With Me.InvoiceDetailBuffer
.AddRow()
...

'more outputs and more columns
' until we get to the last result set which will be the output parameter (SessionID)

sqlReader.NextResult()

'Session ID
'We know this result set has only 1 row
sqlReader.Read
With Me.SessionIDBuffer
.AddRow()
.SessionID = sqlReader.GetInt32(0)
End With

sqlReader.Read 'Clear the read queue

End Sub

This code goes thru each result set in the SQL Reader and assigns the value of the result set to the output column. I did not show all of the columns or all of the outputs since it’s the same concept for each.

Once that is done, I clean up after myself:

Public Overrides Sub PostExecute()

sqlReader.Close()

End Sub

Public Overrides Sub ReleaseConnections()

connMgr.ReleaseConnection(Conn)

End Sub

This closes the SQL Reader and releases the connection to the database.

Once this is done, close the script window and click on OK on the script component properties.

The script component will now have multiple outputs that you can select from when linking it to another data flow component.

Conclusion
I hope this will help you when you need to return multiple result sets from stored procedures into SSIS. If you are familiar with VB.NET coding, you should pick this up easily, and even if not, the example at least gives you the basic steps and something to copy from.

peace

Hug a Developer...

2008-08-29T08:31:00.007-04:00

This would be a lot funnier if it weren't so true...

What are your thoughts?

peace

SSIS 2008 Sets Record

2008-08-28T08:52:00.003-04:00

First of all, hello from the depths of an enterprise ETL project!

I haven't been working on reporting at all in the last few months, dedicated to ETL and optimization.

Second, I realize that this article is from February, but I thought it was a really good read.
http://blogs.msdn.com/sqlperf/archive/2008/02/27/etl-world-record.aspx

peace

Removing Recent Projects in .NET

2008-05-28T10:03:00.003-04:00

I recently had a need to cleanup my "recent project list" in BIDS.

Since you cannot do this from the .NET UI, you will have to resort to messing with the registry.

* Serious problems might occur if you modify the registry incorrectly!!! *
* Modify the registry at your own risk!!! *

That being said... Open up RegEdt32 using the Run command from the start menu.
Navigate to:

HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\8.0\ProjectMRUList

(for .NET 2003, use 7.1 instead of 8.0, for .NET 2002, use 7.0)

Then, from the right hand window, select the values that you do not want, and press delete. The value names are File1, File2, ...

Be aware that you will need to renumber these to be in order from 1 on...
If they are not in exact sequential order, they will not load on the start page. This is not documented on the Microsoft KB article (http://support.microsoft.com/kb/919486)

peace

View of 1999...From the 1960's

2008-03-31T20:28:00.004-04:00

Here is another interesting view of the future from the mid 60's... again, they seem to have gotten the concepts correct, but not the implementation...

And they also didn't see the social norms changing. The wife does the shopping while the husband grimaces and pays for it!

From:
View of 1999 from the '60s

peace

BobP

New Small SSIS Features in 2008

2008-03-31T13:48:00.003-04:00

I have posted on Microsoft connect to request new features and have met with about a 75% success rate.

Among my requests:

Hide a column in a matrix

Problems with autohide tool boxes

Here is a good write up on 2 new features for SSIS that Jamie Thomson requested via Connect and they will be in SSIS 2008:

http://blogs.conchango.com/jamiethomson/archive/2008/03/20/ssis-some-new-small-features-for-katmai.aspx

peace

BobP

A view of 2008... from 1968

2008-03-27T11:44:00.004-04:00

This is an interesting article from "Modern Mechanix" magazine from 1968.

Click here for article

I find it very interesting that missing one small piece of information skews the entire article. For example, he talks about "Not every family has its private computer. Many families reserve time on a city or regional computer to serve their needs." and "TV-telephone shopping is common." He did not see a computer in every home that is connected to every other computer via the internet.

But apart from that, it is a good article, and many of the concepts are a reality today, although not in the same form as he anticipated.

peace

Bob

ReportingService2005 Web Service

2008-01-11T14:37:00.000-05:00

Well, I know it’s been a while, but I am finally back after the holidays!

The next phase of our reporting project is about to take off, and I had a little down time while waiting on requirements.

So, using the ReportingService2005 web service, I created a report deployment application in C#.

It allows me to select the directory of my RDL/RDS files, and then select my report server and any folder on that server.

Then it deploys the selected reports to the report server with the one button click.

I can then change the report server, and deploy the same reports to that server. This is helpful if you have multiple systems that should stay in sync. Using dynamic web service connections allows me to change the target server within code.

Working with the web service was rather straightforward, and allowed me to do everything I needed to do as far as deploying reports and data sources.
However, what I wanted to write about was a catch that had me stumped for a few minutes.

When you use the CreateReport method of the web service, the data sources are not linked to the server data sources. So what I did was use the GetItemDataSources method to get the report data sources, built a new data source in code, representing the server data source, and then used the SetItemDataSources method to update the report data sources.

DataSource[] dataSources = rs.GetItemDataSources(TargetFolder + "/" + ReportName);

foreach (DataSource ds in dataSources)
{
if (ds.Item.GetType() == typeof(InvalidDataSourceReference))
{
string dsName = ds.Name.ToString();

DataSource serverDS = new DataSource();
DataSourceReference serverDSRef = new DataSourceReference();
serverDSRef.Reference = "/Data Sources/" + dsName;
serverDS.Item = serverDSRef;
serverDS.Name = dsName;
DataSource[] serverDataSources = new DataSource[] { serverDS };
rs.SetItemDataSources(TargetFolder + "/" + ReportName, serverDataSources);
}
}

This nicely updated the report data sources to use the server shared data sources.

All in all, the 1½ days I spent writing this app were well worth it. I learned about the deployment methods of the web service, and kept my coding skills up, plus I have a nice deployment utility for reports!

peace

SSIS Derived Column IIF/IF Expression

2007-12-21T16:23:00.000-05:00

This is for my memory, but I figure if I have a hard time remembering, maybe someone else will...

For those of you who are trying to remember how to do IF/IIF functionality in the SSIS Derived Column control:

({comparison} ? {truepart} : {falsepart})

So, if I want to evaluate the similarity from a fuzzy lookup, it would look like this:

(_Similarity > .78 ? ID : "")

Maybe someday Microsoft will standardize all of the expression languages in their BI tools!

peace

Disappearing Parameter Defaults

2007-12-18T13:23:00.000-05:00

I ran into an interesting problem today with default parameter values.
I had a report with 6 parameters. One of them, it happened to be Language, had a default value of 1 in the parameter window. The rest had values from datasets. Once deployed, if you chose a data driven parameter, the language default would disappear and ask you to select a value. This was very inconvenient, as it was a hidden parameter in this report.

After googling and looking in the forums, I realized this is an ongoing issue in SSRS. So I had to come up with a workaround.

I played around with it for a while, and figured out a way to make it work. I created a dataset called DefaultLanguage, with the SQL:

Select 1 as ID

I then set the default for the language parameter to that dataset.

Now when any other parameter is changed, the default language stays selected!

peace

Parameter Prompts Go Prime Time

2007-12-11T11:11:00.000-05:00

I just was reading Teo Lachev's blog and saw this entry: Parameterized Parameter Prompts.

SQL 2008 will allow expressions on the parameter prompts!!! This means that in my article Multi Language Reports, you will be able to update the prompts to different languages. :-D

Check out Teo's other blogs too... He has been working with the new features in SQL2008, something that I hope to be able to get around to soon.

peace

Javascript in Jump To URL

2007-12-05T08:33:00.000-05:00

You know that if you right click on a textbox, select Properties, and then click on the Navigation tab, you can put an URL into the Jump to URL textbox, and when the user clicks on the data in that textbox it will jump to that URL in the same window as the report. But did you know that you can use javascript in there as well?

This allows you to open the link in a new browser window or display a pop up a message box.

I will sometimes use pop up message boxes on the column headers to define what the data definition is. This is the syntax:

="javascript:void alert('This is a test column header alert')"

To open the link in a new window, use syntax similar to this:

="javascript:void window.open('http://servername/reportserver/pages/reportviewer.aspx?%2freportfolder%2fReport+Name&Parameter1=" & Fields!Field1.Value & "&Field2=" & Fields!Field2.Value & "&rs:Command=Render','_blank','resizeable=1,toolbar=0,status=0,menu=0,top=20,left=20,width=740,height=730')"

peace

And now, for something completely different...

2007-11-28T07:44:00.000-05:00

Came across this today and was totally blown away...

Enjoy!

http://www.youtube.com/watch?v=yf56jYDv2fc

peace

More About Multi-Value Parameters

2007-11-27T09:20:00.000-05:00

Just for fun, I am going to talk about multi value parameters again. In this blog, Shortcomings of Multi-Value Parameters, I discuss two of the shortcomings. Now I am going to talk about how to use them in detail.

Overview
Many times you will need to have a parameter that allows the user to select more than one value. In this instance, when you are creating the parameter, check on the Multi-value check box in the Report Parameters window.

Available Values… Or Not!
If you have a set list of items that you want the user to be able to select from, you can establish this list in the Available Values section of the Report Parameters window. You can either use a dataset, or type in static values.

To use a dataset to populate the available value, create the dataset in the data tab, making sure that you assign field names. Then in the Report Parameters window, click on your parameter, click on the From Query radio button in the available values section. Then select your dataset name, value column and label column. The value is what will be returned when referencing @Parameter in your SQL query, and the label is what will be shown in the drop down parameter. You can access both of these values in expressions:

Parameters!Parm1.Value or Parameters!Parm1.Label

You can have a multi-value parameter with or without available values. For example, you may have a parameter for Color, with the available values being red, green and blue. In this case, when you preview the report, you will have a drop down check list, with Select All, Red, Green and Blue listed. Users can click on one or more checkboxes, and select the colors they want to filter on. (If you have SP1, you will not get the Select All check box. The fix for this is SP2.)

If your multi-value parameter does not have any available values assigned to it when it is set up, your users are presented with a drop down multi line text box. The users can enter values, separated by a carriage return, and SSRS automatically formats it for an IN clause in SQL. The great thing about this: Users can copy a column of data from Excel and paste into the textbox! I had a client that worked with loans. They always needed information based on a list of loan numbers that someone had in an Excel spreadsheet. I developed a report that have a multi value parameter, they would copy the loan number column out of the spreadsheet, paste it into the parameter, run the report, export to Excel. It saved them from having to copy, look up, format, etc.

When using a Multi-value parameter, you cannot have a null value returned. This means that the user will have to select or enter at least 1 value from the parameter list. When setting up the parameter, you will get an error if you try to select the Allow null value check box. If there are no available values, the user must enter at least 1 value into the textbox.

Using in SQL
If you are going to be passing a multi-value parameter to a stored procedure, you will have to pass it as 1 string, and parse it in the stored procedure. See the blog referenced above for more information about this.

However, to use your multi-value parameter in a dataset SQL statement, just use the IN clause:

Where ItemID in (@Item)

SSRS will automatically add the commas and format it for you.

There is no way for you to tell if all of the values have been selected, or if the Select All was clicked on. This is an issue if you have a lot of values because the IN clause is worthless, but SQL still executes it, cost precious time and resources. If you expect to have a long list of values, or if you think that all of them will be selected most of the time, there is a workaround documented in the blog referenced at the top of this article.

Using in Expressions
To determine how many values have been selected or entered, use the Count property:

=Parameters!Parm1.Count

To reference a value directly, use:

=Parameters!Parm1.Value(arrayindex)

where the array index is less than the count.
If you want to display the list of values selected/entered, use the Join function:

=Join(Parameters!Parm1.Label, “, “)

The second argument is the delimiter; what character string is displayed between each parameter entry. You could also reference the parameter value.

Using in Code
You can reference multi-value parameters in embedded code as well. See my article about multi language report labels for a look at how that is used.

Passing in URL
To pass the multi-value parameter values in an URL to report manager, repeat the parameter name. For example:

http://server/reportserver?/dir/Report&rs:Command=Render&Parm1=VALUE1&Parm1=VALUE2&Parm1=VALUE3

That’s all for now. I think this gives a lot of information. Hope it helps!

peace

Dynamic Images in Reports (Slight Return)

2007-11-21T12:34:00.000-05:00

Since dynamic images in reports are by far the most read topic of my blog, I am going to add some more information about them.

The last post that I did about dynamic images concentrated on adding logos to the header, etc. This time I am going to talk more about adding images in the report body. Some of this may be redundant, but it may also give you some ideas for future reports.

I am going to discuss adding an image preview/click thru example.

Since you can read the URL from the database in the main dataset, you can put an image in every row of a table. On that control, you can set the sizing property to fit or fit proportional, and get a thumbnail image. You can then add a Jump to URL expression to have the user be able to click on the thumbnail and display the full size in a new browser window.

="javascript:void window.open('" & Fields!ImageURL.Value & "','_blank','resizeable=1,toolbar=0,status=0,menu=0,top=20,left=20,width=740,height=730')"

Right click on the image control and select Properties. Then click on the Navigation tab and click on the Jump to URL radio button. Paste the above code into the expression text box. You can change the size, or leave out the width and height properties.

This works great if the images are stored on a web server. (In my humble opinion, images should not be stored in the database, but that is a discussion for a different day. Feel free to comment on this!) But what if the images are stored in the database?

Well, you would have to create another report, adding a lone image control, and maybe a caption text box. Add a parameter so that you can pass an image ID from the first report to this report.
Create the dataset, using the parameter as a filter, to select the Image field from the database.

Select ImageField, Caption from Images Where ImageID = @ImageID

When you add the image control to the report, select the dataset and field name, and put in the mime type. Put the caption field in the caption text box.

Back in the original report, right click on the image control and select Properties. Click on the Navigation tab and select Jump To Report. Select the new report that you created. Click on the parameters button and assign the Image ID parameter with the image ID read from the database in the dataset.

The only drawback with doing it this way is that it does not open in a new window.

To open a report in a new window, use the Jump to URL select again, and then put this in the expression box:

="javascript:void window.open('http://servername/reportserver/pages/reportviewer.aspx?%2fImageView&ImageID=" & Fields!ImageID.Value & "&rs:Command=Render','_blank','resizeable=1,toolbar=0,status=0,menu=0,top=20,left=20,width=740,height=730')"

Now it will open the image report in a new browser window.

If you would like more information on dynamic images, or if I can explain something a little clearer, post a comment or drop me an email.

peace

Data based, Row Level Security in Reporting

2007-11-16T15:46:00.000-05:00

Overview

Last month I promised an article on data based security. Well, after I ignored it for a month, someone asked for it. So here is what I am currently implementing in a project for a client.

I import several transactional systems into a data mart of my own design. It is a star schema, and utilizes 4 fact tables and about 20 dimensions. One of the dimensions handles the primary focus of our reports. Let’s call it Orders. Each individual order is assigned an OrderKey that ties all of the fact tables together.

To implement security, I assign OrderKeys to report user groups. The assignment is based on rules setup thru a front end .NET web application, and can be used to filter orders to users based on any field in the data mart. Then the report SQL checks to make sure that every order is in that security table for the user pulling the report.

The Details

I have setup a data dictionary containing all of the metadata that I need to build a query for the fields. The admin application displays the fields to the admin user, and the admin user selects a field, an operator (=, <, >, <>, Like, etc) and then enters a value.

So for instance, the admin user would select the CostCenter field, select the = operator, and then type in 1234. Stored in the database is the line: CostCenter = 1234.

Once the admin user is done, they select a menu item to “build” the rules. Using the data dictionary, the application builds SQL statements to filter the OrderKeys. The statements look like this:

Insert into Security
Select Distinct a1.*
From
(Select 1 as ProfileID, f.OrderKey as KeyVal
From factOrderLine f (NOLOCK)
inner join dimProduct d (NOLOCK)
on f.ProductKey = d.ProductKey

Where d.ProductID <> '555666') a1
Inner Join
(Select 1 as ProfileID, f.OrderKey as KeyVal
From factOrderLine f (NOLOCK)
inner join dimMFG d (NOLOCK)
on f.MFGKey = d.MFGKey

Where d.MFGID = 'PG') a2
On a1.KeyVal = a2.KeyVal

Because I am doing inner joins, this acts as an AND clause. So in the above query, I would get all orders that have a product ID not equal to 555666 and everything where the manufacturer ID is PG.

The resultant list is a profile ID, and then order key itself.

I put this into the Security table.

When I code the SQL in the report, I add an EXISTS statement to the WHERE clause:

and OrderKey in (Select OrderKey from Security where ProfileID = @ProfileID)

The @ProfileID parameter is being passed in by the app calling the reports. It is set when the user logs into the web front end.

That in a nutshell is how I do data based row level security.

This is a huge topic, and I have only scratched the surface in this blog. As I work on it some more, and add new features, I will blog them as well.

In the meantime, if you have any questions or suggestions, please email me!

peace

Report Layout Basics

2007-11-15T11:09:00.000-05:00

I thought I would go over some of the basics of report layouts since I have seen a few forum posts about basic layouts. This is by no means a comprehensive overview, but a starting place.

The first thing to realize about the report layout is that the only sections you have to work with are the page header, page footer and the report body. There is no concept of a report header/footer, etc. But also, unlike Crystal Reports, the report sections are NOT tied to data.

Instead, the report body holds the controls that can act as report sections. Here are some ideas:

Report Header

If you need a report header, you can place any control, like a text box, on the body of the report, and populate it with data, static text, etc. If you place these controls inside a rectangle, you can set the PageBreakAtEnd property to true, and then those text boxes will appear on a page by themselves. If you dont use a rectangle in this manner, your report header will be on the same page as the start of your data area.

Report Footer

As with the report header, if you place controls at the bottom of the body section, in a rectangle, and set the PageBreakAtStart to true, you will have these on the last page of the report.

Multiple Body Sections

You can have multiple body sections just by adding multiple controls to the body section of the report. Say that you have 2 datasets in your report and you want to show them in separate sections of the report. Add a table for the first dataset. Set the PageBreakAtEnd property to true.
Then add another table below that for the second dataset. When you render the report, you will have the first table displayed, a page break, and then the second table. Add the report header and footer from above, and you now have a 4 section report!

Using the List Control

The list control is used to repeat a set of controls within it. You can place a list control on your report, set the dataset and the grouping on the list. Then place some text boxes in the list control and populate them with the fields from the dataset. The list will group the data as you set up, and populate the text boxes, repeating for every group.

You can also put multiple tables in the list control. I will do this often when there is a lot of detail data to display, and I do not want to have to worry about the column layouts.

Well, that was some basic layout ideas. I will add more soon

peace

BobP

Rendering SSRS 2005 Reports with the Web Service

2007-11-07T08:57:00.000-05:00

Even though Report Manager is a decent way to deliver reports to your users, you may have the requirement to embed the report in an existing .NET application. This is very simple using the web services provided with SSRS 2005.

Here is a code sample that I use to show developers at my clients how to pull a report.

string historyID = null;
string deviceInfo = null;
string format = "Excel"; //Can be XML, NULL, CSV, IMAGE, PDF, HTML4.0, HTML3.2, MHTML, EXCEL, and HTMLOWC
Byte[] results;
string encoding = String.Empty;
string mimeType = String.Empty;
string extension = String.Empty;
ReportingExec.Warning[] warnings = null;
string[] streamIDs = null;

ReportExecutionService re = new ReportExecutionService();
re.Credentials = System.Net.CredentialCache.DefaultCredentials;

ExecutionInfo ei = re.LoadReport("/directory/ReportName”, historyID);

ReportingExec.ParameterValue[] rptParameters = new ReportingExec.ParameterValue[1];

rptParameters[0] = new ReportingExec.ParameterValue();
rptParameters[0].Name = "DateFormatID";
rptParameters[0].Value = "fr-FR";

re.SetExecutionParameters(rptParameters, "en-us");

results = re.Render(format, deviceInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);

FileStream stream = File.Create("C:\\report.xls", results.Length);
stream.Write(results, 0, results.Length);
stream.Close();

You will need to set up the reference in your project. The URL for this is: http://servername/reportserver/ReportService2005.asmx

This code, which I give to the developers in a web app, demonstrates the basic use of the web services. It loads a report, adds a parameter value, and then renders the report and saves it to disk. If you wanted to render it back to the user on a web page, you would add:

Response.AddHeader("content-disposition", "attachment; filename=File.xls");
Response.OutputStream.Write(results, 0, results.Length);

Response.End();

By taking this code a little further, you could easily send out an email with the report attached. Or another commonly requested feature for SSRS: emailing multiple reports on 1 email.

You could loop thru a database table, reading the reports to send a user, render that report, save the file, attach it to a system.mail email, and then move to the next report. When the looping for that user is complete, send the email, delete the files, and move on to the next user in the table.

I hope this helps clear up web services in SSRS 2005. Most developers that I work with are not familiar with this web service, and this always gets them going in the right direction. This is by far a VERY basic demonstration. There is a lot more to the web services than just this.

For more reading on the web services, visit:
http://msdn2.microsoft.com/en-us/library/ms155076.aspx

peace

BobP

Creating a Top N Report with ‘Others’

2007-11-03T11:28:00.000-04:00

No, this is not a blog about “Lost” ;-)

Often times I have been asked to create a Top N report. This is relatively easy, as you can just add a TOP N to the SQL statement, and display the results in the detail of your SSRS table.

However, once in a while I get a request for a Top N report, but include 1 line with a total of the “Others”

After a lot of trial and error, some internet searching, and discussion with other SSRS folks, this is what I came up with and what I use every time now.

First, in the SQL statement I rank my results, and then group that ranking into 2 groups. The 2 groups represent Others and Non-Others.

Here is a sample SQL:

SELECT
Row_Number() Over (Order by Sum(TBL.Number) desc) as Rank,
Case
When Row_Number() Over (Order by Sum(TBL.Number) desc) <= Convert(int,10)
Then 1
Else 2
End as GroupNumber,
Description,
Number
...

Then I put a table in my report, and add a group with the GroupNumber field as the grouping field.

I remove the group footer and put an expression on the Hidden property of the entire row:

=iif(Fields!GroupNumber.Value = 1, true, false)

This hides the row if the GroupNumber = 1 (non others)

I then put the string “Others” into a column, and the sum of the number field into another column.

In the details row, I add an expression to the Hidden property:

=iif(Fields!GroupNumber.Value = 1, false, true)

This hides the detail row if the GroupNumber = 2 (Others)

I then put the description field into the column, and the number field into the other column.

When you run the report, this is what you wind up seeing:

City A 100 <-- This is a detail row
City B 90 <-- This is a detail row
City C 80 <-- This is a detail row
Others 230 <-- This is the group header row.

peace

BobP

Deploying SSIS Packages

2007-11-02T08:24:00.000-04:00

Today I am going to talk about easy SSIS package deployment.

I generally am working with multiple data marts, and separate my projects by data mart. However, if I have the same package name in 2 projects, and do a default deploy from SSIS, those files overwrite each other.

You can create subfolders under MSDB in SQL Management Studio when logged into the SSIS instance, however, you have to manually deploy to these sub folders.

Because I am lazy, I wrote a quick batch file to move a project from a development machine to the SSIS machine. The following batch file takes 1 argument, Servername, and copies all of the files from a project to that server. The project path is hard coded in the batch file, so if you want to deploy multiple SSIS projects, just copy the batch file and change the path.

@Echo Off

Echo.
Echo.
Echo Test ETL SSIS Package Installation Script
Echo Written by BobP
Echo 7/6/07
Echo.

if %1a == a goto Error

Echo.
Echo.
Echo -----------------------------------------------------
Echo --This will delete any Test ETL files
Echo --on the server, and reinstall from the local machine
Echo -----------------------------------------------------
Pause

REM Remove Existing files and directory on Server
for %%f in (C:\Projects\TestETL\TestETL\bin\*.dtsx) do dtutil /Q /SourceS %1 /SQL "\TestETL\\"%%~nf /Del
dtutil /Q /SourceS %1 /FDe "SQL;\;TestETL"

Echo.
Echo Preparing to create folder
Echo.
pause

REM Create the Directory
dtutil /Q /SourceS %1 /FC "SQL;\;TestETL"
if errorlevel 1 goto End
Echo.
Echo Preparing to Copy Files to Server
Echo.
pause

REM copy the all project packages to the server
for %%f in (C:\Projects\TestETL\TestETL\bin\*.dtsx) do dtutil /Q /DestS %1 /Fi %%f /C "SQL;\TestETL\\"%%~nf

Echo.
Echo.
Echo Installation Complete!
Echo.
Echo.
Pause
Goto End

:Error
Echo.
Echo.
Echo Missing Servername!
Echo Syntax: Deploy [targetservername]
Echo.
Echo.

Pause

:End

I have several copies of this, one for each of my projects, on my desktop. When I want to deploy, all I have to do is double click.

peace

BobP

Minimum Column Size for Interactive Sort

2007-11-01T10:43:00.000-04:00

I have found out that in order to display the interactive sort button in SSRS, your table column must be at least 0.375in wide. If it's any smaller, the button will not display.

peace

BobP

Dynamic images in reports

2007-10-30T12:41:00.001-04:00

Another question that comes up a lot is how to dynamically change the images in a report.
I do this with client logos, and even the company logos that I put into report. Companies change their logos quite frequently, and you don’t want to update hundreds of reports when the logo changes.

What I usually do is to put the logo out on a web server that is accessible to the report server. Then put the URL in a database table. This can be a static "config" table, or it could be your client table. (I use a table called Config, with 2 columns, ItemType, Item. I have a proc called GetConfigItem @ItemType. I pass in the item type and get back the item.)

Then I create a dataset and a parameter for each logo path that I need. The parameter is populated from the dataset.

Add an image control to your report (since you will populate the URL from a parameter, you can put the image control in the header/footer as well as the body)

Set the source to External, and the Value property to an expression: =Parameters!LogoPath.Value
If you set the image control to Fit Proportional, the logo should resize automatically.

Now if you want to change the logo, you can place the new logo out on the web server, change the URL in the database, and all reports are updated!

peace

Multi Language Reports

2007-10-30T10:03:00.000-04:00

Well, I promised to write about handling multiple languages in SSRS so I thought I would at least get started on that. Judging from the visitor stats, that seems to be what a lot of people are looking for.

As you may already be aware, SSRS does not inherently support multiple languages. And what I am about to demonstrate does not apply to the data, just the labels and text that are pre-populated on a report.

What I do is use database driven report text. I populate a parameter with the label text, and then use code to retrieve the label. Since a parameter is nothing more than a key/value list, this works quite well.

First, I create a master Label table. This will be the list of labels that I will have available to me. I have another table called Language. This table obviously holds my list of available languages. And I have a third table called LabelText. This table holds the actual text in the different languages that will be placed on the report.

Here is the script:

CREATE TABLE [dbo].[Label](
[LabelID] [int] IDENTITY(1,1) NOT NULL,
[Description] [varchar](900) NOT NULL)

CREATE TABLE [dbo].[Language](
[LanguageID] [int] IDENTITY(1,1) NOT NULL,
[Language] [varchar](100) NOT NULL,
[Active] [bit] NOT NULL)

CREATE TABLE [dbo].[LabelText](
[LanguageID] [int] NOT NULL,
[LabelID] [int] NOT NULL,
[LabelText] [nvarchar](max) NOT NULL)

I set the LabelText up as NVARCHAR to support Unicode character sets.

Then I populate the Label table with all of the labels that I will need on the reports. Here is a short example:

71 % Total
75 Account
120 Address 1
121 Address 2
122 Address 3

I also populate the Language table with the languages that will be available:

1 English 1
2 Spanish 0

The LabelText table really does all of the work. That contains the label text in the language of choice.

I am fortunate enough to have a very good background as a software developer, so I created a web page to populate these two tables and the LabelText table. I let the business unit populate this table since my grasp of other languages is not the best.

Once those are set up and populated, it’s time to turn to the report design.

Create a dataset called Languages. Use it to retrieve the rows in the Language table.

Select LanguageID,
Language
From Language
Where Active = 1

Since populating all of the labels in a new language may take some time, we only want to load languages that are set to Active. This gives the business unit time to load the text, without giving the user the ability to select that language on a report.

Create a parameter called Language. Populate it with the values from the Languages dataset. Pick a default if desired.

Now create a dataset called Labels. This dataset is going to pull back the rows from label text for the selected language:

Select LabelID,
LabelText
From LabelText (NOLOCK)
Where LanguageID = @Language

union

Select LabelID,
LabelText
from LabelText lt (NOLOCK)
Where LanguageID = 1
and Not Exists ( Select 1
From LabelText z (NOLOCK)
Where LanguageID = @LanguageID
and z.LabelID = lt.LabelID)

This SQL pulls back all of the labels for the language selected, and then, for any labels missing in that language, unions the labels in language 1 (In my case, ID 1 is English)
This way you always have a label returned.

Go back to the report parameter screen and create a new parameter called Labels. This parameter should be internal, multi-value, and an integer.

For the available values, populate from the Labels dataset, and do the same for the default values. This gives us a parameter filled with the label ID and text, with all of them selected by default. Since inside a report, you can only “see” selected parameter values, and not the populated values, this is an important step.

In the report properties window, under the code tab, add code similar to this to loop through the selected values in the Labels parameter, and return the text for the ID passed in. Since this parameter is loaded with the translated version, this is what will populate your labels.

Public Function GetLabel(Parm as Parameter, LabelID as Integer, Optional AddColon as Boolean=False) as String

Dim t as Integer

For t = 0 to Ubound(Parm.Value)
If (Parm.Value(t) = LabelID) Then
if AddColon = true then
Return Parm.Label(t) & ":"
else
Return Parm.Label(t)
end if
End if
Next t

Return ""

End Function

Notice the AddColon parameter. I put this in because I have a lot of fields in my reports that are setup like a data entry form: Label: Value. Since I didn’t want to type +”:” in every textbox, I just added it to the routine. You can add as much custom code in here as you want. This will make your job easier in the long run.

The last step is to use your label table as a lookup (I usually load it into Excel and sort it alphabetically) and add this formula to every label textbox:

=Code.GetLabel(Parameters!Labels, 105)

And if you wanted the colon:

=Code.GetLabel(Parameters!Labels,2, True)

And that’s it! Now you have multi-language report labels. On the downside, it makes the report harder to maintain, but if the requirement is multi-language, this works very well.

Keep in mind; this will not work on parameter labels. I have not yet found a way to translate these. Probably the only way to do it is to have a custom front end, and create your own parameter display using a combination of your own code and the SSRS web services.

Many thanks to Brian Welcker for the initial idea of using a parameter for the labels. (http://blogs.msdn.com/bwelcker/archive/2007/07/11/laser-guided-missiles-report-localization-through-parameters.aspx)
I was orignally calling a C# assembly to populate each label from the database. That generated way too much database activity.

peace

And I thought I had too much time on my hands...

2007-10-26T23:07:00.000-04:00

Ok, so I was perusing the internet and came across this:

http://www.geekologie.com/2007/10/japanese_lady_produces_unique.php

I really am speechless... Laughing too hard...

Peace

XML/RDL Editor

2007-10-26T16:31:00.000-04:00

I am about 60% done with a project to help me edit RDL files. For those of you unfamiliar, these are the XML files in SSRS that control everything about the report.

My goal in this project is to be able to drag and drop nodes between 2 list views, delete nodes, copy/paste nodes, etc.

I am doing this because I have a need to copy standard parts of reports between almost 100 reports now. And that number is only going to grow in the future.

My question: is there already a tool out there that will do this? I searched before developing, but did not find a decent one.

And, if I did finish this, and thought about distributing it, would anyone else out there use it?

On another subject, last Saturday, my project manager’s wife was hit and killed by a drunk driver. She left behind Jim and their 3 sons. (http://voluntaryredneck.mu.nu/archives/244552.php) Please keep them in your thoughts and prayers.

Peace

BobP

Data Based Security in Reporting

2007-10-22T15:06:00.000-04:00

I sometimes wonder if anyone actually reads these blogs :-P
Well, here is my chance to find out.

I am going to ask for feedback.

In my position as a Business Intelligence Consultant, I have been asked before how to implement data based security (DBS). By DBS I mean the ability for an admin user to restrict a row of data from a user based on a value in the database.

For example, I, as an end user of a report, can only see transactions that occurred within my region: Georgia; whereas my peer across the country can only see transactions in Colorado.

I could add a where clause to every report, but what if the admin wants the ability to filter by ANY field in the database.

I have a solution that works quite well, but I am interested to know if anyone else has run into this request, and how they have solved it.

I am going to post my solution later on this week.

Peace

Formatting Zero Values

2007-10-19T09:49:00.000-04:00

This entry is more for my memory than anything else, but I am sure someone else out there can use it.

Did you know that you can custom format a zero value in a report?

From what I have seen in the MSDN forums, not too many people do. Most people, and myself included, first set up a visibility formula like this:
=iif(Fields!Data.Value = 0, true, false) <-- Sets hidden to true if value is zero.

And that does work great, unless you have any kind of formatting or borders in your cell. Then it hides them too.

The better workaround is to use the Format property on the cell and use a custom format string.
The custom format string has 3 parts separated by semicolons:
Positive value format; Negative value format; Zero format

So if you wanted to hide the zeros in a decimal number cell, the format string would look like:
#,###.##;-#,###.##;””

You could also use that to further document the zero value:
#,##0.0;(#,##0.0);”Zero”

The only limitation that I have found, and I am still looking for a workaround is if you are writing a multi-locale report. If you change the Language property, the number formatting will not change.

I will post an update once I find a way to do this.

Till then…

Peace

2 Shortcomings of Multi Valued Parameters in SSRS

2007-10-18T08:36:00.000-04:00

Wow. What a terrific morning!

We are getting some rain that is so desperately needed (They say the reservoir that feeds Atlanta will dry up in 90 days if we don’t get some substantial rain soon. http://abcnews.go.com/GMA/story?id=3730145&page=1), I had an awesome rehearsal/jam with my band last night, and our production servers are ready to go online to be configured!

So this morning I am going to discuss 2 shortcomings of the multi-valued parameter (MvP) without getting irritated.

The first shortcoming, and in my opinion the most important, is the inability to pass a MvP to a stored procedure with out having to parse it out in the procedure. (See this blog for more information http://otechnology.wordpress.com/2007/04/02/processing-csv-strings-t-sql/ )
I would think that since Microsoft was writing these 2 products together, they could have done a little collaboration and figured out a way to pass this. So far, I have not seen anything showing that this is being fixed in 2008, but I may have missed something. There is an entry in Connect about this, but there has been no activity (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=305511 )

For a workaround on this, I just put the SQL statement in the report. I know that forces a compile every time the report is run, however the processing time to parse up the parameters is much more than the compile.

The second shortcoming is the Select All. I know that in SP1 some developer removed the Select All check box from MvPs and the entire SSRS community replied with a unified roar. I was one of them. So in SP2, the Select All was returned with a collective sigh of relief. BUT there is still one thing missing from the Select All. The ability to handle the All in the SQL query without listing out all in an IN clause. For example, if I have a list of airports of the world in a MvP, my Where clause looks like this: Where AirportCode in (@AirportCode)

Now, if the user selects Atlanta, the SQL becomes: Where AirportCode in (‘ATL’)
However, when the user chooses Select All, the SQL is a concatenation of every value in the MvP. So even though we know that there is no need to even filter on that parameter, the SQL is formed in a way that causes the query to slow way down, even on an indexed field.

Well, here is my workaround for that. And while it is NOT elegant, it is effective.

Suppose you have 1 MvP called Contacts.

I set up a 2nd MvP called AllContacts. I populate it with the same dataset as Contacts, and then set the default values to the same dataset as well, effectively selecting all by default. I make this parameter internal.

I then setup a third parameter, type of integer, names AllContactsSelected. I also make this internal. It is NOT multi value and there is nothing in the available values. For the default value I use this expression: =iif(Parameters!Contacts.Count = Parameters!AllContacts.Count,true,false)

In my SQL where clause I add this:
Where (@AllContactsSelected = 1 or Contact in (@Contacts))

Make sure that you put it in that order, otherwise it will try to evaluate the IN clause. This way it will see that @AllContactsSelected = 1 and not even execute the IN.

Well, that’s all for now. I sure would like to see these things fixed in SQL Server 2008, but I am not expecting it.

For now, I will just go enjoy the rain…

Peace

Is the business view of IT changing?

2007-10-17T07:52:00.000-04:00

Today, I am going to stray from SSRS again and discuss business.

In my normal batch of morning email, I came across a link to an article
(http://www.networkworld.com/news/2007/101607-noncertified-it-pros-better-paid.html) with the headline being: Non-certified IT pros earn more than certified counterparts: survey.

Being a non-certified professional, I was really happy to see that. But after reading it, I realized that the headline was not the only point to the article. It was just the one that would catch people’s eye. For me, the interesting message is that companies are tired of “techies.” They really want business people that can do a technical job.

I have been working with business units for most of my career. My first true technology job offer was: “I can’t get any information out of IT; I want you to work for me, the CFO, and provide me with the reports that I need and can understand.” I understand from the business side how frustrating it is when you ask for a report, and a developer responds with technical jargon about primary keys and cross server queries. The average business person glazes over when they hear the first technical term.

Don’t misunderstand me, I am a technical person. I am a geek. I love the inner workings of almost anything, not just software or gadgets. But first and foremost, I am a business person. I started my first business at age 7, selling lemonade. I made almost $10 in one day! (And I did it without a PC, without a large un-wieldy database application that took 2 years to implement and never worked right, without a tech-head telling me that I shouldn’t even be in business because my network platform was not on the cutting edge.) I have started and run several successful businesses outside of the technology realm, and a couple within. I have been in management, and in the ranks. I have seen a lot from the business side.

So it was refreshing for me to read that more business people want this IT re-alignment. But, I have been hearing this for almost 20 years. Are things really starting to change?

One can hope.

Peace

Passing SSRS Report Parameters in an URL

2007-10-15T14:25:00.000-04:00

There is a lot of discussion on the forums about passing parameters to an SSRS report using an URL however one of the items that you rarely read about is the passing of multi valued parameters in an URL.

To do this, just pass the name/value pair, separated by the ampersand.

For Example: http://server/reportserver?/dir/Report&rs:Command=Render&Parm1=VALUE1&Parm1=VALUE2&Parm1=VALUE3

As long as the parameter Parm1 is set to multi value, and it contains those three values (VALUE1, VALUE2, VALUE3) all three will be selected.

The only exception to this rule is if one of the name/value pairs is a null (Parm1:IsNull=True)
That causes SSRS to ignore the rest of the values passed.

Remember to pass the Value, and not the Label.

Peace

Multi-Language Tips in SSRS

2007-10-11T10:48:00.000-04:00

In looking through the forums at Microsoft, I often see questions pertaining to the formats of dates, number and currency in a report.

In my latest gig with a multinational travel management company, I have had a lot of exposure to the multi-language/culture formatting.

I will cover multiple languages in a future blog, and today focus on the formatting of dates, numbers, and currency values.

To start with, Microsoft has a good page explaining each of the terms used by them to customize the user interface to the user’s regional setting:

http://www.microsoft.com/globaldev/getwr/steps/wrg_lclmdl.mspx

SSRS handles formatting the display of dates, numbers and currency by adding a property to a report and textbox: Language. This is misleading since it really does not change the language except on the verbose date formats. For example, changing a number from the US format: 1,234.56 to a French format: 1 234,56 really isn’t a language change, just a locale change, according to the link above.

The language property displays the locale description, and behind that, the locale code.
For example, the property displays English (United States), and the value behind that is en-US.

I found a good list of these locale codes here:

http://msdn2.microsoft.com/en-us/library/system.globalization.cultureinfo.aspx

I put these into a database table, so that I can use them as a parameter in my reports.

In the reports that I create, I have a dataset called Locales in which I call a stored procedure that returns the locale code and the description, sorted by description.

I then create a parameter called Locale. This is a String parameter that is setup to read available values from the Locales dataset, with the code as the value and the description as the label.

Depending on the report target audience, I set a non-queried default value, usually en-US.

In the report properties window (NOT the properties that show up when you select the menu items: Report, Properties. You have to click on the outlying yellowish area of the report designer and press F4), I set the language as an expression: =Parameters!Locale.Value

Now you should format all of the textboxes that contain dates, numbers and currencies.

You should use the standard formatting strings rather than hardcoding a format into the format property. For example, for a short date format, use d rather than d/M/yy, as this will always format the date d/M/yy no matter what you select for the language property.

Here are some standard formatting codes:

Date/Time: http://msdn2.microsoft.com/en-us/library/az4se3k1(VS.71).aspx
Numeric: http://msdn2.microsoft.com/en-us/library/dwhawy9k(VS.71).aspx

Note that some of the formatting strings are “InvariantCulture.” This means that they will not be affected by the language property.

NOTE: When changing locales on currency fields, all this does is change the formatting of the displayed number. It does NOT do any currency conversion. For this reason, I usually do not format currencies as currency, but as a N2 (Numeric, 2 decimal places) and then display the ISO currency code (USD, EUR, JPY, etc) to indicate which currency is being displayed. I also use a separate parameter to select the currency based on this ISO code. Since the ISO currency codes and the Microsoft locale list do not tie in any way, it makes it a lot more difficult to use the language properties to format currency changes.

The way parameters are displayed is controlled by the browser regional settings. There is no way in reporting services to control that. However, the regional settings on a browser should be set to the way the user likes to see the dates, numbers, etc, so you should not have to worry about this setting.

Well, that wraps up this tip. I hope that someone out there benefits from this. I have done a lot of research, as well as a lot of work on my own, to put this together. I use this extensively on existing reports and it works very well.

Let me know what you think!

Peace…

3 Lessons on Deadlines and Business Requirements

2007-10-10T08:11:00.000-04:00

Well, here I am again, back after a "Got to have it now, pull out all the stops" project has been completed.

Let me give you a quick overview:

I had a client come to me on Sept 27th with an urgent request:

"We have a new system going into beta with several very important clients and we need a quick data mart and 2 reports. And we need them by next Tuesday!"

So with a fair amount of skepticism, I accepted the task. After all, this was a client that I have been dealing with for a while, and it was only 2 reports.

So, using SSIS and SQL 2k5, I put together the data mart (I had already been working on some data marts for this client, so I was familiar with their business and data model) and started looking in depth at the reports.

OOOOOPS!! Guess I should have done that first. By looking at the reports I could tell that the users had asked for some reports, the deadline aware project manager talked to the developers, who told the client that they only had time for 2 reports, not the 33 the users originally needed.

This was not 2 reports. It was "let's take all 33 reports, combine all of the parameters and fields and put them on 1 report, and then add 1 because we are allowed to."

So, I naturally did what any decent consultant would do. I balked. Not in a bad way, not rude or condescending, just factual:

While this 1 master report will work, is it really fulfilling the users needs? Do they need 28 parameters in the parameter bar in the SSRS report manager? It takes up 80% of the maximized browser window. How do they view the report? Also, the report is very slow, because you are joining not only fact tables and dimensions, but also different atomic level fact tables. Let's talk to the users to see what they really need.

So we did. And in doing so, we found out that they had indeed been told that they could only have 2 reports. So:

Lesson #1: If your users tell you they need X functionality, and you restrict them to only Y number of items, they WILL find a way to get X into the Y.

In talking with the user, we also found out that next Tuesday was NOT a hard fast deadline.

Lesson #2: Given the choice between crap in 2 days and gold in 2 weeks, most users will take the gold.

By giving the users my recommendations, they came to appreciate that someone was looking out for their best interests. They felt comfortable telling me their goals, their needs, as it pertains to these reports. I built up a relationship with these people. In turn, I made sure to try and understand what they needed, and provide it for them.

Lesson #3: Users are just people trying to get their job done. Respect that.

And they will get it done with or without your software, reports, data, etc. It's your job to make theirs easier, and to contribute to the well being of the company as a whole.

These seem to be pretty basic lessons, right? However, as a consultant, I see these same mistakes being made everywhere. And yet it surprises me every time. We, as an Information Technology industry, should never let these lessons slip from our minds. They are fundamental to our jobs, and to our industry as a whole.

In summary, the users defined 4 reports that would meet their immediate needs, and I was able to produce those reports for the users in a couple of days, and everyone is happy. Sounds like a cheesy movie, huh?

Peace

FUN with SSIS Script Tasks and 64bit deployment

2007-09-26T14:45:00.000-04:00

I am developing my SSIS packages on my 32 bit, Windows XP Pro SP2 laptop. Everything works fine. My project has a parent package and 26 child packages.

In the child packages, I have a script task that sets the version number into a variable. Nothing big, 1 statement:

Dts.Variables.Item("User::Version").Value = Dts.Variables.Item("System::VersionMajor").Value.ToString() & _
"." & Dts.Variables.Item("System::VersionMinor").Value.ToString() & _
"." & Dts.Variables.Item("System::VersionBuild").Value.ToString()

So I created a "template" package that had my basic tasks in it, and started each child package from this template.

Everything worked great on my laptop.

Deployed to our 64-bit production platform and I get the following error when running the package:

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).

Naturally I follow the link in the error, after cleaning up the water that I spewed from the surprise I got from a very detailed error message in SSIS.

It did not apply to me... I had SQL Server SP2 installed. I even re-installed after reading it, just to make sure. Did not fix it.

Found this forum thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=591709&SiteID=1

Read thru it... Had NO script tasks set to False for pre-compile.

After looking thru my packages, confirming Precompile was set to True, I had an epiphany...

Since I started from a template, I bet the script tasks were NOT being compiled, since I never opened them.

So I went thru all 27 packages, opening up each script task (Control Flow) AND component (Data Flow), and then the script window. Closed the script window, and pressed ok on the script task window.

Re-built and deployed... tested OK!

So, if you use a "template" package with a script task or component, make sure you open the actual script screen and close it again to force a re-compile.

BobP

Welcome to my SSRS blog!

2007-09-20T13:47:00.000-04:00

Hello!

Welcome to my SSRS (and sometimes other subjects) blog.

I have been working with Microsoft Reporting Services since mid 2003, and the SQL Server 2005 BI tools since mid 2005.
I am constantly coming up with work-arounds, and never think to share that knowledge.

Well, here is my attempt.
I hope someone out there benefits from this blog. And remember, if you have a question, do not hesitate to ask it!

Thanks!

BobP