Preventing Deadlock: Preview has data but no columns returned

In my SSIS package, I connect to an external SQL server database. This external database supports a stored procedure that I need to execute to "retrieve data". So in my package, I set the DataAccess Mode property of my OLEDB datasource to "SQL Command" and I provide the command EXEC <proc_name> <Param>,<output_param>. (The proc has an output parameter). The preview shows all the columns and data, but somehow no columns are returned....so when I try to link this data source to a copy column task, I get an error saying the source does not have any columns...any idea why this could be happening. Thanks - Manmeet

I just ran a test on my side and it actually worked. I create a simple sp with a query (select * from adventureworks.production.productcategory) and the use it in the OLE DB. The preview worked fine and when connecting to a copy column transform I was able to see all the columns. However there is another thread going on now about the same issue and they say there are some work arounds:

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

In case you need it this is the xml code of my package

<?xml version="1.0"?><DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ExecutableType="MSDTS.Package.1"><DTS:Property DTS:Name="PackageFormatVersion">2</DTS:Property><DTS:Property DTS:Name="VersionComments"></DTS:Property><DTS:Property DTS:Name="CreatorName">MARINER\rsalas</DTS:Property><DTS:Property DTS:Name="CreatorComputerName">MARINERLAPTOP14</DTS:Property><DTS:Property DTS:Name="CreationDate" DTS:DataType="7">10/4/2006 8:05:36 AM</DTS:Property><DTS:Property DTS:Name="PackageType">5</DTS:Property><DTS:Property DTS:Name="ProtectionLevel">1</DTS:Property><DTS:Property DTS:Name="MaxConcurrentExecutables">-1</DTS:Property><DTS:Property DTS:Name="PackagePriorityClass">0</DTS:Property><DTS:Property DTS:Name="VersionMajor">1</DTS:Property><DTS:Property DTS:Name="VersionMinor">0</DTS:Property><DTS:Property DTS:Name="VersionBuild">3</DTS:Property><DTS:Property DTS:Name="VersionGUID">{28EC0411-DE09-4FBE-AA35-FA11901A0511}</DTS:Property><DTS:Property DTS:Name="EnableConfig">0</DTS:Property><DTS:Property DTS:Name="CheckpointFileName"></DTS:Property><DTS:Property DTS:Name="SaveCheckpoints">0</DTS:Property><DTS:Property DTS:Name="CheckpointUsage">0</DTS:Property><DTS:Property DTS:Name="SuppressConfigurationWarnings">0</DTS:Property>

<DTS:ConnectionManager><DTS:Property DTS:Name="DelayValidation">0</DTS:Property><DTS:Property DTS:Name="ObjectName">ETLRafLab</DTS:Property><DTS:Property DTS:Name="DTSID">{82682565-2AB7-4AE8-AB84-A06FBB937955}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">OLEDB</DTS:Property><DTS:ObjectData><DTS:ConnectionManager><DTS:Property DTS:Name="Retain">0</DTS:Property><DTS:Property DTS:Name="ConnectionString">Data Source=MARINERLAPTOP14;Initial Catalog=ETLRafLabDev;Provider=SQLNCLI.1;Integrated Security=SSPI;</DTS:Property></DTS:ConnectionManager></DTS:ObjectData></DTS:ConnectionManager>

<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><Package xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DtsControlFlowDiagram><dwd:BoundingTop>2593</dwd:BoundingTop><dwd:Layout><dds>

<font>

</font>

</mouseicon>

</diagram>

</layoutmanager>

</control>

</ddsxmlobj>

</layoutobject>

</ddscontrol>

</dds></dwd:Layout></dwd:DtsControlFlowDiagram></Package></DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{5B6C56E1-90E4-4B8E-85C0-E150AF873875}</DTS:Property><DTS:Property DTS:Name="DTSID">{D803BA1C-C91E-408F-9D6F-1226D9333CD0}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable>

<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><ConnectionManager xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DataSourceID>ETLRafLab</dwd:DataSourceID></ConnectionManager></DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{82682565-2AB7-4AE8-AB84-A06FBB937955}</DTS:Property><DTS:Property DTS:Name="DTSID">{CDC092B4-325E-4283-8E03-88E168C82372}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable>

<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><TaskHost xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DtsDataFlowDiagram><dwd:BoundingTop>1667</dwd:BoundingTop><dwd:Layout><dds>

<font>

</font>

</mouseicon>

</diagram>

</layoutmanager>

</control>

</ddsxmlobj>

</layoutobject>

</ddscontrol>

</control>

</ddsxmlobj>

</layoutobject>

</ddscontrol>

</ddsxmlobj>

</control>

</ddsxmlobj>

</layoutobject>

</connector>

</ddscontrol>

</dds></dwd:Layout></dwd:DtsDataFlowDiagram><dwd:DtsComponentDesignerPropertiesList><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">1 DataSourceViewID</dwd:key></dwd:DtsComponentDesignTimeProperty></dwd:DtsComponentDesignerPropertiesList></TaskHost></DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{B59730D8-9045-4EB3-A965-95240CC97D28}</DTS:Property><DTS:Property DTS:Name="DTSID">{A8831B5B-654F-43D4-BAA9-605512FCCDB0}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable>

<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><PipelinePath xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DestinationName>Copy Column Input</dwd:DestinationName><dwd:SourceName>OLE DB Source Output</dwd:SourceName></PipelinePath></DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{B59730D8-9045-4EB3-A965-95240CC97D28}-86</DTS:Property><DTS:Property DTS:Name="DTSID">{2C57A2EE-F8A6-4FD6-8BAF-54C95E9E30B7}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">1033</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>

<DTS:Executable DTS:ExecutableType="DTS.Pipeline.1"><DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property><DTS:Property DTS:Name="ExecutionAddress"></DTS:Property><DTS:Property DTS:Name="TaskContact">Performs high-performance data extraction, transformation and loading;Microsoft Corporation; Microsoft SQL Server v9; (C) 2004 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1</DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">-1</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>

<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions><DTS:Property DTS:Name="ObjectName">Data Flow Task</DTS:Property><DTS:Property DTS:Name="DTSID">{B59730D8-9045-4EB3-A965-95240CC97D28}</DTS:Property><DTS:Property DTS:Name="Description">Data Flow Task</DTS:Property><DTS:Property DTS:Name="CreationName">DTS.Pipeline.1</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property><DTS:ObjectData><pipeline id="0" name="pipelineXml" description="pipelineXml" defaultBufferMaxRows="10000" engineThreads="5" defaultBufferSize="10485760" BLOBTempStoragePath="" bufferTempStoragePath="" runInOptimizedMode="true">

<property id="5" name="SqlCommand" dataType="System.String" state="default" isArray="false" description="The SQL command to be executed." typeConverter="" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" containsID="false" expressionType="None">Exec sp_SelectFromProductCategory</property>

<property id="8" name="AlwaysUseDefaultCodePage" dataType="System.Boolean" state="default" isArray="false" description="Forces the use of the DefaultCodePage property value when describing character data." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property>

</outputs>

</component>

</inputs>

</outputs>

</component>

</components>

<paths>

</paths></pipeline></DTS:ObjectData></DTS:Executable><DTS:Property DTS:Name="ObjectName">OLEDB source with SP</DTS:Property><DTS:Property DTS:Name="DTSID">{5B6C56E1-90E4-4B8E-85C0-E150AF873875}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">MSDTS.Package.1</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property></DTS:Executable>

|||yeah I have a similar package connecting to another database (and executing a stored procedure) and that works just fine. But the package that I described in my last email just refuses to get going...btw..thanks for the other link - let me check if tweaking the procedure helps - appreciate your help!!|||

Manmeet Panigrahi wrote:
In my SSIS package, I connect to an external SQL server database. This external database supports a stored procedure that I need to execute to "retrieve data". So in my package, I set the DataAccess Mode property of my OLEDB datasource to "SQL Command" and I provide the command EXEC <proc_name> <Param>,<output_param>. (The proc has an output parameter). The preview shows all the columns and data, but somehow no columns are returned....so when I try to link this data source to a copy column task, I get an error saying the source does not have any columns...any idea why this could be happening. Thanks - Manmeet

I think the root cause of the problem here is that sprocs don't own their own metadata like tables and views do. It is theoretically possible to return completely different result sets from the same sproc depending on what parameters you pass it so there is no real metadata to bind to. Adam Machanic has a great discussion about this here:

Stored procedures are not parameterized views
(http://www.sqljunkies.com/WebLog/amachanic/archive/2006/05/29/21482.aspx)

Try the workaround that I suggested on the other thread.

-Jamie

|||

ok...now there is a new twist to the tale. I just found out that the select statement in the stored procedure that returns the data does a select on a temp table. And that is the reason why the columns are not being detected correctly. I also found out from another developer that they had the same issue in DTS (2000) and hence they built the DTS package using disconnected edit and it works fine!

So assuming that we are not able to change the proc, is there a way we can do the disconnected edit kind of workaround in SSIS? -Manmeet

|||

Manmeet Panigrahi wrote:

ok...now there is a new twist to the tale. I just found out that the select statement in the stored procedure that returns the data does a select on a temp table. And that is the reason why the columns are not being detected correctly. I also found out from another developer that they had the same issue in DTS (2000) and hence they built the DTS package using disconnected edit and it works fine!
So assuming that we are not able to change the proc, is there a way we can do the disconnected edit kind of workaround in SSIS? -Manmeet

There's similar. You could set the sql dynamically at runtime using an expression. That might be a very clever workaround actually. I'd be interested to see if it works.

-Jamie

|||Unfortunately that didnt work too...this is what I did...I now have this variable that stores the command to execute the proc that returns the data (EXEC pr_....). (This variable is populated at run time). Also this variable becomes the Data Access Mode of my OLEDB data source. Now at design time, I have this variable populated with a select statement that returns data in the correct schema format..so that I am able to setup my source destination mappings. So when the package runs, the variable gets populated (at runtime) with the EXEC statement...but unfortunately SSIS revalidates the schema (and detects that the EXEC does not return any columns) and gives me the "VS_NEEDSNEWMETADATA" errror....sigh...Unfortunately the proc is owned by a different set of ppl and it will take a lot of time for me to convince them to change it..

Saturday, February 25, 2012

Preview has data but no columns returned

No comments:

Post a Comment

Preventing Deadlock

Blog Archive

About Me