ssis EXEC command

[gelangov]

I have 2 variables, one is the column name of the table and the other
one is the table name and I need to write this in the "Execute SQL
task" of a "For each loop" container in a ssis package like this:

Truncate table <tableName>
Insert into <tableName>
Exec (' select [' + ?+ '] from '+ ?)

It gives me error message when I try run the ssis. However if I put
the above statements into a stored procedure and wrote look this, it
works:

spStroedproc ?, ?

Do I need to change any of my settings for my "Exec" to work?

Thank you in advance

[Erland Sommarskog]

(ge******@hotmail.com) writes:

I have 2 variables, one is the column name of the table and the other
one is the table name and I need to write this in the "Execute SQL
task" of a "For each loop" container in a ssis package like this:

Truncate table <tableName>
Insert into <tableName>
Exec (' select [' + ?+ '] from '+ ?)

It gives me error message when I try run the ssis. However if I put
the above statements into a stored procedure and wrote look this, it
works:

spStroedproc ?, ?

Do I need to change any of my settings for my "Exec" to work?

I don't know SSIS per se, but you can never parameterise on a table
name in SQL Server. You need to build the entire query string.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

(ge******@hotmail.com) writes:

It is in xml...and so it looks quite ugly. Do you still want to see
it? Thank you for your help. I will also post it at microsoft site.

Do they really define SSIS packages as XML? Well, anyway the question is
rather: do you want me or someone else to say something or not? If you
don't, there is no reason to post it.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[gelangov]

On Aug 23, 11:18 am, Erland Sommarskog <[e-mail]:

([e-mail]) writes:

It is in xml...and so it looks quite ugly. Do you still want to see
it? Thank you for your help. I will also post it at microsoft site.

Do they really define SSIS packages as XML? Well, anyway the question is
rather: do you want me or someone else to say something or not? If you
don't, there is no reason to post it.

--
Erland Sommarskog, SQL Server MVP, [e-mail]

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Erland,

I certainly will appreciate your input if you don't mind...I was
heistating since it looked very ugly:

<?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">XXXXXXX</DTS:Property><DTS:Property
DTS:Name="CreatorComputerName">XXXXXX</DTS:Property><DTS:Property
DTS:Name="CreationDate" DTS:DataType="7">8/23/2007 9:53:40 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">12</
DTS:Property><DTS:Property DTS:Name="VersionGUID">{B801B6A6-A45E-45CC-
B6D3-86EEC43D8FE2}</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">DestinationConnectionFlatFil e</
DTS:Property><DTS:Property DTS:Name="DTSID">{339b3482-1751-45da-97c6-
a662c9950aa9}</DTS:Property><DTS:Property DTS:Name="Description"></
DTS:Property><DTS:Property DTS:Name="CreationName">FLATFILE</
DTS:Property><DTS:PropertyExpression
DTS:Name="ConnectionString">@[User::FileDestination] +
@[User::Variable1] + ".txt"</
DTS:PropertyExpression><DTS:ObjectData><DTS:Connec tionManager><DTS:Property
DTS:Name="FileUsageType">0</DTS:Property><DTS:Property
DTS:Name="Format">Delimited</DTS:Property><DTS:Property
DTS:Name="LocaleID">1033</DTS:Property><DTS:Property
DTS:Name="Unicode">0</DTS:Property><DTS:Property
DTS:Name="HeaderRowsToSkip">0</DTS:Property><DTS:Property
DTS:Name="HeaderRowDelimiter" xml:space="preserve">_x000D__x000A_</
DTS:Property><DTS:Property DTS:Name="ColumnNamesInFirstDataRow">0</
DTS:Property><DTS:Property DTS:Name="RowDelimiter"
xml:space="preserve"></DTS:Property><DTS:Property
DTS:Name="DataRowsToSkip">0</DTS:Property><DTS:Property
DTS:Name="TextQualifier">&lt;none&gt;</DTS:Property><DTS:Property
DTS:Name="CodePage">1252</DTS:Property>
<DTS:FlatFileColumn><DTS:Property DTS:Name="ColumnType">Delimited</
DTS:Property><DTS:Property DTS:Name="ColumnDelimiter"
xml:space="preserve">_x000D__x000A_</DTS:Property><DTS:Property
DTS:Name="ColumnWidth">0</DTS:Property><DTS:Property
DTS:Name="MaximumWidth">300</DTS:Property><DTS:Property
DTS:Name="DataType">129</DTS:Property><DTS:Property
DTS:Name="DataPrecision">0</DTS:Property><DTS:Property
DTS:Name="DataScale">0</DTS:Property><DTS:Property
DTS:Name="TextQualified">-1</DTS:Property><DTS:Property
DTS:Name="ObjectName">dflt</DTS:Property><DTS:Property
DTS:Name="DTSID">{F74B893C-B713-40CF-9A40-A13D00EBBB5E}</
DTS:Property><DTS:Property DTS:Name="Description"></
DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></
DTS:FlatFileColumn><DTS:Property DTS:Name="ConnectionString">.txt</
DTS:Property></DTS:ConnectionManager></DTS:ObjectData></
DTS:ConnectionManager>
<DTS:ConnectionManager><DTS:Property DTS:Name="DelayValidation">0</
DTS:Property><DTS:Property DTS:Name="ObjectName">Jobs database</
DTS:Property><DTS:Property DTS:Name="DTSID">{be31a102-14c7-4391-
ba7e-3f96b098f440}</DTS:Property><DTS:Property
DTS:Name="Description"></DTS:Property><DTS:Property
DTS:Name="CreationName">OLEDB</
DTS:Property><DTS:ObjectData><DTS:ConnectionManage r><DTS:Property
DTS:Name="Retain">0</DTS:Property><DTS:Property
DTS:Name="ConnectionString">Data Source=crm-db1-srvr;Initial
Catalog=Jobs200708_DB1;Provider=SQLNCLI.1;Integrat ed
Security=SSPI;Auto Translate=false;</DTS:Property></
DTS:ConnectionManager></DTS:ObjectData></DTS:ConnectionManager>
<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue"
DTS:DataType="8">&lt;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"&gt;&lt;dwd:DtsControlFlowDiagram&gt;&lt;dwd:B oundingLeft&gt;-3439&lt;/
dwd:BoundingLeft&gt;&lt;dwd:BoundingTop&gt;-7224&lt;/
dwd:BoundingTop&gt;&lt;dwd:Layout&gt;&lt;dds&gt;
&lt;diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}"
mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}"
defaultlayout="Microsoft.DataWarehouse.Layout.Grap hLayout"
defaultlineroute="Microsoft.DataWarehouse.Layout.G raphLayout"
version="7" nextobject="13" scale="100" pagebreakanchorx="0"
pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0"
scrollleft="-8835" scrolltop="-9494" gridx="150" gridy="150"
marginx="1000" marginy="1000" zoom="100" x="14975" y="12065"
backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3"
PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0"
PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0"
snaptogrid="0" autotypeannotation="1" showscrollbars="0"
viewpagebreaks="0" donotforceconnectorsbehindshapes="1"
backpictureclsid="{00000000-0000-0000-0000-000000000000}"&gt;
&lt;font&gt;
&lt;ddsxmlobjectstreamwrapper
binary="01000000900144420100065461686f6d61" /&gt;
&lt;/font&gt;
&lt;mouseicon&gt;
&lt;ddsxmlobjectstreamwrapper binary="6c74000000000000" /&gt;
&lt;/mouseicon&gt;
&lt;/diagram&gt;
&lt;layoutmanager&gt;
&lt;ddsxmlobj /&gt;
&lt;/layoutmanager&gt;
&lt;ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1"
tooltip="Execute SQL Task" left="-3439" top="-7224" logicalid="3"
controlid="3" masterid="0" hint1="0" hint2="0" width="3598"
height="1164" noresize="0" nomove="0" nodefaultattachpoints="0"
autodrag="1" usedefaultiddshape="1" selectable="1"
showselectionhandles="1" allownudging="1" isannotation="0"
dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1"
snaptogrid="0"&gt;
&lt;control&gt;
&lt;ddsxmlobjectstreaminitwrapper
binary="000800000e0e00008c040000" /&gt;
&lt;/control&gt;
&lt;layoutobject&gt;
&lt;ddsxmlobj&gt;
&lt;property name="LogicalObject"
value="{0a47ae8a-4526-4e42-9b70-bcbdbf1ad690}" vartype="8" /&gt;
&lt;property name="ShowConnectorSource" value="0" vartype="2"/
&gt;
&lt;/ddsxmlobj&gt;
&lt;/layoutobject&gt;
&lt;shape groupshapeid="0" groupnode="0" /&gt;
&lt;/ddscontrol&gt;
&lt;/dds&gt;&lt;/
dwd:Layout&gt;&lt;dwd:PersistedViewPortLeft&gt;-8835&lt;/
dwd:PersistedViewPortLeft&gt;&lt;dwd:PersistedView PortTop&gt;-9494&lt;/
dwd:PersistedViewPortTop&gt;&lt;/dwd:DtsControlFlowDiagram&gt;&lt;/
Package&gt;</DTS:Property><DTS:Property DTS:Name="Namespace">dts-
designer-1.0</DTS:Property><DTS:Property
DTS:Name="ObjectName">{7726A7FB-668B-4BE7-8D04-8C0A65E18ABD}</
DTS:Property><DTS:Property DTS:Name="DTSID">{98EA60BE-D46C-477A-
A965-5EE55AFBD0E3}</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:Variable><DTS:Property DTS:Name="Expression"></
DTS:Property><DTS:Property DTS:Name="EvaluateAsExpression">0</
DTS:Property><DTS:Property DTS:Name="Namespace">User</
DTS:Property><DTS:Property DTS:Name="ReadOnly">0</
DTS:Property><DTS:Property DTS:Name="RaiseChangedEvent">0</
DTS:Property><DTS:VariableValue DTS:DataType="8"></
DTS:VariableValue><DTS:Property DTS:Name="ObjectName">FileDestination</
DTS:Property><DTS:Property DTS:Name="DTSID">{52B271C5-
F5D8-4933-9924-879F48A49746}</DTS:Property><DTS:Property
DTS:Name="Description"></DTS:Property><DTS:Property
DTS:Name="CreationName"></DTS:Property></DTS:Variable>
<DTS:Variable><DTS:Property DTS:Name="Expression"></
DTS:Property><DTS:Property DTS:Name="EvaluateAsExpression">0</
DTS:Property><DTS:Property DTS:Name="Namespace">User</
DTS:Property><DTS:Property DTS:Name="ReadOnly">0</
DTS:Property><DTS:Property DTS:Name="RaiseChangedEvent">0</
DTS:Property><DTS:VariableValue DTS:DataSubType="ManagedSerializable"
DTS:DataType="13"><SOAP-ENV:Envelope xmlns:xsi="http://www.w3.org/2001/
XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:SOAP-
ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:clr="http://
schemas.microsoft.com/soap/encoding/clr/1.0" SOAP-
ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
<xsd:anyType id="ref-1">
</xsd:anyType>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope></DTS:VariableValue><DTS:Property
DTS:Name="ObjectName">ResultSet</DTS:Property><DTS:Property
DTS:Name="DTSID">{E92B650D-6D44-4A5C-80A0-B38D82E72C5C}</
DTS:Property><DTS:Property DTS:Name="Description"></
DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></
DTS:Variable>
<DTS:Variable><DTS:Property DTS:Name="Expression"></
DTS:Property><DTS:Property DTS:Name="EvaluateAsExpression">0</
DTS:Property><DTS:Property DTS:Name="Namespace">User</
DTS:Property><DTS:Property DTS:Name="ReadOnly">0</
DTS:Property><DTS:Property DTS:Name="RaiseChangedEvent">0</
DTS:Property><DTS:VariableValue
DTS:DataType="8">tbl98647kghFoodLionOffers</
DTS:VariableValue><DTS:Property DTS:Name="ObjectName">tblName</
DTS:Property><DTS:Property
DTS:Name="DTSID">{DC5442C8-2CB3-4640-88FC-091B54D8CA17}</
DTS:Property><DTS:Property DTS:Name="Description"></
DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></
DTS:Variable>
<DTS:Variable><DTS:Property DTS:Name="Expression"></
DTS:Property><DTS:Property DTS:Name="EvaluateAsExpression">0</
DTS:Property><DTS:Property DTS:Name="Namespace">User</
DTS:Property><DTS:Property DTS:Name="ReadOnly">0</
DTS:Property><DTS:Property DTS:Name="RaiseChangedEvent">0</
DTS:Property><DTS:VariableValue DTS:DataType="8"></
DTS:VariableValue><DTS:Property DTS:Name="ObjectName">Variable1</
DTS:Property><DTS:Property DTS:Name="DTSID">{82218A9F-19FC-431A-ACF5-
B3DE233C3D0E}</DTS:Property><DTS:Property DTS:Name="Description"></
DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></
DTS:Variable>
<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:Executable
DTS:ExecutableType="Microsoft.SqlServer.Dts.Tasks. ExecuteSQLTask.ExecuteSQLTask,
Microsoft.SqlServer.SQLTask, Version=9.0.242.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91" DTS:ThreadHint="0"><DTS:Property
DTS:Name="ExecutionLocation">0</DTS:Property><DTS:Property
DTS:Name="ExecutionAddress"></DTS:Property><DTS:Property
DTS:Name="TaskContact">Execute SQL Task; Microsoft Corporation;
Microsoft SQL Server v9; © 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">Get the Column names from the lookup table</
DTS:Property><DTS:Property DTS:Name="DTSID">{0a47ae8a-4526-4e42-9b70-
bcbdbf1ad690}</DTS:Property><DTS:Property
DTS:Name="Description">Execute SQL Task</DTS:Property><DTS:Property
DTS:Name="CreationName">Microsoft.SqlServer.Dts.Ta sks.ExecuteSQLTask.ExecuteSQLTask,
Microsoft.SqlServer.SQLTask, Version=9.0.242.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91</DTS:Property><DTS:Property
DTS:Name="DisableEventHandlers">0</
DTS:Property><DTS:ObjectData><SQLTask:SqlTaskData
SQLTask:Connection="{be31a102-14c7-4391-ba7e-3f96b098f440}"
SQLTask:TimeOut="0" SQLTask:IsStoredProc="False"
SQLTask:SqlStmtSourceType="DirectInput"
SQLTask:SqlStatementSource="Exec ('select dbo.fntrim(column_name) as
col from information_schema.columns&#xA;where table_name =''' +?+'''
and Column_name &lt;&gt;''Clientlocid'''&#xA;)"
SQLTask:ResultType="ResultSetType_Rowset"
xmlns:SQLTask="www.microsoft.com/sqlserver/dts/tasks/
sqltask"><SQLTask:ResultBinding SQLTask:ResultName="0"
SQLTask:DtsVariableName="User::ResultSet"/><SQLTask:ParameterBinding
SQLTask:ParameterName="0" SQLTask:DtsVariableName="User::tblName"
SQLTask:ParameterDirection="Input" SQLTask:DataType="129"/></
SQLTask:SqlTaskData></DTS:ObjectData></DTS:Executable><DTS:Property
DTS:Name="ObjectName">Package24</DTS:Property><DTS:Property
DTS:Name="DTSID">{7726A7FB-668B-4BE7-8D04-8C0A65E18ABD}</
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>

Thank you so much!!!

Geetha

[Erland Sommarskog]

(ge******@hotmail.com) writes:

I certainly will appreciate your input if you don't mind...I was
heistating since it looked very ugly:

It could have been a little easier if you had made it as attachment.
Maybe then it would be possible to open it in BIDS? I first tried IE,
and it appears that there line breaks in the wrong place etc.

In any case, the only query I found was this one:

Exec ('select dbo.fntrim(column_name) as
col from information_schema.columns&#xA;where table_name =''' +?+'''
and Column_name &lt;&gt;''Clientlocid'''&#xA;

which is different from the one you posted.

Reviewing the thread I see that you never posted the error message you get.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[gelangov]

On Aug 23, 5:42 pm, Erland Sommarskog <[e-mail]:

([e-mail]) writes:

I certainly will appreciate your input if you don't mind...I was
heistating since it looked very ugly:

It could have been a little easier if you had made it as attachment.
Maybe then it would be possible to open it in BIDS? I first tried IE,
and it appears that there line breaks in the wrong place etc.

In any case, the only query I found was this one:

Exec ('select dbo.fntrim(column_name) as
col from information_schema.columns&#xA;where table_name =''' +?+'''
and Column_name &lt;&gt;''Clientlocid'''&#xA;

which is different from the one you posted.

Reviewing the thread I see that you never posted the error message you get.

--
Erland Sommarskog, SQL Server MVP, [e-mail]

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

I wanted to send it as an attachment. I do not see that there is an
option to attach files through this group. This is the error message
I get:

"Syntax error, permission violation, or other nonspecific error".

Thanks.

[Erland Sommarskog]

(ge******@hotmail.com) writes:

I wanted to send it as an attachment. I do not see that there is an
option to attach files through this group.

I see that you post through Google. I never post there, so I don't know
about their interface. If you use a regular newsreader, attachments should
not a be a problem.

Another option is to put whatever you want to attach on a web site and
post the line.

This is the error message I get:

"Syntax error, permission violation, or other nonspecific error".

This error does not come from SQL Server, but from the client API, which
appears to have problems of parsing the query batch.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[John Heimiller]

I'm having the same original problem...can someone else besides Erland
comment on the issue. Erland, with all due respect, I don't think you
understand the original question. It is an SSIS question and you have
stated you "don't know SSIS per se".

Basically in SSIS, using the OLE connection, it will not parse:

INSERT TABLENAME EXEC STORED_PROC

This syntax is fine in query analyzer.

it has nothing to do with the "?" parmamters.

What we want to know is what SSIS settings (if there is any) that will
allow SSIS to parse this as good SQL?

I've tried changing BypassPrepare and IsStoredProcedure but no luck. I
could write a stored procedure that has this code and make it work like
the original poster did as a test...but I would rather have the
INSERT...EXEC...syntax.

I am going to try the ADO connector to see if that parses it right.



*** Sent via Developersdex http://www.developersdex.com ***

[John Heimiller]

Note: the ADO.NET adapter parses the SQL fine....I suggest switching
from the OLE unless your platforms don't support the ADO adapter.

One thing, you don't use "?" for parameters but actual variable names
with @...like @parm1. You will also need to re-add them as parameters in
SSIS after you change the connector to ADO.NET.

Hope that makes sense.

Would still like an answer to the SQL SERVER OLE connector not parsing

INSERT TABLE EXEC STOREDPROC

*** Sent via Developersdex http://www.developersdex.com ***

[Erland Sommarskog]

John Heimiller (jh********@starkinvestments.com) writes:

I'm having the same original problem...can someone else besides Erland
comment on the issue. Erland, with all due respect, I don't think you
understand the original question. It is an SSIS question and you have
stated you "don't know SSIS per se".

Hey, I may not know SSIS, but I do have experience of OLE DB, and
I doubt that SSIS performs any parsing of its own. So the problem
should appear about any code that uses OLE DB.

But you are right that I did not understand the question in full, but
I'm used to that: too many questions are posted with incomplete
information.

I am going to try the ADO connector to see if that parses it right.

Ah, my bad. That much I know of SSIS that it can use either an OLE DB
provider or SqlClient, so I should have given that advice.
<Aside>
Actually some time back, I had a bit of fun in our private MVP forum. A
fellow MVP had just started using SSIS on a gig, and was not able to
get parameters to work, and made noise as if it was a misdesign in SSIS.
That is about the only time I've composed an SSIS package, just to show
how to get parameters working, which I was able to. I think my MVP
colleague's problem was that he had had very little experience of client-
side programming overall.
</Aside>
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx