August 15, 2017

SSIS: Error the metadata could not be determined because statement uses a temp table

This post is related to an error that I faced when executing SSIS package. The issue was running a stored procedures using temp tables within Execute SQL task in DataFlow task. The package failed with error message:

Load Results: error: SSIS Error Code DTS_E_OLEBERROR. An OLE DB error has occurred. Error code: 0x80004005
Description: The metadata could not be determined because statement uses a temp table

After doing some research I found that it was caused by the limitation of system stored procedure sp_describe_first_result_set, and It does not support temporary tables. SSIS package uses a this procedure to look up output metadata for any given command, and it does not support temp table.

The work  around was to use table variable or CTE. If you have to use temp table then you need to execute the stored procedure with result sets.

Example:

1) For single result sets.

EXEC [dbo].[USP_StoredProcedure]  1000
WITH RESULT SETS
(
(
Id INT,
Name VARCHAR(100)
        )
)

2) For more than one result sets

EXEC [dbo].[USP_StoredProcedure]  1000
WITH RESULT SETS
(
(
Id INT,
Name VARCHAR(100)
        ),
       (
Id INT,
Address VARCHAR(100)
        ),
       ........
)

August 1, 2017

Converting String to GUID

DECLARE @STRING NVARCHAR(50) = '447d16b308b24073ac5a7bf07c14bc8g'

SET @STRING  = REPLACE(REPLACE(@str,'0x',''), '-' ,'')

SET @STRING  = STUFF(STUFF(STUFF(
STUFF(@str,21,0,'-')
,17,0,'-')
,13,0,'-')
,9,0,'-')

SELECT CAST(@STRING AS UNIQUEIDENTIFIER)

April 28, 2017

SSIS: Script Component Asynchronous Transformation

We can configure the SSIS Script component output as a Synchronous or Asynchronous as required. Using the Synchronous setting each input row is processed as it passes through the component, where as Asynchronous setting waits until multiple input rows have been received before processing.

I had to use this when I was integrating two system using SSIS package. The scenario was to send the massive amount of data in a chunk from the source system into the other system in the JSON format. This Asynchronous setting helped to send the data in a limited batch size.

In order to configure in Asynchronous setting, we need to configure Output 0 to be an asynchronous output. Click on Output 0 in the Inputs and outputs and set the Synchronous property to None.



February 6, 2017

Loading Dll from Embedded Resource to SSIS Script Component

1) Add dll file as a reference.
2) Added dll must be saved in the project as a Embedded Resource.
        a) Right click on the added dll file
        b) Build Action -> Embedded Resource
        c) Copy to Output Directory -> Do not Copy


Use the script below:

 static ScriptMain()
    {
        AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(CurrentDomain_AssemblyResolve);
    }
    static System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
    {
        Assembly executingAssembly = Assembly.GetExecutingAssembly();

        if (args.Name.Contains("AddedFile1"))
        {
            if (AddedFile1 == null)
            {
                using (Stream stream = executingAssembly.GetManifestResourceStream("AssemblyName.AddedFile1.dll"))
                {
                    byte[] assemblyRawBytes = new byte[stream.Length];
                    stream.Read(assemblyRawBytes, 0, assemblyRawBytes.Length);
                    IdentityModel = Assembly.Load(assemblyRawBytes);
                }
            }
            return AddedFile1;
        }
        else if (args.Name.Contains("AddedFile2"))
        {
            if (Newtonsoft == null)
            {
                using (Stream stream = executingAssembly.GetManifestResourceStream("AssemblyName.AddedFile2.dll"))
                {
                    byte[] assemblyRawBytes = new byte[stream.Length];
                    stream.Read(assemblyRawBytes, 0, assemblyRawBytes.Length);
                    Newtonsoft = Assembly.Load(assemblyRawBytes);
                }
            }
            return AddedFile2;
        }
        return null;
    }

We can get AssemblyName from the properties of the script component project.

September 6, 2016

SSIS - Lookup Transformation Different Cache Mode

SSIS Lookup component plays vital role to search existing record in the Data Flow. There are three different cache modes available in this transformation and it is very important to know/understand how these modes affects the performance of the package.

1) Full Cache
2) Partial Cahche
3) No Cache




By default, we will see Lookup component will select Full cache. Once the 

July 5, 2016

SQL Server Query Optimization

In order to optimize the long-running queries, we need to take following steps:

1) Make sure to use correct data type and length for the table columns.

2) Always try to get the necessary columns, never use SELECT * FROM tblName.
Example:
SELECT col1, col2 FROM tblName

3) Don't use sub-queries. Try to make temp table and then use it where it is necessary.

4) Try to use filter with WHERE clause to get necessary rows/records.

5) Don't use JOIN unless it is necessary, try only using INNER JOIN.

6) Try avoiding LEFT JOIN, use Not Exists/Exists if needed.

7) Don't use ORDER BY clause unless it is needed.

8) Instead of using CURSOR,  always use a loop

9) Make sure to use right Index, and remove unwanted Index.

10) Use Execution Plan on SQL Server Management Studio, and identify where the execution plan is using Index scan, Index seek, lookup etc and use the suggested index.

11) Try to partition the large tables, and if the table is extremely large try to partition the index as well.

May 4, 2016

Getting Nth Position Char Index

I had to write a function which will return NthCharIndex when text, subtext and position to return its char index. Below function will help to get it.

CREATE FUNCTION [GetNthCharIndex]
(
@text    nvarchar(MAX),
@subText nvarchar(MAX),
@numCharIndex INT
)
RETURNS INT
AS
BEGIN
DECLARE @startPos INT
DECLARE @subTextLen INT
DECLARE @subTextIndex INT
DECLARE @lastSubTextIndex INT
DECLARE @count INT = 0

SET @subTextLen = LEN(@subText)
SET @startPos = 1
SET @lastSubTextIndex = 0

SET @subTextIndex = CHARINDEX(@subText, @text, @startPos)

WHILE @subTextIndex > 0
BEGIN
IF @count = @numCharIndex
BREAK;

SET @lastSubTextIndex = @subTextIndex
SET @startPos = @subTextIndex + @subTextLen
SET @subTextIndex = CHARINDEX(@subText, @text, @startPos)
SET @count = @count + 1
END

RETURN @lastSubTextIndex
END


Result:

SELECT [Control].[GetNthCharIndex]('Avesh Dhakal', 'a', 1)

Output: 1

SELECT [Control].[GetNthCharIndex]('Avesh Dhakal', 'a', 2)

Output: 9

SELECT [Control].[GetNthCharIndex]('Avesh Dhakal', 'a', 3)

Output: 11