September 30, 2010 at 6:59 pm
I am trying to build a variable that I can then loop through to do a number of table drops with a SQL Execute task. I build the ArrayList with a list of repeating items that look like:
ArrayList aList - new ArrayList();
aList.Add("drop table t.table1");
aList.Add("drop table t.table2") etc.
Then in the script I load the variable:
Dts.Variables["dropTablesSQL"].value = aList;
The above assignment fails. Believe it or not(!) it succeeded once and fell into the SQL Execute task and executed. After that it never worked. Is this an unresonable function to ask of SSIS?
October 1, 2010 at 1:27 am
What is the data type of dropTablesSQL?
Please post the error message.
October 1, 2010 at 8:14 am
Great question and one I should have clarified in my original post. I originally declared as an Object. Do so allows the ForLoop to work but then the SQL Exec task fails saying the variable is not a string. So I change it to a string in hope that the 'smarts' in SSIS interprets the string variable as an Array of strings. No good with that one, now the script before the exec task fails when I try to set the SISS var, with an error saying essentially telling me I can't change the SSIS var type (implying to an Array) from an Object. Hope I'm covering all bases here, I've spent 2 days trying to figure this one out since the MS documentation *seems to* indicate what I want to do is possible.
Thanks very much for your response!
October 1, 2010 at 8:31 am
The datatype of your array variable needs to be Object.
Once it has been populated, you can feed it into a Foreach loop.
Change the enumerator to 'Foreach From Variable'. The loop will execute once for each of your array items. Put an Execute SQL task in the FEL to execute the SQL (using the string from the array, as retrieved for you by the FEL.
October 1, 2010 at 9:36 am
Thanks Phil, but herein lies the problem. I did that very thing first time through (and many many times since, swearing at the screen each time it fails). When I do what you outline it fails, saying that the siss var I am using is not of type string (it's an object). Now on to the even stranger than fiction problem. In the last 2 days I have run this (after making a number of changes) and it has actually worked twice but after iterating through the ArrayList those 2 times the progress log first shows all drop taking place but with these entries from top down in log:
[Execute SQL Task] Error: "User::strTablesMatched" is not a string.
Validation is completed(74)
--> Start (73)
Progress: Executing query "drop table [t.name]". 100 percent complete
... runs through all tables and finishes.
I restore the tables and rerun the script and it never runs, throwing the same error. I just don't get it! runs once and won't run again.
October 1, 2010 at 11:52 am
I can now recreate the error at will so I thought I'd post here and give a workaround (maybe this is the way I should have done it from the beginning?) solution. First, if I run as configured (SISS var holding the ArrayList with SQL commands) typed as a String, Execute SQL Task fails. I go into Variables tab and changed type to Object, run of project Fails. I then go back into variables tab, change var to type String again and it runs once but still throws error previously noted.
Solution, insert a script before Exec SQL Task and take value from each iteration of the ForLoop, casting into a second SISS variable that is a string, using that as the input to the SQL task rather than the object var containing the ArrayList. As with all SISS issues, simple solution when one you finally realize something doesn't work as expected...
October 2, 2010 at 5:04 am
Going back to my suggested method, can you confirm that you had configured the variable mappings page for the FEL?
October 2, 2010 at 8:58 am
Yes, I sure can, I did set up the FEL early on in the manner you had mentioned. I played with the Loop parms extensively and never got it to work in the way I expected it to. Since I am new to SSIS I will freely admit it may be due to the way I think things work vs. the way they actually do. I would have expected that an SSIS var (take string array for example) could be stored in an object and without having to explicitly deal with what's in the object, any of the built-in facilities would do the intepretation for typeof for the programmer/user. Specifically (I think) what your note was indicating. That does not seem to be the case but the way I've solved the problem works, just seems a little esoteric... but then again I am finding SSIS to be that way. I am hoping the upcoming release for VS2010 solves some of these problems. I am struggling with another and will post another query on that one.
October 2, 2010 at 1:37 pm
OK, I've just mocked this up and it works.
First, I have two package-scoped variables:
DropTablesSQL - object
strSQL - string
Next, a script task with DropTablesSQL in ReadWrite variables:
Public Sub Main()
Dim aList As New ArrayList()
aList.Add("drop table t.table1")
aList.Add("drop table t.table2")
Dts.Variables("User::DropTablesSQL").Value = aList
Dts.TaskResult = ScriptResults.Success
End Sub
Then a FEL, Foreach from Variable enumerator, with DropTablesSQL as the variable and strSQL on the variable mappings page (index 0).
Within the FEL, another Script task with strSQL in ReadOnlyVariables. Here is the script:
Public Sub Main()
MsgBox(Dts.Variables("User::strSQL").Value)
Dts.TaskResult = ScriptResults.Success
End Sub
As expected, two message box prompts appear ...
Hopefully you will be able to work out where you went wrong.
Phil
October 4, 2010 at 6:30 am
Hi Phil,
Yep, I did the same small piece of code (but in C#) when the SQL Execute Task originally failed. The script works when popping up the Message box. What is failing is just going straight to the SQL Execute task (no interim script) and trying to loop through the same varialble that serves as the enumerator. This variable is an ArrayList of "drop table xxxx" commands. When passed through the SSIS variable (an object that contains the ArrayList) the FEL iterates once and the SQL Exec Task fails saying the variable is not a string ('officially' it's an object that the combo of the FEL and Foreach Loop *I would think* would cast members as strings). So as I mentioned, I have to do an intermit script step, setting a second var in that script to a string and setting it equal to the iterated value using .ToString() (of obj from the ArrayList), run the loop (script and SQL task reside in the FEL, the script falling into the Execute SQL task with each iteration) with the SQL task now taking the single string variable as it's parameter. That works so the issue is that a Exec SQL Task will only accept a string as a parameter, it will not take an object and do the casting. I thought it would but may be my misundertanding of doc.
October 4, 2010 at 6:55 am
I see. No, you need to use variable mappings to get the array members into a string variable.
But then you can directly execute the contents of the string variable in an Execute SQL task ...
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply