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.