Variables Set Via Script Task Don't Change

Aug 24, 2006

I've got a container with two variables: xxxNextFile and xxxLastFile, both with EvaluateAsExpression = False.

My container has a dataflow followed by a script task followed by another dataflow. The first dataflow starts with ae existing raw file source from variable xxxLastFile and wants to write output to a new raw file from variable xxxNextFile. The script component then sets the value for xxxLastFile equal to the value of xxxNextFile and generates a new value for xxxNextFile. The final dataflow component does pretty much the same as the first one, i.e. it uses xxxLastFile for the source and xxxNextFile for the destination.

I've got two package scoped variables pkgFolderTemp and pkgCurrentTime that I use to come up with a unique filename, due to the fact that pkgCurrentTime evaluates at runtime. The script below should be pretty self explanatory.

The problem is that I expect the variable values to have changed after the script task runs but they don't. I have checked to see if I possibly had the xxxLastFile and xxxNextFile variables scoped at the script task level or even at either of the dataflow levels but they are only scoped for the container. BTW, I have xxxLastFile and xxxNextFile declared as readwrite variables and pkgFolderTemp and pkgCurrentTime delared as read only.

For anyone wondering why I have the script component jammed between the two dataflows rather than in a PostExecute event for the container, it's because I couldn't get it to run at all when I tried that. One thing at a time :)

Here's my script:
Imports SystemImports System.DataImports System.MathImports Microsoft.SqlServer.Dts.RuntimePublic Class ScriptMain Public Sub Main() Dim varLastFile As Variable Dim varNextFile As Variable Dim pkgFolderTemp As Variable Dim pkgCurrentTime As Variable Dim folder As String Dim time As String varLastFile = Dts.Variables.Item("User::xxxLastFile") varNextFile = Dts.Variables.Item("User::xxxNextFile") pkgFolderTemp = Dts.Variables.Item("User::pkgFolderTemp") pkgCurrentTime = Dts.Variables.Item("User::pkgCurrentTime") varLastFile.Value = varNextFile.Value folder = pkgFolderTemp.Value.ToString() time = pkgCurrentTime.Value.ToString() varNextFile.Value = folder + " emp_" + time MsgBox("User::xxxLastFile=" + varLastFile.Value.ToString() + " User::xxxNextFile=" + varNextFile.Value.ToString()) Dts.TaskResult = Dts.Results.Success End SubEnd Class

View 5 Replies


ADVERTISEMENT

Execute DTS 2000 Package Task Editor (Inner Variables Vs Outer Variables)

Sep 4, 2006

Hi,

I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.

João





View 8 Replies View Related

Can't Use Variables In SQL-Task

Jul 25, 2006

Hi,

I have a problem in my SSIS-package: If I try to execute a SQL-statement which uses a variable, it say "syntax-error". Here is how I tried:

1. a) Declared a variable. Name: tableName; Bereich (Sorry, I use the german version, no clue whats that on english versions): Package2; Type: STRING; Value: staticDB.StaticData_provider.dbo.C0123

b) Assigned this variable on SQL-Task->"Parameterzuordnung" as VARCHAR, parametername= NewParameterName

c) Used this on my SQL-Statement, SQLSourceType is directinput. Statement: "DELETE
FROM @NewParameterName"

d) Running this results in following error: "Der Parametername wird nicht erkannt" Translation: "Parametername was not recognized."

2. a) see 1.a)

b) see 1.b)

c) Used this on my SQL-Statement, SQLSourceType is directinput. Statement: "DELETE
FROM ?"

d) Running this results in following error: "Syntaxfehler, Berechtigungsverstoß oder anderer allgemeiner Fehler" Translation: "Syntaxerror, permision violation or other common error."

Any ideas?

Regards,

Jan

View 3 Replies View Related

SQL Task With Variables

May 4, 2006

I am trying to get the max of a fields in to a user variable

SELECT User::maxnafseq = MAX(nafseq) FROM naf

The error I get is

[Execute SQL Task] Error: Executing the query "SELECT User::maxnafseq = MAX(nafseq) FROM naf" failed with the following error: "SQL: Column 'MAXNAFSEQ' is not found.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.



Can someone help me with this?

Thanks



View 1 Replies View Related

SQL 2012 :: SSIS Variables - Change Value In Hundreds Place

May 21, 2015

in SSIS

( Left(@[User::YourFileName],3) == "AP_" ? 101 : ( Left(@[User::YourFileName],3) == "VD_" ? 102 : ( Left(@[User::YourFileName],3) == "BK_ " ? 103 : 000)))

In the above Variable i want to change the value in Hundreds place i.e from 101,102,103 to 201 ,202, 203 depending upon some Project param

lets say if project param is 1, the values assigned should be as 101,102,103

if project param is 2, the values assigned should be as 201,202,203

if project param is 3, the values assigned should be as 301,302,303 etc...

View 0 Replies View Related

How Can I Use The Variables In SQL Execute SQL Task?

Aug 24, 2006

I define a package variables "varOutTable" and "varFromTable".

and I insert a SQL Execute SQL Task into Control Flow

my sql command is

"Select * into @[User::varOutTable] from @[User::varFromTable]"

but the task failed,

it seems that sql task can't get the varOutTable and varFromTable

How can I use the Variables in SQL Execute SQL task?



thanks!!

View 14 Replies View Related

Executing SQL Task And Variables

Nov 20, 2007



Hi,

I'm trying to run a statement against all of my servers - I've got about 10 statements working, but am having trouble with this one. It's in a for loop with a connection that is changed by a recordset which is a list of my servers. Here is the code - In the step before, I've created the tempdb.dbo.DBROLES table.




Code Block
declare @dbname as varchar(200)
declare @mSql1 as varchar(8000)
DECLARE DBName_Cursor CURSOR FOR
select '[' + name + ']'
from master.dbo.sysdatabases
where name not in ('mssecurity','tempdb')
Order by name
OPEN DBName_Cursor
FETCH NEXT FROM DBName_Cursor INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
Set @mSQL1 = 'Insert into tempdb.dbo.DBROLES ( DBName, UserName, db_owner, db_accessadmin,
db_securityadmin, db_ddladmin, db_datareader, db_datawriter,
db_denydatareader, db_denydatawriter )
SELECT '''+ @dbName +''' as DBName ,UserName, '+char(13)+ '
Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner,
Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin ,
Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin,
Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin,
Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader,
Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter,
Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader,
Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter
from (
select b.name as USERName, c.name as RoleName
from ' + @dbName+'.dbo.sysmembers a '+char(13)+
' join '+ @dbNAme+'.dbo.sysusers b '+char(13)+
' on a.memberuid = b.uid join '+ @dbName +'.dbo.sysusers c
on a.groupuid = c.uid )s
Group by USERName
order by UserName'
--Print @mSql1
Execute (@mSql1)
FETCH NEXT FROM DBName_Cursor INTO @dbname
END
CLOSE DBName_Cursor
DEALLOCATE DBName_Cursor;





failed with the following error: "Must declare the variable '@dbName'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Put dbroles into table

The program '[10320] DBA_Repository_Load (1).dtsx: DTS' has exited with code 0 (0x0).


I'm going to try creating the procedure on each server - that will probably work - I just don't get why it's not recognizing that variable. The line it is referring to is FETCH NEXT FROM DBName_Cursor INTO @dbName. Thanks for any info on this!

View 3 Replies View Related

Script Task Variables

Sep 6, 2006

script task: there should be another way to select variables than the comma seperated list

One has to type in a whole list of variables, hoping not to make any mistakes

IntelliSense for example?

But hey, I'm not complaining...

greets

 

View 3 Replies View Related

Variables In Script Task

Oct 27, 2006



I recieve the below error when I attempt to execute a Script Component in a Data Flow Task:

The collection of variables locked for read and write access is not available outside of PostExecute.

This is the code I used:

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Output0Buffer.AddRow()

Output0Buffer.BillerId = CType(ReadWriteVariables("CustId").Value, Int32)

Output0Buffer.UserAccount = CType(Row.Column0, String)

Output0Buffer.UserSubAccount = CType("001", String)

Output0Buffer.PaymentAmount = CType(Row.Column1, Decimal)

Output0Buffer.DueDate = CType(Row.Column2, String)

Output0Buffer.EmailAddress = CType(Row.Column3, String)

Output0Buffer.ImportDateTime = DateTime.Now

Output0Buffer.FileName = CType(ReadWriteVariables("FileName").Value, String)

Output0Buffer.FileCreationDate = CType(ReadWriteVariables("FileCreateDate").Value, String)

End Sub

End Class



I use the variables "CustId,FileName,FileCreateDate" in the ReadWriteVariables collection. Any ideas would be appreciated as to how to get around this error.

View 19 Replies View Related

Integration Services :: DTSConfig File Being Used When Try To Edit To Change Some Variables

Jul 20, 2015

I've deployed my ssis pkg to the server and created a sql job to run this pkg. So far, everything is fine. Today, I got a request to change some variables inside the package which is part of the .dtsconfig.  I want to edit the deployed .dtsConfig but it won't allow me and always complained  that this file has been opened by another program. I am sure i've closed my ssis designer and other notpad, why can't I edit and save .dtsconfig file?

View 4 Replies View Related

DTS Task Properties And Global Variables

Mar 2, 2004

Hey 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

View 1 Replies View Related

Execute SQL Task - Passing Variables

Aug 28, 2007

how can you pass variables from one 'Execute SQL Task' to another?

View 9 Replies View Related

Execute SQL Task To Update Variables

Jun 30, 2006

Hi there

I'm attempting to update a variable using the Execute SQL task, I've read a lot of posts on this and seems reasonably simple but obviously not. The first time I ran it the variable was updated correctly, I then manually changed the variable value and since then it doesn't work.

I have a task with the following properties;

Resultset: SingleRow

SQLStatement: SELECT MAX(Player_Daily_Data_Pull_Date) as 'PlayerDaily' From Job_Control

On the resultset tab I have the resultsetname = PlayerDailyand the variable I want to update.

The variable has a type of datatime and it's scope is the container that I'm running the sql task within.

Any help would be appreciated.

Derek





View 5 Replies View Related

File Task Moving With Variables...

Apr 25, 2007

I have a 'file system task' moving files from one server to another for processing. I have defined the path and filename as separate variables. When I attempt to pass them together into the task I receive an error.



Source Path & Filename:

@[User:: DataSourceFolder]+"\"+ @[User::CD_PaidClaimSource]

Outputs:

\umrdwh2FTPCLAIMDAILY.DATA



Destination Path Now: (Filename not specified per another thread.)

@[User::WorkingFolder]



Original Destination Path: (Which did not work.)

@[User::WorkingFolder]+"\"+ @[User::CD_PaidClaimSource]



I receive validation errors:

Error 1 Validation error. CLAIMDAILY Move : Failed to lock variable "\umrdwh2FTPCLAIMDAILY.DATA" 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.". ClaimLoading_MASTER.dtsx 0 0





How do I overcome this without hard coding the path in the task?












View 11 Replies View Related

Problem With Variables In Execute SQL Task

May 2, 2007



I am trying to run a simple update (just to establish it works) passing in a variable in the execute SQL task. It is within a Foreach loop and the incoming data is from a RecordSet destination. One of the fields in the recordset is UniqueID. I have a variable called User::UniqueID which holds the uniqueID value. I then have a second variable which is the insert statement of

"INSERT rptlifespan_transactionimagecopy SELECT " + (DT_WSTR, 20)@[User::UniqueID] + ", null, null,null,null,null,null,null"

with delayvalidation set to True.



when I run the package i get the following error:



Error: 0xC0014054 at Execute SQL Task: Failed to lock variable "INSERT rptlifespan_transactionimagecopy SELECT 543, null, null,null,null,null,null,null" 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.".



543 is the value of my first UniqueID so that bit is working ok. What I do not know is why the insert statement is failing.



Please help i have very little hair left!!

View 16 Replies View Related

ExecutePackage 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

View 5 Replies View Related

For Loop * Sql Task Question About Variables

Oct 18, 2007

I want to iterate through a for loop and run some SQL update statements:

I have a variable wk_day that has a scope of the container

I want it to run this code:

update daily_sls_curr set mtd_ty_sls = (
select sum(ty_sls) from daily_sls_curr ci
where ci.fmonth = (select max(fmonth) from daily_sls_curr)
and wk_day <=@wk_day
and ci.store = daily_sls_curr.store
group by store)
where fmonth = (select max(fmonth) from daily_sls_curr)
and wk_day = @wk_day

repeatedly from the loop for 35 days of the fiscal month

When I parse the query, it gripes at the variable

How do I set up the query to work with the variable?

Thanks
JPS

View 7 Replies View Related

Execute SQL Task Editor - Variables

Jun 11, 2007

Hi,



Im trying to do an Insert:



INSERT INTO myTable(column1)

VALUES(..)



How can I use my parameter mapping value inside the SQL statement ablove?



Thank you.

View 5 Replies View Related

Using Execute SQL Task Expression With Variables

Jan 15, 2008

I have a package with variables which are set to default values, in this instance table names.

I then use a script task to change these variable values, these change fine and I have looked in the watch window whilst debugging to ensure that the values have changed correctly, and they have.

My Script Task then pipes to a Execute SQL Task, where I am using Expressions to set the SqlStatementSource. The Expressions statement uses the variables I change in my script task to form a SQL query.

However, at run-time the Expression appears to be fixed with the default variable values, it does not change even though the variable values have changed. Below is an extract from my expression for the SqlStatementSource.

i.e




Code Block
"USE [DBNAme]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].["+ @[User::StagingEventLossTable] +"](
[PolicyId] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
<snip>
...







Is an Expression value fixed from the moment the package is run and is therefore not dynamic during run-time?

If this is the case, how can I get around this? As I want to be able to change this SQL Statement on the fly at run-time.

Thanks

View 8 Replies View Related

Assigning Variables Via Execute SQL Task

Apr 15, 2008

Somebody provided me with this link http://www.sqlis.com/58.aspx that helped me understand Execute SQL Task and how to assign values to variables from a resulting SQL query.

But I'm not sure if this tutorial addresses my particular example.

Here is what I'm trying to do.

I have a a simple table called VarTable with 2 columns. Col1 is called VarName, and Col2 is called VarVal. As you may have guessed from the column names, I want to hold variable and value pairs in this table.

Right now, I have 2 entries as follows.

Var1, Hello
Var2, Bye

I want to assign Hello and Bye into two variables in my package.

In using the above tutorial, I figured out how to assign one variable by doing the following SQL and using "Single row" Result Set property.

Select VarVal from VarTable
Where VarName = 'Var1'

Then in Result Set section of the Execute Task Editor I map VarVal with one of my user variables.

How do I take care of the 2nd variable in the same Execute SQL Task, or do I need to have another Execute SQL Task? Based on my understanding, in order for me assign multiple variables in one Execute SQL Task, I need somehow construct a SQL statement to return as many columns as I have variables to assign, but this seems hard to do given the variable table structure I have.

Please let me know if my example is unclear.

View 13 Replies View Related

Update Variables With Scripting Task

Aug 28, 2006

Helo,
I'm using a simple scripting task to update the value of a variable:

Public Sub Main()
Dim EndeJahr As String
EndeJahr = InputBox("Text")
Dts.Variables("EndYear").Value = EndeJahr
MsgBox(Dts.Variables("EndYear").Value)
Dts.TaskResult = Dts.Results.Success
End Sub

The Package is working fine and the message box shows the actual value.

My problem is that when I change the of value of my variable, the value of variable is not changing, when I restart my package!!!
What's the problem?
any ideas?

View 8 Replies View Related

Trouble With Execute SQL Task And Variables

Mar 2, 2007

I have 3 Execute SQL tasks to find the max value of some fields. In two tasks the SQL

SELECT MAX(field bigint)
FROM TABLE

Is returned and stored as a string variable. My 3rd execute SQL task is

SELECT MAX(field int)
FROM TABLE

Is returned and stored as an object type. If I try to save it as a string, the execution fails with a mismatch.

WHY? Why aren't they all strings? What am I missing/doing wrong? This is very frustrating behavior. The queries are running against tables on SQL Server 2005 the field types are listed in the SQL above.

Thanks,
Matt

View 7 Replies View Related

Unable To Set Variables From Script Task

Dec 29, 2006

Hi,

I have a SSIS package that copies a file from one location and puts in in another with a datetime suffix on the end. This works fine in the script task. I've then added some additional code to the script task to set a variable with this new file location. The variable value gets set in the code, as I have a messagebox displaying the value, but doesn't get set to pass to the next step.



Public Sub Main()



Dim Filename As String

Dim FileLocation As String

Filename = CStr(Dts.Variables("AgencySpendFileNameWithDateTime").Value)

FileLocation = CStr(Dts.Variables("FileLocation").Value)

FileIO.FileSystem.CopyFile(FileLocation + "AgencySpend.txt", FileLocation + Filename, True)

Dts.Variables("SendLocation").Value = CStr(FileLocation + Filename)

MsgBox(Dts.Variables("SendLocation").Value)



Dts.TaskResult = Dts.Results.Success

End Sub

SendLocation is the value I want to set for the next step. I've checked and this variables scope is at the package level.

Any suggestions?

Paul

View 3 Replies View Related

SSIS Script Task Reading Variables In Vb.net DTS

Dec 15, 2006

I'm trying this code in a script task inside a foreach loop that gets a file in a foreach file enumerator and maps it to a variable FileName.
 In the script task I'm setting the ReadOnlyVariables to @[User::FileName]
: Imports System.IO Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
    Public Sub Main()         Dim fname As String = CType(Dts.Variables("FileName").Value, String)         File.Move(fname, fname + ".processed")         Dts.TaskResult = Dts.Results.Success     End Sub
End Class
But now getting this error:
Error: Failed to lock variable "\xxxxDataf1.csv" 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.".
 
As you can see the variable does appear to be resolving.

View 1 Replies View Related

Update SSIS Variables Inside DTS Task ?

Mar 12, 2008

Hi all,

I use a "Exec DTS 2000" task.

I have a variable user::MYVAR in my SSIS package, wich value is "PARENT".

I pass this variable as an outer variable to my dts 2000 package, wich receive it correctly, and then update it to "UPDATED".

After this task finishes, when I come back to SSIS, MYVAR is still at "PARENT".


Is it possible to see the update from the parent package ?


Thanks

View 6 Replies View Related

Read All Variables In A Script Task W/o Enumerating Them

Sep 19, 2007

I'm having trouble with my configurations. All of my configurations are targeting variables and the various properties are all set to expressions containing the targeted variables. I would like (during debugging) to dump out all of the variables.

Is there a way to access all variables from a script task? It looks like you have to enumerate the variables you want in the ReadOnlyVariables or ReadWriteVariables properties. I'd rather not miss one by forgetting to list it.

View 7 Replies View Related

How To Access Package Variables In A Custom Task

Jul 3, 2006

Hi Everyone,

I created a script task that uploads a file to a HTTPs site. The script task works well and now I want to turn this script task into a custom component...

I'm having problems on how to call the variables I have in the package, I already locked them for reading, but how am I suppose to access them and how can I put their values into strings so I can easily call them in the WebClient.UploadFile method?

Also, I am very new to custom tasks in SSIS.. and would appreciate if anyone of you can send me something (code, link, etc.) of a very simple custom task without any form or properties, that I can try to study.

Basically, what I want my custom task to do is,

1. Read the varialbes in the package.
2. use the variables values as input in my WebClient.UploadFile("varURLstring", "PUT", "varFilePath")

as you can see, my custom task is really simple... but I'm a noob here and I don't know where to go.

Thanks in advance,
Kervy

View 1 Replies View Related

Using Variables As Input Params In WebService Task

Jul 24, 2007

Hi,



I have a SSIS package with a Sequence which Contains a Webservice Task, in the input section of this task i want to pass a User Variable as Parameter for my webmethod. but it doesn´t work, it allways sends the variable definition as string "@[User::Filename]". so i searched Microsoft Technet how to pass User Variables in Webservice Tasks and found this site: http://technet.microsoft.com/en-us/library/ms187617.aspx



which says :



"
Variable


Select the check boxes to use variables to provide inputs. "

but there is no such checkbox on the input page of my Webservice Task... there is just the Value column which i can edit... but as mentioned before when i try to set the value to a variable it doesn work



i tried the following strings in the value column:



@[User::Filename]

@Filename

User::Filename

@User::Filename



any ideas?



thanks for your help

bye

as

View 5 Replies View Related

Setting Global Variables In A Script Task, HOW?

Jan 3, 2007

I'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....

View 17 Replies View Related

Using Package Variables As Parameters In Execute Sql Task

Feb 23, 2006

Hi,

I need to run an SQL command, in which one of the fields is based on the current time, represented as a string in a specific format.

I used a script task to create the string from the current time, and store it in a package variable named "newDate". Later, in the execute sql task, I have the following update statement: "update table_1 set field_1 = ?" (OLE DB connection is used)

and in the parameters mapping I set parameter 0 to the string package variable "newDate".

The problem is that on runtime, the sql executes with the default value of the variable - i.e. it doesn't take the new value.

I checked it and placed another script task after the first one, that simply tries to read the variable "newDate" and print it to screen, but even here the default value is used.

how can I use the new value of the variable?

thanks.

View 7 Replies View Related

Package Variables Passed To Execute SQL Task

Aug 14, 2007

Hi,

I am having trouble getting the Execute SQL Task to recognize the package-level variables in an SSIS package. The tasks fail execution. The package contains three Execute SQL tasks and none of them receive the variables. However, when I replace the '?' in the code with the variable values they execute fine. These are the steps I have taken thus far:

Made sure the three variables are package level.
Verified I'm using an OLE DB valid connection.
Verified the variables are properly mapped in the parameters mapping section of the task properties.
Tried all 4 types of variable to parameter mapping (even though '?' in the query and '0,1,2..' in the name should work).
Changed the Bypassprepare property to 'True'.
Breakpoints and watch windows to verify the variable values.



I actually tried the simple tutorials on a one-liner SQL statement that uses variables, but had the same problem. I know there must be something simple that I'm missing here, but any info would be greatly appreciated!

Thanks!

View 5 Replies View Related

Usage Of Global Variables Inside SQL Task

Mar 11, 2008

I'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,




View 6 Replies View Related

Loading Multiple Variables From Within The Same Execute Sql Task

Aug 28, 2007

Hi All,

I was wondering if it is possible to assign values to multiple variables from within the same execute sql task, ie I want to use only one execute sql task and have multiple T-SQL statements within it and then assign the results of these sql statemenst as values to multiple variable.

Typically I would declare variables var1 , var2 and var3 , then can I just add one execute sql task and have mutiple sql statements within it? something like this

select max(id) from table1

select max(id) from table2

select max(id) from table3

Thanks

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved