InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 04:19:21 GMT20http://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxI have a csv file which has 18 rows and 5 columns. I have transferred the file into a 2 dimensional array.Now i am using a ForEach from Variable Enumerator.In the Variable Mappings i have mapped the 5 variables with different indexes.Refer to the Attachements...The problem is The loop is running 18 x 5 = 90 times as ForEach loop is not considering it as a 2D array.Please suggest.Fri, 24 Feb 2012 04:56:37 GMTagg_rakeshhttp://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxThanks for the article, Andy. I have a project that needs to read the XML file and load it into JDE tables. In my SSIS package, I loaded the XML file with XML Source and saved the result to a variable, User::XML using Recordset Destination. Then I used a Foreach ADO Enumerator in Foreach Loop Container and mapped the contents of the XML variable into 4 other variables. I am stuck after this since I don't know how to load those variable to JDE table. I was thinking to use Script Task to load into the JDE table but I keep getting error of System.InvalidCastException: Unable to cast object of type 'System.Data.OleDb.OleDbConnection' to type 'System.Data.SqlClient.SqlConnection'.Hopefully you can give me some ideas. Thanks in advance.Yik WingWed, 08 Feb 2012 10:01:05 GMTYWShttp://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxThanks Andy for posting such a useful article!!Tue, 23 Aug 2011 16:53:47 GMTdurga palepuhttp://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxHey, this wouldn't work for me if I make the variable assigment (in your example "LastName" type string, it will only run if that variable is of type Object as well....if I do that, then I can't use the variable in anything useful afterwards. What am I doing wrong here?!Thu, 07 Jul 2011 10:06:05 GMTdaphnezobelhttp://www.sqlservercentral.com/Forums/Topic556277-208-1.aspx[quote][b]ianc-1096396 (9/8/2010)[/b][hr]Hi, I cant find the article. Would like to read it.Mean time, maybe someone can help. I am getting a result set from a query. into an object variable. I need to use it in a conditional expression. if the object has data, continue. If it NULL, end the job.objects cannot be used in an expression. Is there another way?One way was to put a SQL statement before this section that returned a COUNT, and then use that in the precedence contraint expression. But I'd rather not.Can it be done using the object variable?cheers Ian[/quote]I have the same basic question. How can I determine if the object variable contains no records after my execute sql task so I can end the package, throw an error, etc. Great article Andy.Sat, 12 Mar 2011 11:24:25 GMTdavid.stein-1140863http://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxExcellent Article, is exactly what I was looking.Thank you, so much.Fri, 10 Dec 2010 10:13:08 GMTwaynerb87http://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxHi, I cant find the article. Would like to read it.Mean time, maybe someone can help. I am getting a result set from a query. into an object variable. I need to use it in a conditional expression. if the object has data, continue. If it NULL, end the job.objects cannot be used in an expression. Is there another way?One way was to put a SQL statement before this section that returned a COUNT, and then use that in the precedence contraint expression. But I'd rather not.Can it be done using the object variable?cheers IanWed, 08 Sep 2010 01:17:03 GMTIan C0ckcrofthttp://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxThanks, BrunoYes, I can do that fine...Best wishesPaulTue, 03 Aug 2010 07:17:08 GMTPaulSphttp://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxHi Paul,Andy has given the solution for the "one result" problem.As for the pop-up message I use it for debugging (sometimes it helps me understand). I use a script task in Control Flow view.1. drag a script task object from the tool box2. pass the variables that you want to see the result (read or write it does not matter)3. edit the script and the code in C# is as follows, just adapt for what you need. I included a string concatenation with the variable (VariableName) value because it can come in handy:...public void Main() { MessageBox.Show("YOUR MESSAGE: " + Dts.Variables["VariableName"].Value); Dts.TaskResult = (int)ScriptResults.Success; }...hope it helps and feel free to ask :-)I have registered very few SSIS contents in my blog ([url=http://ismamad.blogspot.com/]http://ismamad.blogspot.com/[/url]) if want to take a look it might have something to help you out.Complements,BrunoTue, 03 Aug 2010 06:26:03 GMTbpimentahttp://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxHi AndyThanks again for your reply.What I was hoping to be able to do was to buzz off a lot of emails by using the 'People' rescordset and iterating through all the email addresses by using an email variable as per your 'LastName'. Is this not possible - or am I misunderstanding how it works..?It was easy in DTS to create a loop and iterate through an address table and attach a list from another table to the body text. I'm really struggling to get my head around how it is done in SSIS 2008/10.Hi Bruno - thanks for your reply. The ability to use popups sounds great. How do I make them work..?Many thanks to both of youPaulTue, 03 Aug 2010 03:04:53 GMTPaulSphttp://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxHi Paul, If your Execute Sql Task is only returning a single record, that's all that will be displayed. AndyMon, 02 Aug 2010 10:45:49 GMTAndy Leonardhttp://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxHi Paul,I still had the received response activated on this post and hope I can help out. For your problem verify that the loop variables are well mapped and that the values do change. You can do this by simply writing to a pop-up.Hope it helps,Complements,Bruno PimentaMon, 02 Aug 2010 10:45:46 GMTbpimentahttp://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxHi AndyThanks for your reply.Just the first one. I click OK, expecting to see the next one, but nothing happens...ThanksPaulMon, 02 Aug 2010 10:22:58 GMTPaulSphttp://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxHi Paul, How many rows are being returned by the query in the Execute Sql Task?AndyMon, 02 Aug 2010 10:20:42 GMTAndy Leonardhttp://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxHiExcellent article, Andy.I hope someone is still watching this. I'm just upgrading from DTS and need all the help I can get...I want to eventually use this code for email scripts - taking values from the resultset. I've made everything work - and it looks just as in the article - with the green for the resultset and two yellows in the foreach loop. However, I only get the first value, as shown, but no iterations. What am I doing wrong..?Many thanks in anticipationPaulMon, 02 Aug 2010 10:06:37 GMTPaulSphttp://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxThanks Andy! This is a very useful example!Wed, 07 Jul 2010 09:15:47 GMTsaad.subhanhttp://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxAndy, Thanks for the article. It is just what I needed. All of the SSIS books I have use the file enumerator in all of their examples and do not even touch on the looping through a result set which I think is much more important than a file enumerator. But anyway, thanks again and keep it up.Thu, 01 Jul 2010 08:26:28 GMTJarom Richardsonhttp://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxSOLUTION: I have already solved this issue.1) Load the data into a tempTable2) Load the file value to the MyObjectVariable3) After loading the object variable with the value read from the file I used a Foreach Loop to read from the object variable: - Enumerator -> Foreach ADO Enumerator - ADO object source variable -> MyObjectVariable4)Then using a script I compared the two variable values: - the row count from the tempTable (a simple count(*)) - the value from the file retrieved with the previous stepHope this helps someone with the same issue.Any doubts feel free to drop a reply,ThanksFri, 26 Mar 2010 08:21:19 GMTbpimentahttp://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxGreat article Andy, thanks!I'm new to SSIS (2008) - last used DTS in SQL Server 2000 - oh man, what a change!!! :hehe:I have a series of SQL files that I would like to execute, and dump the resultset of each out to a different tab on the same Excel workbook. Sounds simple, but can I do it?! SSIS is just frustrating the hell out of me with it's counter-intuitivity (if that's such a word?!).It would be a great help if someone could point me in the right direction :) please :) I already have no hair left to tear out :w00t:PeteFri, 26 Mar 2010 04:33:26 GMTpetehughes247http://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxIt is a very good solution. Could you please advise if the record set contains hundreds of thousand of records with up to a hundred or more columns (I am uploading data like this numerous times as part of our customer support) is it still economical to use Object data type for capturing the result set. Thank youJulienTue, 09 Feb 2010 20:16:38 GMTJulien.Chappelhttp://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxNice refresher with the article republish. ThanksFri, 05 Feb 2010 11:07:45 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxExcellent article. I applied your example to my situation and it greatly reduced the time the SSIS package ran and eliminated errors and problems I was having.We are looking for more articles from you, Andy.Wed, 27 Jan 2010 01:06:13 GMTgrxclshttp://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxThanks a lot. Good article.Wed, 13 Jan 2010 13:40:40 GMTAleksey Urusov-142798http://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxHy Andy,Your article is very good so thanks for that.Here is my problem:I have a data file and a control file. The data file has N rows and the control files has that RowCount value, so if they are the same it's OK to load the data, otherwise the package will not load anything.I loaded the data into an Object variable DataTable using a flat file connection and a record set destination.I also loaded the value RowValue into a package variable.My idea problem is how do I compare the two off them? More exactly how can I read a specific column from an object Variable?I was trying to use a script for this, but with no success, can you help me on this one?Thanks in advance,Bruno PimentaWed, 13 Jan 2010 11:48:59 GMTbpimentahttp://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxthank you very much all. I got it working ! I assigned a value to a variable using an object variable to hol the "recordset" then index 0 to refer to a field in a For Each loop. Now I am dumping Excel file to a share then attach it in email. Wow! This stuff is delicious!Tue, 03 Nov 2009 09:33:27 GMTyosiaszhttp://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxHi Yosiasz, There are several ways to put values into variables. One easy way is to supply the value in the Value column of the Variables dialog. Another way is to select a value (or values) from a database using a query in an Execue SQL Task. If the query returns a scalar or a single row of date, you configure the ResultSet property to Single Row Resultset. Then on the Result Set tab, you can assign the returned results to variables. Yet another method is to use a Script Task to push a value into an SSIS variable.Hope this helps,AndyMon, 02 Nov 2009 16:23:04 GMTAndy Leonardhttp://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxgreetingsBOL says In SQL Server 2005 Integration Services (SSIS), expressions can be used to define conditions for CASE statements, create and update values in data columns, assign values to variables, update or populate properties at run time, define constraints in precedence constraints, and provide the expressions used by the For Loop container. But how do you assign a value to a variable in an expression.Thank youMon, 02 Nov 2009 13:56:46 GMTyosiaszhttp://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxWell i would like to say nice post there, but one of the downfall of ssis so far experienced on using it, is its inability to work pretty well with variables of object type..The whole foreach loop what it does is iterate through a one column of one row at a time, why not just iterate through the whole row, and give out the row, instead of giving out a single column at a time, this clearly frustrates. In order to get a row, like what i have to do , you need to declare as many variables as there are the numbers of columns in the row..and later concatenate these values in the script task, which is ridiculous..Mon, 26 Oct 2009 07:11:30 GMThans2mikehttp://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxThanks for the reply Andy. It is a package-scoped variable, however I had left the 'Value' part of the variable blank. After I entered some sample into the Value field it worked like a charm. Thanks again for your article - I couldn't have accomplished my package without it! :-DFri, 02 Oct 2009 07:40:51 GMTShairal-763732http://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxThanks all for the feedback!Shairal, it is very easy to define a variable at some unintended scope. Most of the time, package-scoped variables will meet your need. Check the Variables window and see if the variable scope is set to the package.:{> AndyThu, 01 Oct 2009 20:38:09 GMTAndy Leonardhttp://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxExcellent article! I have SO much to learn! Question – I want to set up variables to use in a Send Mail Task. I created the variable up to the point where you put it in a Script Task. I put a Send Mail Task into a Foreach Loop Container, however when I run the package I get “Error: Failed to lock variable "xxx@xxx.com" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created."Can I use these variables in a Send Mail Task? Is there a trick that I’m overlooking? Any help would be appreciated.Thu, 01 Oct 2009 17:06:31 GMTShairal-763732http://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxJust wanted to thank you for the article. This gave me EXACTLY what I needed.Tue, 29 Sep 2009 01:12:26 GMTscott elkinhttp://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxI would like to request some clarification/assistance. Is this suppose to displace a dialog box with one entries, and not iterate through the remaining rows? This is what I get when I follow the instruction of this article. I get the same when I download the example and run the package.I was hoping this would assist me in develop a method to place all the rows of a query into the body of an email (through mail task); I am still stuck at getting the rows (all of them) instead of getting the first row and the last row.Any help would be appreciated.BrandonFri, 28 Aug 2009 11:26:57 GMTbmburchhttp://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxExcellent article, Andy. SSIS can be daunting at first, even for experienced DTS developers. You have provided what is missing in Microsoft documentation.I'm looking forward to other SSIS articles you may wish to write. For the record, I'm using SSIS 2008, but I know that your 2005 articles can translate easily.PaulMon, 06 Apr 2009 11:09:07 GMTPaul Paivahttp://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxThanks for the Article. I had been fighting to get the object type into foreachloop for a day. this article saved a lot of time. very well written.Tue, 24 Feb 2009 00:17:26 GMTsheetalchordiahttp://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxHi RBarry, I didn't take it as a criticism - I appreciate the comment. In my experience, scripts don't convert well from SSIS 2005 to SSIS 2008. I suspect it has a lot to do with the changes to the script engine. SSIS 2005 uses Visual Studio for Applications (VSA) and SSIS 2008 uses Visual Studio Tools for Applications (VSTA). (You probably already know this. I type it for the benefit of those who happen upon this thread while searching...):{> AndySun, 08 Feb 2009 07:12:29 GMTAndy Leonardhttp://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxOh I think that you're right, Andy. I wasn't trying to be critical, I just happened to have the 2008 version open at the time, so I tried it out there. I had initially thought that there would be no code-level difference, but was surprised that there was. I wonder if that breaks when you upgrade or does it convert it?Sun, 08 Feb 2009 06:35:33 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxThanks for all the encouragement! RBarry, I mostly write about SSIS 2005 because I sense most of the SSIS market is using SSIS 2005. I could be wrong. I plan to switch to writing about SSIS 2008 this year.:{> AndySun, 08 Feb 2009 04:32:51 GMTAndy Leonardhttp://www.sqlservercentral.com/Forums/Topic556277-208-1.aspxGreat article....Sat, 07 Feb 2009 04:48:17 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic556277-208-1.aspx[quote][b]Jack Corbett (2/6/2009)[/b][hr][quote][b]RBarryYoung (2/6/2009)[/b][hr]Nice article, Andy. I tried this on SS2k8 and one thing that surprised me was a number of small details that changed and threw me first: Like "Dts.Results.Success" no longer works and is replaced by "ScriptResults,Success".[/quote]Is it really ScriptResults[comma]Success?:D[/quote]Ooops. Heh, yeah I guess that would be a [i]very[/i] surprising change!Fri, 06 Feb 2009 17:21:13 GMTRBarryYoung