SQL Theory: How to Store (and Retrieve) Non-English Characters (e.g. Hindi, Czech, Arabic etc.) in SQL Server

If you have to store and retrieve characters of any other language besides English in SQL Server, you must do the following –

  1. Use a Unicode compatible data type for the table column. NVACHAR, NCHAR, NTEXT are the datatypes in SQL Server that can be used for storing non-English characters.
  2. Precede the Unicode data values with an N (capital letter) to let the SQL Server know that the following data is from Unicode character set. Without the N prefix, the string is converted to the default code page of the database. This default code page may not recognize certain characters.
  3. The N should be used even in the WHERE clause.

REFERENCE: Microsoft Support KB 239530
You must precede all Unicode strings with a prefix N when you deal with Unicode string constants in SQL Server
http://support.microsoft.com/kb/239530

If the correct data-type is not used or the data is not preceded with an N, SQL Server will save the data to the table as ‘?’ or another garbled character.

The following scripts demonstrate saving and retrieving multi-lingual data to and from SQL Server. I have used Google Translate to get the characters of other languages. I left out far-east languages like Japanese and Chinese from the following example on purpose because those languages have a few other considerations that I’ll save for another blog post.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
DROP TABLE dbo.unicodeData;
GO
CREATE TABLE dbo.unicodeData
( languageUsed VARCHAR(50)
, unicodeData NVARCHAR(200)
, nonUnicodeData VARCHAR(200) -- same data in a normal VARCHAR column for comparison
, comments VARCHAR(100)
);
GO
INSERT INTO dbo.unicodeData
( languageUsed
, unicodeData
, nonUnicodeData
, comments)
VALUES
('English'
, N'This is an example'
, N'This is an example'
, NULL)
,('Hindi'
, N'यह एक उदाहरण है.'
, N'यह एक उदाहरण है.'
, 'Using the preceding N in both strings but VARCHAR is still a ?')
,('Hindi'
, 'यह एक उदाहरण है.'
, 'यह एक उदाहरण है.'
, 'Not using the preceding N in both strings so both are a ?')
,('Kannada'
, N'ಈ ಒಂದು ಉದಾಹರಣೆಯಾಗಿದೆ.'
, N'ಈ ಒಂದು ಉದಾಹರಣೆಯಾಗಿದೆ.'
, NULL)
,('Arabic'
, N'هذا مثال على ذلك.'
, N'هذا مثال على ذلك.'
, NULL)
,('Czech'
, N'To je příklad.'
, N'To je příklad.'
, NULL);
GO
SELECT *
FROM dbo.unicodeData;
GO
-- Example of using N' in the WHERE clause
SELECT *
FROM dbo.unicodeData
WHERE unicodeData like N'%एक%';
Unicode Results
Unicode Results

Further Reading:

SSIS: Use regex in the Foreach File Enumerator

Regex filter for Foreach Loop

Case
The standard Foreach Loop File Enumerator only has a wildcard filter, but in some cases a regular expression filter would be more useful. See/vote this request at Microsoft Connect.Solution
At the moment I’m working on a custom File Enumerator with regular expression support, but for those who don’t want to wait or don’t want to use custom components… You could achieve the same result with a Script Task.

*update: Now also available as Custom Foreach Enumerator. *

1) Foreach Loop
For this case I will use a standard Foreach File Enumerator that fills a variable FilePath. The filter is *.* so all files will be returned.

Standard foreach loop

2) Variables
I will use two extra variables in this example: RegexFilter (string) for storing the regular expression and PassesRegexFilter (boolean) for indicating whether the filename passes the regular expression filter.

Variables

3) Script Task
Add a Script Task in front of the Data Flow Task and give it a suitable name.

Script Task

4) Add variables
Edit the Script Task and add the FilePath and RegexFilter as ReadOnlyVariables and the PassesRegexFilter as ReadWriteVariable.

Variables

5) The Script
Copy the following script to the Script Task.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
// C# Code for filtering filenames with Regex
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Text.RegularExpressions;   // Added
using System.IO;                        // Added
namespace ST_02b6595da2274d7182409fb43af929ae.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion
        public void Main()
        {
            // Get the filename from the complete filepath
            String FileName = Path.GetFileName(Dts.Variables["User::FilePath"].Value.ToString());
            // Create a regex object with the pattern from the SSIS variable
            Regex FileFilterRegex = new Regex(Dts.Variables["User::RegexFilter"].Value.ToString());
            
            // Check if it is match and return that value (boolean) to the SSIS variable
            Dts.Variables["User::PassesRegexFilter"].Value = FileFilterRegex.IsMatch(FileName);
            
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

or with VB.Net

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
' VB.Net Code for filtering filenames with Regex
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Text.RegularExpressions   ' Added
Imports System.IO                        ' Added
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
 Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
 Enum ScriptResults
  Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
  Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
 End Enum
 
    Public Sub Main()
        ' Get the filename from the complete filepath
        Dim FileName As String = Path.GetFileName(Dts.Variables("User::FilePath").Value.ToString())
        ' Create a regex object with the pattern from the SSIS variable
        Dim FileFilterRegex As Regex = New Regex(Dts.Variables("User::RegexFilter").Value.ToString())
        ' Check if it is match and return that value (boolean) to the SSIS variable
        Dts.Variables("User::PassesRegexFilter").Value = FileFilterRegex.IsMatch(FileName)
        Dts.TaskResult = ScriptResults.Success
    End Sub
End Class

6) Precedence Constraints Expression
Connect the Script Task to the Data Flow Task and add an expression that does the real filtering:
@[User::PassesRegexFilter] == true

Precedence Constraint with expression

7) The result
For testing the result, I added an other Script Task below the Data Flow Task that logs all files. My regular expression in this test case: .*[0-9]\.csv$    (all csv files that end with a number in the filename)

The result

Note 1: to gain some performance I should have changed the Foreach Loop File Enumerator wildcard from *.* to *.csv

Note 2: this method could be a bit inefficient if your file collection contains thousands of files and you only need two of them.

SSIS: Transposing rows and columns in SQL Server Integration Services

Transposing rows and columns in SQL Server Integration Services


Okay, this was a rough one. The challenge was that a customer had Excel spreadsheets where we needed to transpose the rows and columns. In other words, the spreadsheet looked like this:

But we needed the records to list Program values by date, like this:

Of course, I did that by simply copying the cells and doing a “Paste | Transpose” in Excel. But when you have several hundred spreadsheets being used by folks, that’s a nontrivial operation.
At first it looked like my only option was going to be writing a script in a Script Transform component – there’s no “transpose” component in SSIS. But every time I asked someone about this, their first response was the Pivot component, which wasn’t quite right. Today I ran across some comments on the Unpivot component, and something about the output called to me:
TechNet Unpivot Resultset
And suddenly I figured it out – I could unpivot the spreadsheet, then pivot it again on the other field. Voila! A transpose.
So let’s walk through this. I’m going to skip many of the SSIS basics – if you need help with SSIS, check out the tutorials here.
First, let’s set up our Excel spreadsheet. This is the one step you really need to do – set a table name on your data. Click and select your data range, including the headers, then type a name into the Name box in the top left:

Hit the “Enter” key then save the spreadsheet. (Note: Save as Excel 95-2003; SSIS doesn’t do Office 2007 yet)
Add a data flow task to a new SSIS project. In the data flow pane, add an Excel source pointing to the ProgramData table in the spreadsheet we just saved. If you preview the data, note the date columns have column names of F2, F3, F4 – the date values of 1/1/2007, etc don’t map as column names. So let’s go to the columns pane in the Excel source editor and give them intelligent names:

Next we’re going to add an Unpivot transformation component and link our Excel Source to it. Double-click on the Unpivot component to open the transformation editor. Then you’ll check each of the boxes to the left of each of the month columns which adds them to the “Input Column” list. For each of them you’ll enter “MeasureValue” as the Destination Column. Finally, enter “MeasureDate” as the Pivot key value column name at the bottom:

 
The output of this transform will look like this:

The next step is a bit of trickery – due to the way the Pivot transform works, we need to sort the output of the Unpivot transform so that the appropriate records will be adjacent, otherwise you’ll get a staggered output. Add a Sort Transformation component and connect the Unpivot output to it:

Configure the Sort Transform to sort by the MeasureDate column.
Now let’s add a Pivot transform and connect the output of the Sort component to it:

Now comes the really tricky part – configuring the Pivot component. Double-click on it to open the Advanced Editor. In the Component Properties tab we don’t have to change anything.
Open the Input Columns tab. Check all three available input columns. That’s all we have to do here.
Open the Input and Output Properties tab. Open up the Privot Default Input and Input Columns folder. Select “MeasureDate.” Note the LineageID number here – we’ll need that later. Set the PivotUsage to “1”. The PivotUsage indicates to SSIS how to use each column:

    • 0: Row Attribute
    • 1: Row ID
    • 2: Column ID
  • 3: Values

