June 20, 2008 at 3:49 am
Hi All,
i am new to SSIS. i have problems in connecting to the sqlserver database and execute a query on it , inside a script task.
my requirement is - i hav to query a table . My required output from the query is , say 'X' . If the query doesnt return 'X' , i should wait for some specific time(say 5 min) and then have to query the table again . If i get the output as 'X' , then i should come out of the loop and proceed with rest of the steps.
can anyone help me ?
Thanks
June 20, 2008 at 5:10 am
uhmmmmm, why dont you have a SQL server agent job which checks for the data marked as 'X'
Then you can have it retry lets say 100 times.
If it finds it, then invoke the SSIS package
Good luck
~PD
June 20, 2008 at 5:45 am
You certainly do not need a script task for this.
Use a loop container and have it loop forever - or some pre-defined number of times.
In the loop container, add an Execute SQL task to run your query. Return the result set as a single-row result set and assign the value to a variable.
Now, go back to your loop expression and also have it break if the variable value is what you need it to be to continue through your package.
Next, add a second execute SQL task, attach it after your first Execute SQL task within the loop and have it execute a WAITFOR statement against your SQL Server.
Finally, in the control flow link between the two execute SQL tasks, change it to an expression and a constraint and have it not go to the WAITFOR execute SQL task if your variable has the value you are looking for (so it does not wait one last time).
I hope you followed all of that.
June 21, 2008 at 12:50 am
Michael,
can u elaborate more on this.
June 21, 2008 at 1:09 am
Michael,
As u said , i have put "FOR LOOP container" and two "Execute SQL task" inside this "FOR LOOP container" . i still get issues.
In the "FOR LOOP container", i have set the following :
InitExpression : @V_X = 0
EvalExpression : @V_X < 10
AssignExpression : @V_X = @V_X + 1
In the 1st "Execute sql task", i have set the following :
Under GENERAL:-
Resultant Set : Single row
Connection type : OLE_DB
Connection : My_Conn
SQL Statement : Select count(*) from dbo.country_lkp
Under Result Set:-
Result Name: NewResultName
Variable Name : User::V_Y
In the 2nd "Execute sql task", i have set the following :
Under GENERAL:-
Connection type : OLE_DB
Connection : My_Conn
SQL Statement : WAITFOR DELAY '00:00:30'
in the control flow link between the two execute SQL tasks, i have changed it to "expression and a constraint"
Value : Success
Expression : @V_Y > 0
If i execute this, i get the following error message :
Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "V_Y": "Unable to find column NewResultName in the result set.".
Task failed: Execute SQL Task
June 21, 2008 at 1:24 am
Mayby you have to check the security checking of your SSIS package. Put it on don'tsavesensisitive. But don't forget to put all the connections to databases on what was that again? automatic anyway no username.
:hehe:
June 21, 2008 at 3:11 am
I am not sure how you co-relate this issue with security check?
SSIS Masters, pls provide the solutions
June 23, 2008 at 5:03 am
[font="Courier New"]"Unable to find column NewResultName in the result set.".[/font]
Your result set name on the ExecuteSQL task you are using to set your variable should be "0", not "NewResultName".
June 23, 2008 at 5:20 am
Thanks Micheal. The issue got resolved.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply