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
Insert into
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
Re: ssis EXEC command
am 21.08.2007 23:24:33 von Erland Sommarskog
(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
> Insert into
> 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/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
Re: ssis EXEC command
am 22.08.2007 14:22:58 von gelangov
On Aug 21, 5:24 pm, Erland Sommarskog 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
> > Insert into
> > 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/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.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
Re: ssis EXEC command
am 22.08.2007 23:40:30 von Erland Sommarskog
(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/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
Re: ssis EXEC command
am 23.08.2007 16:12:44 von gelangov
On Aug 22, 5:40 pm, Erland Sommarskog 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/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.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.
Re: ssis EXEC command
am 23.08.2007 17:18:24 von Erland Sommarskog
(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/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
Re: ssis EXEC command
am 23.08.2007 20:05:27 von gelangov
On Aug 23, 11:18 am, Erland Sommarskog 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/prodt=
echnol/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:
(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
where table_name =''' +?+'''
and Column_name <>''Clientlocid'''
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/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
Re: ssis EXEC command
am 24.08.2007 14:25:54 von gelangov
On Aug 23, 5:42 pm, Erland Sommarskog 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
where table_name =''' +?+'''
> and Column_name <>''Clientlocid'''
>
> 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/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.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.
Re: ssis EXEC command
am 24.08.2007 23:21:04 von Erland Sommarskog
(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/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
Re: ssis EXEC command
am 27.08.2007 19:49:26 von John Heimiller
I'm having the same original problem...can someone else besides Erland
comment on the issue. Erland, with all due respect, I don't think you
understand the original question. It is an SSIS question and you have
stated you "don't know SSIS per se".
Basically in SSIS, using the OLE connection, it will not parse:
INSERT TABLENAME EXEC STORED_PROC
This syntax is fine in query analyzer.
it has nothing to do with the "?" parmamters.
What we want to know is what SSIS settings (if there is any) that will
allow SSIS to parse this as good SQL?
I've tried changing BypassPrepare and IsStoredProcedure but no luck. I
could write a stored procedure that has this code and make it work like
the original poster did as a test...but I would rather have the
INSERT...EXEC...syntax.
I am going to try the ADO connector to see if that parses it right.
*** Sent via Developersdex http://www.developersdex.com ***
Re: ssis EXEC command
am 27.08.2007 22:36:24 von John Heimiller
Note: the ADO.NET adapter parses the SQL fine....I suggest switching
from the OLE unless your platforms don't support the ADO adapter.
One thing, you don't use "?" for parameters but actual variable names
with @...like @parm1. You will also need to re-add them as parameters in
SSIS after you change the connector to ADO.NET.
Hope that makes sense.
Would still like an answer to the SQL SERVER OLE connector not parsing
INSERT TABLE EXEC STOREDPROC
*** Sent via Developersdex http://www.developersdex.com ***
Re: ssis EXEC command
am 27.08.2007 23:58:29 von Erland Sommarskog
John Heimiller (jheimiller@starkinvestments.com) writes:
> I'm having the same original problem...can someone else besides Erland
> comment on the issue. Erland, with all due respect, I don't think you
> understand the original question. It is an SSIS question and you have
> stated you "don't know SSIS per se".
Hey, I may not know SSIS, but I do have experience of OLE DB, and
I doubt that SSIS performs any parsing of its own. So the problem
should appear about any code that uses OLE DB.
But you are right that I did not understand the question in full, but
I'm used to that: too many questions are posted with incomplete
information.
> I am going to try the ADO connector to see if that parses it right.
Ah, my bad. That much I know of SSIS that it can use either an OLE DB
provider or SqlClient, so I should have given that advice.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx