ssis EXEC command

[gelangov@hotmail.com]

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]

Re: ssis EXEC command

(gelangov@hotma il.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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Erland Sommarskog]

Re: ssis EXEC command

(gelangov@hotma il.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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[gelangov@hotmail.com]

Re: ssis EXEC command

On Aug 23, 11:18 am, Erland Sommarskog <esq...@sommars kog.sewrote:

(gelan...@hotma il.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, esq...@sommarsk og.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/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:Executab le xmlns:DTS="www. microsoft.com/
SqlServer/Dts" DTS:ExecutableT ype="MSDTS.Pack age.1"><DTS:Pro perty
DTS:Name="Packa geFormatVersion ">2</DTS:Property><D TS:Property
DTS:Name="Versi onComments"></DTS:Property><D TS:Property
DTS:Name="Creat orName">XXXXXXX </DTS:Property><D TS:Property
DTS:Name="Creat orComputerName" >XXXXXX</DTS:Property><D TS:Property
DTS:Name="Creat ionDate" DTS:DataType="7 ">8/23/2007 9:53:40 AM</
DTS:Property><D TS:Property DTS:Name="Packa geType">5</
DTS:Property><D TS:Property DTS:Name="Prote ctionLevel">1</
DTS:Property><D TS:Property DTS:Name="MaxCo ncurrentExecuta bles">-1</
DTS:Property><D TS:Property DTS:Name="Packa gePriorityClass ">0</
DTS:Property><D TS:Property DTS:Name="Versi onMajor">1</
DTS:Property><D TS:Property DTS:Name="Versi onMinor">0</
DTS:Property><D TS:Property DTS:Name="Versi onBuild">12</
DTS:Property><D TS:Property DTS:Name="Versi onGUID">{B801B6 A6-A45E-45CC-
B6D3-86EEC43D8FE2}</DTS:Property><D TS:Property
DTS:Name="Enabl eConfig">0</DTS:Property><D TS:Property
DTS:Name="Check pointFileName"> </DTS:Property><D TS:Property
DTS:Name="SaveC heckpoints">0</DTS:Property><D TS:Property
DTS:Name="Check pointUsage">0</DTS:Property><D TS:Property
DTS:Name="Suppr essConfiguratio nWarnings">0</DTS:Property>
<DTS:Connection Manager><DTS:Pr operty DTS:Name="Delay Validation">0</
DTS:Property><D TS:Property
DTS:Name="Objec tName">Destinat ionConnectionFl atFile</
DTS:Property><D TS:Property DTS:Name="DTSID ">{339b3482-1751-45da-97c6-
a662c9950aa9}</DTS:Property><D TS:Property DTS:Name="Descr iption"></
DTS:Property><D TS:Property DTS:Name="Creat ionName">FLATFI LE</
DTS:Property><D TS:PropertyExpr ession
DTS:Name="Conne ctionString">@[User::FileDesti nation] +
@[User::Variable1] + ".txt"</
DTS:PropertyExp ression><DTS:Ob jectData><DTS:C onnectionManage r><DTS:Propert y
DTS:Name="FileU sageType">0</DTS:Property><D TS:Property
DTS:Name="Forma t">Delimited </DTS:Property><D TS:Property
DTS:Name="Local eID">1033</DTS:Property><D TS:Property
DTS:Name="Unico de">0</DTS:Property><D TS:Property
DTS:Name="Heade rRowsToSkip">0</DTS:Property><D TS:Property
DTS:Name="Heade rRowDelimiter" xml:space="pres erve">_x000D__x 000A_</
DTS:Property><D TS:Property DTS:Name="Colum nNamesInFirstDa taRow">0</
DTS:Property><D TS:Property DTS:Name="RowDe limiter"
xml:space="pres erve"></DTS:Property><D TS:Property
DTS:Name="DataR owsToSkip">0</DTS:Property><D TS:Property
DTS:Name="TextQ ualifier">&lt;n one&gt;</DTS:Property><D TS:Property
DTS:Name="CodeP age">1252</DTS:Property>
<DTS:FlatFileCo lumn><DTS:Prope rty DTS:Name="Colum nType">Delimite d</
DTS:Property><D TS:Property DTS:Name="Colum nDelimiter"
xml:space="pres erve">_x000D__x 000A_</DTS:Property><D TS:Property
DTS:Name="Colum nWidth">0</DTS:Property><D TS:Property
DTS:Name="Maxim umWidth">300</DTS:Property><D TS:Property
DTS:Name="DataT ype">129</DTS:Property><D TS:Property
DTS:Name="DataP recision">0</DTS:Property><D TS:Property
DTS:Name="DataS cale">0</DTS:Property><D TS:Property
DTS:Name="TextQ ualified">-1</DTS:Property><D TS:Property
DTS:Name="Objec tName">dflt</DTS:Property><D TS:Property
DTS:Name="DTSID ">{F74B893C-B713-40CF-9A40-A13D00EBBB5E}</
DTS:Property><D TS:Property DTS:Name="Descr iption"></
DTS:Property><D TS:Property DTS:Name="Creat ionName"></DTS:Property></
DTS:FlatFileCol umn><DTS:Proper ty DTS:Name="Conne ctionString">.t xt</
DTS:Property></DTS:ConnectionM anager></DTS:ObjectData> </
DTS:ConnectionM anager>
<DTS:Connection Manager><DTS:Pr operty DTS:Name="Delay Validation">0</
DTS:Property><D TS:Property DTS:Name="Objec tName">Jobs database</
DTS:Property><D TS:Property DTS:Name="DTSID ">{be31a102-14c7-4391-
ba7e-3f96b098f440}</DTS:Property><D TS:Property
DTS:Name="Descr iption"></DTS:Property><D TS:Property
DTS:Name="Creat ionName">OLEDB</
DTS:Property><D TS:ObjectData>< DTS:ConnectionM anager><DTS:Pro perty
DTS:Name="Retai n">0</DTS:Property><D TS:Property
DTS:Name="Conne ctionString">Da ta Source=crm-db1-srvr;Initial
Catalog=Jobs200 708_DB1;Provide r=SQLNCLI.1;Int egrated
Security=SSPI;A uto Translate=false ;</DTS:Property></
DTS:ConnectionM anager></DTS:ObjectData> </DTS:ConnectionM anager>
<DTS:PackageVar iable><DTS:Prop erty DTS:Name="Packa geVariableValue "
DTS:DataType="8 ">&lt;Packa ge xmlns:xsd="http ://www.w3.org/2001/
XMLSchema" xmlns:xsi="http ://www.w3.org/2001/XMLSchema-instance"
xmlns:dwd="http ://schemas.microso ft.com/DataWarehouse/Designer/
1.0"&gt;&lt;dwd :DtsControlFlow Diagram&gt;&lt; dwd:BoundingLef t&gt;-3439&lt;/
dwd:BoundingLef t&gt;&lt;dwd:Bo undingTop&gt;-7224&lt;/
dwd:BoundingTop &gt;&lt;dwd:Lay out&gt;&lt;dds& gt;
&lt;diagram fontclsid="{0BE 35203-8F91-11CE-9DE3-00AA004BB851}"
mouseiconclsid= "{0BE35204-8F91-11CE-9DE3-00AA004BB851}"
defaultlayout=" Microsoft.DataW arehouse.Layout .GraphLayout"
defaultlinerout e="Microsoft.Da taWarehouse.Lay out.GraphLayout "
version="7" nextobject="13" scale="100" pagebreakanchor x="0"
pagebreakanchor y="0" pagebreaksizex= "0" pagebreaksizey= "0"
scrollleft="-8835" scrolltop="-9494" gridx="150" gridy="150"
marginx="1000" marginy="1000" zoom="100" x="14975" y="12065"
backcolor="1533 4399" defaultpersiste nce="2" PrintPageNumber sMode="3"
PrintMarginTop= "0" PrintMarginBott om="635" PrintMarginLeft ="0"
PrintMarginRigh t="0" marqueeselectio nmode="1" mousepointer="0 "
snaptogrid="0" autotypeannotat ion="1" showscrollbars= "0"
viewpagebreaks= "0" donotforceconne ctorsbehindshap es="1"
backpictureclsi d="{00000000-0000-0000-0000-000000000000}"& gt;
&lt;font&gt;
&lt;ddsxmlobjec tstreamwrapper
binary="0100000 090014442010006 5461686f6d61" /&gt;
&lt;/font&gt;
&lt;mouseicon&g t;
&lt;ddsxmlobjec tstreamwrapper binary="6c74000 000000000" /&gt;
&lt;/mouseicon&gt;
&lt;/diagram&gt;
&lt;layoutmanag er&gt;
&lt;ddsxmlob j /&gt;
&lt;/layoutmanager&g t;
&lt;ddscontr ol controlprogid=" DdsShapes.DdsOb jectManagedBrid ge.1"
tooltip="Execut e SQL Task" left="-3439" top="-7224" logicalid="3"
controlid="3" masterid="0" hint1="0" hint2="0" width="3598"
height="1164" noresize="0" nomove="0" nodefaultattach points="0"
autodrag="1" usedefaultiddsh ape="1" selectable="1"
showselectionha ndles="1" allownudging="1 " isannotation="0 "
dontautolayout= "0" groupcollapsed= "0" tabstop="1" visible="1"
snaptogrid="0"& gt;
&lt;control& gt;
&lt;ddsxmlobjec tstreaminitwrap per
binary="0008000 00e0e00008c0400 00" /&gt;
&lt;/control&gt;
&lt;layoutobjec t&gt;
&lt;ddsxmlobj&g t;
&lt;property name="LogicalOb ject"
value="{0a47ae8 a-4526-4e42-9b70-bcbdbf1ad690}" vartype="8" /&gt;
&lt;property name="ShowConne ctorSource" 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:Persiste dViewPortLeft&g t;-8835&lt;/
dwd:PersistedVi ewPortLeft&gt;& lt;dwd:Persiste dViewPortTop&gt ;-9494&lt;/
dwd:PersistedVi ewPortTop&gt;&l t;/dwd:DtsControlF lowDiagram&gt;& lt;/
Package&gt;</DTS:Property><D TS:Property DTS:Name="Names pace">dts-
designer-1.0</DTS:Property><D TS:Property
DTS:Name="Objec tName">{7726A7F B-668B-4BE7-8D04-8C0A65E18ABD}</
DTS:Property><D TS:Property DTS:Name="DTSID ">{98EA60BE-D46C-477A-
A965-5EE55AFBD0E3}</DTS:Property><D TS:Property
DTS:Name="Descr iption"></DTS:Property><D TS:Property
DTS:Name="Creat ionName"></DTS:Property></
DTS:PackageVari able><DTS:Prope rty DTS:Name="Force ExecValue">0</
DTS:Property><D TS:Property DTS:Name="ExecV alue" DTS:DataType="3 ">0</
DTS:Property><D TS:Property DTS:Name="Force ExecutionResult ">-1</
DTS:Property><D TS:Property DTS:Name="Disab led">0</
DTS:Property><D TS:Property DTS:Name="FailP ackageOnFailure ">0</
DTS:Property><D TS:Property DTS:Name="FailP arentOnFailure" >0</
DTS:Property><D TS:Property DTS:Name="MaxEr rorCount">1</
DTS:Property><D TS:Property DTS:Name="ISOLe vel">1048576</
DTS:Property><D TS:Property DTS:Name="Local eID">1033</
DTS:Property><D TS:Property DTS:Name="Trans actionOption">1 </
DTS:Property><D TS:Property DTS:Name="Delay Validation">0</DTS:Property>
<DTS:Variable>< DTS:Property DTS:Name="Expre ssion"></
DTS:Property><D TS:Property DTS:Name="Evalu ateAsExpression ">0</
DTS:Property><D TS:Property DTS:Name="Names pace">User</
DTS:Property><D TS:Property DTS:Name="ReadO nly">0</
DTS:Property><D TS:Property DTS:Name="Raise ChangedEvent">0 </
DTS:Property><D TS:VariableValu e DTS:DataType="8 "></
DTS:VariableVal ue><DTS:Propert y DTS:Name="Objec tName">FileDest ination</
DTS:Property><D TS:Property DTS:Name="DTSID ">{52B271C5-
F5D8-4933-9924-879F48A49746}</DTS:Property><D TS:Property
DTS:Name="Descr iption"></DTS:Property><D TS:Property
DTS:Name="Creat ionName"></DTS:Property></DTS:Variable>
<DTS:Variable>< DTS:Property DTS:Name="Expre ssion"></
DTS:Property><D TS:Property DTS:Name="Evalu ateAsExpression ">0</
DTS:Property><D TS:Property DTS:Name="Names pace">User</
DTS:Property><D TS:Property DTS:Name="ReadO nly">0</
DTS:Property><D TS:Property DTS:Name="Raise ChangedEvent">0 </
DTS:Property><D TS:VariableValu e DTS:DataSubType ="ManagedSerial izable"
DTS:DataType="1 3"><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.microso ft.com/soap/encoding/clr/1.0" SOAP-
ENV:encodingSty le="http://schemas.xmlsoap .org/soap/encoding/">
<SOAP-ENV:Body>
<xsd:anyType id="ref-1">
</xsd:anyType>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope></DTS:VariableVal ue><DTS:Propert y
DTS:Name="Objec tName">ResultSe t</DTS:Property><D TS:Property
DTS:Name="DTSID ">{E92B650D-6D44-4A5C-80A0-B38D82E72C5C}</
DTS:Property><D TS:Property DTS:Name="Descr iption"></
DTS:Property><D TS:Property DTS:Name="Creat ionName"></DTS:Property></
DTS:Variable>
<DTS:Variable>< DTS:Property DTS:Name="Expre ssion"></
DTS:Property><D TS:Property DTS:Name="Evalu ateAsExpression ">0</
DTS:Property><D TS:Property DTS:Name="Names pace">User</
DTS:Property><D TS:Property DTS:Name="ReadO nly">0</
DTS:Property><D TS:Property DTS:Name="Raise ChangedEvent">0 </
DTS:Property><D TS:VariableValu e
DTS:DataType="8 ">tbl98647kghFo odLionOffers</
DTS:VariableVal ue><DTS:Propert y DTS:Name="Objec tName">tblName</
DTS:Property><D TS:Property
DTS:Name="DTSID ">{DC5442C8-2CB3-4640-88FC-091B54D8CA17}</
DTS:Property><D TS:Property DTS:Name="Descr iption"></
DTS:Property><D TS:Property DTS:Name="Creat ionName"></DTS:Property></
DTS:Variable>
<DTS:Variable>< DTS:Property DTS:Name="Expre ssion"></
DTS:Property><D TS:Property DTS:Name="Evalu ateAsExpression ">0</
DTS:Property><D TS:Property DTS:Name="Names pace">User</
DTS:Property><D TS:Property DTS:Name="ReadO nly">0</
DTS:Property><D TS:Property DTS:Name="Raise ChangedEvent">0 </
DTS:Property><D TS:VariableValu e DTS:DataType="8 "></
DTS:VariableVal ue><DTS:Propert y DTS:Name="Objec tName">Variable 1</
DTS:Property><D TS:Property DTS:Name="DTSID ">{82218A9F-19FC-431A-ACF5-
B3DE233C3D0E}</DTS:Property><D TS:Property DTS:Name="Descr iption"></
DTS:Property><D TS:Property DTS:Name="Creat ionName"></DTS:Property></
DTS:Variable>
<DTS:LoggingOpt ions><DTS:Prope rty DTS:Name="Loggi ngMode">0</
DTS:Property><D TS:Property DTS:Name="Filte rKind">1</
DTS:Property><D TS:Property DTS:Name="Event Filter" DTS:DataType="8 "></
DTS:Property></DTS:LoggingOpti ons>
<DTS:Executab le
DTS:ExecutableT ype="Microsoft. SqlServer.Dts.T asks.ExecuteSQL Task.ExecuteSQL Task,
Microsoft.SqlSe rver.SQLTask, Version=9.0.242 .0, Culture=neutral ,
PublicKeyToken= 89845dcd8080cc9 1" DTS:ThreadHint= "0"><DTS:Proper ty
DTS:Name="Execu tionLocation">0 </DTS:Property><D TS:Property
DTS:Name="Execu tionAddress"></DTS:Property><D TS:Property
DTS:Name="TaskC ontact">Execute SQL Task; Microsoft Corporation;
Microsoft SQL Server v9; © 2004 Microsoft Corporation; All Rights
Reserved;http://www.microsoft.c om/sql/support/default.asp;1</
DTS:Property><D TS:Property DTS:Name="Force ExecValue">0</
DTS:Property><D TS:Property DTS:Name="ExecV alue" DTS:DataType="3 ">0</
DTS:Property><D TS:Property DTS:Name="Force ExecutionResult ">-1</
DTS:Property><D TS:Property DTS:Name="Disab led">0</
DTS:Property><D TS:Property DTS:Name="FailP ackageOnFailure ">0</
DTS:Property><D TS:Property DTS:Name="FailP arentOnFailure" >0</
DTS:Property><D TS:Property DTS:Name="MaxEr rorCount">1</
DTS:Property><D TS:Property DTS:Name="ISOLe vel">1048576</
DTS:Property><D TS:Property DTS:Name="Local eID">-1</
DTS:Property><D TS:Property DTS:Name="Trans actionOption">1 </
DTS:Property><D TS:Property DTS:Name="Delay Validation">0</DTS:Property>
<DTS:LoggingOpt ions><DTS:Prope rty DTS:Name="Loggi ngMode">0</
DTS:Property><D TS:Property DTS:Name="Filte rKind">1</
DTS:Property><D TS:Property DTS:Name="Event Filter" DTS:DataType="8 "></
DTS:Property></DTS:LoggingOpti ons><DTS:Proper ty
DTS:Name="Objec tName">Get the Column names from the lookup table</
DTS:Property><D TS:Property DTS:Name="DTSID ">{0a47ae8a-4526-4e42-9b70-
bcbdbf1ad690}</DTS:Property><D TS:Property
DTS:Name="Descr iption">Execute SQL Task</DTS:Property><D TS:Property
DTS:Name="Creat ionName">Micros oft.SqlServer.D ts.Tasks.Execut eSQLTask.Execut eSQLTask,
Microsoft.SqlSe rver.SQLTask, Version=9.0.242 .0, Culture=neutral ,
PublicKeyToken= 89845dcd8080cc9 1</DTS:Property><D TS:Property
DTS:Name="Disab leEventHandlers ">0</
DTS:Property><D TS:ObjectData>< SQLTask:SqlTask Data
SQLTask:Connect ion="{be31a102-14c7-4391-ba7e-3f96b098f440}"
SQLTask:TimeOut ="0" SQLTask:IsStore dProc="False"
SQLTask:SqlStmt SourceType="Dir ectInput"
SQLTask:SqlStat ementSource="Ex ec ('select dbo.fntrim(colu mn_name) as
col from information_sch ema.columns&#xA ;where table_name =''' +?+'''
and Column_name &lt;&gt;''Clien tlocid'''&#xA;) "
SQLTask:ResultT ype="ResultSetT ype_Rowset"
xmlns:SQLTask=" www.microsoft.c om/sqlserver/dts/tasks/
sqltask"><SQLTa sk:ResultBindin g SQLTask:ResultN ame="0"
SQLTask:DtsVari ableName="User: :ResultSet"/><SQLTask:Param eterBinding
SQLTask:Paramet erName="0" SQLTask:DtsVari ableName="User: :tblName"
SQLTask:Paramet erDirection="In put" SQLTask:DataTyp e="129"/></
SQLTask:SqlTask Data></DTS:ObjectData> </DTS:Executable> <DTS:Property
DTS:Name="Objec tName">Package2 4</DTS:Property><D TS:Property
DTS:Name="DTSID ">{7726A7FB-668B-4BE7-8D04-8C0A65E18ABD}</
DTS:Property><D TS:Property DTS:Name="Descr iption"></
DTS:Property><D TS:Property DTS:Name="Creat ionName">MSDTS. Package.1</
DTS:Property><D TS:Property DTS:Name="Disab leEventHandlers ">0</
DTS:Property></DTS:Executable>

Thank you so much!!!

Geetha

[Erland Sommarskog]

Re: ssis EXEC command

(gelangov@hotma il.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(colu mn_name) as
col from information_sch ema.columns&#xA ;where table_name =''' +?+'''
and Column_name &lt;&gt;''Clien tlocid'''&#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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[gelangov@hotmail.com]

Re: ssis EXEC command

On Aug 23, 5:42 pm, Erland Sommarskog <esq...@sommars kog.sewrote:

(gelan...@hotma il.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(colu mn_name) as
col from information_sch ema.columns&#xA ;where table_name =''' +?+'''
and Column_name &lt;&gt;''Clien tlocid'''&#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, esq...@sommarsk og.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/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]

Re: ssis EXEC command

(gelangov@hotma il.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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[John Heimiller]

Re: ssis EXEC command

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 IsStoredProcedu re 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]

Re: ssis EXEC command

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]

Re: ssis EXEC command

John Heimiller (jheimiller@sta rkinvestments.c om) 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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx