July 28, 2011 at 11:12 am
Hi,
I posted a similar question in another forum but received no responses. Possibly because no one has an answer there, so I am trying my luck here.
If I execute either of these statements in an Execute SQL Task, the results are successful:
CREATE TABLE #TEMP (Result INT)
INSERT INTO #TEMP (Result)
VALUES (2)
OR
DECLARE @Variable TABLE
(Result INT)
INSERT INTO @Variable (Result)
VALUES(2)
BUT, if I substitute the actual value with the SSIS Variable like this:
CREATE TABLE #TEMP (Result INT)
INSERT INTO #TEMP (Result)
VALUES (?)
OR
DECLARE @Variable TABLE
(Result INT)
INSERT INTO @Variable (Result)
VALUES(?)
I get the following error:
[Execute SQL Task] Error: Executing the query "CREATE TABLE #TEMP (Result INT)
INSERT INTO #TEMP ..." 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.
If I use an existing physical table on the server and execute something like:
INSERT INTO Variable (Result)
VALUES (?)
Then the issue goes away.
I believe there are apparent limitations on the use of SSIS variables within the scope of the SQL Task component but a confirmation of this would be appreciated.
July 28, 2011 at 11:39 am
I'd contact MS about that one. Sounds like a bug to me.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 28, 2011 at 11:53 am
Jamie Thompson wrote a blog post several years back on this:
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply