dbTalk Databases Forums  

ssis EXEC command

Go Back dbTalk Databases Forums Databases comp.databases.ms-sqlserver ssis EXEC command

comp.databases.ms-sqlserver comp.databases.ms-sqlserver


Discuss ssis EXEC command in the comp.databases.ms-sqlserver forum.



Reply
Page 1 of 2 1 2 >
 
Thread Tools Display Modes
  #1  
Old   
gelangov@hotmail.com
 
Posts: n/a

Default ssis EXEC command - 08-21-2007 , 10:38 AM





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


Reply With Quote
  #2  
Old   
Erland Sommarskog
 
Posts: n/a

Default Re: ssis EXEC command - 08-21-2007 , 04:24 PM





(gelangov (AT) hotmail (DOT) com) writes:
Quote:
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 (AT) sommarskog (DOT) 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


Reply With Quote
  #3  
Old   
gelangov@hotmail.com
 
Posts: n/a

Default Re: ssis EXEC command - 08-22-2007 , 07:22 AM


On Aug 21, 5:24 pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Quote:
(gelan... (AT) hotmail (DOT) 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, esq... (AT) sommarskog (DOT) se

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- Hide quoted text -

- Show quoted text -
Erland,

I think I did not write it clearly...May be I should have written it
like this:
Truncate table table1
Insert into table1
Exec (' select [' + ?+ '] from '+ ?)

The "?" are where the variables are. It works fine in the query
analyzer (of course, after changing the question mark to variable
names).

My question is:
Are there any settings I need to change in order for "Exec" to work in
SSIS?


Thank you,
Geetha



Reply With Quote
  #4  
Old   
Erland Sommarskog
 
Posts: n/a

Default Re: ssis EXEC command - 08-22-2007 , 04:40 PM


(gelangov (AT) hotmail (DOT) com) writes:
Quote:
I think I did not write it clearly...May be I should have written it
like this:
Truncate table table1
Insert into table1
Exec (' select [' + ?+ '] from '+ ?)

The "?" are where the variables are. It works fine in the query
analyzer (of course, after changing the question mark to variable
names).

My question is:
Are there any settings I need to change in order for "Exec" to work in
SSIS?
I can't answer any questions about SSIS per se, since I don't know SSIS. But
I believe that SSIS is essentially just another client application as far as
the engine, and since I know the engine and some client programming, I
might be able to provide some guessworks if you post the SSIS code
where you invoke the batch above.

Of course, asking the friendly people in microsoft.public.sqlserver.dts
may also be a good idea - they are likely to produce more than guessworks.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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


Reply With Quote
  #5  
Old   
gelangov@hotmail.com
 
Posts: n/a

Default Re: ssis EXEC command - 08-23-2007 , 09:12 AM


On Aug 22, 5:40 pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Quote:
(gelan... (AT) hotmail (DOT) com) writes:
I think I did not write it clearly...May be I should have written it
like this:
Truncate table table1
Insert into table1
Exec (' select [' + ?+ '] from '+ ?)

The "?" are where the variables are. It works fine in the query
analyzer (of course, after changing the question mark to variable
names).

My question is:
Are there any settings I need to change in order for "Exec" to work in
SSIS?

I can't answer any questions about SSIS per se, since I don't know SSIS. But
I believe that SSIS is essentially just another client application as far as
the engine, and since I know the engine and some client programming, I
might be able to provide some guessworks if you post the SSIS code
where you invoke the batch above.

Of course, asking the friendly people in microsoft.public.sqlserver.dts
may also be a good idea - they are likely to produce more than guessworks.

--
Erland Sommarskog, SQL Server MVP, esq... (AT) sommarskog (DOT) se

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,

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.

Geetha.



Reply With Quote
  #6  
Old   
Erland Sommarskog
 
Posts: n/a

Default Re: ssis EXEC command - 08-23-2007 , 10:18 AM


(gelangov (AT) hotmail (DOT) com) writes:
Quote:
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 (AT) sommarskog (DOT) 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


Reply With Quote
  #7  
Old   
gelangov@hotmail.com
 
Posts: n/a

Default Re: ssis EXEC command - 08-23-2007 , 01:05 PM


On Aug 23, 11:18 am, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Quote:
(gelan... (AT) hotmail (DOT) 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... (AT) sommarskog (DOT) se

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 xmlnsTS="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" DTSataType="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"
DTSataType="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;dwdtsControlFlowDiagram&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"/
Quote:
&lt;/ddsxmlobj
&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;/dwdtsControlFlowDiagram&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" DTSataType="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 DTSataType="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 DTSataSubType="ManagedSerializable"
DTSataType="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
DTSataType="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 DTSataType="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" DTSataType="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" DTSataType="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" DTSataType="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"
SQLTasktsVariableName="User::ResultSet"/><SQLTask:ParameterBinding
SQLTask:ParameterName="0" SQLTasktsVariableName="User::tblName"
SQLTask:ParameterDirection="Input" SQLTaskataType="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



Reply With Quote
  #8  
Old   
Erland Sommarskog
 
Posts: n/a

Default Re: ssis EXEC command - 08-23-2007 , 04:42 PM


(gelangov (AT) hotmail (DOT) com) writes:
Quote:
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, esquel (AT) sommarskog (DOT) 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


Reply With Quote
  #9  
Old   
gelangov@hotmail.com
 
Posts: n/a

Default Re: ssis EXEC command - 08-24-2007 , 07:25 AM


On Aug 23, 5:42 pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Quote:
(gelan... (AT) hotmail (DOT) 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;>''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, esq... (AT) sommarskog (DOT) se

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.



Reply With Quote
  #10  
Old   
Erland Sommarskog
 
Posts: n/a

Default Re: ssis EXEC command - 08-24-2007 , 04:21 PM


(gelangov (AT) hotmail (DOT) com) writes:
Quote:
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.

Quote:
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 (AT) sommarskog (DOT) 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


Reply With Quote
Reply
Page 1 of 2 1 2 >

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top