|
|
You're in the
sql server dts
group:SSIS use loop to set dynamic global variables
sql server dts:
or For loop task. I have a sproc that returns one row containing a dynamic list of column names and values. I want to use that resultset to create global variables in the SSIS package with the assigned values. All of the examples I've seen require you to create the variables first in SSIS and then assign them. I don't like hard coding global variables inside packages. We have a table that stores all of the variables for each package along with variables common to all packages. This makes changes much easier then having them all hard coded in packages. I was using ActiveX task in DTS to loop through the result set and create the variables but now that we are using SSIS, I want to take advantage of the built in loop tasks. I have created SQL task and a variable to conain the result set. How do I loop through and create new global variables for each column and save the value assigned as type string. Any suggestions? --
Hi RMoore,
I understand that you would like to convert some of your existing ActiveX script to use Foreach/For Loop Container and dynamically create a new globar variable for each column of your sproc query result and save the value assinged as type String in your SSIS package. If I have misunderstood, please let me know. I think that For Loop Container may be more appropriate for your scenario, however from my research, Variables seemed not be able to be created within an existing package at runtime. I will further consult the product team on this issue. Now could you please let us know why you would like to do in this way? I think that a better way is that you use your ActiveX Script in a C# or VB application, and after you get the query result, you can have your application dynamically create a new package and use Package.Variables.Add to add variables for it. You may refer to: Variables.Add Method http://msdn2.microsoft.com/de-de/library/microsoft.sqlserver.dts.runtime.var iables.add.aspx For Loop Container http://technet.microsoft.com/en-us/library/ms139956.aspx If you have any questions or concerns, please feel free to let me know. Best regards, Charles Wang Microsoft Online Community Support ===================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from this issue. ====================================================== This posting is provided "AS IS" with no warranties, and confers no rights. ======================================================
Charles,
We are starting to migrate some of our DTS packages to SSIS and we should not have to completly redo them due to limitations in SSIS. For speed of development, we have developed package templates that we use at the start of every new project. For ease of maintenance and inventory, we maintain all of our global variables in a table. This is why we do not hard code package variables. We also need the variables to be static at run time so I would appreciate it if you would follow-up on this item. We frequently need to post process packages which will be easier for us to do with our variables being stored/pulled from a table since SSIS packages are installed. Thanks for the link to the Variables.Add Method. Currently I have modified my sproc to return the data as rows instead of columns. I have created a SQL task to store that resultset. I have created a For Each Loop with a VBscript task that displays the variable name and value to the screen via MSGBox. I am working on changing the MSgBox to use the variables.add method you mentioned. -- RMoore [quoted text, click to view] "Charles Wang[MSFT]" wrote:
> Hi RMoore, > I understand that you would like to convert some of your existing ActiveX > script to use Foreach/For Loop Container and dynamically create a new > globar variable for each column of your sproc query result and save the > value assinged as type String in your SSIS package. > If I have misunderstood, please let me know. > > I think that For Loop Container may be more appropriate for your scenario, > however from my research, Variables seemed not be able to be created within > an existing package at runtime. I will further consult the product team on > this issue. > Now could you please let us know why you would like to do in this way? I > think that a better way is that you use your ActiveX Script in a C# or VB > application, and after you get the query result, you can have your > application dynamically create a new package and use Package.Variables.Add > to add variables for it. > > You may refer to: > Variables.Add Method > http://msdn2.microsoft.com/de-de/library/microsoft.sqlserver.dts.runtime.var > iables.add.aspx > For Loop Container > http://technet.microsoft.com/en-us/library/ms139956.aspx > > If you have any questions or concerns, please feel free to let me know. > > Best regards, > Charles Wang > Microsoft Online Community Support > ===================================================== > When responding to posts, please "Reply to Group" via > your newsreader so that others may learn and benefit > from this issue. > ====================================================== > This posting is provided "AS IS" with no warranties, and confers no rights. > ====================================================== > >
[quoted text, click to view]
On Sep 7, 2:32 pm, Rmoore <RMo...@noemail.noemail> wrote: > Charles, > We are starting to migrate some of our DTS packages to SSIS and we should > not have to completly redo them due to limitations in SSIS. For speed of > development, we have developed package templates that we use at the start of > every new project. For ease of maintenance and inventory, we maintain all of > our global variables in a table. This is why we do not hard code package > variables. We also need the variables to be static at run time so I would > appreciate it if you would follow-up on this item. We frequently need to post > process packages which will be easier for us to do with our variables being > stored/pulled from a table since SSIS packages are installed. Thanks for the > link to the Variables.Add Method. Currently I have modified my sproc to > return the data as rows instead of columns. I have created a SQL task to > store that resultset. I have created a For Each Loop with a VBscript task > that displays the variable name and value to the screen via MSGBox. I am > working on changing the MSgBox to use the variables.add method you mentioned. > -- > RMoore > > > > "Charles Wang[MSFT]" wrote: > > Hi RMoore, > > I understand that you would like to convert some of your existing ActiveX > > script to use Foreach/For Loop Container and dynamically create a new > > globar variable for each column of your sproc query result and save the > > value assinged as type String in your SSIS package. > > If I have misunderstood, please let me know. > > > I think that For Loop Container may be more appropriate for your scenario, > > however from my research, Variables seemed not be able to be created within > > an existing package at runtime. I will further consult the product team on > > this issue. > > Now could you please let us know why you would like to do in this way? I > > think that a better way is that you use your ActiveX Script in a C# or VB > > application, and after you get the query result, you can have your > > application dynamically create a new package and use Package.Variables.Add > > to add variables for it. > > > You may refer to: > > Variables.Add Method > >http://msdn2.microsoft.com/de-de/library/microsoft.sqlserver.dts.runt... > > iables.add.aspx > > For Loop Container > >http://technet.microsoft.com/en-us/library/ms139956.aspx > > > If you have any questions or concerns, please feel free to let me know. > > > Best regards, > > Charles Wang > > Microsoft Online Community Support > > ===================================================== > > When responding to posts, please "Reply to Group" via > > your newsreader so that others may learn and benefit > > from this issue. > > ====================================================== > > This posting is provided "AS IS" with no warranties, and confers no rights. > > ======================================================- Hide quoted text - > > - Show quoted text - Hi RMoore, Have you looked into using the ForEach container to loop through an Object variable containing a Recordset? I think that this sounds like the best option in your case. Check out http://msdn2.microsoft.com/en-us/library/ms141724.aspx for general For Each info, and http://msdn2.microsoft.com/en-us/library/ms187670.aspx for info on the different collection types. In particular you should check out the ForEach ADO enumerator - this allows you to assign each column in the recordset to a different package variable for use within the package. Good luck! J
Hi RMoore,
Thanks for your response. For your requirements, I think that a reasonable way is that you write an application with C#/VB.NET to create SSIS packages programmatically. In this case, all variables are dynamically created and statically used at runtime. Variables.Add may not work within a SSIS package if you create a variable for it at runtime. You may refer to: Integration Services Programming Architecture http://technet.microsoft.com/en-us/library/ms403344.aspx I will consult the product team on this issue to get the confirmation and see if they could provide better suggestions. The process may need a little long time. I appreciate your patience. If it is convenient for you, you can just leave me (changliw_at_microsoft_dot_com) an email response so that I can timely update you. Please feel free to let me know if you have any questions or concerns. Have a good day! Best regards, Charles Wang Microsoft Online Community Support ===================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from this issue. ====================================================== This posting is provided "AS IS" with no warranties, and confers no rights. ======================================================
Hi Rmoore,
I have not got the response from the product team. I will try following up them. Meanwhile, could you please let me know if the suggestions of writing an application to dynamically create packages are helpful for you? If you have any questioins or concerns, please feel free to let me know. Best regards, Charles Wang Microsoft Online Community Support ===================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from this issue. ====================================================== This posting is provided "AS IS" with no warranties, and confers no rights. ======================================================
Charles,
Using code from http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/03/03/126.aspx I was able to successfully loop through my result set and return each variable name and value to the screen. I attempted to modify the script task inside the Foreach loop to then add the variable to the global variables with no success. I've even copied the code samples from VS2k5 and received various errors. Can you send me the exact code for adding a static variable? Below is the latest code I tried: Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Public Class ScriptMain Public Sub Main() Dim var As Variable = Dts.Variables.Add("myVar", False, "User", 5) Dts.TaskResult = Dts.Results.Success End Sub End Class I got error: Exception from HRESULT: 0xC0016026 at Microsoft.SqlServer.Dts.Runtime.Variables.Add(String name, Boolean readOnly, String nameSpace, Object val) at ScriptTask_7449cb7d6c9348a08a9bad09bcc50d5a.ScriptMain.Main() -- RMoore [quoted text, click to view] "Charles Wang[MSFT]" wrote:
> Hi Rmoore, > I have not got the response from the product team. I will try following up > them. > Meanwhile, could you please let me know if the suggestions of writing an > application to dynamically create packages are helpful for you? > > If you have any questioins or concerns, please feel free to let me know. > > Best regards, > Charles Wang > Microsoft Online Community Support > ===================================================== > When responding to posts, please "Reply to Group" via > your newsreader so that others may learn and benefit > from this issue. > ====================================================== > This posting is provided "AS IS" with no warranties, and confers no rights. > ====================================================== > > > >
Hi RMoore,
Thanks for your feedback. The problem is that Variables could not be added into a package which is in runtime. My original suggestion was that you may consider writing a .NET application for dynamically creating a package with static variables. I think that for this specific issue, you can take advantage of LOOP tasks in SSIS, however for dynamically creating variables into a running package, though I am trying to consult the product team to confirm if this issue can be implemented, from my current research from our internal databases, it is most likely not supported by SSIS currently. Actually SSIS also supports ActiveX Script Task which should be the easiest way for such complex scenario. Could you please also consider this method? Also, since the consulting process may need a long time, could you please just send me (changliw_at_microsoft_dot_com) an email response so that I can timely update you when I get their response? If you have any questions or concerns, please feel free to let me know. I am very glad to work with you for further assistance. Best regards, Charles Wang Microsoft Online Community Support ===================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from this issue. ====================================================== This posting is provided "AS IS" with no warranties, and confers no rights. ======================================================
Charles,
I will send you an email. Initially I tried to keep the AciveX task as is but I received the following error when trying to run in SSIS. --Begin Error-- Error: 0xC0048006 at Get Application Variables, ActiveX Script Task: Retrieving the file name for a component failed with error code 0x04190F7C. Task failed: Get Application Variables --End Error-- If you could help me get this task to run in the interim, I would be most grateful! Below is the code of the Active X task --Start Code- '******************************************************************************* '* Visual Basic ActiveX Script '* This script will retrieve all the Variables defined for this application '* in BA_Logging..Application_Constants and create and initialize them in this DTS packag '******************************************************************************* Function Main() Dim oRS DIM strConnect DIM oConn DIM NewPassedDate DIm RunDate '------------------------------------------------------ ' Retrieve the connect string '------------------------------------------------------ strConnect= DTSGlobalVariables("strConnect").value Set oConn = CreateObject("ADODB.Connection") With oConn .Mode = 1 ' adModeRead .CursorLocation = 3 ' adUseClient - Client side cursor. .ConnectionString = strConnect .ConnectionTimeout = 60 '0 .CommandTimeout = 60 '0 .Open End With Set oRS = Createobject("ADODB.Recordset") Set oRS = oConn.Execute("EXEC dbo.usp_Application_Constants_All_AndGlobals_Sel '" & DTSGlobalVariables("PG_Packagename") & "'") '------------------------------------------------------ ' Initialize all the constants '------------------------------------------------------ DIM n FOR n = 0 to oRS.Fields.Count -1 'DTSGlobalVariables.AddGlobalVariable oRS.Fields(n).Name, oRS.Fields(n).value DTSGlobalVariables(oRS.Fields(n).Name).Value = oRS.Fields(n).value NEXT RunDate = DTSGlobalVariables("PassedDate").value DTSGlobalVariables("RunDate").value = RunDate Main = DTSTaskExecResult_Success End Function --End Code-- -- RMoore [quoted text, click to view] "Charles Wang[MSFT]" wrote:
> Hi RMoore, > Thanks for your feedback. > > The problem is that Variables could not be added into a package which is in > runtime. My original suggestion was that you may consider writing a .NET > application for dynamically creating a package with static variables. I > think that for this specific issue, you can take advantage of LOOP tasks in > SSIS, however for dynamically creating variables into a running package, > though I am trying to consult the product team to confirm if this issue can > be implemented, from my current research from our internal databases, it is > most likely not supported by SSIS currently. > > Actually SSIS also supports ActiveX Script Task which should be the easiest > way for such complex scenario. Could you please also consider this method? > > Also, since the consulting process may need a long time, could you please > just send me (changliw_at_microsoft_dot_com) an email response so that I > can timely update you when I get their response? > > If you have any questions or concerns, please feel free to let me know. I > am very glad to work with you for further assistance. > > Best regards, > Charles Wang > Microsoft Online Community Support > ===================================================== > When responding to posts, please "Reply to Group" via > your newsreader so that others may learn and benefit > from this issue. > ====================================================== > This posting is provided "AS IS" with no warranties, and confers no rights. > ====================================================== > > >
Hi RMoore,
Just kindly remind that I replied you via email, please check it and reply back. Have a good day! Best regards, Charles Wang Microsoft Online Community Support ===================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from this issue. ====================================================== This posting is provided "AS IS" with no warranties, and confers no rights. ======================================================
Charles,
I received the final verdict on dynamically creating global variables using SSIS from MS. Although the dts.variables.add method is available and used widely in MS SSIS examples, it does not work as needed in SSIS. Basically, when you use dts.variables.add in SSIS, the variables appear to be created globablly but they can not actually be used by subsequent tasks. They are only available to the task they are created in and should just be created as local variables instead. Therefore, it is pointless to use this method in SSIS. Any global variables you need to use for multiple tasks must be defined during design. Further, MS developers confirmed that this functionality would not be available in SQL Server 2008 or any other future versions. This answers the question for my first problem. Now I need your assistance with the second problem. I went ahead and created my Global Variables using the GUI. Now I want to dynamically set their values. I tried several different ways to do it and can't seem to make it work. Here is what I am currently having issues with. My first task is a SQL task that gets the global variable name and value from a table and returs as a resultset. My second task is a foreach loop that loops through the result set of the previous task and executes a VB.net Script task which is inside it. The foreach loop returns GVStrShiftColName which is the Global Variable Name, e.g. "ManualRun" and GVStrShiftColValue returns its corresponding value e.g. "0" from a table. I have the below code in the script task that is inside the Foreach loop. The below line correctly displays: ManualRun : 0 'MsgBox(CType(Dts.Variables("GVStrShiftColName").Value, String) & ": " & CType(Dts.Variables("GVStrShiftColValue").Value, String)) Instead of returning ManualRun : 0 to the screen, I want to set Global Variable ManualRun's valule to 0. I'm guessing I need to do something like this: 'Set local variables to values returned by loop Dim GVColName As Object = Dts.Variables("GVStrShiftColName").Value 'This would set GVColName = ManualRun Dim GVColValue As Object = Dts.Variables("GVStrShiftColValue").Value 'This would set GVColValue = 0 'Now I want to set ManualRun's value = 0 in the global variables 'The below statement does not return what I need. It tries to find a global 'variable called GVColName instead of ManualRun. Dts.Variables("GVColName").Value = GVColValue How do I replace "GVColName" in the last statement with "ManualRun" programmatically? I tried adding & but that didn't work either. I really appreciate your help!! -- RMoore [quoted text, click to view] "Charles Wang[MSFT]" wrote:
> Hi RMoore, > Just kindly remind that I replied you via email, please check it and reply > back. Have a good day! > > Best regards, > Charles Wang > Microsoft Online Community Support > ===================================================== > When responding to posts, please "Reply to Group" via > your newsreader so that others may learn and benefit > from this issue. > ====================================================== > This posting is provided "AS IS" with no warranties, and confers no rights. > ====================================================== >
Hi RMoore,
Appreciate your detailed feedback. For your current issue, I would like to check with you whether you input the GLOBAL VARIABLES into the "ReadWriteVariables" field of your script task. If no, please set it via the following steps: Double click your Script Task; select Script; and input GVColName to the "ReadWriteVariables" field. If there are other global variables which also require to be written, you can input them into the filed and use commas for the separation, such as "GVColName ,GVColVal". Hope this helps. Please feel free to let me know if you have any other questions or concerns. Have a nice day! Best regards, Charles Wang Microsoft Online Community Support ====================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from this issue. ====================================================== This posting is provided "AS IS" with no warranties, and confers no rights. ======================================================
Charles,
I did put GVStrShiftColName, GVStrShiftColValue into the ReadWriteVariables field. I don't want to hard code the actual variables into this field as that would defeat the purpose of what I am trying to do. I want this task to be generic not specific which is why I am looping through a table to dynamically set the global variables. If I can't set the values without adding them to the ReadWriteVariables property, is there a way to do this programatically? Do you have an answer for me on my below question? 'Now I want to set ManualRun's value = 0 in the global variables 'The below statement does not return what I need. It tries to find a global 'variable called GVColName instead of ManualRun. Dts.Variables("GVColName").Value = GVColValue How do I replace "GVColName" in the last statement with "ManualRun" programmatically? I tried adding & but that didn't work either. -- RMoore [quoted text, click to view] "Charles Wang[MSFT]" wrote:
> Hi RMoore, > Appreciate your detailed feedback. > > For your current issue, I would like to check with you whether you input > the GLOBAL VARIABLES into the "ReadWriteVariables" field of your script > task. If no, please set it via the following steps: > Double click your Script Task; select Script; and input GVColName to the > "ReadWriteVariables" field. > > If there are other global variables which also require to be written, you > can input them into the filed and use commas for the separation, such as > "GVColName ,GVColVal". > > Hope this helps. Please feel free to let me know if you have any other > questions or concerns. Have a nice day! > > Best regards, > Charles Wang > Microsoft Online Community Support > > ====================================================== > When responding to posts, please "Reply to Group" via > your newsreader so that others may learn and benefit > from this issue. > ====================================================== > This posting is provided "AS IS" with no warranties, and confers no rights. > ====================================================== >
[quoted text, click to view]
On Oct 1, 2:01 pm, Rmoore <RMo...@noemail.noemail> wrote: > Charles, > I did put GVStrShiftColName, GVStrShiftColValue into the ReadWriteVariables > field. I don't want to hard code the actual variables into this field as that > would defeat the purpose of what I am trying to do. I want this task to be > generic not specific which is why I am looping through a table to dynamically > set the global variables. If I can't set the values without adding them to > the ReadWriteVariables property, is there a way to do this programatically? > Do you have an answer for me on my below question? > > 'Now I want to set ManualRun's value = 0 in the global variables > 'The below statement does not return what I need. It tries to find a global > 'variable called GVColName instead of ManualRun. > Dts.Variables("GVColName").Value = GVColValue > > How do I replace "GVColName" in the last statement with "ManualRun" > programmatically? I tried adding & but that didn't work either. > > -- > RMoore > > > > "Charles Wang[MSFT]" wrote: > > Hi RMoore, > > Appreciate your detailed feedback. > > > For your current issue, I would like to check with you whether you input > > the GLOBAL VARIABLES into the "ReadWriteVariables" field of your script > > task. If no, please set it via the following steps: > > Double click your Script Task; select Script; and input GVColName to the > > "ReadWriteVariables" field. > > > If there are other global variables which also require to be written, you > > can input them into the filed and use commas for the separation, such as > > "GVColName ,GVColVal". > > > Hope this helps. Please feel free to let me know if you have any other > > questions or concerns. Have a nice day! > > > Best regards, > > Charles Wang > > Microsoft Online Community Support > > > ====================================================== > > When responding to posts, please "Reply to Group" via > > your newsreader so that others may learn and benefit > > from this issue. > > ====================================================== > > This posting is provided "AS IS" with no warranties, and confers no rights. > > ======================================================- Hide quoted text - > > - Show quoted text - Hi RMoore, Sounds like you're pushing the boundaries of SSIS's dynamic abilities here :) As I'm sure you know - to access variables from within your script task, you need to include them as a comma seperated list (with no spaces!) in either the ReadOnlyVariables or ReadWriteVariables property of the script task. So in your case you'll have the following: ReadOnlyVariables set to GVColName,GVColValue ReadWriteVariables set using an Expression to @[User::GVColName] (in the script task, click Expressions and then choose the ReadWriteVariables property) - this will set the ReadWriteVariables at runtime and will change each time the ForEach loop is executed as it loops through your resultset. Your script should then look something like this: Dim i As Int32 MsgBox(Dts.Variables("GVColName").Value.ToString() + " : " + Dts.Variables("GVColValue").Value.ToString()) For i = 0 To Dts.Variables.Count - 1 'MsgBox(i.ToString()) If Dts.Variables(i).Name = Dts.Variables("GVColName").Value.ToString() Then Dts.Variables(i).Value = Dts.Variables("GVColValue").Value MsgBox(Dts.Variables(i).Name + " : " + Dts.Variables(i).Value.ToString()) End If Next Dts.TaskResult = Dts.Results.Success I only had a couple minutes to play around with this, but I have a working package that demonstrates variable assignment using the above technique. Please email me at lumpywater at googlemail dot com if you'd like a copy of the package. Good Luck! J
J,
I wasn't able to get it to work following your suggestions using the expressions property of the script task. I sent you an email from my home account. Please send the package to that address. Thanks again for your help! -- RMoore [quoted text, click to view] "jhofmeyr@googlemail.com" wrote:
> On Oct 1, 2:01 pm, Rmoore <RMo...@noemail.noemail> wrote: > > Charles, > > I did put GVStrShiftColName, GVStrShiftColValue into the ReadWriteVariables > > field. I don't want to hard code the actual variables into this field as that > > would defeat the purpose of what I am trying to do. I want this task to be > > generic not specific which is why I am looping through a table to dynamically > > set the global variables. If I can't set the values without adding them to > > the ReadWriteVariables property, is there a way to do this programatically? > > Do you have an answer for me on my below question? > > > > 'Now I want to set ManualRun's value = 0 in the global variables > > 'The below statement does not return what I need. It tries to find a global > > 'variable called GVColName instead of ManualRun. > > Dts.Variables("GVColName").Value = GVColValue > > > > How do I replace "GVColName" in the last statement with "ManualRun" > > programmatically? I tried adding & but that didn't work either. > > > > -- > > RMoore > > > > > > > > "Charles Wang[MSFT]" wrote: > > > Hi RMoore, > > > Appreciate your detailed feedback. > > > > > For your current issue, I would like to check with you whether you input > > > the GLOBAL VARIABLES into the "ReadWriteVariables" field of your script > > > task. If no, please set it via the following steps: > > > Double click your Script Task; select Script; and input GVColName to the > > > "ReadWriteVariables" field. > > > > > If there are other global variables which also require to be written, you > > > can input them into the filed and use commas for the separation, such as > > > "GVColName ,GVColVal". > > > > > Hope this helps. Please feel free to let me know if you have any other > > > questions or concerns. Have a nice day! > > > > > Best regards, > > > Charles Wang > > > Microsoft Online Community Support > > > > > ====================================================== > > > When responding to posts, please "Reply to Group" via > > > your newsreader so that others may learn and benefit > > > from this issue. > > > ====================================================== > > > This posting is provided "AS IS" with no warranties, and confers no rights. > > > ======================================================- Hide quoted text - > > > > - Show quoted text - > > Hi RMoore, > > Sounds like you're pushing the boundaries of SSIS's dynamic abilities > here :) > > As I'm sure you know - to access variables from within your script > task, you need to include them as a comma seperated list (with no > spaces!) in either the ReadOnlyVariables or ReadWriteVariables > property of the script task. So in your case you'll have the > following: > > ReadOnlyVariables set to GVColName,GVColValue > ReadWriteVariables set using an Expression to @[User::GVColName] (in > the script task, click Expressions and then choose the > ReadWriteVariables property) - this will set the ReadWriteVariables at > runtime and will change each time the ForEach loop is executed as it > loops through your resultset. > > Your script should then look something like this: > > Dim i As Int32 > > MsgBox(Dts.Variables("GVColName").Value.ToString() + " : " + > Dts.Variables("GVColValue").Value.ToString()) > For i = 0 To Dts.Variables.Count - 1 > 'MsgBox(i.ToString()) > If Dts.Variables(i).Name = > Dts.Variables("GVColName").Value.ToString() Then > Dts.Variables(i).Value = Dts.Variables("GVColValue").Value > MsgBox(Dts.Variables(i).Name + " : " + > Dts.Variables(i).Value.ToString()) > End If > Next > > Dts.TaskResult = Dts.Results.Success > > I only had a couple minutes to play around with this, but I have a > working package that demonstrates variable assignment using the above > technique. Please email me at lumpywater at googlemail dot com if > you'd like a copy of the package. > > Good Luck! > J >
Hi RMoore,
Thanks for your response. Currently I am afraid that it is not possible to set the global variables if you do not add them to the ReadWriteVariables property. I think that the issue here is that you used Dts.Variables("GVColName"). Please note that in this case, SSIS will search the global variable "GVColName" but not the variable which name is the value of the variable GVColName. If you want to set GVColValue to the ManualRun variable, you should use: Dim GVColName As Object = Dts.Variables("GVStrShiftColName").Value Dts.Variables(GVColName.ToString).Value = GVColValue Also you should first add all those global variables like ManualRun to the ReadWriteVariables in design time. Hope this helps. If you have any other questions or concerns, please feel free to let me know. Have a nice day! Best regards, Charles Wang Microsoft Online Community Support ====================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from this issue. ====================================================== This posting is provided "AS IS" with no warranties, and confers no rights. ======================================================
Charles,
I was able to successfully set my global variables following Jhofmeyr's previous post with exception in the way he was using the expressions. I was unable to get my package to work using the expressions to set the ReadWriteVariables. Instead I had to add all of the variables to the ReadWriteVariables property of the script task. I am trying to get a copy of the package he developed so I can see if I can use the expressions method instead. I really don't want to hard code all of my global variables anywhere in the ForEach Loop or the script task inside the loop. My ultimate goal is for these steps to be completely dynamic and therefore very reusable. -- RMoore [quoted text, click to view] "Charles Wang[MSFT]" wrote:
> Hi RMoore, > Thanks for your response. > Currently I am afraid that it is not possible to set the global variables > if you do not add them to the ReadWriteVariables property. > > I think that the issue here is that you used Dts.Variables("GVColName"). > Please note that in this case, SSIS will search the global variable > "GVColName" but not the variable which name is the value of the variable > GVColName. If you want to set GVColValue to the ManualRun variable, you > should use: > Dim GVColName As Object = Dts.Variables("GVStrShiftColName").Value > Dts.Variables(GVColName.ToString).Value = GVColValue > > Also you should first add all those global variables like ManualRun to the > ReadWriteVariables in design time. > > Hope this helps. If you have any other questions or concerns, please feel > free to let me know. Have a nice day! > > Best regards, > Charles Wang > Microsoft Online Community Support > > ====================================================== > When responding to posts, please "Reply to Group" via > your newsreader so that others may learn and benefit > from this issue. > ====================================================== > This posting is provided "AS IS" with no warranties, and confers no rights. > ====================================================== > > > >
Charles,
I'm going to close out this post. Jhofmeyr sent me a sample package that solved my last issue. You are correct that at some point I will need to refer to those variables by name but I'd rather do that only in the tasks they are used instead of all thorugh out the package design. I now have a completely dynamic way of setting my global variables allowing my team to use these tasks as templates. If everything was hard coded as you and others have suggested I do, this would not allow us to create template tasks that are used in every project and would increase our development efforts. I am disappointed that SSIS 05/08 will not allow us to dynamically create the global variables as this has added an additional step in every package that was not needed in DTS 2000. It is a shame that good functionality was lost in the conversion to SSIS. In good programming 101, dynamic is always better than hard coding. -- RMoore [quoted text, click to view] "Charles Wang[MSFT]" wrote:
> Hi Rmoore, > I understand your concern. Though I think that it is necessary to input the > variables into the ReadWriteVariables property field, I will try to consult > the product team for confirmation. > > Also I have a question, I think that your intention should be dynamically > set the variables values, and you can do it now but just need to input some > variables names in design time. Since you had manually created all the > global variables, is it important not to manually input those read-write > variables into the ReadWriteVariables property field now? > > Have a nice day! > > Best regards, > Charles Wang > Microsoft Online Community Support > ===================================================== > When responding to posts, please "Reply to Group" via > your newsreader so that others may learn and benefit > from this issue. > ====================================================== > This posting is provided "AS IS" with no warranties, and confers no rights. > ====================================================== > > > > > >
Hi Rmoore,
I understand your concern. Though I think that it is necessary to input the variables into the ReadWriteVariables property field, I will try to consult the product team for confirmation. Also I have a question, I think that your intention should be dynamically set the variables values, and you can do it now but just need to input some variables names in design time. Since you had manually created all the global variables, is it important not to manually input those read-write variables into the ReadWriteVariables property field now? Have a nice day! Best regards, Charles Wang Microsoft Online Community Support ===================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from this issue. ====================================================== This posting is provided "AS IS" with no warranties, and confers no rights. ======================================================
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |