HowTo: Read/write Package Level Variables In Custom Task
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
ADVERTISEMENT
Sep 13, 2007
Is this possible. How can one do it? Thanks for any info you have.
View 2 Replies
View Related
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
Feb 1, 2007
Hi,
I'm trying to simplify the deployment process of my project. I already had some troubles with the config files but lets say I solved that issue. I'm going to read a flat file and set the variables of my packages from this file. I was thinking to use a Script Task to do that but I will need to copy this task in every package (I have at least 30). So if I want to make some change this will be painful.
Then, I came up with the idea of creating a Custom Task called Config File Task. I'm working on this but I got stuck trying to get the variables from the package that is running my Config File Task.
This is the code I had in the Script Task:
Dim streamReader As New StreamReader(Dts.Variables("ConfigFilePath").Value.ToString)
Dim line As String
Dim lineArray As String()
Dim variableName As String
Dim variableValue As String
Dim readConfigurations As Boolean = False
While (streamReader.Peek() <> -1)
line = streamReader.ReadLine()
If line = "[CONFIGURATIONS]" Then
readConfigurations = True
ElseIf line = "[/CONFIGURATIONS]" Then
readConfigurations = False
Else
If readConfigurations And line <> "" Then
lineArray = line.Split("|".ToCharArray())
variableName = lineArray(0).Trim()
variableValue = lineArray(1).Trim()
If Dts.Variables.Contains(variableName) Then
Dts.Variables(variableName).Value = variableValue
End If
End If
End If
End While
Dts.TaskResult = Dts.Results.Success
All I want to do is set the variables that exists in each package from the config file. In my UI Class (ConfigFileTaskUI.cs) I can have access to the variables via the TaskHost which is passed as an argument of Initialize() method.
Any thoughts? I'd really appreciate some help!
P.S. I've been working on this for 2 days!
View 11 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
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
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
Nov 1, 2007
I want to write a variable say testVar in PostExecute of my script. I also want to read the same variable at the start of my script block.
How to specify this variable in the script transformation editor
ReadOnlyVariables = ????
WriteOnlyVariables = ???
If i just define
WriteOnlyVariables = testVar
will it work . I mean i cannot use testVar in both read & write
Any suggestions...
Thanks
View 3 Replies
View Related
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
Feb 27, 2006
I am trying to reference a package level variable in a script component (in the Code) and am unable to do so successfully. I have it listed as a ReadOnlyVariables in the custom properties of the script component, however unable to reference it in the code.
Any help will be appreciated.
Thanks,
Andy.
View 10 Replies
View Related
Feb 21, 2007
Hi,
Given that Task.Validate() exposes the package's VariableDispenser, but LogProviderBase.Validate() doesn't...
http://msdn2.microsoft.com/fr-fr/library/microsoft.sqlserver.dts.runtime.task.validate.aspx
http://msdn2.microsoft.com/fr-fr/library/microsoft.sqlserver.dts.runtime.logproviderbase.validate.aspx
...I guess that simply means that I can't access a package's variables within a custom log provider? Can anyone comment/confirm? Any other options/routes to achieving the same..?
We live in hope,
Tamim.
View 3 Replies
View Related
Oct 1, 2007
I am interested in determining if package variables can be read from a custom log provider. I am able to pass the values into the custom log provider using an expression in a custom connection manager. My intention is to used the values of these variables upon package failure so I can capture the context of the failure. Unfortunately, if the value of the variable is changed at runtime, this update is not reflected in the custom log provider.
Is it possible to get direct access to the package variables from the custom log provider?
Thanks,
Marc
View 1 Replies
View Related
Oct 26, 2006
Hi
I am developing custom dataflow component ,I need to get the of variables of the current package in the component , how can i get it?
Thanks
Mani
View 6 Replies
View Related
Sep 26, 2006
Hi,
I am trying to develop a SSIS package which will read the records from the flat file and insert them into a destination table. I have some validations written in script component. I have declared two Read Write variables with package level scope. when i try to assign a value to the variable in the script component and run the package, the package throws me an error "The collection of variables locked for read and write access is not available outside of PostExecute".
What should be done to over come the problem please help me on this regard
Thanks
Madhavan.M
View 15 Replies
View Related
Oct 27, 2006
Hello All,
I have searched on this forum for a similar question but couldn't find it so I apologize if this has been asked. If so, I'd greatly appreciate a link to the question. I have created a custom task and am trying to read an xml package configuration file within my custom task. To be more specific, I have added an ADO.Net Connection to my database onto the package and have generated the appropriate tags within my package's configuration xml file. I'm not seeing the classes I should use to access the configuration file of the package I've created. Does anybody have any ideas how I can accomplish this or a link to a document that might cover the material? Thanks!
Jay_G
View 3 Replies
View Related
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
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
Apr 28, 2004
I have a package (Package1) that is run from another package (Package2) via a Execute Package Task. I set a Global Variable called sErrorMessage in the in Package1 and would like to access that Global Variable in an ActiveX Script Task in Package2. How can I do this?
View 6 Replies
View Related
Feb 13, 2007
Hi,
Let's say I have a package taking as parameter "InvoiceID". I want to execute this package as a child in another package. The parent package gets the list of invoices to produce and calls the child package for each entry of the list.
How do I pass the InvoiceID to the child? I know I can use the parent's variables from the child but I don't want the child package to be dependant on the parent package. For example, I might want to execute the "child" package as a stand-alone in development (providing it with a predefined InvoiceID). I might also want to call the same child package from another parent package with other variable names.
What I would like to do is "push" the value instead of "pulling" it. I know it's possible using the command line and the /SET option (ex.: /SET Package.Variables[InvoiceID].Value;' 184084)... Is it possible using the Execute Package Task?
Thanks
View 4 Replies
View Related
Feb 6, 2007
Hi,
I created a Custom Task which it has a Property called ConfigFilePath. I'm overriding the Validate() method from Task. I want to throw an error if my property ConfigFilePath is empty and if the expression for this property is empty. So far, I can check if the property is empty but I don't see how I can access the Expressions Collection of my Custom Task.
Any thoughts? I'd appreciate your help.
View 5 Replies
View Related
Jul 3, 2006
I have a requirement to create many SSIS packages and no datatransform is required so the BCP task looks a good contender providing it can do both import & export
is it possible to parse the values in bold as package variables into the BCP task. If so how?
BULK INSERT ipcs_wvg.dbo.extract
FROM 'D:IPCSextract.csv'
WITH (FORMATFILE = 'D:ipcsqueryextract.xml');
Thanks in advance
Dave
I have a global database called ETL Configuration for all my SSIS packages that uses a single table. So I can create three global variables
USE [ETLConfiguration]
CREATE TABLE [dbo].[SSIS Configurations](
[ConfigurationFilter] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[ConfiguredValue] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[PackagePath] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[ConfiguredValueType] [nvarchar](20) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
Here is the data I would put in here
ConfigurationFilter = 'MySSISPackageName'
ConfiguredValue = 'D:IPCSextract.csv'
PackagePath = 'Package.Variables[User::gsFileName].Properties[Value]'
ConfiguredValueType = 'String'
ConfigurationFilter = 'MySSISPackageName'
ConfiguredValue = 'D:ipcsqueryextract.xml'
PackagePath = 'Package.Variables[User::gsFormatFile].Properties[Value]'
ConfiguredValueType = 'String'
ConfigurationFilter = 'MySSISPackageName'
ConfiguredValue = 'ipcs_wvg.dbo.extract'
PackagePath = 'Package.Variables[User::gsTableName].Properties[Value]'
ConfiguredValueType = 'String'
--Database connection info
ConfigurationFilter = 'MySSISPackageName'
ConfiguredValue = '.mssql2005'
PackagePath = 'Package.Connections[MyDatabaseName].Properties[ServerName]'
ConfiguredValueType = 'String'
ConfigurationFilter = 'MySSISPackageName'
ConfiguredValue = 'MyDatabaseName'
PackagePath = 'Package.Connections[MyDatabaseName].Properties[InitialCatalog]'
ConfiguredValueType = 'String'
I have looked at lots of options to automaticly create SSIS packages and have a hunch that that simple can be better: All these solutions look way to complex to what I want to achieve--
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=17484&SiteID=1
http://lakshmik.blogspot.com/2005/05/how-to-programmatically-create-ssis.html
Create DataFlow Package Sample
http://msdn2.microsoft.com/en-us/library/ms161541.aspx
SMOTableList Sample
http://msdn2.microsoft.com/en-us/library/ms161564.aspx
View 4 Replies
View Related
Mar 29, 2004
This came from our actuaries, - create a formula generation and processing engine.
They have a dozen of well-normalized base tables that contain statistical data on competitors, broken down by state, county, city, and zip.
The result should contain aggregated breakdown by the same set of data elements, but also include along with other things an input formula and its result for the Base Rate.
The whole thing is a mini-Monte Carlo simmulation.
THE TRICK:
If the formula today looks like:
x = ((a + b) * (c - d + e)) / (a * (c - d) * (f - d) * (e + d))
then tomorrow it may look like:
x = a * b * c * d * e * f
THE REAL TRICK: It should not be based on dynamic SQL!!!
Any thoughts?...
View 4 Replies
View Related
Mar 4, 2014
I've got a package in SSIS 2012 that has an Execute SQL task in the control flow level.
The SQL in question does an Upsert via the SQL merge statement. What I want to do, is return the count of records inserted and records updated (No deletes going on here to worry about). I'm using the output option to output the changed recs to a table variable.
I've tried returning the values as:
Select Count(*) as UpdateCount from @mergeOutput where Action = 'Update'
and
Select Count(*) as InsertCount from @mergeOutput where Action = 'Insert'
I've tried setting the resultset to both Single rowset and Full rowset, but i'm not seeing anything returned to the package variables I've set for them (intInsertcount and intUpdatecount).
View 2 Replies
View Related
Mar 31, 2004
something like max,min?
help please!!!
View 5 Replies
View Related
Jan 12, 2012
i attached adventure works in sql server 2008 and it showing as read only ,make it read write or remove read only tag from database.
View 11 Replies
View Related
Mar 24, 2015
How to identify whether the files are in read write or read only?
View 1 Replies
View Related
Aug 26, 2015
I'm trying to do Sharepoint DR with Log Shipping and every thing configured except one thing which is switch the WSS_Content (Standby /Read-Only) DB to be ready and Write.
I tried from
GUI or ALTER DATABASE [WSS_Content] SET
READ_WRITE WITH NO_WAIT
but I received the below error:
Database WSS_Content is in Warm Standby
View 9 Replies
View Related
Jan 18, 2008
I have two database files, one .mdf and one .ndf. The creator of these files has marked them readonly. I want to "attach" these files to a new database, but cannot do so because they are read-only. I get this message:
Server: Msg 3415, Level 16, State 2, Line 1
Database 'TestSprintLD2' is read-only or has read-only files and must be made writable before it can be upgraded.
What command(s) are needed to make these files read_write?
thanks
View 7 Replies
View Related
Aug 15, 2014
Can a user of db owner role of a database change the databse option to read only and read-write?If not what permission I need to grant to the user?
View 1 Replies
View Related
May 19, 2008
Hi,
i created a ssis task "send mail" and everything works fine. I uses parameter to pass recipient-email-address, mail-subject and mail-body.
Now I like to use linebreaks in the mail-body but I don't know how. I tried:
'my mail text ' + char(13) + char(10) + ' next line for my mail'
I also tried: 'my mail text next line for my mail' and 'my mail text \n next line for my mail'
I created a procedure to run the dts-package with the following code:
Code Snippet
@toAddress VARCHAR(255),
@subject VARCHAR(255),
@mailBody VARCHAR(max),
@exitcode INT = 0 OUTPUT
AS
SET NOCOUNT ON
--lokale Variablen
DECLARE @String VARCHAR(1024)
BEGIN TRY
-- next line just for testing linebreak
SET @mailBody = 'line one in mail ' + char(13) + char(10) + 'next line in mail'
SET @String='dtexec /Ser <myServerName> /SQ DELELPLGDB01/DTS-Pakete/pkt_sendMail /Set Package ask_sendMail.Variables[Benutzer::toAddress].Value;"'+@toAddress+'" /Set Package ask_sendMail.Variables[Benutzer::subject].Value;"'+@subject+'" /Set Package ask_sendMail.Variables[Benutzer::mailBody].Value;"'+@mailBody+'"'
EXEC @exitcode = master.dbo.XP_CMDSHELL @String
select @exitcode as exitcode
END TRY
Thanks for your Help.
MatDie
View 11 Replies
View Related
Jun 22, 2007
I have used an access linked table to import data from a sharePoint list hosted on SharePoint 2007 server.
I hope someone else has done this. (1)I Does anyone know of any other way to pull data from a SharePoint 2007 list.
(2)Has anyone pushed data to a SharePoint List from a SQL Server table?
I tried doing that via the linked table but the destination task of the data flow task does not allow me to push data into the table.
(3) Has anyone designed an SSIS package to write data to an AccESS table from a SQL Server table?
Any help would be appreciated
View 4 Replies
View Related
Aug 16, 2006
I am writing a custom task that has some custom properties. I would like to parameterize these properties i.e. read from a varaible, so I can change these variables from a config file during runtime.
I read the documentation and it says if we set the ExpressionType to CPET_NOTIFY, it should work, but it does not seem to work. Not sure if I am missing anything. Can someone please help me?
This is what I did in the custom task
customProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
In the Editor of my custom task, under custom properties section, I expected a button with 3 dots, to click & pop-up so we can specify the expression or at least so it evaluates the variables if we give @[User::VaraibleName]
Any help on this will be very much appreciated.
Thanks
View 3 Replies
View Related