So MeasureValue gets a PivotUsage of 3, and note its LineageID. Program gets a 2.
Now let’s create our outputs – open up the Pivot Default Output and click on “Output Columns”
Click on the “Add Column” button at the bottom. Our first column is going to be the MeasureDate – set the name to “MeasureDate” and the SourceColumn to the LineageID of the MeasureDate input column.
Now we’re going to add the four columns for our four programs. Click on the Output Columns again (if you have “MeasureDate” highlighted when you add a column, it will be inserted above MeasureDate. There’s no way to rearrange columns once you create them. It’s not a huge deal, but if you’re aesthetically nitpicky…).
Once you have “Output Columns” selected, click the “Add Column” button. Name the new column “Program1” (no space). We’re going to have to create a column for each value we want to transpose – this is one place that makes this fragile; you’re going to have to be sure to set up your error checking in case someone adds a new Program.
This column is going to get values from any row where the Program column has the text value “Program 1” (with space). So put the LineageID for the MeasureValue input column into the SourceColumn field (this indicates where the value will come from). Then type “Program 1” in the PivotKeyValue field. (Or better yet, copy/paste from the spreadsheet itself).
Add columns for Programs 2, 3, and 4 the same way – make sure to select “Output Columns” before adding a column, and the source column will be the same for all four programs.
Now let’s add an Excel output to view the results – use the Excel Destination Editor to create a new connection manager with the default outputs after you connect the Pivot:

When you run the package, here are the results you’ll get in the Excel spreadsheet you configured as the output:

 
Note that the dates are sorted alphabetically, as the result of our sorting to group the results. If you’re inserting the output into a database, it shouldn’t matter. Alternatively you can use other transforms to translate the date column (which is currently a string) into a proper date and sort the dataset again.
Again, a word of warning that this is a bit fragile; our dates are coded in the original source and unpivot, and the programs are listed in our pivot transform – be sure to use robust error checking and reporting. But it’s definitely easier to work with and maintain than a script component!!!

SSIS – reading blank rows from Excel 2007 which are actually deleted

Folks,

We are facing a strange issue with SSIS and thought I would share the same with you and see if there are any solutions. We are developing a solution where users upload their data in the form of Excel 2007 spreadsheets to a web application interface and then, the file is loaded into an Oracle RDBMS using SSIS 2008 (SSIS called using SSIS Object Model from .NET). Now, there are specific templates provided to the users for their input files and they are using the same. However, it is being observed that when the user deletes certain rows from Excel manually and tries to load it, the file load fails. The reason for the same   is seen when the package is run in debug mode – the excel source is reading a few rows (we are assuming the recently deleted rows) as NULL values in all the columns. Now, some searching revealed at http://www.tech-recipes.com/rx/2189/excel_2007_eliminate_blank_rows/ that the following manual set of operations are resolving the problem –

1. Go to the Ribbon, and select the Home tab.

2. In the Editing section, click the small arrow next to Find & Select .

3. Select Go To Special .

4. Select the Blanks radio button and click OK.

5. All blank columns/rows will be highlighted.

6. Delete

However, this requires a user intervention and obviously, the users are not really loving it J . I just wanted to check that if there are any possible workarounds using which this can be resolved in an automated manner – like a third party component maybe. I do understand the following –

1.     Excel 2007 is not really the best file format for loading data – a tab or comma-delimited file works much better. However, that is not an option here since the users are not comfortable and this decision has been already taken after many rounds of discussion.

2.     Excel being a rich file format would have its own complexities to store the data. However, I am looking for a fix similar to “IMEX=1 ” if it exists for this case

 

Solution

Just select the rows to be deleted and rather than clicking the delete key on ur keybord, goto the rowheader in the selected section -> right click-> delete.

Actually what happens when you just do a simple delete the cell has data which is blank. Same as NULL and no value are different.

When you right click and then delete a row then the row is actually deleted and SSIS will not pick up that row.

Another option is to modify your package to ignore those rows by using ISNULL functions inside a Conditional Split within your Data Flow.  For those rows where one of the columns that you expect an actual value in ISNULL, then you direct that row to a “deleted row” output, which you don’t use.

