Steve Fibich

SSIS Execute SQL Task and RaisError (Using a OLEDB Connection)

2008-11-03T16:52:00Z

This is a relatively short post about raising an error in T-SQL and having SSIS not recognize that error. If you have an SSIS package that calls a SQL Task and that T-SQL for whatever reason ends up raising an error the SSIS package that called the T-SQL may complete with the step without error. There may be two separate reasons why this is not working as expected.

The first reason why it may not be working has to do with an apparent bug is SQL Server 2005 SP2. (It looks like it was a bug in 2008 as well) There are a number of good posts on this, but I believe you can fix this by installing SP 2 Updates. The first step is to make sure you know what version of SQL Server you are on. (http://support.microsoft.com/default.aspx/kb/321185) The second step is to upgrade to a SQL Server build that has this error corrected. (http://support.microsoft.com/kb/937137/) I have chosen to installed the most recent Cumlative Update that was released on August 18th 2008, it will bring you to version 09.00.3282.00.

About the SP2 Error:

http://www.windows-tech.info/15/2c8831412be41b1f.php

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=276828

The second reason has to do wiht the RaisError() call itself. The Error Level set in the RaisError statement needs to be set to a level of 11 or higher. That's it, it's that simple. Below is a simple example of testing this process.

Procedure:

Create proc [dbo].[failure_proc]

BEGIN

RaisError('Steves Error',18,1)

END

Unexpected Success (Error Level 11):

Expected Failure (Error Level 11):

Now I just need to integrate these changes into some of my previous packages and upgrade a few servers.

dtexec.exe what are you doing? (part II)

2008-09-02T16:15:00Z

This is the second part in a very short series covering dtexec and how to see exactly what package it is executing. Last time we covered how to see what dtexec was doing using PowerShell and WMI. This was great for checking all the different packages that might be running on a server at any given time. What we will cover this time is how to create a package that will not allow any work to be done if it is already running. If all of your jobs are called through the SQL Server Agent this is not really concern you need to have, as that job scheduler will not allow the same job to be called twice. If you have the same package imbedded in different SQL Server Agent Jobs, or you are using a third party job scheduler (that will call the same job even if it is already running), or if you are using a windows scheduler calling dtexec.exe or a mixed of all 3 this should be a concern.

Package Overview:

Alright you will notice the first object in the SSIS package is the WMI Data Reader Task. This is a underutilized task in my opinion and really opens up the possibilities of data collection for SSIS.

I use the WMI Connection Manager to connect to the WMI object I would like to query. (Details below) Its query is 'select * from WIN32_process' where it selects all of the process on the local machine to where the package is being executed. It dumps the result set into a package variable that is of type Object.

The WMI Connection Manager object is the standard connection manager with the default NameSpace (\root\cimv2). I set the Server name value to \\localhost and selected the use windows authentication check box. All pretty basic options.

The forEach loop then iterates of the record set object an runs the script task once for each row. Pulling out the Caption and CommandLine columns from the recordset and placing them into two variables WMICaption and WMICommandLine. (If you would like to see the entire record set to see what is available configure the WMI task to output its data to a text file and run it once.)

The Script task inside the forEach Loop is where all of the real work is done. Most of the work being done consists of string parsing. First I grab the processes that have a Caption of DTEXEC. Caption is the process name. Then when I have a record with a DTEXEC caption value I parse out the package name between the '/F(ile)' and the '.dtsx' values that will be there in a dtexec.exe call. I also remove any directory information that was used in the call. I output this to the screen for this example in a message box for each time there is a dtexec process. If the script detects the same package name in two different records where they match the System Package Variable 'PackageName' with the process name of DTEXEC it then sets a package level variable dtexecFlag to True. This is the indicator to run or not run the rest of the package.

Public Sub Main()
  '
  ' Add your code here
        '
        '--------------------------------------------------------------
        '
        'SFIBICH
        '9/2/2008
        'This strips out the package name from a CommandLine column of a
        'WMI Record set returned from Win32_process object where the
        'Caption value is DTEXEC
        '
        '--------------------------------------------------------------
        Dim WMICaption As String
        Dim WMICommandLine As String
        Dim WMIPackageName As String
        Dim PackageName As String
        Dim IndexOfSlashF As Integer
        Dim IndexOfSpace As Integer

        WMICaption = Dts.Variables("WMICaption").Value.ToString
        WMICommandLine = Dts.Variables("WMICommandLine").Value.ToString
        PackageName = Dts.Variables("PackageName").Value.ToString

        If WMICaption.Trim.Length > 5 Then
            If (WMICaption.Trim.ToUpper.Substring(0, 6) = "DTEXEC") Then
                IndexOfslashF = WMICommandLine.ToUpper.IndexOf("/F")
                IndexOfSpace = WMICommandLine.Substring(IndexOfslashF).ToUpper.IndexOf(" ") + IndexOfslashF

                WMIPackageName = WMICommandLine.Substring(IndexOfSpace + 1, WMICommandLine.ToUpper.IndexOf(".DTSX") - IndexOfSpace - 1)
                WMIPackageName = WMIPackageName.Substring(WMIPackageName.LastIndexOf("\") + 1)
                WMIPackageName = WMIPackageName.Trim

                MsgBox("WMICaption:" + WMICaption + vbCrLf + "WMICommandLine:" + WMICommandLine + vbCrLf + _
                "Package Name:" + PackageName + vbCrLf + "WMIPackage Name:" + WMIPackageName + vbCrLf + _
                "IndexOfslashF:" + IndexOfSlashF.ToString + vbCrLf + "IndexOfSpace:" + IndexOfSpace.ToString)

Dts.Variables("PackageCount").Value = CInt(Dts.Variables("PackageCount").Value) + 1

                If (WMIPackageName = PackageName And CInt(Dts.Variables("PackageCount").Value) > 1) Then
                    Dts.Variables("dtexecFlag").Value = True
                End If
            End If
        End If
        Dts.TaskResult = Dts.Results.Success
    End Sub

Precedence Constraints.

These are pretty straight forward one is set for Success and dtexecFlag=FALSE and the other is set to Success and dtexecFlag=TRUE.

Never Ending For Loop and Script task: This combination is to give me a way to have my example package run forever so I can test the WMI portion of this package. The loop is set to 1==1 and the script task waits 5 seconds.

There are some things to remember.

1.) DTEXEC is not executed when running a package using Visual Studio BIDS 2005. So this package will not find copies of itself running if you open two BIDS sessions and execute the package twice.

2.) Run the packages through command line to avoid the issue above and to allow the message boxes to PopUp. If you run it through SQL Agent you will get an error when it comes to the Message boxes.

3.) Read the message boxes as they pop up, you will not get the "Shut Down" message box until you run the second package, and you will have to click through two boxes prior to that.

Here are the screen shots of the package in action.

Running in BIDS with a copy of the package running in the command line in the background.

Running through the command line:

Well that's it, and you can download the package here! (I think registration is required, it redirects to a user group site sorry but I am having issues uploading to this site.)

dtexec.exe what are you doing?

2008-09-01T00:14:00Z

dtexec.exe is the program that is executed to run dtsx packages in SSIS 2005. This executable is called if you call a Integration Services package through SQL Server Agent or through the command line directly. The problem I am trying to solve today is to know what package dtexec.exe is running. When you run an SSIS package you can look in task manager and see a dtexec.exe process executing. If you only have one SSIS package executing at a time then you know which package dtexec is running. If you have an enviorment where you have two or more packages on mulitple schedules and some packages that run on demand then you are left wondering which package is a particular dtexec.exe process running? I have written a short (and do I mean short) powershell script to get this information. I hope some of you find this usefull. (This script can also be modified to handle looking into a "job number/name" if you are running your SSIS packages in some sort of parent/child framework. (more on that in a future post).

So for this example I have 1 SSIS packages, NeverEndingPackage.dtsx and another instance of NeverEndingPackage2.dtsx. (NeverEndingPackage2.dtsx is a copy of the NeverEndingPackage.dtsx) These packages are simply a for loop where 1==1 and the have a script task that waits for 5 seconds inbetween each loop.

I have called these packages through the command line, once each and you will see two dtexec.exe process in my task manager, but the question is which one is which if I needed to stop one of these processes?

Now enters PowerShell (using WMI) to solve the issue. I'll try and walk through the script below.

################################
#
# SFIBICH
# 8/21/2008
# This script gets the process of a given machine
# that are running dtexec.exe (SSIS executable)
# It then intergates the CommandLine String of the
# Win32_process object to display the SSIS package
# that is running.
#
################################
#one liner
#gwmi Win32_process |?{$_.ProcessName -eq 'dtexec.exe'} | select-object -property processId, @{Name='PackageName'; Expression={$_.CommandLine.substring($_.CommandLine.IndexOf('/F')+2,($_.CommandLine.indexOf('.dtsx')-$_.CommandLine.IndexOf('/F')-2)).trim()+'.dtsx'}}, threadCount, WS,VM | format-table

#multi liner
gwmi Win32_process |?{$_.ProcessName -eq 'dtexec.exe'} | select-object -property processId,`
@{Name='PackageName'; Expression={$_.CommandLine.substring($_.CommandLine.toUpper().IndexOf('/F')`
+2,($_.CommandLine.toUpper().indexOf('.DTSX')-$_.CommandLine.toUpper().IndexOf('/F')-2)).trim()+'.dtsx'}}, `
threadCount, WS,VM | format-table -auto

Ok, the # are just comments so nothing there.

There really is only one line of code here I just broke it into different lines using the ` at the end of each line.

Line 1 - This uses the get-wmi command (shorten to gwmi) to get the Win32_process object for this computer. (Note: you can pass -ComputerName ComputerName to get the same information on a remote machine.) IT then passes the output of this to a where-object (?) PowerShell command searching on ProcessName equal to dtexec.exe. After that it passes the filtered output to a select-object command where it selects the processID and some other things

Line2 - We create a custom property of PackageName that is a substring of the Win32_process property CommandLine where I have substringed the CommandLine to get the information between the /F and the .dtsx portion of the line. Which for a dtexec.exe process will be the name of the package. (More trimming maybe nescessary if you are runing the dtexec command from a directory that the SSIS packages are not in as it will length the file name that is passed to the command.)

Line3 - Finishing up the custom property and adding .dtsx to the name of the package since I cut it off

Line 4 - adding a few other properties like WorkingSet and Virtual Memory both in KB. Finally passing it to a format-table command to get the output in a manner that I like.

Thats it....here is the script in action:

You can see that the first package NeverEndingPackage is processID 312 and the second call to NeverEndingPackage2 is 4484. You can compare the process ID in the powershell script to that in the Task Manager. You can not match a process in task manager to a SSIS package. You can use the WMI Win32 Process object to pull more details about each SSIS package execution. Next time I'll show you how to use this type of information inside an SSIS package so that an automated job scheduler (other than SQL Server Agent) can't call your package while there is an instance of it already running.

Thats it.

space_used_sp vs. sp_spaceUsed

2008-07-08T12:07:00Z

Ok I'm not one to re-write standard system stored procedures for SQL Server. That being said I have been working a good deal with files and file groups within SQL Server over the past few days moving data and indexes to files and file groups other than the default PRIMARY file group. Sp_spaceUsed is great procedure for giving you overall storage utilization information for a database or database objects (table, index view, or queue). As I have been moving indexes and tables over to different file groups I wanted to see how this was splitting my data as far as size and row counts was going and how it was being disturbed across the file system. Unfortunately sp_spaceUsed does not give you this level of detail and I could not find a system view or other system procedure to give me all of the information that I wanted.

sp_spaceUsed

So I set out to create my own and now that I have it working and figured I'll share it with everyone. I ended up creating two objects, one a view sp_space_used_view and the second a procedure space_used_sp. I’m a database guy so I love organization so I put both of these objects into a schema named metadata that is owned by DBO. The view displays the space used by all objects in the database with rollup levels being displayed as ' -db-level', ' -schema-level', and ' -table-level'. The view relies upon the sys.objects (due to the fact that system tables are not in sys.tables so I was losing the space and row count information for these objects.), sys.indexes, sys.partions, sys.database_files, sys.schemas, sys.dm_db_partion_stats, sys.internal_tables, and sys.index_columns catalog views; all of which have detail coverage in BOL. The view utilizes these system catalog views to show rows counts, reserved space, data space, index space, and unused space in the database files broken down by table. I figure I would want to see all of the detailed data for a given database sometimes, rollups others, and individual objects separately as well so encapsulation of the select logic into a view allows re-use of this code in other objects. The procedure utilizes the view to show you information for a particular table or for the overall database. I hope this code is useful to others as well. One of the main differences between sp_spaceUsed and my space_used_sp is that sp_spaceUsed allows for the updating of system information which my procedure does not, it only returns data to the screen. The second difference is that sp_spaceUsed requires the schema name to be passed as part of the table name if you want to view information on an object outside of your default schema, space_used_sp does not require this and displays all objects with the name you provide giving you the schema name as part of the output.

space_used_sp vs. sp_spaceUsed

Here it is: (Note:everything is created in a schema call metadata)

IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[metadata].[space_used_vw]'))
DROP VIEW [metadata].[space_used_vw]
go
create view metadata.space_used_vw
-------------------------------------------
--SFIBICH
--7/7/2008
--The purpose of this view is to show space used broken
--down by SQL Server data files. Currently is does not calculate
--space for fulltext indexes and or XML indexes.
--
-------------------------------------------
as
select
top 100 PERCENT
coalesce([schema_name],' -db level-') schemaName
,coalesce(table_name,' -schema level-') tableName
,coalesce(data_file_name,' -table level-') dataFileName
,[rowCount]
,reserved*8 reservedKB
,data*8 dataKB
,case when used_page_count>data then (used_page_count-data)*8 else 0 end indexKB
,case when reserved>used_page_count then (reserved-used_page_count)*8 else 0 end unusedKB
from
(
SELECT
t.name table_name
,df.name data_file_name
,s.name [schema_name]
,SUM(
    CASE
  WHEN (p.index_id < 2) THEN row_count
  ELSE 0
    END
) [rowCount]
,SUM(reserved_page_count)+coalesce(max(IT_reserved_page_count),0) reserved
,SUM(
    CASE
  WHEN (p.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
  ELSE lob_used_page_count + row_overflow_used_page_count
    END
)data
,SUM(used_page_count)+coalesce(max(IT_used_page_count),0) used_page_count
FROM sys.dm_db_partition_stats p
inner join
sys.objects t
on
p.object_id=t.object_id
inner join
sys.schemas s
on
t.schema_id=s.schema_id
inner join
sys.indexes i
on
p.object_id=i.object_id
and
p.index_id=i.index_id
inner join
sys.database_files df
on
i.data_space_id=df.data_space_id
left outer join
(--Internal tables are placed on the same filegroup as the parent entity. (BOL)
  select
  pp.object_id
  ,min(pp.index_id) index_id
  ,sum(p.reserved_page_count) IT_reserved_page_count
  ,sum(p.used_page_count) IT_used_page_count
  FROM
  sys.dm_db_partition_stats p
  inner join
  sys.internal_tables it
  on
  p.object_id = it.object_id
  inner join
  sys.dm_db_partition_stats pp
  ON
  it.parent_object_id=pp.object_id
  WHERE
  it.internal_type IN (202,204)
  group by
  pp.object_id
)it
on
p.object_id=it.object_id
and
p.index_id=it.index_id
group by
s.name
,t.name
,df.name
with rollup
) dataTable
order by
[schema_name]
,table_name
,[rowCount] desc
,data_file_name asc
go
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[metadata].[space_used_sp]') AND type in (N'P', N'PC'))
DROP PROCEDURE [metadata].[space_used_sp]
go
create procedure metadata.space_used_sp
-------------------------------------------
--SFIBICH
--7/7/2008
--This is a replacement for sp_spaceused, it utlizes the view
--[metadata].[space_used_vw]. Currently it only reports on tables
--and the total for tables in a schema, or database level broken
--down by database files.
--
-------------------------------------------
@tableName sysname =null
as
if @tableName is null
begin
--------------------
--
--database, schema info
--
--------------------
select
' -db level-' schemaName
,' -schema level-' tableName
,dataFileName
,sum([rowCount]) [rowCount]
,sum(reservedKB) reservedKB
,sum(dataKB) dataKB
,sum(indexKB) indexKB
,sum(unusedKB) unusedKB
from
[metadata].[space_used_vw]
where
dataFileName <>' -table level-'
or
(
dataFileName =' -table level-'
and
schemaName=' -db level-'
and
tableName =' -schema level-'
)
group by
dataFileName
order by [rowCount] desc,dataFileName
end
else
begin
--------------------
--
--particular table
--
--------------------
select
schemaName
,tableName
,dataFileName
,[rowCount]
,reservedKB
,dataKB
,indexKB
,unusedKB
from
[metadata].[space_used_vw]
where
tableName =@tableName
end

Ordering SSIS packages in Visual Studio 2005 (part II)

2008-05-29T00:19:00Z

Ok the first post on Ordering SSIS packages in Visual Studio received a large number of hits...no comments but a good number of hits so I figured a follow up was due. After my first post I found myself ordering all of my SSIS packages with the order-ssisProj.ps1 code but I found it to be a bother since I had to put in the location of each .dtproj file. So with this second post I will introduce a script I wrote a few days ago that orders all of my SSIS packages. It finds any .dtproj file in the default USERPROFILE + \my documents\ and orders them. Before I get into the powershell script a quick intro about running a powershell script. I'm not going to go into great detail other than to say you have to enable scripting in powershell. Second you have to take the code below and put it into a text file and save it with a .ps1 extension. (As always you use this script at your own risk...and you should always understand what code you grab off a website is doing)

order-SSISproj2.ps1

#-----------------------------------------------------------------------------
#
#SFIBICH
#5/18/08
#Version 1.0
#order-SSISproj.ps1
#This script saves a backup file as a .bak
#This script will search the USERPROFILE enviorment variable + My documents
#as its starting place for .dtproj files
#This script allows args that will replace the default search path
#Use at your own risk, no guarantees
#-----------------------------------------------------------------------------
#
#$ars[0] - relative path to the file name
#$a = path to this file when executed
#$c = counter variable
#$i = counter for progress bar
#$x = xml object varaible
#$y = list of all dtproj files
#$z = list of dtspackages node items inside the xml document
#
#-----------------------------------------------------------------------------
$a=$^
$i=0
if ($args[0].length -gt 0) {
$fileLocation=$args[0]
if ($fileLocation -eq '?') {
  get-content $a | ? {$_.length -gt 0} | ? {$_.substring(0,1) -eq '#' }
  exit
}
else {
  date;$y=gci -path ($fileLocation) -include *.dtproj -recurse;date
}
}else{
write-warning('starting search for *.dtproj files at '+$x.value+'\My Documents\   this is a recursive search and may take a while')
date;$y=gci -path ($x.value+'\My Documents\') -include *.dtproj -recurse;date
}
$y | % {
$i+=1
write-progress "re-ordering dtproj files- progress" "% complete" -perc ($i/$y.length*100)
$_.Name
[xml]$x = get-content $_.FullName
  $x.save($_.FullName+'.bak')
  $z=$x.project.dtspackages.dtspackage | sort-object -property name
  $c=0
  $z| % {if ($c -eq 0) {$c+=1} else {$x.project.dtspackages.RemoveChild($_)}} | out-null
  $z| % {if ($c -eq 1) {$c+=1} else {$x.project.dtspackages.AppendChild($_)}} | out-null
  $x.save($_.FullName)
}

order-SSISproj2.ps1 (the walk thru)

Ok the first few lines 1 thru 22 are standard documentation. This will be use later and is useful to anyone who wants to update or modify the code in the future. Lines 23 grabs the first token of the previous command line (The file name in this case) and assigns it to the variable $a. Line 24 sets $i to a default value of 0. Lines 24 thru 34 parse the arguments passed to the script to figure out first if there are any arguments and if they are what are they. If there where arguments passed to the script and they happen to be '?' (the help command) then the first argument of the previous commands contents are read. (basically go read the file that was entered into the command) Sending on the lines with comments to the screen for ouptut. If the arguments passed to the script are not '?' then it is assumed that it is a starting location for a search for *.dtproj files. That directory and every directory inside it are searched for proj files and assigned to the array $y. Else the default search path is searched for *.dtproj files and those locations are assinged to the array $y. Lines 38 thru 49 is where the bulk of the work happen. Line 38 executes a for each on the $y array looping through the code block line 39 to 49. Line 39 increments the $i variable so we can show progress. Line 40 brings up the progress bar in powershell and shows the percent complete incrementing with each loop through this code block. Line 41 writes the name of the file the script is working on to standard output. Line 42 gets the content of the dtproj file for this loop and places it into a XML casted variable $x. Now that line of code is not mine, I can't remember where I saw it but it is very slick! Now I have an XML object to work with full of data! First thing I do is to save the xml file with the same file name and a.bak extension just in case I mess something up. Line 43 grabs all of the nodes in the project.dtspackages node and places them sorted into the $x variable. In line 45 I set the $c variable to 0 so I can check this later to know how many times I have gone thru inner loops. Line 46 removes all except for the first dtspackage node objects, based on the ordered set in $x. I had to do this because when I removed all of the nodes the parent dtspackages node no longer existed and I couldn't add anything back to it. Line 47 adds all of the dtspackage nodes back in the sorted order. Line 48 saves the file and then line 49 you loop back to start the next file. That’s it short and sweet!

Ok and here is the before and after:

Before (notice the XX_XXX_DX packages at the bottom)

After (notice that the XX_XXX_DX packages are in the correct location)

Once again I am not any way a powershell expert but I wanted to share a useful script that I developed for my own use with anyone who is working in an environment with a large number of SSIS packages in a single project or solution. Let me know your thoughts? If you have an improvement to the script send it to me or post it!

Ordering SSIS packages in Visual Studio 2005

2008-05-17T00:45:00Z

Problem: SSIS Packages in Microsoft Visual Studio 2005 are ordered in the order of package creation by default, or more precisely the order they have been added to that solutions/projects folder. What this means is that if you have an SSIS project that has more than a few SSIS packages let’s say 70+ it becomes an issue when you go to look for a particular package. You can't just have a good naming scheme for your packages and hope to find them quickly (though that does help). Because the packages are not ordered in alphabetical order and MSVS 2005 does not give you a way to change the default order easily you are stuck searching through what is pretty much an un-ordered list. Ok so if you have encountered this problem you know it’s real pain to deal with.

The Answer: Visual Studio uses metadata in an xml file to know which packages are in your project and what you want these packages to be called. Note: The file names and the package names in the project do not have to be the same but they are by default. This file is the [ProjectName].dtproj file located in the root of the project folder for any given SSIS project.

My Solution: Powershell to the rescue

#------------------------------------------
#
#SFIBICH
#5/16/08
#Version 1.0
#order-SSISproj.ps1
#This script saves a backup file as a .bak
#------------------------------------------
#$ars[0] - relative path to the file name
#$c = counter variable
#$x = xml object varaible
#------------------------------------------
if ($args[0].length -gt 0) {
$fileLocation=$args[0]
[xml]$x = get-content $fileLocation
$x.save($fileLocation+'.bak')
$y=$x.project.dtspackages.dtspackage | sort-object -property name
$c=0
$y| % {if ($c -eq 0) {$c+=1} else {$x.project.dtspackages.RemoveChild($_)}}
$y| % {if ($c -eq 1) {$c+=1} else {$x.project.dtspackages.AppendChild($_)}}
$x.save($fileLocation)
}else{
write-warning('Please enter the location of a SSIS project .dtproj file (NO WORK EXECUTED)')
}

Thats it I hope some of you find this useful!

trap [exception] { #insert code here} .... -ea stop

2008-04-15T01:33:00Z

First off I'm not The PowerShellGuy (if you want tons of powershell information go there first)...I'm just getting started with PowerShell but I can already see how it can be extremely useful to a database developer and database administrator. I plan on posting on PowerShell from time to time in this blog when I find something particularly useful to either of the previously mentioned rolls but for now I'm going to go over the TRAP object in PowerShell more to the point of what I have learned on how to use it. The trap object is pretty straight forward and is similar to a onError event. The main thing I want to explain in this post is that any cmdlet that you want to trap an error needs its ErrorAction or -ea property set to stop. That’s it, that short, that sweat, but I had a hard time finding anything on the web or in three different PS books (Not mentioned to protect the guilty) on how to properly execute a trap. Below I give a simple example:

$NoSuchDirectory="c:\noSuchDirectory"

#nothing happens as expected....script continues
function noTrap
{
get-childItem $NoSuchDirectory=
}
noTrap

#nothing happens as expected(?)....script continues
function withTrap
{
trap {
#insert trap info here
"Trapped Error! kind of"
}
get-childItem $NoSuchDirectory=
}
withTrap

#error is trapped....script stops then continues
function withTrap_2
{
trap {
  #insert trap info here
  "Trapped Error! kind of"
  continue;
}
get-childItem $NoSuchDirectory= -ea stop

#notice I've chanaged the ErrorAction on GCI to STOP from
#the default continue, this is what allows the script to
#trap the error. If you change it to continue the trap
#never fires
}
withTrap_2
"After the Trap"

Now I just need to figure out how to get the SQL 2008 powershell snapins to port over to a machine that doesn't have SQL 2008 installed on it.

theSystem part X

2008-02-17T01:55:00Z

Well one of my topics, "theSystem" listed in a previous post TwoProjects has moved over to SQL Server Central. I may have some more background information on this project from time to time but the majority of the writing will take place at SQL Server Central. If you missed the second article here is a link..."theSystem".

I'm still working on SOAK but its taken a backseat to the ramping up of a large project at my job, developing some SQL Server for iSereis programers training, and a speaking engagement. (Roanoke Code Camp)

Connecting to an iSeries for SSIS Users (this pertains to any .Net connection)

2008-02-17T01:45:00Z

There are a number of useful pieces of information to be gleaned from reams and reams of documentation on both sides of the house when it comes to connect SSIS to an IBM iSeries. I will try to present as much of it as I can in an easy to read fashion here. I am going to start off with the basics- Terminology.

General iSeries Terminology:

¨ iSeries – name for the actual hardware; re-branded to System I

¨ i5/OS – Native operating system for AS/400 – previous called OS 400 operating system running on System I hardware

¨ i5-Processor for the System I; System I –i5

¨ DB2/400 – IBM’s DB database running on the OS 400 operating system. This is different from DB2 UDB (IBM is not exactly clear on this sometimes)

¨ System I can run i5/OS(OS 400), AIX, UNIX(other tan AIX), LINUX, Windows (through the use of add on cards)

i5/OS (AS400) Terminology:

¨ DBFS – Database File System; Native AS/400 file system everything is an object in a database.

¨ IFS – Integrated File System; Windows compatible file system, folders, files.

¨ LPAR- Logical Partition- this is similar to a virtual server running

¨ iASP – integrated Asynchronous Storage Pools; This allows the AS/400 to run multiple DB instances similar to a named SQL Server instance. Not very common on this platform. Also known as a Catalog

¨ Library – This matches to a schema on MS SQL Server. Libraries are heavily used on the AS/400 much more common that schema use on MS SQL Server

¨ Files – Tables

¨ Logical Files – Logics – MS SQL Server equivalent is an indexed view.

¨ Indexes – MS SQL Server equivalent to indexes

¨ Journals and Journal Receivers – This is how the 400 handles commitment control. (Basically a log file can be set at the object level)

¨ QGPL –General Purpose Library-Lots of things get dumped here by default including SQL Packages

¨ SQL Packages-Not 100% sure but it is required to run a disturbed SQL Program and handles the file system access plan.

Well thats it for now, I'll give more specific examples of the IBM OLE and ODBC drivers as well as MS DB2 OLE drivers in future posts.

Data Dictionary Part II

2008-01-25T17:26:00Z

Ok, after putting up my first post on the Data Dictionary I had posters remorse. There is a simple change that makes the script shown in the previous post a little more user friendly. First the view metadata.columnsDataDictionary need to be changed from a SQL Select using a pivot to just a self join from the metadata.columnsExtendedProperties to allow for columns that shared the same domain to easily display different MS_Description values. The second change was to allow the procedure metadata.columnsExtendedPropertiesRepopulate to allow for an overall overwrite or just to add new values and retain old MS_Description extended propertie values. I don't think I mentioned this earlier the extended property of 'MS_Description' will be picked up by a number of development applications so it is useful to stick your data dictionary value in there but not to overwrite one if it came supplied with say a third part application installation or if you are allowing a third party applicaiton manage your descriptioins in the database.

Well thats it here is the code with a simple example:

USE [steves]

IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[metadata].[columnsExtendedProperties]'))

DROP VIEW [metadata].[columnsExtendedProperties]

IF EXISTS (SELECT * FROM sys.views

WHERE object_id = OBJECT_ID(N'[metadata].[columnsDataDictionary]'))

DROP VIEW [metadata].[columnsDataDictionary]

IF EXISTS (SELECT * FROM sys.objects

WHERE object_id = OBJECT_ID(N'[metadata].[dataDictionary]') AND type in (N'U'))

DROP TABLE [metadata].[dataDictionary]

IF EXISTS (SELECT * FROM sys.objects

WHERE object_id = OBJECT_ID(N'[dbo].[testExtendedProperties]') AND type in (N'U'))

DROP TABLE [dbo].[testExtendedProperties]

IF EXISTS (SELECT * FROM sys.objects

WHERE object_id = OBJECT_ID(N'[metadata].[columnsExtendedPropertiesRepopulate]') AND type in (N'P', N'PC'))

DROP PROCEDURE [metadata].[columnsExtendedPropertiesRepopulate]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [metadata].[dataDictionary](

[domainName] [varchar](50) NOT NULL,

[domainDesc] [varchar](1000) NOT NULL,

PRIMARY KEY CLUSTERED

(

[domainName] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF

, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

SET ANSI_PADDING OFF

exec sp_addextendedproperty 'domain', 'domainName'

, 'schema', 'metadata', 'table', 'dataDictionary', 'column', 'domainName'

exec sp_addextendedproperty 'domain', 'domainDesc'

, 'schema', 'metadata', 'table', 'dataDictionary', 'column', 'domainDesc'

insert into metadata.dataDictionary

(domainName,domainDesc)

values('domainName','Short text name for the classification of a businessdata type or attribute')

insert into metadata.dataDictionary

(domainName,domainDesc)

values('domainDesc','Description of the a business data type or attribute')

create view [metadata].[columnsExtendedProperties]

----------------------------------------------

--Steve Fibich

--1.11.2007

--This view is used to match columns to their extended

--properties. This is an extension of sys.extended_properties

--system view.

----------------------------------------------

select

top 100 percent

s.name as schemaName

,t.name as TableName

,c.name as columnName

,e.name as EName

,e.value as EDesc

,c.column_id as columnID

from

sys.extended_properties e

inner join

sys.tables t

t.object_id=e.major_id

inner join

sys.columns c

e.minor_id=c.column_id

and

e.major_id=c.object_id

inner join

sys.schemas s

t.schema_id=s.schema_id

where

class=1

order by

s.name

,t.name

,c.column_id

,e.name

create view [metadata].[columnsDataDictionary]

----------------------------------------------

--Steve Fibich

--1.11.2007

--This view is used to match columns to their extended

--properties. It does this for particular extended properties

--"domain" and "MS_Description. It then pivots these results for a

--more useful layout.

----------------------------------------------

select

top 100 percent

cep1.schemaName

,cep1.tableName

,cep1.columnName

,cep1.eDesc as domain

,cep2.eDesc as MS_Description

from

(

select

schemaName

,tableName

,columnName

,eDesc

,eName

,columnId

from [metadata].[columnsExtendedProperties]

where

EName='domain'

) cep1

full outer join

(

select

schemaName

,tableName

,columnName

,eDesc

,eName

,columnId

from

[metadata].[columnsExtendedProperties]

where

EName='MS_Description'

) cep2

cep1.schemaName=cep2.schemaName

and

cep1.tableName=cep2.tableName

and

cep1.columnID=cep2.columnID

order by

cep1.schemaName

,cep1.tableName

,cep1.columnID

create proc metadata.columnsExtendedPropertiesRepopulate

----------------------------------------------

--Steve Fibich

--1.11.2007

--This proc removes any 'MS_Description' extended properties

--from any columns of any tables that have matching entires in the

--metadata.datadictionary table and that have an extended propertie of 'domain'

--This is to try to make keeping 'MS_Description' descripions upto date eaiser

--@overwrite=0 turns off overwriting so existing MS_Descriptions will be left alone

--@overwrite=1 existing MS_Description values will be deleted and re-created

--@debug =0 sets additional logging off

--@debug=1 sets additional looging on

----------------------------------------------

@overwrite bit=0

,@debug bit=0

--if @debug=0

-- BEGIN

-- select @debug=debug from metadata.logging where schemaName='metadata'

-- END

declare @schemaName sysname

declare @tableName sysname

declare @columnName sysname

declare @domainDesc varchar(1000)

declare extProp_cursor cursor for

select

e.schemaName

,e.tableName

,e.columnName

,dd.domainDesc

from

metadata.dataDictionary dd

inner join

[metadata].[columnsExtendedProperties] e

dd.domainName=edesc

where

ename='domain'

open extProp_cursor

Fetch next from extProp_cursor into

@schemaName,@tableName,@columnName,@domainDesc

while @@fetch_status=0

BEGIN

if @debug=1

BEGIN

select @schemaName,@tableName,@columnName,@domainDesc

END

if (exists (select * from ::fn_listextendedproperty('MS_Description', 'schema'

, @schemaName, 'table', @tableName, 'column', @columnName)) and (@overwrite=0))

BEGIN

execute sp_dropextendedproperty 'MS_Description','schema'

,@schemaName,'table',@tableName,'column',@columnName

END

if not exists (select * from ::fn_listextendedproperty('MS_Description', 'schema'

, @schemaName, 'table', @tableName, 'column', @columnName))

BEGIN

execute sp_addextendedproperty 'MS_Description',@domainDesc,'schema'

,@schemaName,'table',@tableName,'column',@columnName

END

Fetch next from extProp_cursor into

@schemaName,@tableName,@columnName,@domainDesc

END

close extProp_cursor

deallocate extProp_cursor

-----------------------------------------

--Example Table and Extended Properties--

-----------------------------------------

create table dbo.testExtendedProperties

(

column1 int

,column2 int

,column3 int

,column4 int

,column5 int

,column6 int

)

exec sp_addextendedproperty 'domain', 'domainName'

, 'schema', 'dbo', 'table', 'testExtendedProperties', 'column', 'column1'

exec sp_addextendedproperty 'domain', 'domainDesc'

, 'schema', 'dbo', 'table', 'testExtendedProperties', 'column', 'column2'

exec sp_addextendedproperty 'domain', '3rdDomainDesc'

, 'schema', 'dbo', 'table', 'testExtendedProperties', 'column', 'column3'

exec sp_addextendedproperty 'domain', '4thDomainDesc'

, 'schema', 'dbo', 'table', 'testExtendedProperties', 'column', 'column4'

exec sp_addextendedproperty 'domain', '3rdDomainDesc'

, 'schema', 'dbo', 'table', 'testExtendedProperties', 'column', 'column5'

exec sp_addextendedproperty 'domain', '4thDomainDesc'

, 'schema', 'dbo', 'table', 'testExtendedProperties', 'column', 'column6'

insert into metadata.dataDictionary

(domainName,domainDesc)

values('3rdDomainDesc','This is the 3rd domain description that we have it should be on column3 and column5')

insert into metadata.dataDictionary

(domainName,domainDesc)

values('4thDomainDesc','This is the 4th domain description that we have it should be on column4 and column6')

execute metadata.columnsExtendedPropertiesRepopulate

select * from [metadata].[columnsDataDictionary]

select * from [metadata].[columnsExtendedProperties]