SEO

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

(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

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

(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

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.

(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

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

(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

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.

(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