Help needed on Ezecute SQL Task

Question

Help needed on Ezecute SQL Task

Peter Newman-311337

Old Hand

Points: 394
More actions
May 4, 2006 at 5:59 am

#172422

Im trying to do an insert statement using an execute SQL task but it keeps failing with the following error
failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Insert Header record
the insert statement is as follows

Insert into Table1
Select ? , a.field1, b.field2
from table3 as a, table 4 as b

the above is just a representation of the acutual SQL script, the ? is an paramater direction input

any clues as to where i have gomne wrong or the mistake ?

ta

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Peter Schott SSCrazy Eights Points: 9676 More actions · Answer 1

I seem to remember having the same issue and eventually got around it through a merge join and a derived column or something similar to convert my variable to a column I could use. That didn't seem right and I'm hoping someone has a better answer than my workaround.

-Pete Schott

Dan Murray Right there with Babe Points: 779 More actions · Answer 2

I'm not sure just what's happening but is it something like:

spProc @Var as varchar(X)

declare @Str as varchar(Y)

set @Str='Insert into Table1 select '+@var+' ', a.field1, b.field2 from table3 as a, table 4 as b'

sp_SqlExec @Str

????

Peter Newman-311337 Old Hand Points: 394 More actions · Answer 3

Dan thanks for the help, unfortunatly i havent managed to resolve the issue, instead im having to build the statement in a scrip task then parm it into the execute sal task... if anyone can show me how to convert this code to a T-SQL statement using input paramaters from global variables i would me eternally greatful. ok i know it doesnt look pretty, but hey it works

Dts.Variables(

"SqlStatement").Value = "INSERT Bossdata.dbo.INPUTReport_ERRORS " & _

" Select '" & CStr(Dts.Variables("LicenceNumber").Value) & "', '" & CStr(Dts.Variables("ReportNumber").Value) & "', a.erroritemtype, a.reference, a.transactioncode, a.currency, a.valueof, a.errorcode, " & _

" b.number AS 'ORIGAccNumber', b.ref AS 'ORIGAccReference', b.name as 'ORIGAccName', b.sortcode as 'ORIGAccSortCode', " & _

" c.number AS 'DESTAccNumber', c.name as 'DESTAccName', c.sortcode as 'DESTAccSortCode', c.type, " & _

" d.type as 'ErrorType' , d.messageline as 'ErrorMessage', a.error_Id " & _

" from [XML Processing].dbo.Temp_LInput_ErrorItem as a " & _

" FULL Join [XML Processing].dbo.Temp_LInput_ErrorItem_OriginatingAccount as b " & _

" on a.ErrorItem_Id = b.ErrorItem_Id " & _

" Full Join [XML Processing].dbo.Temp_LInput_ErrorItem_DestinationAccount as c " & _

" on a.ErrorItem_Id = c.ErrorItem_Id " & _

" Left Join [XML Processing].dbo.Temp_LInput_ErrorItem_ErrorMessage as d " & _

" on a.Error_Id = d.Error_Id " & _

" Order BY a.error_Id "

Peter Schott SSCrazy Eights Points: 9676 More actions · Answer 4

Peter, try to use a MergeJoin with a Derived Column transform in SSIS. That will probably be easier than trying to convert this using a script task.

Set up a Derived Column transform to derive two new columns from your Variables.

Merge that with your output from the query without the parameters.

You should then be able to map into your target the correct values from the source and your variables. It looks a little more complicated in the data flow, but should be easier than what you're trying above.

-Pete