Horkay Blog
The postings on this site are my own and do not represent my Employer's positions, advice or strategies.
Friday, 15 August 2008

Working with SSIS, a stored procedure was recently changed from a simple select statement to include some more procedural statements that utilized temporary tables.  Surprisingly the SSIS package stopped working, even though the result set from the stored procedure remained the same.  One of the reasons to use the stored procedure in ssis, was to avoid embedding the login in the SSIS packages related to data access, what gives ?

Error is:


The component is not in a valid state. The validation errors are:
Warning at {6D024AC3-A299-4316-B8D6-BF5A7006768A} [OLE DB Source [1]]: The external metadata column collection is out of synchronization with the data source columns. The "external metadata column "employee_office" (36)" needs to be removed from the external metadata column collection.

Do you want the component to fix these errors automatically?



Some Quick research indicates this error is exactly what it says, metadata.  SSIS needs to know the metadata, for stored procedures, using an ole db data source, it does this by querying the first statement in the stored procedure, if the first statements are temporary tables and not a select statement , you've got the problem.   I've seen instances where temporary variables are ok and don't cause the problem.

I've seen several solutions to this problem using "set fmtonly off" in the SSIS call and "set nocount on" in the stored procedure.  While "set nocount on" is usually recommended, i'm not too pleased with the "set fmtonly off" solution, as I've read issues where this may or may not work and cause performance issues.

There seem to be several different ways to fix this, each of them slightly different, my preference is option 3. 

1.  Put "set nocount on" in the stored procedure, put "set fmtonly off" before the execution of the procedure [in the ssis call]. 

2.  re-write the stored procedure to use table variables instead of temporary tables, a good solution as well.

3.  Add some meta-data and the "set nocount on" to the stored procedure with a "short circuited if clause" (if 1=0), and a bogus select statement at the top.  I've tested with trying to leave the "set nocount on" out and it did not work.

CREATE PROCEDURE [dbo].[GenMetadata]

AS

    SET NOCOUNT ON

 

    IF 1 = 0

        BEGIN

            -- Publish metadata

            SELECT  CAST(NULL AS INT) AS id,

                    CAST(NULL AS NCHAR(10)) AS [Name],

                    CAST(NULL AS NCHAR(10)) AS SirName

        END

 

    -- Do real work starting here

    CREATE TABLE #test

        (

          [id] [int] NULL,

          [Name] [nchar](10) NULL,

          [SirName] [nchar](10) NULL

        )

 


References:

http://www.delphi.co.za/PermaLink,guid,56a56e23-d9d5-44dd-9f0b-0f8b80d0b462.aspx

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=572199&SiteID=1

http://scotta-businessintelligence.blogspot.com/2007/05/ssis-ole-db-source-component-stored.html

http://blogs.conchango.com/jamiethomson/archive/2006/12/20/SSIS_3A00_-Using-stored-procedures-inside-an-OLE-DB-Source-component.aspx

 

Friday, 15 August 2008 11:56:35 (Central Standard Time, UTC-06:00) | Comments [0] | SQL Server |  SSIS#
Search
Popular Posts
Unpatched Vulnerabiltiy discovered ...
Spring Fornicator brewed...
SQL Server Security, not where it n...
Pied Piper of Grandview II
Buick drops the ball, er...muffler
Recent Posts
Archive
June, 2018 (1)
May, 2018 (3)
April, 2018 (2)
February, 2018 (3)
January, 2018 (3)
November, 2017 (1)
October, 2017 (1)
August, 2017 (1)
June, 2017 (2)
May, 2017 (2)
April, 2017 (2)
March, 2017 (1)
February, 2017 (1)
December, 2016 (2)
October, 2016 (2)
September, 2016 (1)
August, 2016 (1)
July, 2016 (1)
March, 2016 (2)
February, 2016 (3)
December, 2015 (4)
November, 2015 (6)
September, 2015 (1)
August, 2015 (2)
July, 2015 (1)
March, 2015 (2)
January, 2015 (1)
December, 2014 (3)
November, 2014 (1)
July, 2014 (2)
June, 2014 (2)
May, 2014 (3)
April, 2014 (3)
March, 2014 (1)
December, 2013 (1)
October, 2013 (1)
August, 2013 (1)
July, 2013 (1)
June, 2013 (2)
May, 2013 (1)
March, 2013 (3)
February, 2013 (3)
January, 2013 (1)
December, 2012 (3)
November, 2012 (1)
October, 2012 (1)
September, 2012 (1)
August, 2012 (1)
July, 2012 (4)
June, 2012 (3)
April, 2012 (1)
March, 2012 (3)
February, 2012 (3)
January, 2012 (4)
December, 2011 (3)
October, 2011 (2)
September, 2011 (2)
August, 2011 (8)
July, 2011 (4)
June, 2011 (3)
May, 2011 (3)
April, 2011 (1)
March, 2011 (2)
February, 2011 (3)
January, 2011 (1)
September, 2010 (1)
August, 2010 (2)
May, 2010 (2)
April, 2010 (3)
March, 2010 (1)
February, 2010 (4)
January, 2010 (1)
December, 2009 (3)
November, 2009 (2)
October, 2009 (2)
September, 2009 (5)
August, 2009 (4)
July, 2009 (8)
June, 2009 (2)
May, 2009 (3)
April, 2009 (9)
March, 2009 (6)
February, 2009 (3)
January, 2009 (8)
December, 2008 (8)
November, 2008 (4)
October, 2008 (14)
September, 2008 (10)
August, 2008 (7)
July, 2008 (7)
June, 2008 (11)
May, 2008 (14)
April, 2008 (12)
March, 2008 (17)
February, 2008 (10)
January, 2008 (13)
December, 2007 (7)
November, 2007 (8)
Links
Categories
Admin Login
Sign In
Blogroll