Windows: Restore Show Desktop Icon

Move the Show Desktop Icon to Quick Launch or Taskbar in Windows 7

If you aren’t a fan of scrolling your pointer over to the lower right corner of your monitor to Show the Desktop, we have a cool tweak that will let you create a Show Desktop icon in the Quick Launch or anywhere on your Taskbar.

If you want to easily get access to the Desktop in Windows 7 you’ve undoubtedly noticed they moved it to the lower right corner. This can be annoying if you have a dual monitors, or even a large monitor that is 22” or higher.

There are a few ways you can go about it and we’ll take a look at them and you can choose which method works best for you.

sshot-2010-09-06-[02-46-54]

Put Show Desktop Icon Back to Where it Used to Be

First let’s look at how to put it back where it used to be in earlier versions of Windows. One of the easiest ways is to Add the Quick Launch Bar to the Windows 7 Taskbar.

sshot-2010-09-06-[03-44-31]

After following The Geek’s article on restoring the Quick Launch Bar, you should see the Show Desktop icon. Then just move the icon to where you want it in the Quick Launch bar. For example, here we moved it along with the Switch Between Windows icon next to the IE icon.

sshot-2010-09-06-[03-41-21]

This method will “kill two birds with one stone” by getting the Quick Launch bar, and Show Desktop icon back in Windows 7.

Pin Show Desktop Icon to the Taskbar

You might not care to get the Quick Launch icon back in Windows 7, but would like to pin the icon to the Taskbar where you want it. Unfortunately the process isn’t as easy as a simple drag and drop.

sshot-2010-09-06-[03-52-54]

We need to use a workaround similar to the one we showed you earlier for pinning an external hard drive to the Taskbar in Windows 7. Right-click the Desktop and select Text Document.

sshot-2010-09-06-[04-08-58]

Now name it Show Desktop.exe and click Yes when the warning message comes up.

Note: You will need to have file extensions viewable in order for this to work.

sshot-2010-09-06-[04-10-29]

Right-click on the dummy exe file we just made and select Pin to Taskbar.

sshot-2010-09-06-[04-13-10]

Now create your own Show Desktop icon by typing or pasting the following code into Notepad.

[Shell]
Command=2
IconFile=explorer.exe,3
[Taskbar]
Command=ToggleDesktop

sshot-2010-09-08-[14-24-05]

When you save it, make sure to select All Files in Notepad.

sshot-2010-09-11-[17-37-07]

Then save it as Show Desktop.scf and place it in the following folder.

C:\Users\computername\AppData\Roaming\Microsoft\Internet Explorer\Quick Launch\User Pinned\TaskBar

Note: If you don’t see the folders like AppData make sure you have Show hidden files, folders, and drives selected in Folder Options.

sshot-2010-09-07-[02-40-15]

Next right-click on the Show Desktop.exe icon we pinned to the Taskbar, then right-click Show Desktop and select Properties.

sshot-2010-09-07-[02-44-26]

Enter the following into the Target field under the Shortcut tab.

C:\Users\computername\AppData\Roaming\Microsoft\Internet Explorer\Quick Launch\User Pinned\TaskBar\Show Desktop.scf

sshot-2010-09-11-[17-44-25]

Note: In the path change “computername” to the actual name of your computer for both steps.

Change Show Desktop Icon

Now we have our icon on the Taskbar and you can move it wherever is convenient to you, and clicking on it will indeed show your desktop. However, you might want to change the icon to something more appealing.

sshot-2010-09-11-[17-46-42]

One option is to open the Show Desktop icon Properties again and select the Change Icon button.

sshot-2010-09-11-[17-49-38]

sshot-2010-09-11-[17-52-01]

When we did this we got the following message…Click OK and it will open up to %SystemRoot%\system32\SHELL32.dll.

sshot-2010-09-11-[17-50-37]

Where you can go through the list of icons included by default.

sshot-2010-09-11-[17-54-47]

You might have to do a reboot for the icon to show up correctly, and here is our final result where we have it in the Quick Launch bar and the Taskbar. Of course the regular Show Desktop in the lower right corner as well.

sshot-2010-09-11-[18-03-54]

For more on changing icons to something more unique, we have a couple good articles you might want to check out:

SSIS: Package Design Standard Template – Nachi Hosakoti

This is a standard design steps to create an SSIS Project.

d1

There will be two types of packages in our design pattern:

  1. Master package
  2. Child package

Say for example: we have 3 packages:

  1. Master package – Run controller
  2. Child package – Package A
  3. Child package – Package B.

We have two SQL agent jobs –

  1. Run Package A
  2. Run Package B.

Run Package A job will execute the master package Run Controller and pass “A” as parameter and as a result it will execute the Package A. Run Package B job will execute the master package Run Controller and pass “B” as parameter and as a result it will execute the Package B.

Master package will have 3 main components:

  1. Execute SQL Task – which will execute a store proc to log the package start- exec dbo.LogPackageStart
    @BatchLogID = ?
    ,@PackageName = ?
    ,@ExecutionInstanceID = ?
    ,@MachineName = ?
    ,@UserName = ?
    ,@StartDatetime = ?
    ,@PackageVersionGUID = ?
    ,@VersionMajor = ?
    ,@VersionMinor = ?
    ,@VersionBuild = ?
    ,@VersionComment = ?
    ,@PackageGUID = ?
    ,@CreationDate = ?
    ,@CreatedBy = ?
    d2d2-2
  2. Execute Package Task – it will decide what package to call depending on the parameter passed. Precedence Constraint – @[$Project::DataSetType]==”A”. Parameter passing to the child package
    d4
  3. Execute SQL Task – which will execute a store proc to log the package end – exec dbo.LogPackageEnd
    @PackageLogID = ?
    ,@BatchLogID = ?
    ,@EndBatchAudit = ?
    d3
  4. Execute SQL Task (Event handler – OnError):  which will execute a store proc to log the package error – exec dbo.LogPackageError
    @PackageLogID = ?
    ,@BatchLogID = ?
    ,@SourceName = ?
    ,@SourceID = ?
    ,@ErrorCode = ?
    ,@ErrorDescription = ?
    ,@EndBatchAudit = ?
    d5
    d6

Database design for Package logging. Say for example the database name for package logging is SSIS_Log. SSIS_Log will have the following tables:

  1. BatchLog
  2. Package
  3. PackageVersion
  4. PackageErrorLog

BatchLog table script: 

