SSIS package variables not visable in Execute SQL Task
-
I am using SSIS 2005 and have variables defined at the package level. When I try to execute a query in an Execute SQL Task it doesn't recognize my variable. In fact the query won't even parse. Example:
variable name : V_ROW_CNT Int32 value=0
Sql Query: Select * from tablename where column_name > V_ROW_CNT
I also tried Select * from tablename where column_name > @V_ROW_CNT
and still the same problem.
I have hard time believing it is so hard to work with variables in SSIS. Why are they not visable in the other tasks?
-
You may want to take a look at some of the options outlined here. It shows how to use parameters in the query. Of course, if you really just want to build a string & execute that, then concat the string into a parameter and pass the parameter as the execute statement. There's another option I saw somewhere but I can't seem to track it down at the moment.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
-
Grant,
Thanks for your reply. I am following the SQLIS examples exactly and still the same results. My query will not parse or work with the ? as a parameter. I don't understand why I cannot reference my package variables in a query that is within a data flow task. I must be missing something??
-
The way you've got it typed up above, it looks like you're trying to parse the string directly in the SQLStatement. That doesn't work. You either have to use the parameters through the ? and the Parameter Mapping screen, or you have to set an Expression on the SQLStatementSource like this: "SELECT * FROM dbo.TestDate WHERE 1 =" + (DT_WSTR,4)@[User:
aramValue]It's just working in both instances. What kind of errors are you getting?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
-
Grant,
Thanks again for your reply. What I'm really trying to do is to capture the Row Count in a user variable using the Row Count transformation (seems easy enough) and at the end of the package I need to update a table with the Row Count I stored in the variable. The problem is I can't seem to reference the user variable in the Execute SQL task. I am somewhat new to SSIS and am having problems accessing the user variables later on in the package even though all my variables are at the package level.
-
Oh. That's a bit different. Hmmm... Have you tried debugging it? Stopping on the step that you're running & verifying that the variables are loaded? How about adding logging to watch when the variable changes after the fact? I'd still be interested in knowing if you're getting an error message or just bad data.
BTW, that little tongue thing up there was unintentional. I typed a parameter with the name ParamValue. Apparently if you put a colon & capital p next to each other it makes a face.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
-
The value of my row count variable is 0 for some reason. If I set up a data viewer right after my Row Count transformation, my row count variable isn't even available for me to look at? I don't understand that part. The variable is at the package level.
-
Sorry to say, I'm stumped. I tried replicating it, but I got it seemed to work. I'm just not sure what I'm missing. Sorry.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
-
Walter, I'm trying the exact same thing...saving a rowcount to a package variable. Have you found a solution?
-
I've never done this. But wouldn't your t-sql be select @mycount = count(*) from etc... And then you'd map the @mycount to a parameter?? I'm assuming you are using sql server and an ado.net provider
Cheers
-
Hi,
Create one variable ( Rcount int )and ado.net db connection. Drag the execute sql task and set the connection type as ADo.net and choose the the above created connection and write the sql statement as per the requirement.
for example.
select * from table1 where sno>@cnt
In parameter mapping choose the above created (Rcount) user variable and give the name as @cnt in Paramter name column. Based on the data just define the result set as single/full result etc.
Then Parse it . I have done it in so many packages. This is the way using ADO.Net connection.
Regards,
V
-
can u guide wht is sqlis
-
Define @V_ROW_CNT in the Parameter Mapping section of the Execute SQL Task.
Then write the query like this:
Select * from tablename where column_name > ?
The "?" represents the variable defined in the parameter mapping.
-
I am having a similar issue...but just can't get this too work. Why is SSIS so frustrating? To read about how to do something and then actually trying to get it to work is so freaking hard! Then when you actually see it done you feel like a total boob for not realizing how it should have been done to begin with...
I have the following query in my execute SQL task:
UPDATE _ReadyTables
SET Loaded = 1
WHERE [Table] = ?
AND ETLDate = (
SELECT ETLDate FROM dbo._ReadyTables
WHERE [Table] = ? AND Loaded = 0 )
I created a variable:
Variable Name: User::FL_PRICE
Direction: INPUT
Data Type: VARCHAR
Parameter Name: FileName
Parameter Size: 50
All I'm trying to do is update a simple table, setting the value to 1 for each file name in the ForEach Loop container.
The error:
SSIS package "F-Tables.dtsx" starting.
Error: 0xC002F210 at FL-PRICE loaded, Execute SQL Task: Executing the query "UPDATE _ReadyTables
SET Loaded = 1
WHERE [Table] ..." failed with the following error: "Parameter Information cannot be derived from SQL statements with sub-select queries. Set parameter information before preparing command.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: FL-PRICE loaded
Warning: 0x80019002 at DELETE Files: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "F-Tables.dtsx" finished: Failure.
What am I doing wrong???
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
-
Just to confirm, you have the pre-check option (don't have it in front of me) turned to false, correct?
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply