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
ADVERTISEMENT
Oct 13, 2006
Hello all,
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.)
Jay_G
View 3 Replies
View Related
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
Aug 3, 2007
Hi,
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.
How do I repair this?
Thanks,
cherriesh
View 6 Replies
View Related
Oct 23, 2007
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"] =
Thanks in advance!
Fouwaaz
View 2 Replies
View Related
Aug 2, 2007
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
View 1 Replies
View Related
Mar 11, 2007
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:
<asp:GridView
ID="teamGrid"
EmptyDataText="n/a"
DataKeyNames="TeamId"
AutoGenerateColumns="false"
DataSourceID="teamSource"
OnRowEditing="validateEdit"
OnRowDeleting="validateDelete"
runat="server">
<Columns>
<asp:CheckBoxField DataField="TeamApproved" HeaderText="Approved" />
<asp:BoundField DataField="TeamName" HeaderText="Team Name" />
<asp:TemplateField HeaderText="City">
<ItemTemplate>
<asp:Label runat="server"><%# Eval("City") %></asp:Label>, <asp:Label runat="server"><%# Eval("ProvinceCode") %></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="RequestedDivision" HeaderText="Division Req." ControlStyle-Width="60px" />
<asp:BoundField DataField="DivisionCode" HeaderText="Assigned Division" ControlStyle-Width="60px" ConvertEmptyStringToNull="true" NullDisplayText="n/a" />
<asp:BoundField DataField="DivisionNumber" HeaderText="Division Number" ControlStyle-Width="60px" ConvertEmptyStringToNull="true" NullDisplayText="n/a" />
<asp:BoundField DataField="Password" HeaderText="Password" ConvertEmptyStringToNull="true" NullDisplayText="n/a" />
<asp:CheckBoxField DataField="Paid" HeaderText="Paid" />
<asp:HyperLinkField Text="view players" ItemStyle-Width="70px" ItemStyle-HorizontalAlign="Center" DataNavigateUrlFields="TeamId" DataNavigateUrlFormatString="viewTeamPlayers.aspx?teamId={0}" ShowHeader="false" />
<asp:CommandField ItemStyle-HorizontalAlign="Center" ItemStyle-Width="30px" ButtonType="Link" EditText="edit" ShowEditButton="true" ShowHeader="false" />
<asp:TemplateField ShowHeader="False" ItemStyle-HorizontalAlign="Center" ItemStyle-Width="30px">
<ItemTemplate>
<asp:LinkButton runat="server" CausesValidation="False" CommandName="Delete" OnClientClick='return confirm("Deleting this team will also delete the players. Are you sure you wish to continue?");' Text="delete" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<asp:SqlDataSource
ID="teamSource"
ConnectionString="<%$ ConnectionStrings:MB %>"
SelectCommand="SELECT TeamId, TeamName, ProvinceCode, City, DivisionCode, DivisionNumber, TeamApproved, RequestedDivision, Paid, Password, CaptainId, Player2Id, Player3Id, Player4Id FROM [Team]"
UpdateCommand="UPDATE [Team] SET TeamName = @TeamName, DivisionCode = @DivisionCode, DivisionNumber = @DivisionNumber, TeamApproved = @TeamApproved, Paid = @Paid, Password = @Password WHERE TeamId = @TeamId"
runat="server" />
</form>
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,
- Branden
View 2 Replies
View Related
Dec 6, 2006
I have a set of comma separated variables in a Script Component list. I want to access them in Script code and use them to build string in the code.
View 9 Replies
View Related
Apr 7, 2008
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 ?
Kind Regards.
View 4 Replies
View Related
Sep 13, 2007
Is this possible. How can one do it? Thanks for any info you have.
View 2 Replies
View Related
Jan 17, 2008
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.
Thanks,
John T
View 5 Replies
View Related
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
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
Apr 8, 2008
Hi All,
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.
FTP Folder structure is like that
Root-
-----------1 (any name)
-----------------1.1(any name)
--------------------------1.1.1 (Static name)
----------------------------------files...(any name)
I tried to read file through loop but i am getting error. i able to get folder name at this level "-----------1 (any name)".
Please help me.
Thanks in advance
From
Manish Jain
View 6 Replies
View Related
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
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
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
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
Apr 25, 2007
Hi everyone,
I€™d like to read the SqlStatement property for a Sql Task from a Script Task (previously Sql Task is executed) in execution.
Keeping on mind that such SqlStatement receives an input parameter.
Is it possible? I think so but how do I such thing?
Let me know if you need further details.
Thanks a lot for your time and thoughts,
View 1 Replies
View Related
May 22, 2006
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")
local_SQLConnectionManager.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:DepartmentsDataServicesProjectsQNXT ConversionXWalksDesciptionAbbreviations.xls;Extended Properties=Excel 8.0;HDR=YES;"
local_SQLConnection = CType(local_SQLConnectionManager.AcquireConnection(Nothing), System.Data.SqlClient.SqlConnection)
local_SQLConnection.Open()
local_SQLCommand.Connection = local_SQLConnection
local_SQLCommand.CommandText = "SELECT * from [sheet1$]"
local_SQLDataReader = local_SQLCommand.ExecuteReader()
If local_SQLDataReader.Read Then
'Microsoft.SqlServer.Dts.Runtime.Wrapper.Variable("Just a variable").Value = local_SQLDataReader.GetString(0)
End If
local_SQLDataReader.Close()
End Sub
View 5 Replies
View Related
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
Aug 28, 2007
how can you pass variables from one 'Execute SQL Task' to another?
View 9 Replies
View Related
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
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
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
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
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
View Related
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
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
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
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
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