CREATE TABLE [dbo].[BatchLog](
[BatchLogID] [int] IDENTITY(1,1) NOT NULL,
[StartDateTime] [datetime] NOT NULL DEFAULT (getdate()),
[EndDateTime] [datetime] NULL,
[Status] [char](1) NOT NULL,
CONSTRAINT [PK_BatchLog] PRIMARY KEY CLUSTERED
(
[BatchLogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Package table script: 

CREATE TABLE [dbo].[Package](
[PackageID] [int] IDENTITY(1,1) NOT NULL,
[PackageGUID] [uniqueidentifier] NOT NULL,
[PackageName] [varchar](255) NOT NULL,
[CreationDate] [datetime] NOT NULL,
[CreatedBy] [varchar](255) NOT NULL,
[EnteredDateTime] [datetime] NOT NULL DEFAULT (getdate()),
CONSTRAINT [PK_Package] PRIMARY KEY CLUSTERED
(
[PackageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

PackageVersion table script: 

CREATE TABLE [dbo].[PackageVersion](
[PackageVersionID] [int] IDENTITY(1,1) NOT NULL,
[PackageVersionGUID] [uniqueidentifier] NOT NULL,
[PackageID] [int] NOT NULL,
[VersionMajor] [int] NOT NULL,
[VersionMinor] [int] NOT NULL,
[VersionBuild] [int] NOT NULL,
[VersionComment] [varchar](1000) NOT NULL,
[EnteredDateTime] [datetime] NOT NULL DEFAULT (getdate()),
CONSTRAINT [PK_PackageVersion] PRIMARY KEY CLUSTERED
(
[PackageVersionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

PackageErrorLog table script: 

CREATE TABLE [dbo].[PackageErrorLog](
[PackageErrorLogID] [int] IDENTITY(1,1) NOT NULL,
[PackageLogID] [int] NOT NULL,
[SourceName] [varchar](64) NOT NULL,
[SourceID] [uniqueidentifier] NOT NULL,
[ErrorCode] [int] NULL,
[ErrorDescription] [varchar](2000) NULL,
[LogDateTime] [datetime] NOT NULL,
CONSTRAINT [PK_PackageErrorLog] PRIMARY KEY CLUSTERED
(
[PackageErrorLogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

 

Store proc [LogPackageStart]:

CREATE PROCEDURE [dbo].[LogPackageStart]
( @BatchLogID int
,@PackageName varchar(255)
,@ExecutionInstanceID uniqueidentifier
,@MachineName varchar(64)
,@UserName varchar(64)
,@StartDatetime datetime
,@PackageVersionGUID uniqueidentifier
,@VersionMajor int
,@VersionMinor int
,@VersionBuild int
,@VersionComment varchar(1000)
,@PackageGUID uniqueidentifier
,@CreationDate datetime
,@CreatedBy varchar(255)
)

AS
BEGIN
SET NOCOUNT ON

DECLARE @PackageID int
,@PackageVersionID int
,@PackageLogID int
,@EndBatchAudit bit

/* Initialize Variables */
SELECT @EndBatchAudit = 0

/* Get Package Metadata ID */
IF NOT EXISTS (SELECT 1 FROM dbo.Package WHERE PackageGUID = @PackageGUID AND PackageName = @PackageName)
Begin
INSERT INTO dbo.Package (PackageGUID, PackageName, CreationDate, CreatedBy)
VALUES (@PackageGUID, @PackageName, @CreationDate, @CreatedBy)
End

SELECT @PackageID = PackageID
FROM dbo.Package
WHERE PackageGUID = @PackageGUID
AND PackageName = @PackageName

/* Get Package Version MetaData ID */
IF NOT EXISTS (SELECT 1 FROM dbo.PackageVersion WHERE PackageVersionGUID = @PackageVersionGUID)
Begin
INSERT INTO dbo.PackageVersion (PackageID, PackageVersionGUID, VersionMajor, VersionMinor, VersionBuild, VersionComment)
VALUES (@PackageID, @PackageVersionGUID, @VersionMajor, @VersionMinor, @VersionBuild, @VersionComment)
End
SELECT @PackageVersionID = PackageVersionID
FROM dbo.PackageVersion
WHERE PackageVersionGUID = @PackageVersionGUID

/* Get BatchLogID */
IF ISNULL(@BatchLogID,0) = 0
Begin
INSERT INTO dbo.BatchLog (StartDatetime, [Status])
VALUES (@StartDatetime, ‘R’)
SELECT @BatchLogID = SCOPE_IDENTITY()
SELECT @EndBatchAudit = 1
End

/* Create PackageLog Record */
INSERT INTO dbo.PackageLog (BatchLogID, PackageVersionID, ExecutionInstanceID, MachineName, UserName, StartDatetime, [Status])
VALUES(@BatchLogID, @PackageVersionID, @ExecutionInstanceID, @MachineName, @UserName, @StartDatetime, ‘R’)

SELECT @PackageLogID = SCOPE_IDENTITY()

SELECT @BatchLogID as BatchLogID, @PackageLogID as PackageLogID, @EndBatchAudit as EndBatchAudit

END

Store proc [LogPackageEnd]:

CREATE PROCEDURE [dbo].[LogPackageEnd]
( @PackageLogID int
,@BatchLogID int
,@EndBatchAudit bit
)

AS
BEGIN
SET NOCOUNT ON
UPDATE dbo.PackageLog
SET Status = ‘S’
, EndDatetime = getdate()
WHERE PackageLogID = @PackageLogID

IF @EndBatchAudit = 1
Begin
UPDATE dbo.BatchLog
SET Status = ‘S’
, EndDatetime = getdate()
WHERE BatchLogID = @BatchLogID
End
END

Store proc [LogPackageError]:

ALTER PROCEDURE [dbo].[LogPackageError]
( @PackageLogID int
,@BatchLogID int
,@SourceName varchar(64)
,@SourceID uniqueidentifier
,@ErrorCode int
,@ErrorDescription varchar(2000)
,@EndBatchAudit bit
)

AS
BEGIN
SET NOCOUNT ON
INSERT INTO dbo.PackageErrorLog (PackageLogID, SourceName, SourceID, ErrorCode, ErrorDescription, LogDateTime)
VALUES (@PackageLogID, @SourceName, @SourceID, @ErrorCode, @ErrorDescription, getdate())

UPDATE dbo.PackageLog
SET Status = ‘F’
, EndDatetime = getdate()
WHERE PackageLogID = @PackageLogID

IF @EndBatchAudit = 1
Begin
UPDATE dbo.BatchLog
SET Status = ‘F’
, EndDatetime = getdate()
WHERE BatchLogID = @BatchLogID
End
END