I need help converting some existing ActiveX script to use either the Foreach
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?
--
RMoore

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/lib...ts.runtime.var
iables.add.aspx
For Loop Container
http://technet.microsoft.com/en-us/l.../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


"Charles Wang[MSFT]" wrote:

On Sep 7, 2:32 pm, Rmoore <RMo... (AT) noemail (DOT) noemail> wrote:
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/l.../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...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(Stri ng name, Boolean readOnly,
String nameSpace, Object val)
at ScriptTask_7449cb7d6c9348a08a9bad09bcc50d5a.Script Main.Main()
--
RMoore


"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.
================================================== ====

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 package
'************************************************* ******************************
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


"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.
================================================== ====