Unix Technical Forum

ssis EXEC command

This is a discussion on ssis EXEC command within the SQL Server forums, part of the Microsoft SQL Server category; --> I have 2 variables, one is the column name of the table and the other one is the table ...


Go Back   Unix Technical Forum > Database Server Software > Microsoft SQL Server > SQL Server

ssis EXEC command ssis EXEC command

FAQ Members List Calendar Search Today's Posts Mark Forums Read

Reply
Page 1 of 2 1 2 >

 

LinkBack Thread Tools Display Modes
  #1 (permalink)  
Old 03-01-2008, 03:40 PM
gelangov@hotmail.com
 
Posts: n/a
Default ssis EXEC command
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

Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #2 (permalink)  
Old 03-01-2008, 03:40 PM
Erland Sommarskog
 
Posts: n/a
Default Re: ssis EXEC command
(gelangov@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, esquel@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
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #3 (permalink)  
Old 03-01-2008, 03:40 PM
gelangov@hotmail.com
 
Posts: n/a
Default Re: ssis EXEC command
On Aug 21, 5:24 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> (gelan...@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, esq...@sommarskog.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

Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #4 (permalink)  
Old 03-01-2008, 03:40 PM
Erland Sommarskog
 
Posts: n/a
Default Re: ssis EXEC command
(gelangov@hotmail.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, esquel@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
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #5 (permalink)  
Old 03-01-2008, 03:40 PM
gelangov@hotmail.com
 
Posts: n/a
Default Re: ssis EXEC command
On Aug 22, 5:40 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> (gelan...@hotmail.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...@sommarskog.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.

Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #6 (permalink)  
Old 03-01-2008, 03:40 PM
Erland Sommarskog
 
Posts: n/a
Default Re: ssis EXEC command
(gelangov@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, esquel@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
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #7 (permalink)  
Old 03-01-2008, 03:40 PM
gelangov@hotmail.com
 
Posts: n/a
Default Re: ssis EXEC command
On Aug 23, 11:18 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
> (gelan...@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, esq...@sommarskog.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"/
&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;/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

Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #8 (permalink)  
Old 03-01-2008, 03:40 PM
Erland Sommarskog
 
Posts: n/a
Default Re: ssis EXEC command
(gelangov@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, esquel@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
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #9 (permalink)  
Old 03-01-2008, 03:40 PM
gelangov@hotmail.com
 
Posts: n/a
Default Re: ssis EXEC command
On Aug 23, 5:42 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> (gelan...@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, esq...@sommarskog.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.

Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #10 (permalink)  
Old 03-01-2008, 03:40 PM
Erland Sommarskog
 
Posts: n/a
Default Re: ssis EXEC command
(gelangov@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, esquel@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
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
Reply
Page 1 of 2 1 2 >

« Problem Connect to Reporting Services via Mgmt Studio | sql server 2008 »

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
Trackbacks are On
Pingbacks are On
Refbacks are On
Forum Jump


All times are GMT. The time now is 07:16 AM.

Contact Us - Unix Technical Forums - Top

Powered by vBulletin® Version 3.6.5
Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
Search Engine Optimization by vBSEO 3.2.0
www.UnixAdminTalk.com