Execute SQL Task Insert with parameter

Question

Post reply

Execute SQL Task Insert with parameter

omhoge

SSCertifiable

Points: 5416
More actions
December 22, 2006 at 12:09 pm

#118486

I'm trying to use a parameter in an insert statement, the task is using the project data source which seems to be an OLEDB connection.
Can anyone tell me why this would error? It's following the same method in all examples I found.
Thanks gang!
(please excuse if this is repetitive I did search this forum but did not get any results for this topic.)
================ execute sql task statement ===================
INSERT DatabaseName.dbo.Log(ReferenceNumber, JobName, Status, LogDateTime, Comments)
VALUES( ?, 'My Job, 'started', getdate(), 'My Job SSIS package started normally.' )
========================= parameter mapping ==================
User::BatchRunNumber | Input | Numeric | 0
Skål - jh

Viewing 11 posts - 1 through 10 (of 10 total)

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

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

What error are you getting?

You're missing a single quote at the end of 'MY Job'.

And then again, I might be wrong ...
David Webb

omhoge SSCertifiable Points: 5416 More actions · Answer 2

Here's the error below.

Sorry about that missing quote mark, that got deleted by mistake when I was cleaning up the code sample.

It worked before the pamarater version. This statement with the parameter did run as a test in the query builder, where it asks you to input the value manually as it runs the query.

thanks for taking a look...

=============================================

Error: 0xC002F210 at Execute SQL Log Start, Execute SQL Task: Executing the query "-- Log start in the monitor database

INSERT Monitor.dbo.BKS_Log(ReferenceNumber, JobName, Status, LogDateTime, Comments)

VALUES(?, 'BKS_Store_Data_ETL', 'started', getdate(), 'BKS_Store_Data_ETL SSIS package started normally.' )

" 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.

Skål - jh

Rich Hansell SSCrazy Points: 2346 More actions · Answer 3

How are you populating that "referencenumber" value? from another SQL task?

omhoge SSCertifiable Points: 5416 More actions · Answer 4

There is an execute SQL task that sets a user variable in the package having package level scope, that at least seems to happen, then in the parameter mapping I'm trying to use that local variable as the reference number. thanks!

Skål - jh

omhoge SSCertifiable Points: 5416 More actions · Answer 5

One down, a more involved one now....

Hey gang, finally got the simple insert to work. Tried lots of different things but the last thing that got it to work was trying every type of numeric in the mapping panel, LONG finally worked. I wish it was a little more self evident and explicitly documented as to which SSIS type works with which SQL type.

Now in the same package there is a second SQL Task, applying all the changes and that type fix has not gotten it to work. Without the "?" parameter it works, with it the same generic error as before greets me. the sql from the task is below.

Since this is hopefully my last working day this year, if the servers behave this weekend, I want to thank you all in this forum for the help and moral support. Here's wishing you all the best for 2007!

Ok back to the matrix...

============================= sql from SSIS task =========================

DECLARE @rowcount int

DECLARE @rowtext varchar(20)

SELECT @rowcount = COUNT(*) FROM BKS_Stores

SET @rowtext = CAST(@rowcount AS varchar(20))

INSERT Monitor.dbo.BKS_Log(ReferenceNumber, JobName, Status, LogDateTime, Comments)

VALUES( ?, 'BKS_Store_Data_ETL', 'ended', getdate(), 'BKS_Store_Data_ETL SSIS package ended normally. ' + @rowtext + ' rows written' )

Skål - jh

omhoge SSCertifiable Points: 5416 More actions · Answer 6

Well I'm still at this one. Haven't figured out why the changes that fixed the first one haven't worked here. Have seen mentions of using insert statments in the SQL task but so far those directions won't work. I did try using another SQL var.: Declare @BatchRunID int; SET @BatchRunID = ? ; but that produced the same error. Again this worked fine with out the ? parameter. Still stuck, any thoughts welcome. Happy 2007 y'all.

Skål - jh

omhoge SSCertifiable Points: 5416 More actions · Answer 7

Wellllll, so far it seems I can have variables, or I can have parameters, but I cannot have both.

Converting everything to package variables and replacing the SQL vars with parameters seems to be working.

Also any SQL comments at the head of the SQL statement micro-window editor sometimes also cause a problem. This sorta makes sense but it still feels odd that basic things in query analyzer are a problem in the micro-editor.

If anyone does find a way to combine sql vars and parameters please chime in, I'd still like to have that option.

thanks gang!

Skål - jh

Daniel Forrester 123 SSCrazy Points: 2216 More actions · Answer 8

Hi,

I got this to work. The most important step is to set the BypassPrepare option on the SQL Task to true else I get he error you described above.

Is this what you are trying to do?

Regards

Daniel

-------------------------------------

Test table

-------------------------------------

Create Table myTable

(

myField varchar(10)

)

-------------------------------------

SQL Task code

-------------------------------------

Declare @myParameter varchar(10)

Set @myParameter = ?

Insert Into myTable(myfield) Values(@myParameter)

-------------------------------------

omhoge SSCertifiable Points: 5416 More actions · Answer 9

Thanks Daniel!! will try it out as soon as I can, great tip.

Skål - jh

pat-261672 Hall of Fame Points: 3325 More actions · Answer 10

Hi

I tried that but got the error saying:

[Execute SQL Task] Error: Executing the query "declare @file nvarchar(255) set @file = ? insert into emailtemp(c1) values (?)" failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.