Thursday, October 16, 2008

sql quick ref

GRANT EXECUTE ON dbo.spProcedure to Login01
DENY EXECUTE ON dbo.spProcedure to Login01

DENY VIEW ANY DATABASE to Login01 --only works in SQL 2005

SELECT * INTO tempBackup FROM SourceTable
SELECT * INTO #temptable FROM (select * from table1)
drop #temptable

--insert
insert into mytable exec myStoredProc;

INSERT INTO destTable(col1, col2) SELECT col1, col2 FROM sourceTable

--temp table or variable
declare @table1 Table(@field1 int primary key, @field2 varchar(50) not null)

--create with different datatype

--convert date with different format

--alter add constraints, etc

--different SETs
SET IDENTITY_INSERT dbo.mytable ON
SET IDENTITY_INSERT dbo.mytable OFF
SET CONCAT_NULL_YIELDS_NULL OFF --changing the default concat null with anything yields a null

--The following code sets the database option and the connection option to disable the default
--behavior:
-- set database option
sp_dboption 慍HA2? CONCAT_NULL_YIELDS_NULL, 慺alse?
-- examine the database option
SELECT DATABASEPROPERTYEX(慍HA2? 慖sNullConcat?


--global variables
@@error, @@trancount, @rowcount

--linked server and OPENROWSET, OPENQUERY, etc

--db metadata
select * from sysobjects (all objects including tables, views, stored procs)
select * from information_schema.tables

--math
100 % 4 get remainder
100 /3 = 33 (100.0/3.0 = 33.3333) get quotient
leap year, year % 4 = 0 and year % 100 != 0 or year % 400 = 0
leap year 365 + 1 days, Feb 29 days.

--function
--date
MONTH(getdate()), YEAR(getdate()), DAY(getdate())
DATENAME(dw, getdate()) = 'Friday'
DATENAME(dy, getdate()) = 271

--error handling
raiserror ('Error encountered in cascading trigger.', 16,1)

RAISERROR ( { msg_id | msg_str | @local_variable }
{ ,severity ,state }
[ ,argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]

RAISERROR('Problem with ProductId %i', 16, 1, @ProductId)

EXEC sp_addmessage
@msgnum = 50005,
@severity = 16,
@msgtext = 'Problem with ProductId numbers %i, %i, %i',
@Replace = 'Replace'

In order to monitor for exceptions, start a trace and select the Exception and User Error
Message events.

--retry deadlock-prone code
DECLARE @Retries INT
SET @Retries = 3
WHILE @Retries > 0
BEGIN
BEGIN TRY
/*
Put deadlock-prone code here
*/
--If execution gets here, success
BREAK
END TRY
BEGIN CATCH
IF ERROR_NUMBER = 1205
BEGIN
SET @Retries = @Retries - 1
IF @Retries = 0
RAISERROR('Could not complete transaction!', 16, 1)
END
ELSE
RAISERROR('Non-deadlock condition encountered', 16, 1)
END CATCH
END

In order to determine whether an active transaction can be committed or rolled forward,
check the value of the XACT_STATE function. This function returns 0 if there are no active transactions,
1 if the transaction is in a state in which more work can be done, and -1 if the transaction
is doomed. It is a good idea to always check XACT_STATE in any CATCH block that involves an
explicit transaction.

--admin

--start sql server in single user mode
sqlservr -m

USE [master]
GO
ALTER DATABASE [Hydra2] SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

I can抰 take credit for generating this list, but a friend of mine Eric sent these
to me a few months ago. I found these very useful recently

Function: Set the database into restricted user mode (dbo/admin type users) immediately.
Notes: This drops the connections and rolls back all current transactions immediately.


ALTER DATABASE NorthWinds
SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE


Function: Set the database into single user mode immediately.
Notes: This drops the connections and rolls back all current transactions immediately.
(Be careful not to lock yourself out)


ALTER DATABASE NorthWinds SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

[Preferred Method]
Function: Set the database into restricted user mode
Notes: Allow 10 seconds for all currents transactions to complete before the connections
are dropped and the transactions are manually rollback.


ALTER DATABASE NorthWinds
SET RESTRICTED_USER WITH ROLLBACK AFTER 10


Function: Set the database into offline mode immediately.
Notes: This drops the connections and rolls back all current transactions immediately.


ALTER DATABASE NorthWinds
SET OFFLINE WITH ROLLBACK IMMEDIATE


Function: Set a database into multi-user mode.



ALTER DATABASE NorthWinds
SET MULTI_USER



ALTER DATABASE NorthWinds
SET MULTI_USER | ONLINE | READ_ONLY



SET XACT_ABORT ON (OFF, it will print) statement level exception
SELECT POWER(2, 32)
PRINT 'This will NOT print!'
GO

--schema granting permission
GRANT SELECT ON SCHEMA::Sales
TO Alejandro
GO

--transfer Sales.SalesData to a new schema
--Create a new schema
CREATE SCHEMA Purchases
GO
--Move the SalesData table into the new schema
ALTER SCHEMA Purchases
TRANSFER Sales.SalesData
GO

--assign an owner to a table
--Create a table
CREATE TABLE JaviersData
(
SomeColumn INT
)
GO
--Set Javier as the owner of the table
ALTER AUTHORIZATION ON JaviersData
TO Javier
GO


--Impersonation
Execute as
revert
USER_NAME
SUSER_NAME
ORIGINAL_LOGIN


--ownership chaining cross database
DB_CHAINING

--impersonation at the procedure level
CREATE PROCEDURE SelectSensitiveData
WITH EXECUTE AS 'Louis'
AS
BEGIN
SET NOCOUNT ON
SELECT *
FROM dbo.SensitiveData
END

--reseed
dbcc checkident (mytable, reseed, 30)

--create certificate
CREATE CERTIFICATE Greg_Certificate
WITH SUBJECT='Certificate for Greg'
GO
CREATE USER Greg
FOR CERTIFICATE Greg_Certificate
GO

--sining a sp with a certificate
--no matter who owns the sp, the underlying table
--is owned by Greg, so using certificate can solve the problem
ADD SIGNATURE TO SelectGregsData
BY CERTIFICATE Greg_Certificate

/*
Keep in mind when working with certificates that any time the stored procedure is altered,
all signatures will be automatically revoked by SQL Server. Therefore, it is important to keep
signatures scripted with stored procedures, such that when the procedure is modified, the
permissions can be easily kept in sync.*/




--grant all permissions to a table
CREATE TABLE GregsData
(
DataColumn INT
)
GO
GRANT ALL ON GregsData
TO Greg
GO

--backup certificate to a file and restore
BACKUP CERTIFICATE alter_db_certificate
TO FILE = 'C:\alter_db.cer'
WITH PRIVATE KEY
(
FILE = 'C:\alter_db.pvk',
ENCRYPTION BY PASSWORD = 'an0tHeR$tRoNGpaSSWoRd?',
DECRYPTION BY PASSWORD = 'stR()Ng_PaSSWoRDs are?BeST!'
)
Once backed up, the certificate can be restored in a database. For the purpose of this
example, a new database can be created and used to keep things simple:
CREATE DATABASE alter_db_example
GO
USE alter_db_example
GO
CREATE CERTIFICATE alter_db_certificate
FROM FILE = 'C:\alter_db.cer'
WITH PRIVATE KEY
(
FILE = 'C:\alter_db.pvk',
DECRYPTION BY PASSWORD = 'an0tHeR$tRoNGpaSSWoRd?',
ENCRYPTION BY PASSWORD = 'stR()Ng_PaSSWoRDs are?BeST!'
)
GO

--fragmentation
DBCC SHOWCONTIG (trades) with ALL_INDEXES

--Show fragmentation information on a specific index
--Clean up the display
SET NOCOUNT ON

--Use the pubs database
USE pubs

DBCC SHOWCONTIG (authors,aunmind)

--de frag
Execute DBCC INDEXDEFRAG --non-blocking

DBCC CHECKDB
DBCC CHECKTABLE
DBCC CHECKALLOC
DBCC CHECKCONSTRAINTS


--tuning
SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO

--string concatenation, concatenate
declare @months varchar(200)
set @months = ''
select @months = @months + '"' + Monthstring + '",' from Monthstrings
select @months

SELECT Coalesce(
Address1 + str(13)+str(10) + Adress2,
Address1,
Address2,
Address3,
SalesNote) AS NewAddress
FROM TempSalesContacts
/*
For each row in the TempSalesContacts table, the coalesce() function will search through
the listed columns and return the first non-null value. The first expression returns a value
only if there抯 a value in both Address1 and Address2, because a value concatenated with a
null produces a null. Therefore, if a two-line address exists, then it will be returned.
Otherwise, a one-line address in Address1, Address2, or Address3 will be returned. Failing
those options, the SalesNote column will be returned. Of course, the result from such a
messy source table will still need to be manually scanned and verified.*/

//clear cache
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;

//compare current year and last year
SELECT Cur.OrderYear,
Cur.NumCusts AS CurNumCusts, Prv.NumCusts AS PrvNumCusts,
Cur.NumCusts - Prv.NumCusts AS Growth
FROM (SELECT YEAR(OrderDate) AS OrderYear,
COUNT(DISTINCT CustomerID) AS NumCusts
FROM dbo.Orders
GROUP BY YEAR(OrderDate)) AS Cur
LEFT OUTER JOIN
(SELECT YEAR(OrderDate) AS OrderYear,
COUNT(DISTINCT CustomerID) AS NumCusts
FROM dbo.Orders
GROUP BY YEAR(OrderDate)) AS Prv
ON Cur.OrderYear = Prv.OrderYear + 1;


//use synonym
USE model; --do it in model db, you have a synonym in every new db you create to point to Ad***.dbo.Nums
CREATE SYNONYM dbo.Nums FOR AdventureWorks.dbo.Nums;


--Lock
exec sp_lock spid (optional)
--if you want to see what is doing the lock, then
dbcc inputbuffer(spid) --spid is the first col returned from sp_lock, it will print the sql statement which holds the lock
sp_who spid
kill spid
select object_name(objid)

--sql error log location
C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\LOG
--you can get it from configuration manager, advanced options
--enable trace to be written to errorlog file, -1 means trace all connections
DBCC TRACEON (1222, -1)

select * from sys.dm_os_waiting_tasks

--assembly
select * from sys.assembly_files



--sql command sequence
(8) SELECT (9) DISTINCT (11)
(1) FROM
(3) JOIN
(2) ON
(4) WHERE
(5) GROUP BY
(6) WITH {CUBE | ROLLUP}
(7) HAVING
(10) ORDER BY


--statistics
update statistics tablename

--check when statistics last updated
USE databasename
GO
SELECT 'Index Name' = i.name,
'Statistics Date' = STATS_DATE(i.id, i.indid)
FROM sysobjects o, sysindexes i
WHERE o.name = 'tablename' AND o.id = i.id
GO

Wednesday, October 15, 2008

Object orientation and data centric

Data lives in database as tables, rows and columns. Objects, on the other hand, mostly live in memory and perishable. In a data centric application, objects are mostly data containers.

Tuesday, October 14, 2008

CS template for most efficient search sprocs

<%------------------------------------------------------------------------------------------
* Author: Eric J. Smith
* Description: This template will generate standard CRUD stored procedures for a given
* database table.
------------------------------------------------------------------------------------------%>
<%@ CodeTemplate Debug="True" Language="C#" Inherits="CodeSmith.BaseTemplates.SqlCodeTemplate" TargetLanguage="T-SQL"
Description="Generates standard CRUD procedures based on a database table schema." %>
<%-- Context --%>
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="1. Context"
Description="Table that the stored procedures should be based on." %>
<%-- Options --%>
<%@ Property Name="ExcludedColumns" Type="StringCollection" Default="" Optional="True" Category="2. Options"
Description="If supplied, any columns in this list will be excluded from all stored procedures unless the column is part of the primary key. (* is treated as a wildcard)" %>

<%@ Property Name="ReadOnlyColumns" Type="StringCollection" Default="" Optional="True" Category="2. Options"
Description="If supplied, any columns in this list will be treated as read only. (* is treated as a wildcard)" %>
<%-- Procedure Types --%>

<%-- Assembly References --%>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="CodeSmith.BaseTemplates" %>
<%@ Assembly Name="CodeSmith.CustomProperties" %>
<%@ Assembly Name="System.Data" %>
<%-- Namespace Imports --%>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="CodeSmith.CustomProperties" %>
<%@ Import Namespace="CodeSmith.BaseTemplates" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Text.RegularExpressions" %>
Create Procedure dbo.Search<%= SourceTable.Name %>
<% GenerateParameters(FilterReadOnlyAndExcludedColumns(SourceTable.Columns), 1); %>
AS
BEGIN
Select
<% GenerateColumns(SourceTable.Columns ,1); %> FROM <%= SourceTable%>
WHERE
<% GenerateWhereConditions(FilterReadOnlyAndExcludedColumns(SourceTable.Columns), 1); %>
END


CodeSmith Template--Most efficient search SProcs

Here is the generated code: (template follows)

Create Procedure dbo.SearchEmployees
@EmployeeID int = 0,
@LastName nvarchar(20) = '',
@FirstName nvarchar(10) = '',
@Title nvarchar(30) = '',
@TitleOfCourtesy nvarchar(25) = '',
@BirthDate datetime = '',
@HireDate datetime = '',
@Address nvarchar(60) = '',
@City nvarchar(15) = '',
@Region nvarchar(15) = '',
@PostalCode nvarchar(10) = '',
@Country nvarchar(15) = '',
@HomePhone nvarchar(24) = '',
@Extension nvarchar(4) = '',
,
@Notes ntext = '',
@ReportsTo int = 0,
@PhotoPath nvarchar(255) = ''
AS
BEGIN
Select
[EmployeeID],
[LastName],
[FirstName],
[Title],
[TitleOfCourtesy],
[BirthDate],
[HireDate],
[Address],
[City],
[Region],
[PostalCode],
[Country],
[HomePhone],
[Extension],
[Photo],
[Notes],
[ReportsTo],
[PhotoPath]
FROM dbo.Employees
WHERE
1 = Case When @EmployeeID = 0 Then 1 When @EmployeeID = EmployeeID Then 1 END AND
1 = Case When @LastName = '' Then 1 When LastName like '%' + @LastName + '%' Then 1 END AND
1 = Case When @FirstName = '' Then 1 When FirstName like '%' + @FirstName + '%' Then 1 END AND
1 = Case When @Title = '' Then 1 When Title like '%' + @Title + '%' Then 1 END AND
1 = Case When @TitleOfCourtesy = '' Then 1 When TitleOfCourtesy like '%' + @TitleOfCourtesy + '%' Then 1 END AND
1 = Case When @BirthDate = '' Then 1 When @BirthDate = BirthDate Then 1 END AND
1 = Case When @HireDate = '' Then 1 When @HireDate = HireDate Then 1 END AND
1 = Case When @Address = '' Then 1 When Address like '%' + @Address + '%' Then 1 END AND
1 = Case When @City = '' Then 1 When City like '%' + @City + '%' Then 1 END AND
1 = Case When @Region = '' Then 1 When Region like '%' + @Region + '%' Then 1 END AND
1 = Case When @PostalCode = '' Then 1 When PostalCode like '%' + @PostalCode + '%' Then 1 END AND
1 = Case When @Country = '' Then 1 When Country like '%' + @Country + '%' Then 1 END AND
1 = Case When @HomePhone = '' Then 1 When HomePhone like '%' + @HomePhone + '%' Then 1 END AND
1 = Case When @Extension = '' Then 1 When Extension like '%' + @Extension + '%' Then 1 END AND
AND
1 = Case When @Notes = '' Then 1 When Notes like '%' + @Notes + '%' Then 1 END AND
1 = Case When @ReportsTo = 0 Then 1 When @ReportsTo = ReportsTo Then 1 END AND
1 = Case When @PhotoPath = '' Then 1 When PhotoPath like '%' + @PhotoPath + '%' Then 1 END
END

some statistics handy

STATS_DATE gives you the info when the statistics updated last time

USE mydb
GO
SELECT 'Index Name' = i.name,
'Statistics Date' = STATS_DATE(i.id, i.indid)
FROM sysobjects o, sysindexes i
WHERE o.name = 'mytable' AND o.id = i.id
GO

Monday, October 13, 2008

ORM critiques

ER Model has been used in the industry for more than 50 years and it is a great way to model the data centric problems enterprises are trying to solve. Now Domain Driven Design seems to want to overtake ER Model. The issue from DDD proponents are applications are OO while ER is relational; there are impediment between those two.

I intend to look at ORM (specifically through the book NHibernate In Action) to investigate if ORM is a great idea that will work in cohorts with DDD to finally spell the death toll for ER Modeling.

For me, I am strongly biased against DDD and ORM. I think both of them add too much complexity to a relatively simple problem. Here I don't mean business problems are simple. By simple I mean business problems can be modeled effectively by ER model without extra explosion of patterns and what not. Just look at how many words those DDD and ORM bigwits have thrown into the fray, I can only wonder if it's a plot to milk more money from the poor business people who have no clue what's going on. Most of the time in the case of DDD and ORM, they are ending paying more money but get a slower system. What a joke!

OK, let me start with some excerpts from NHibernate in Action:

"The Unit of Work pattern
When working with a relational database, we tend to think of commands: saving or loading. But an application
can perform operations involving many entities. When these entities are loaded or saved depends on the
context.
For example, if you want to load the last item created by a user, you must first save this user (and his
collection of items), then you can run a query retrieving this item. If you forget to save the user, you will start
getting hardly-detectable bugs.
The Identity Map pattern
A pattern called Identity Map is used by NHibernate to make sure that this item’s user is the same
object as the user you had before loading this item (as long as you are working in the same
transaction). "

Unit of work pattern? Fancy. Why can't we just say "transaction". So you are not working with data; instead, you are working with millions of entities at the same time, all alive and kicking in memory? Are you insane? In this example, if you look from an ER/database/data point of view, it's extremely simple; users are in user table and items in item table. In item table there is a foreign key userID pointing back to user table, so you know what this item belongs to. When you are working with item, you really don't care about users, because item is pointing back to the user. Why do you want to save user first before you save item? Is OO/ORM really that dumb to think through simple stuff like this?

So you get unit of work pattern; so you get Identity map and what else? Then you still have to put data in the user table and item table. From the businessman point of view, you sold me user table and item table and now you want to add unit of work pattern and identity map for no added benefits? Wow, I am too stupid to buy that combo!

Friday, October 10, 2008

Error handling in SQL server 2005

CREATE PROCEDURE DeleteEmployee ( @EmployeeID int )
AS

BEGIN TRY
BEGIN TRANSACTION -- Start the transaction

-- Delete the Employee's phone numbers
DELETE FROM EmployeePhoneNumbers
WHERE EmployeeID = @EmployeeID

-- Delete the Employee record
DELETE FROM Employees
WHERE EmployeeID = @EmployeeID

-- If we reach here, success!
COMMIT
END TRY
BEGIN CATCH
-- Whoops, there was an error
IF @@TRANCOUNT > 0
ROLLBACK

-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()

RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

Developing a custom Control Flow Task for SSIS

Here is the article

Wednesday, October 08, 2008

CodeSmith Tutorial Videos

CODESMITH PROJECTS - http://codesmithtools.com/video/codesmith-projects.html
One of CodeSmith's most powerful features is CodeSmith Projects. Jump start your knowledge of CodeSmith Projects and get familiar with some tips and tricks to make the most use of CodeSmith Projects. Whether you are working with Visual Studio, MSBuild, Windows Explorer, a command line / batch file, or CodeSmith itself, CodeSmith Projects makes automating your code generation process easy and consistent.

SCHEMAEXPLORER - http://codesmithtools.com/video/schemaexplorer.html
SchemaExplorer is CodeSmith's built-in interface for working with metadata from databases. Take a quick tour of the features of SchemaExplorer and how they can be used with CodeSmith.

The complete CodeSmith video tutorial library can be seen at http://community.codesmithtools.com/blogs/videotutorials/default.aspx

As always, I love hearing feedback, comments, and suggestions. To provide feedback, send your comments to sales@codesmithtools.com.

Implement update or insert in SSIS

Using the SSIS transformation script component in an ETL

This example shows how to ETL (Extract Translate and Load) customer records from a source (staging) table to a destination table. It uses a standard update or insert algorithm.



Written by: Greg Van Mullem

Date: September 13, 2006



This web page is part of a collection of web pages containing difficult to find technical information about Microsoft SQL Server 2005 Standard Edition. Click here to go to the main index.





Overview:

Microsoft’s documentation and the third party books I have do a good job of explaining the components in SQL Server Integration Services. Unfortunately, they are very short on practical examples like this article. I don’t know if my design is the best way to do it or not. It’s more of an open proposal than a certified best practice. However, it is easy to code and it works!





Another method for doing this:

I recently learned of another very different method for implement this algorithm from Jamie Thompson (see the link below). He talks about using a Lookup (or Merge Join) together with a Conditional Split component. I have not tested his method but it looks very good! I highly recommend reading his blog and my web page before implementing anything. I usually have strong opinions about technical issues. However, this is one case where I really can’t decide which method I like better for general use.

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx





The algorithm documentation:

My code is a very loose adaptation of the unrelated code samples found in these Microsoft web pages. You might be able to get a little extra understanding from looking at them.



Creating a Destination with the Script Component

Developing Specific Types of Script Components



The algorithm extracts each record from the source table and looks it up in the destination table. If the record does not exist in the destination then it is inserted. If the destination record does exist then it will be compared to the source record. If there is a difference then the record will be updated. If the records are exactly the same then an update statement will not be issued. Deletes are not allowed with this data so I’m skipping that part. The algorithm does not rely on date fields to detect changes.



During the ETL I will use counter components to calculate the following values that will be written to a custom logging table.



1. RowsComplete; Count of the rows extracted from the source table.

2. RowsInserted; Count of the rows inserted.

3. RowsUpdated; Count of the rows updated.

4. RowsNotUpdated; Count of the rows where no action was taken. They matched exactly in the source and destination.

Running SSIS package programmatically

Michael Entin's notebook


I got several questions asking what is the best way to run SSIS packages programmatically. One question is about running SSIS from a .NET 1.1 application (SSIS uses .NET 2.0). Another about running package remotely "Do I really have to write an ASP.net app just to run a package on the server?" There were also questions about running package from ASP.NET page (which second user tries to avoid, but surprisingly many people want).

Let's review what options are available, and discuss which is most appropriate for each case.



1. Run package programmatically using SSIS Object Model. This is discussed in details in Books Online here: http://msdn2.microsoft.com/en-us/library/ms136090.aspx

Benefits: everything runs in process, it is very easy to set variables or modify package before executing it. You can also get events about package progress or ask it to stop by setting CancelEvent.

Drawbacks: Obviously this is local execution - you need to install SSIS on same machine where your app runs. This method also can't be used from .NET 1.1 application, unless it is moved to .NET 2.0 (which should be very easy to do, and in my experience improves the performance as well).

ASP.NET specific: the impersonation context does not get passed to additional threads SSIS package creates, so the data source connections will not be impersonated. Also, ASP.NET can be configured to recycle the worker process in case it consumes too much memory to improve availability of ASP.NET application. Since SSIS is likely to consume a lot of memory if you have lots of data, it can trigger this recycling and lower reliability of your application.



2. Start DTEXEC.EXE process. DTEXEC is command line utility for executing SSIS packages. See its command line options here: http://msdn2.microsoft.com/en-us/library/ms162810.aspx

Benefits: running package out of process gains reliability. Can be used from any programming language (including .NET 1.1 :)). Easy to pass parameters by setting variables values.

Drawbacks: Also local only. Harder to get information about package progress (but SSIS logging can give you most functionality). Some overhead on starting new process (likely minimal compared to execution time for big packages).

ASP.NET specific: Win32 CreateProcess function ignores the thread impersonation. So if you want DTEXEC to run under account different from ASP.NET process account, you should either make user enter name/password and pass it to Process.Start, or use method described in the following KB to run child process under impersonated account http://support.microsoft.com/kb/889251.



3. Use SQL Agent. You can configure an Agent job to run your package (either do it manually in advance if the package is static, or programmatically using SMO or using SQL stored procedures just before running the package), and then start it programmatically using SMO or sp_start_job.

MSDN has sample of calling sp_start_job from managed code: http://msdn2.microsoft.com/en-us/library/ms403355.aspx. Also make sure you implement logging as described in this KB http://support.microsoft.com/kb/918760 so if anything goes wrong you could troubleshoot it. This article also describes what can go wrong with the package when scheduled in Agent.

Benefits: You get remote package execution. You get execution serialization (only one instance of a job runs at a time). You can run the package under any account (use Agent proxy).

Drawbacks: Agent requires installation of SQL Server engine. You can't pass parameters directly - it requires modification to the job, or some side-channel, e.g. config file or SQL table.



4. Use some other utility to start DTEXEC for you.

Of course, you can use any other generic task scheduler instead of SQL Agent, if you don't want to use Agent for some reason. If you have some scheduler or remote execution infrastructure already in place - use it. All you need is a tool that can start an executable (DTEXEC) and pass command line arguments.



5. Create a custom application that will run the package (either using OM as described in method #1, or using DTEXEC as in method #2). Expose it as a web service or DCOM class, call this service from your program.

MSDN has sample code for both the web service and its client: http://msdn2.microsoft.com/en-us/library/ms403355.aspx (second part of this page).

Benefits: Easy to add custom logic. You get remote package execution. Easy to pass parameters.

Drawbacks: Need to write code.



6. Invent your own - I probably missed several ways. Please post comments with any ideas.



Now you should be able to answer the questions in the beginning of this blog:

* .NET 1.1 app - #2 is probably the easiest way to do it.
* Running package remotely: Agent (#3) is likely the simplest way.
* ASP.NET app - Again, #3 is usually the best and most reliable, as it allows you to configure the package to run as a user different from account of ASP.NET process, and you can install SQL and run SSIS packages on a separate box to isolate it from web server even more.

Published Thursday, March 22, 2007 3:27 PM by michen
Filed under: SSIS, SSIS Programming

SSIS Using Sequence Container to direct control flow

In "control flow", you can set completion as constraint or expression. Usually you want to direct the control flow based on the change of some variables. For example, when you are extracting records from a bunch of excel files and insert records into database; sometimes, you don't want to insert old records or duplicate records.

You can have a SQL task to check if there is a duplicate, if there is, then you want to delete them; after that you want to insert the new records. This kind of branching doesn't quite work because two branches depend on each other.

You can use sequence container to achieve the goal as detailed here

Tuesday, October 07, 2008

How to do continue and break in Foreach loop container

I am surprised that SSIS doesn't have a mechanism to deal with continue and break in foreach loop container. A hack is to use script task to set task status to success or failure then in the failure branch create another "do nothing" script task to consume one cycle. That's for "continue"

Script task to detect path

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

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

Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()

Dim pattern As String
pattern = "\\{1}[0-9]+\\{1}(\w+\s*)*\\{1}"

'Dim regex As Regex
'regex = New Regex(pattern)
Dim m As Match
Dim newDir As String
Dim vars As Variables
Dim currentExcelPath As String

Dts.VariableDispenser.LockOneForRead("CurrentExcelPath", vars)

currentExcelPath = vars(0).Value.ToString()
'debug
'Windows.Forms.MessageBox.Show(currentExcelPath)
vars.Unlock()

m = Regex.Match(currentExcelPath, pattern)
If m.Success Then
newDir = m.ToString()
newDir = CStr(IIf(newDir.StartsWith("\"), newDir.Substring(1), newDir))
newDir = CStr(IIf(newDir.EndsWith("\"), newDir.Substring(0, newDir.Length - 1), newDir))

'debug
'System.Windows.Forms.MessageBox.Show(newDir)

Dts.VariableDispenser.LockOneForWrite("DirectoryName", vars)
vars(0).Value = newDir
vars.Unlock()

Dts.TaskResult = Dts.Results.Success
Else
Dim file As String
file = String.Format("{0} is not a valid directory/file path for invoice; path should be like 200809\IQ.", currentExcelPath)

'debug
'System.Windows.Forms.MessageBox.Show(file)

Throw New Exception(file)
Dts.TaskResult = Dts.Results.Failure
End If


End Sub

End Class