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.
I have been struggling trying to read and/or write package level variables from within my custom task. I'd like to be able to get and set values from within the Execute method of my custom task. I have searched this forum and the books online and can't seem to find the answer. I thought maybe I could use an expression on my task (mapping the package variable to a custom task public property) but that doesn't seem to be working for me. I also would have thought I could use the VariableDispenser object from within my task but the collection is empty. I have 3 package level variables configured and can't seem to find a way to access them (with intentions of getting/setting). Could someone point me to a good doc or provide an example that may accomplish this? Thanks!
(I'm using package level variables as a means of passing simple information between tasks that are not using a DB, if there is a better way I'm open to suggestions.)
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.
I am using a Script Component and I have a Read/Write Variable varStatusCase (as assigned in the Custom Properties of my Script Component). I used this inside my script to get a specific value. However, when I ran it I get this error:
The collection of variables locked for read and write access is not available outside of PostExecute.
Hello ASP.NET C# and SQL gurus I want to read the results of a set of rows into session variables -- how is it possible? Let me try explain. I have a query which returns multiple rows, e.g. the following query SELECT PROFILE_ID, PROFILE_NAME FROM USER_PROFILES returns 5 rows i.e 5 sets of profile_ids and profile_names. Now, I want to capture these and store them in session variables thus. Session["PROFILEID_1"] = Session["PROFILEID_2"] = Session["PROFILEID_3"] = Session["PROFILEID_4"] = Session["PROFILEID_5"] = Session["PROFILENAME_1"] = Session["PROFILENAME_2"] = Session["PROFILENAME_3"] = Session["PROFILENAME_4"] = Session["PROFILENAME_5"] =
Hello Guys, in SSIS I want to get a set of data and do some modifications on it before I insert it into the destinatipn. So far so good. Some of the modifications will include comparisions between two columns and if certain field is NULL then I want to get the value from the other one I was comparing to. When using conditional splits, I only get the rows to be redirected so that I can do whatever next. However, I want like use the variables as a storage so that I can put the value of one of the two columns in this variable which will be actually loaded into the destination finally. Any help? Thanks
Hi all, first post, and I am desperate. I have a SqlDataSource with a Select, Update and Delete command. From what I understand, scalar variables should be read automatically from the GridView's BoundField columns when it executes a command on it. Here is my code:
I apologize for the way the code is put in, the code thing cut off a lot of the text! The problem I'm getting is, when the 'Update' button is hit, I get the error: Must declare the variable '@TeamId'.
I've tried putting "Update Parameters", that takes away the error, but the row does not update. I've browsed the internet and saw the same problems in lots of areas, but either a) none of the solutions work for me, or b) they don't really apply to my case. I'm using ASP .NET 2.0, and (obviously) C#. SQL Server database. Any help is greatly appreciated. Thanks in advance,
I'm reading a Flat File. The 1ste record containts special Info that is needed for first preparing the database on a buld insert of the remaining lines.
How could i realize this, just read 1 line, store some of that data into variables, execute some proc's and then read the rest of the file ?
Hello, I am trying to configure a Script Component as a data source. Although this should be a simple exercise, I am running into a problem.
My control flow contains a Foreach Loop with a file iterator. The Directory Expression of the Foreach Loop Editor is supplied by an expression mapped to a package level variable called inputdirectory. The FileNameRetrieval Expression is mapped to a package scoped variable called filename.
My data flow is encapsulated by the Foreach Loop, and contains a Script Component as Source for the Data Flow Source, and a Flat File for the Data Flow Destination. The contents of the Script Designer are as follows:
Code Block Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Imports System.IO Public Class ScriptMain
Inherits UserComponent Dim thisFileDate As Date Dim thisFileName As String Dim thisFilePath As String Public Overrides Sub CreateNewOutputRows()
thisFileName = ReadOnlyVariables("filename").Value.ToString() thisFilePath = ReadOnlyVariables("inputdirectory").Value.ToString() thisFileDate = File.GetCreationTime(thisFilePath & "" & thisFileName) FileSpecBuffer.AddRow() FileSpecBuffer.FileName = thisFileName FileSpecBuffer.FullPath = thisFilePath & "" & thisFileName FileSpecBuffer.CreateDate = thisFileDate End Sub End Class
When I debug the package, I get the following error:
The collection of variables locked for read access is not available at this point.
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponent.get_ReadOnlyVariables()
at ScriptComponent_67311120e6eb4162a3ea1f70847f04de.ScriptMain.CreateNewOutputRows()
at ScriptComponent_67311120e6eb4162a3ea1f70847f04de.UserComponent.PrimeOutput(Int32 Outputs, Int32[] OutputIDs, PipelineBuffer[] Buffers)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)
My googlefu fails me at reconciling this. I have read various posts about changing the line ReadOnlyVariables("filename").Value to ReadWriteVariables("filename").Value, and handling the buffer assignment in PostExecute. The problem is that all examples shown are either for Script Component as Destination or Script Component as Transformation. I have tried playing with the Custom Properties in the Script Commponent set the ReadOnlyVariables and ReadWriteVariables, using a PreExecute method, a PostExecute method, all with different errors returning. I'm at a loss here. Could anybody provide me with a simple working example so that I can correctly populate my output buffer in the context of Script Component as Source?
I fully understand that I could just run a Script Task Using System.IO.Directory, and System.IO.File, but I really want to make this package work in the manner I've described. Any help would be appreciated.
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."
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.
I have to implement a functionality which is able to read files from FTP and file reside at subfolders and some of subfolder is also dynamic means it may be any thing.
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!
I use the variables "CustId,FileName,FileCreateDate" in the ReadWriteVariables collection. Any ideas would be appreciated as to how to get around this error.
I'm trying to read an xcel file from within the script task of SSIS. I've seen posts that kind of steer me in the right direction, but none to do exactly what I'm looking for. I am loading data from flat files into a database where the source file has text longer than the target column. In order to get the text to "fit" i would like to apply a series of standard abbreviations, for example replace "DOCTOR" with "DR". I started off by simply writing a series of if-then logic; if length (<source>) > <targetlengh>, then replace <long> with <short>. After writing this for 40+ abbreviations I soon realized that this is not the best way to do this. So I came up with the idea of storing all the abbreviations in a .xls file which would be opened and applied via a script task. The problem is that I'm new to VB.net (actually any flavor of VB) and have not been able to read the abbreviations file as of yet.
This is what I've tried thus far, which results with this error: Exception from HRESULT: 0xC001400B
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
' Add your code here
'
Dim local_SQLConnectionManager As New Microsoft.SqlServer.Dts.Runtime.Wrapper.ConnectionManagerAdoNet()
Dim local_SQLConnection As New System.Data.SqlClient.SqlConnection
Dim local_SQLDataReader As System.Data.SqlClient.SqlDataReader
Dim local_SQLCommand As New System.Data.SqlClient.SqlCommand
'local_SQLConnectionManager = Microsoft.SqlServer.Dts.Runtime.Wrapper.cConnections("Name of ConnectionManager")
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
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.
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.
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.
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?
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
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.
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.
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
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?
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.
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.
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?
