Accessing Global Variables In SSIS Script Components
Feb 6, 2008How do i access global variables in SSIS scripts ?
the Dts.variables("VarName").Value
should work but doesn't ?
do i need to reference the variable another way ?
ADVERTISEMENT
SSIS Global Variables
Jul 10, 2007Before in DTS, I used to using ActiveX scripts to setup simple Global Variables that would later be used by the package, let's say a give state or a date.
I'm completely lost how to do the same in SSIS, can someone give me a hand?
I'm not sure what component to use and how to read this variable.
SSIS Script Global Variables
Feb 6, 2008How do i access global variables in SSIS scripts ?
the Dts.variables("VarName").Value
should work but doesn't ?
do i need to reference the variable another way ?
How To Retrieve Global Variables In An ActiveX Script Task Using VBScript In SSIS
Oct 27, 2006I need to retrieve the Global Variables set in my package configuration file within an ActiveX Script Task within an SSIS package. In DTS, I could access the Global Variables to execute a SQLXMLBulkLoad for the following statement:
==========================================
Function Main()
Response.Expires=-1
set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString =
"provider=SQLOLEDB.1;server=ABC123;database=MyDB;Trusted_Connection=Yes;"
objBL.KeepIdentity = False
objBL.CheckConstraints = False
objBL.Execute DTSGlobalVariables("gv_XSDSchemaFile").Value, DTSGlobalVariables("gv_XMLFullPath").Value
Main = DTSTaskExecResult_Success
set objBL=Nothing
End Function
=========================================
I have tried using the Script Task to write this in VB.NET, however the MSXML4.0 is not exposed within the limited object model of the Script Task Designer. I have written a Data Flow Object using the XML Source, however it requires quite a bit of effort to have the Data Flow Component parse the XML (with 10 hierarchical nodes), transform each and provide a SQL Server Destination. This works, however the XML Source Component requires a hardcoded reference to the XSD Schema file and does not allow for a Global Variable to used. (They do provide this functionality for the XML file source though).
My requirement is to allow for the Global Variable to be passed for the Schema file at runtime. The only way I can think of is to recreate what I was doing in DTS where I could simply pull in the XML and XSD Global Variables and execute the SQLXMLBulkLoad in VB Script.
Any ideas on how to write this in VBScript within the ActiveX Script Task in SSIS?...
Michael
Accessing Variables In SSIS Code
Nov 21, 2006Hi
I am not able to access SSIS variables which are defined at Data Flow Task in a custom component. This custom component is developed by me in C#. How can i access these variables?
Please let me know if theres a way to access SSIS variables.
Thanks,
Vipul
SSIS: Problem Mapping Global Variables To Stored Procedure. Can't Pass One Variable To Sp And Return Another Variable From Sp.
Feb 27, 2008I'm new to SSIS, but have been programming in SQL and ASP.Net for several years. In Visual Studio 2005 Team Edition I've created an SSIS that imports data from a flat file into the database. The original process worked, but did not check the creation date of the import file. I've been asked to add logic that will check that date and verify that it's more recent than a value stored in the database before the import process executes.
Here are the task steps.
[Execute SQL Task] - Run a stored procedure that checks to see if the import is running. If so, stop execution. Otherwise, proceed to the next step.
[Execute SQL Task] - Log an entry to a table indicating that the import has started.
[Script Task] - Get the create date for the current flat file via the reference provided in the file connection manager. Assign that date to a global value (FileCreateDate) and pass it to the next step. This works.
[Execute SQL Task] - Compare this file date with the last file create date in the database. This is where the process breaks. This step depends on 2 variables defined at a global level. The first is FileCreateDate, which gets set in step 3. The second is a global variable named IsNewFile. That variable needs to be set in this step based on what the stored procedure this step calls finds out on the database. Precedence constraints direct behavior to the next proper node according to the TRUE/FALSE setting of IsNewFile.
If IsNewFile is FALSE, direct the process to a step that enters a log entry to a table and conclude execution of the SSIS.
If IsNewFile is TRUE, proceed with the import. There are 5 other subsequent steps that follow this decision, but since those work they are not relevant to this post.
Here is the stored procedure that Step 4 is calling. You can see that I experimented with using and not using the OUTPUT option. I really don't care if it returns the value as an OUTPUT or as a field in a recordset. All I care about is getting that value back from the stored procedure so this node in the decision tree can point the flow in the correct direction.
CREATE PROCEDURE [dbo].[p_CheckImportFileCreateDate]
/*
The SSIS package passes the FileCreateDate parameter to this procedure, which then compares that parameter with the date saved in tbl_ImportFileCreateDate.
If the date is newer (or if there is no date), it updates the field in that table and returns a TRUE IsNewFile bit value in a recordset.
Otherwise it returns a FALSE value in the IsNewFile column.
Example:
exec p_CheckImportFileCreateDate 'GL Account Import', '2/27/2008 9:24 AM', 0
*/
@ProcessName varchar(50)
, @FileCreateDate datetime
, @IsNewFile bit OUTPUT
AS
SET NOCOUNT ON
--DECLARE @IsNewFile bit
DECLARE @CreateDateInTable datetime
SELECT @CreateDateInTable = FileCreateDate FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName
IF EXISTS (SELECT ProcessName FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName)
BEGIN
-- The process exists in tbl_ImportFileCreateDate. Compare the create dates.
IF (@FileCreateDate > @CreateDateInTable)
BEGIN
-- This is a newer file date. Update the table and set @IsNewFile to TRUE.
UPDATE tbl_ImportFileCreateDate
SET FileCreateDate = @FileCreateDate
WHERE ProcessName = @ProcessName
SET @IsNewFile = 1
END
ELSE
BEGIN
-- The file date is the same or older.
SET @IsNewFile = 0
END
END
ELSE
BEGIN
-- This is a new process for tbl_ImportFileCreateDate. Add a record to that table and set @IsNewFile to TRUE.
INSERT INTO tbl_ImportFileCreateDate (ProcessName, FileCreateDate)
VALUES (@ProcessName, @FileCreateDate)
SET @IsNewFile = 1
END
SELECT @IsNewFile
The relevant Global Variables in the package are defined as follows:
Name : Scope : Date Type : Value
FileCreateDate : (Package Name) : DateType : 1/1/2000
IsNewFile : (Package Name) : Boolean : False
Setting the properties in the "Execute SQL Task Editor" has been the difficult part of this. Here are the settings.
General
Name = Compare Last File Create Date
Description = Compares the create date of the current file with a value in tbl_ImportFileCreateDate.
TimeOut = 0
CodePage = 1252
ResultSet = None
ConnectionType = OLE DB
Connection = MyServerDataBase
SQLSourceType = Direct input
IsQueryStoredProcedure = False
BypassPrepare = True
I tried several SQL statements, suspecting it's a syntax issue. All of these failed, but with different error messages. These are the 2 most recent attempts based on posts I was able to locate.
SQLStatement = exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
SQLStatement = exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
Parameter Mapping
Variable Name = User::FileCreateDate, Direction = Input, DataType = DATE, Parameter Name = 0, Parameter Size = -1
Variable Name = User::IsNewFile, Direction = Output, DataType = BYTE, Parameter Name = 1, Parameter Size = -1
Result Set is empty.
Expressions is empty.
When I run this in debug mode with this SQL statement ...
exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the following error message appears.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
When the above is run tbl_ImportFileCreateDate does not get updated, so it's failing at some point when calling the procedure.
When I run this in debug mode with this SQL statement ...
exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the tbl_ImportFileCreateDate table gets updated. So I know that data piece is working, but then it fails with the following message.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC001F009 at GLImport: The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
The IsNewFile global variable is scoped at the package level and has a Boolean data type, and the Output parameter in the stored procedure is defined as a Bit. So what gives?
The "Possible Failure Reasons" message is so generic that it's been useless to me. And I've been unable to find any examples online that explain how to do what I'm attempting. This would seem to be a very common task. My suspicion is that one or more of the settings in that Execute SQL Task node is bad. Or that there is some cryptic, undocumented reason that this is failing.
Thanks for your help.
Developing Custom Components By Extending The SSIS Stock Data Flow Components
Sep 7, 2006Everything I've read says that custom data flow components are built by inheriting from the Microsoft.SqlServer.Dts.Pipeline.PipelineComponent class.
But the stock components such as the Derived Column data flow transformation must each be implemented by their own class. So how do I base my custom components on those classes? The documentation for the PipelineComponent class doesn't list any such subclasses.
Accessing Global Cursor
Mar 3, 2004hi friends,
Here is the stored procedures that I used.
------------------------------------------------------------------------------------
create procedure globalCursor
AS
DECLARE abc CURSOR GLOBAL FOR
select * from sales
OPEN abc
create procedure globalCursorTest
AS
DECLARE @sdate datetime
DECLARE @sperson varchar(15)
DECLARE @sregion varchar(15)
DECLARE @sales int
EXECUTE globalCursor
FETCH NEXT FROM abc INTO @sdate, @sperson, @sregion, @sales
print @sdate
print @sperson
print @sregion
print @sales
------------------------------------------------------------------------------------
When I execute globalCursorTest using SQL Query Analyser, it says
------------------------------------------------------------------------------------
Server: Msg 16915, Level 16, State 1, Procedure globalCursor, Line 4
A cursor with the name 'abc' already exists.
Server: Msg 16905, Level 16, State 1, Procedure globalCursor, Line 5
The cursor is already open.
------------------------------------------------------------------------------------
how to solve this? or in other words, how to simply create the procedure in the database without executing it, as i can see the execution of the first procedure globalCursor causes this problem.
Jake
Accessing SQL Server From Global.ASA
Sep 21, 2005Hi
I have an application written in classic ASP which uses Windows Authentication in SQL Server.
This seems to work fine, except when we try to write to the database in Session_OnEnd in Global.ASA.
We don’t get an error and although logging is enabled in SQL Server, no failed logins are reported. Nevertheless, the database is not updated.
I’ve tried adding the IWAM_Servername user to SQL Server, but that hasn’t made any difference.
Has anyone any thoughts on how to rectify this? At the moment, both SQL Server and IIS are running on the same PC, but we need to find a solution when they are on different PCs as well.
Accessing Global Temporary Table
Jan 29, 2001We are trying to use a ## table (global temporary table) in Access or Excel thru a ODBC connection to tempdb. We are able to see the system tables, but not any ## tables. We are able to perform selects on the same ## table with the same userid and password in Query Analyzer. If we use sa or make the userid dbo we are able to see the ## tables, but I don't want to give these type of permissions.
Any help would be appreciated. Thanks in advance.
DTS Global Variables
Nov 10, 2004Hello,
In the properties for a DTS package, I have a string variable. I need to execute an update statement based on this variable. I was going to use Execute SQL, but I didn't know now to get a value from the variables section.
I store the value there because I will be using dtsrun or the DTS library to execute the package, which I can change this value upon execution. How do I execute an update based on the value?
Brian
DTS Global Variables
Aug 6, 2001Have anyone encountered a problem with DTS Global variables. What I am trying to do is keep a counter in a global variable. It works fine when I run the job manually but if I schedule the job it doesn't increment the counter. Any ideas?
Thanks in advance!
Nancy
Global Variables
May 10, 2004cani define a global time in sql server2000, which all tables in DIFFERENT databases can use. I need this in order to INNER JOIN all tables in SAME/DIFFERENT DBs on basis of this datetime field.
View 7 Replies View RelatedGlobal Variables
Feb 17, 2004Hi,
Is there a way to declare a persistent global variable in SQL Server?
I'd like my stored procs to fetch data in a different source depending on a debug (or development) variable.
For example, I'd like to be able to set a variable to either 0 or 1 (true or false) and have a static SP defined as:
IF @MYVARIABLE = 1
SELECT * FROM Openquery(Server1, 'SELECT * FROM Table1")
ELSE
SELECT * FROM Openquery(Server2, 'SELECT * FROM Table2')
What do you think? Since these SPs should be called a lot, I don't want to store the info in a table, I want it as a global variableso it will be as fast as possible.
Any other suggestions are also welcomed.
Thanks,
Skip.
Q: DTS And Global Variables
Jul 23, 2005Using SQL2000, I have a DTS that takes data from MySQL to sqlserver, thecatch is I want to specify a specific range of dates.How to use a global variable? At the moment I manually changes the dates andjobs run on a daily basis.sample sql statement from Mysql connection:select *from Table1where date between '1/1/2004' and '6/30/2004'CHANGE TO:select *from Table1where date between @fromdate and @todateTIABob
View 1 Replies View RelatedGlobal Server Variables
Apr 1, 2004Is there a way in SQL Server to setup Global Server Variables or Constants
We are working with an Off the Shelf Constituent Management application based on SQL Server. We can only read from the App's DB. So we setup another DB to run SP to access the data in the main DB. One problem we have is that there codes that the app DB uses that we need to reference as criteria in our SP. Example: Code for a phone type of email is 731. So if we want to pull email addresses we need to Select where PhoneType = 731. We found out that each time the main db is rebuilt those codes change. That means finding everytime we used that code and changing it.
It would be great to be able to set a global variable and use it anytime that code is needed.
Any ideas.
DTS Import With Global Variables
Dec 7, 2000A short explanation: I have 4 locations that will import from text files.
These text files will need to be 'stamped' with a location_id. I'd like to pass the location_id to the dts package via a VB program. The reason for the VB program is I'd like to keep users from the Enterprise Manager. Is this possible to accomplish? Thanks for your help.
Global Variables (passing)
Feb 23, 2001I am executing:
--------------------------------
DECLARE @x AS int
SET @lcQuery = 'UPDATE ... WHERE .... ' + ' SET @x = @@ROWCOUNT'
EXEC (@lcQuery)
PRINT @x
--------------------------------
@x does NOT return a value, because it is "local" to the lcQuery execution. As a matter of fact, to execute it, I have to write:
SET @lcQuery = 'DECLARE @x AS int ' +
'UPDATE ... WHERE .... ' +
'SET @x = @@ROWCOUNT'
EXEC (@lcQuery)
How can I pass the variable @x to the progam from within EXEC (@lcQuery)?
How does EXEC (@lcQuery) execute? In a different space?
Thank you.
DTS Global Variables DateAdd
Jan 22, 2002As a newcomer to SQL2000, I need to create a DTS Global Variable that will import a file daily automatically. I've got the DTS working great, but I have to manually execute it every day. The log file name Accounting 02-22-2002.csv changes by the date every day. So the next record set would be Accounting 02-23-2002.csv and so on. Any ideas on how to create this? I think some VBScript is necessary from what I understand. I'm looking at the function DATEADD(d, 1, date).
View 1 Replies View RelatedUsing Global Variables In Triggers ?
Mar 20, 2007We're using SQL Server 2000. I have a table with a couple fields I'd like to automatically updatre with a atrigger. One of the fields needs to log the current time the record has been modified. The other field needs to log the current user ID.Storing the current time is easy with UPDATE and GETDATE(), but obtaining the current user from my ASP.NET code isnt as pretty as I would have to pass in the user ID from my backend code into the server, perhaps as a stored procedure variable that will manually update the table with the new ID. I was hoping to perhaps set some global variable of the user ID for the current connection to the dbase so that once the trigger is made, it feeds off this connection-level global variable to plug in the needed User ID for the record.So for instance:SET @@MyCurrentUserID = 4; UPDATE MyTable SET myStuff='games';The MyTable trigger will be initiated and the LastModified field of MyTable will be auto update with the current datetime, but also the LastModifiedUser field of MyTable will be update with MyCurrentUserID.Is this possible with SQL Server 2000 for Windows 2K3?Tx
View 7 Replies View RelatedCreating Global Variables
Apr 11, 2008I have a dts package that has 4 global variables. Migrated the package to SSIS thru the wizard. But I need to fix the global variable in order to work.
This global variable is the path where the txt file is residing after you run the DTS package.
I want o know how could I create the global variable in SSIS?
Transfering All Global Variables???
Jan 30, 2007Hi
I have created two SSIS packages one is development and another is test. I used near about 15 global variables in my test SSIS package.Both development and test package working on transfering data from database and put into text files now i want to implement all these variables into development package
how i will do that wheather i have to create all these variables in development package also or there is way to transfer these variables from one package to another
Please help me out
Functions With Global Variables
Jul 20, 2005Hello,I am porting a stored procedure from Oracle. It uses a variable thatremembers its previous values from each invocation. (It uses a PRAGMAREFERENCES clause for those who are familiar with Oracle.) In otherwords, the variable in a particular stored procedure acts as a globalvariable. So the each invocation of the stored procedure can see itslast value, instead of its initial default value.Is there something similar in SQLServer?
View 1 Replies View RelatedDTS For Bulk Insert With Global Variables...
Feb 11, 2005I have a flat file of records in csv format. I want ti create a dts that can be executed from asp.net using vb.net to load data from .csv file into sql server 2000 table.
Now what further i want that this DTS must have a parameter mapped to file name of .csv. so when i execute this dts from asp.net web application i will set the file path to this variable and then execute it on server.
So that meanz dts stays on server all it require before execution a new file path to load data from file that is already uploaded on server in a directory.
if any one have any idea to how to create such a dts and secondly how to execute in asp.net.
thanks in advance.
Sp_msforeachtable In A DTS Package With Global Variables
Feb 27, 2001Hi,
what I basically want to do is to delete all records from all tables where the field loadnumber equals a certain value.
To do this I've defined the global variable 'DSLoadNumber' (integer) in a DTS package, and I want to use the stored procedure sp_msforeachtable in an Execute SQL Task.
This is the code in the Execute SQL Task:
sp_msforeachtable
@replacechar = '~',
@command1 = "delete from ~ where loadnumber = ?",
@whereand = " and name not in ('dtproperties', 'dsloadlog', 'FotoMediaProduct', 'ProductFF', 'ComStat', 'ProdStat')"
This code can be parsed without any problem, but the ? is not seen as a variable.
When I get the ? out of the quotes the code cannot be parsed any longer.
Finally I've written a cursor to the job which looks like this:
DECLARE @tablename varchar(35),
@loadnumber int
SELECT @loadnumber = ?
DECLARE delete_cursor CURSOR FOR
select name
from sysobjects
where type = 'U'
and name not in ('dtproperties', 'dsloadlog', 'FotoMediaProduct', 'ProductFF', 'ComStat', 'ProdStat')
OPEN delete_cursor
FETCH delete_cursor INTO @tablename
WHILE (@@fetch_status = 0)
BEGIN
exec('delete from ' + @tablename + ' where loadnumber = ' + @loadnumber)
FETCH NEXT FROM delete_cursor INTO @tablename
END
CLOSE delete_cursor
DEALLOCATE delete_cursor
But this code doesn't compile either...
Any suggestions on how to get the code working within DTS?
Stef
How To Use DTS To Export To Access Using Global Variables ...
May 6, 2002Hi,
I am trying to export data from SQL7 to Access using a DTS package. I want to use the global variables within the package in sql statements.
How can I do that?
I have 2 global variables set: @DID & @Rowid
When tranfering data I used the sql statement to get the data from SQL7:
Select * from Tablex where ColumnX=@DID AND ColumnY > @rowid
but when I do that I get an error: You must declare the variable @DID
Why is that, if I declared it in the global variables section????
TIA.
DTS Global Variables Behaviour With &> 1 User
Jun 22, 2004Hi all
I have a DTS package that users of the database can run which basically acts like a 'live update' (as the database is based on a values produced from another system) and it takes roughly 30 or so seconds to run...
The dts package is not going to have a particularly large hit rate but i am interested in knowing what will happen if a user (user 1) attempts to run the package when it is in already in use by another user (user 2) ?
The dts package is from a sp using the dtsrun utility (passes in global variables).
I know that a second DTS will start before the first has finished but what i am not sure about is what will happen to the Global Variables when the second DTS package starts - i.e. will starting a second instance of the package with different variables have an effect on the first DTS's global variables while it is running?
I hope the answer is "No Tom, it work fine!"
DTS Task Properties And Global Variables
Mar 2, 2004Hey all,
I have a stored procedure, which need one variable as parameter. I am trying to call this stored procedure from my DTS Task and my parameter is defined as the Global Variable in DTS. here is the SP call within my DTS Task
declare @id int
select @id = DTSGlobalVariables('ClientId' ).value
exec sp_Update_DayPart @ClientId= @id
it gives me an error that DTSGlobalVariables function not defined. In this case how can i pass the value of Client Id which is my global variable to my SP.
Thanks in Advance
Creation Of Global Variables In Runtime.
Nov 9, 2006Is it possible to create "global" (package scope) variables in runtime in SSIS packages? If yes, please give some examples.
View 3 Replies View RelatedExecutePackage Task Global Variables
Jan 8, 2008
I'm trying to pass a global variable from a DTS package to the child packages that it calls using ExecutePackage tasks. I have selected the child's global variable on the Inner Global Variable tab and I have selected the parent's global variable on the Outer Global Variable tab. That doesn't work. Whatever I type into the Value column of the Inner Global Variable tab gets passed to the child package. How do I get the parent's global variable passed to the child package? Do I need to set the value on the Inner Global Variable tab to some special word to make it look for the parent's global variable? If I set it to nothing, nothing gets passed.
I have been able to make this work using an ActiveX Script task. I can set the Inner Global Variable value of the Task object to the parent's global variable value, but that's not the clean solution I'm looking for. There must be a simple way to do this because Microsoft's documentation brags about this feature, but they don't explain exactly how to do it.
Thanks
Steve
Setting Global Variables In A Script Task, HOW?
Jan 3, 2007I'm playing (and trying to learn)...
I have an FTP task in a for each containter and am setting the RemotePath using an expression (works great). Thought I could use this to start learning some of the scripting funtionality in SSIS (in a script task) so found some code in this forum (thanks Original Posters!) and tried my hand at some coding... Intent was to create a variable and then dynamically overwrite the Expression in the FTP Task from the script (I know I don't need to do this, I just wanted to use it for learning purposes)....
I have a variable named varFTPDestPathFileName (string) and want to set it to the value of varFTPDestPath (string) + varFTPFileName (string). Note: all variables are scoped at the package level (could this be the problem?). I did not assign any of the variables to ReadOnly or ReadWrite on the Script Task Editor page (seems to me that doing this in the code is a whole lot cleaner [and self documenting] than on the Task Editor page)...
I keep getting the following error:
"The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there."
Here is the script:
Public Sub Main()
Dim vars As Variables
' Lock for Read/Write the variables we are going to use
Dts.VariableDispenser.LockForRead("User::varFTPDestPath")
Dts.VariableDispenser.LockForRead("User::varFTPFileName")
Dts.VariableDispenser.LockForWrite("User::varSourcePathFileName")
Dts.VariableDispenser.GetVariables(vars)
' Set Value of varSourcePathFileName <<--- ERROR OCCURS HERE
vars("User::varSourcePathFileName").Value = _
Dts.Variables("User::varFTPDestPath").Value.ToString + _
Dts.Variables("User::varFTPFileName").Value.ToString
vars.Unlock()
Dts.TaskResult = Dts.Results.Success
End Sub
I would also like to be able to loop through the Dts.VariableDispensor to see the contents of the variables and their values.
Somthing like
For each ??? in vars
msgbox(???.Value)
Next
One other question... Do we always have to preface the variable with "User::" or "System::", if so can you explain why?
Any help would be much appreciated....
Usage Of Global Variables Inside SQL Task
Mar 11, 2008I've been looking around but haven't yet found the syntax for usage of global variables in an SQL Task.
I've set the global variable Id (see code below):
if (select field from table where id = @[User::id]) is null
select top 1 1 as response from table
else select top 1 0 as response from table
My objective with it is to set another global variable (@isNull). Supposably, when the selection returns null, I should set the variable to null, I did it by using the selections and mapping the response to that variable (is ther a better way to do so?).
When I try to execute this, it says the variable has not been defined.
Here is the error:
Error: Must declare the variable '@'.
I've also tryed it withou the brackets and the User:: thing in the beggining, (@id directly) and here is the response:
Error: Must declare the variable '@id'.
How should I access the global variables in the SQL code?
(BTW, I've checked the field in execution time and it is set to 23, the correct Id, so the block that preceedes this one is working properly)
Thanks,
How To Access Static Global Variables In WPF Form
Dec 5, 2007
Hi,
can i keep my global variables in app.xaml.cs file.
if yes then how will i access those variables in MyFirstApp.xaml.cs file??
if no then what is other alternate to create global variables so that it can be access through out the project??
thanx alot
Mohan