Variable Cannot Be Bound in SSIS Package

Question

Variable Cannot Be Bound in SSIS Package

bkmooney

SSCrazy

Points: 2041
More actions
August 3, 2012 at 2:12 pm

#265307

Hey All,
I am trying to insert a normalization procedure into SSIS package so that when I load from the csv file, it checks to see whether the rows are already normalized, and if not, normalizes them.
Problem is, code that seems to work in the SQL Mgmt Studio is throwing errors when I insert it into an "Execute SQL Task" object in the SSIS package.
Can anyone see (in the code below) why I might be getting the errors: S.ObjectID and S.Ddate cannot be bound when this executes from within the SSIS package?
I feel like a dummy for not being able to get this, but maybe a fresh pair of eyes will do worlds of good here. Obviously this error fails the whole package from that point on, so troubleshooting forward this point has yet to even start.
Any and all help is greatly appreciated!
Thanks in advance,
Brigid =)
SELECT D.ObjectID, D.Ddate, D.MinBatchDate
, D.YestValue_SA / A.YestValue AS RMult
INTO #tempRMult
FROM
(
SELECT S.ObjectID, S.Ddate, S.YestValue_SA AS YestValue_SA, M.MinBatchDate AS MinBatchDate
FROM #tempSArows S
LEFT JOIN (SELECT ObjectID, MIN(Ddate) AS MinBatchDate FROM #tempSArows GROUP BY ObjectID) AS M
ON S.ObjectID = M.ObjectID AND S.Ddate = M.MinBatchDate
) D
LEFT JOIN tblHist_SPX_Act A
ON D.ObjectID = A.ObjectID AND D.Ddate = A.Ddate
WHERE D.MinBatchDate IS NOT NULL
AND A.YestValue IS NOT NULL AND D.YestValue_SA IS NOT NULL
AND A.YestValue<> 0 AND D.YestValue_SA <> 0

Viewing 3 posts - 1 through 2 (of 2 total)

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

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 1

I'm a bit of an SSIS n00b but I'm okay with temporary tables, so you'll have to excuse me if I'm totally wrong - but I believe your problem might be the # table you're pulling from (aliased as 'S').

#temp tables are valid only in the session you are executing from. If you try and select from a table that doesn't exist using an alias then you'll get the 'cannot be bound' message.

Ergo logically it seems that SSIS is not using one session for the task execution workflow. When I look over your code I see that your # table isn't defined (the two # table names given differ) in the same code snippet.

Could it be that one part of your control/data flow in SSIS is setting up your source #temp table, only for it to be binned by the time your SELECT rolls around?

EDIT: (Sort of) verified by Phil Factor:

The classic temporary table comes in two flavors, the Global, or shareable, temporary table, prefixed by ‘##’, and the local temporary table, whose name is prefixed with ‘#’.The local temporary tables are less like normal tables than the Global temporary tables: You cannot create views on them, or associate triggers with them. It is a bit tricky to work out which process, session or procedure created them. We’ll give you a bit of help with that later. Most importantly, they are more secure than a global temporary table as only the owning process can see it.

Another oddity of the local temporary table (and the local temporary stored procedure) is that it has a different name in the metadata to the one you give it in your routine or batch. If the same routine is executed simultaneously by several processes, the Database Engine needs to be able to distinguish between the identically-named local temporary tables created by the different processes. It does this by adding a numeric string to each local temporary table name left-padded by underscore characters. Although you specify the short name such as #MyTempTable, what is actually stored in TempDB is made up of the table name specified in the CREATE TABLE statement and the suffix. Because of this suffix, local temporary table names must be 116 characters or less.

Source: http://www.simple-talk.com/sql/t-sql-programming/temporary-tables-in-sql-server/

It would be very interesting to see if you got the same result using a temporary table or a table variable (@table_name) instead. Can you test this?

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

bkmooney SSCrazy Points: 2041 More actions · Answer 2

I'm not 100% sure what resolved the problem, as I was changing a few things at once.

1 - I moved all of the SQL commands into a single Execute SQL task in the SSIS package.

2 - I replaced one of the temporary tables (there were actually 2 being used! - the example was only showing one) with a static table in the database that acts as a temporary table with all the rows cleared at the end of the SSIS package run.

3 - I fixed a couple typos... oops!

But the problem is resolved - so thanks so much for your help!