Archive

Archive for the ‘SQL’ Category

SSIS – Read C# ‘List’ collection in ‘Execute SQL Task’

In one of our integration requirements, we had to read the ‘List<>’ collection values, which was set in ‘Script Task’ control and pass to ‘Execute SQL Task’ control.

In this article I am going to list out the step by step process to set and get the ‘List<>’.

Assume that you have a collection List<string> which holds ‘Product Names’

var listProducts  = new List<string>() {“Product1″, ” Product2″, ” Product3″};

Lets see how to loop through and read the ‘Product names’ and pass to ‘Execute SQL Task’.

Required SSIS components:

  • A SSIS ‘Variable’ (i.e., varProducts) of type ‘Object’ to set the ‘List<>’ object in ‘Script Task’ control

SSIS_1

  • A SSIS ‘Variable’ (i.e., varProductName) of type ‘String’ to set the ‘Product Name’ for each List<> value.

SSIS_2

  • ‘Foreach Loop container’ to parse and read the ‘List<>’

Steps:

  • Choose ‘varProducts’ as ‘ReadWriteVariables’

SSIS_3

  • In ‘ScriptTask’ set ‘listProduct’ object to ‘varProducts’ variable.
    • var listProducts  = new List<string>() {“Product1″, ” Product2″, ” Product3″};
    • Dts.Variables[“User:: varProducts”].Value = listProducts ;
  • In ‘Foreach Loop container’,
    • Choose ‘Enumerator’ as ‘Foreach From Variable Enumerator’
    • Enumerator Variable as ‘User:varProducts’

SSIS_4

  • As the ‘List’ contain ‘Product Name’ collection, read the ‘Product Name’ to ‘varProductName’ variable. ‘Index’ 0 denotes, first value in collection (i.e., Product Name’.

SSIS_5

  • As the final step, In ‘Execute SQL Task’, map the ‘varProductName’ variable to a ‘Parameter’.

SSIS_6

  • In above screen, we are setting ‘@prodName’ parameter with ‘Product Name’ and ‘@prodName’ further used in ‘SQL Statement’ of ‘Execute SQL Task.

🙂

Categories: SQL Tags: ,

Transaction count after EXECUTE indicates a mismatching number – SQL Error

December 8, 2014 Leave a comment

I was getting the below exception when I execute a stored procedure from my C# console application.

SQL Transaction Error

SQL Transaction Error

My Stored procedure executes in Transaction mode and wrapped in Try/Catch block (Refer below)

CREATE PROCEDURE [Name]

BEGIN TRY

BEGIN TRANSACTION;

— My SQL Script

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

IF XACT_STATE() <> 0

ROLLBACK TRANSACTION;

END CATCH

Reason

  • We get this error if the transaction started and terminated suddenly without committed or rollback.
  • In my Stored Procedure I had a ‘return’ statement after ‘BEGIN TRANSACTION’ , so the stored procedure terminated neither calling COMMIT nor ROLLBACK.

Fix

  • Put “SET XACT_ABORT, NOCOUNT ON” statement which suppresses the error.
  • So the modified Stored Procedure template is as below

CREATE PROCEDURE [Name]

AS

SET XACT_ABORT, NOCOUNT ON 

DECLARE @starttrancount int

BEGIN TRY

SELECT @starttrancount = @@TRANCOUNT

IF @starttrancount = 0

BEGIN TRANSACTION

      — Your SQL Script

IF @starttrancount = 0

COMMIT TRANSACTION

END TRY

BEGIN CATCH

IF XACT_STATE() <> 0 AND @starttrancount = 0

ROLLBACK TRANSACTION

END CATCH

🙂

SQL query to split a string separated by Comma (or any special character)

Assume that you have a String with Comma separated value and wanted to split and get the collection in SQL.

My string looks ‘A,B,C,D, ‘ and I want output as

Value

A

B

C

D

Approach

The approach I am following here is

  • Declare a Temporary table
  • Split the string
  •  Insert the split values in to Temporary table

Query

DECLARE @MyValuevarchar(100)

DECLARE @posINT

DECLARE @lenINT

DECLARE @valuevarchar(8000)

SET @pos= 0

SET @len= 0

— Set the Comma separated value

SET @MyValue=‘A,B,C,D,’

— Declare Temporary Table to store split values

DECLARE @MyTempTbl TABLE (

[Value] [nvarchar](100)

)

WHILE CHARINDEX(‘,’,@MyValue,@pos+1)>0

BEGIN

SET @len=CHARINDEX(‘,’,@MyValue,@pos+1)@pos

SET @value=SUBSTRING(@MyValue,@pos,@len)

— Insert the splitter value in to Temporary Table

INSERT INTO @MyTempTbl Values(@value)

SET @pos=CHARINDEX(‘,’,@MyValue,@pos+@len)+1

END

— Get the resultset

SELECT * FROM @MyTempTbl

Output you get as below

String Split Result

String Split Result

Note

  • My requirement was to split the string and use in ‘IN’ condition, hence I created a Temporary table.
  • Also you can replace ‘,’ with any of the character

🙂

 

Categories: SQL Tags: ,

Auto generate new GUID for ‘uniqueidentifier’ column in SQL Table

If you a have a column of type ‘uniqueidentifier’ in your SQL table, and you want to auto generate a new Guid every time when you insert a record, you can use the ‘Default Value or Binding’ property of ‘Column Properties’.

Set Default Value or Binding = newid()

uniqueidentifier Column

uniqueidentifier Column

🙂

Categories: SQL Tags: , ,

SQL Query to fetch top 1 record from each group

I have a SQL table ‘Market’ with 3 Groups (i.e., Fruits, Vegetable and Meat) and below is the data

SQL Table - Market

SQL Table – Market

 

My requirement is

  • From each group fetch the top 1 item by ‘Priority’ column (i.e., Priority = 1) like below
Name Group

Priority

Apple Fruits 1
Fish Meat 1
Potato Vegetable 1

The approach is to provide ‘Rank’ using ROW_NUMBER to records in each group and get the Top 1 record

Below is the Query

— Create a temporary table with ‘Rank’ column

;WITH TempTbl AS

(

SELECT *,

ROW_NUMBER() OVER (

PARTITION BY [Group]

ORDER BY [Priority] ASC

) AS Rank

FROM [dbo].[Market]

)

— Select the Rank = 1 records

SELECT *

FROM TempTbl

WHERE

Rank = 1

🙂

Categories: SQL Tags: , , ,