I have a component who's OnPreExecute event handler contains a Script Task. I would like to use this Script Task to access the component's properties. Can anyone provide script examples for doing this if this is possible?
View 6 RepliesIn a Data Flow, I have the necessity to use a SSIS variable of type €œObject€? inside Script Component and assign to it the content of 'n' variables of string type.
On exiting from the script the variable of type object should contain something like in the following lines:
AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
BBBBBBBBBBBBBBBBBBBBBBBBBBBBB
CCCCCCCCCCCCCCCCCCCCCCCCCCCCC
DDDDDDDDDDDDDDDDDDDDDDDDDDDDD
€¦€¦€¦€¦€¦€¦€¦.
€¦€¦€¦€¦€¦€¦€¦.
On exiting from the data flow I will use the variable of type Object in a Script Task, by reading each element in a cyclic fashion.
Is there anyone who have experienced something like this? Could anyone provide any example of that?
Thanks in advance!
A common issue that I run across with clients is they want only want to process a file if it's finished transmitting to the server. This SQL Server 2005 task reads the properties of a file and writes the values to a series of variables. For example, you can use this task to determine if the file is in use (still be uploaded or written to) and then conditionally run the Data Flow task to load the file if it's not being used. You can also use it to determine when the file was created in order to determine if it must be archived.
http://www.pragmaticworks.com/filepropertiestask.htm
Is there a way of disabling a custom property in a component so that during design-time the property is grayed out? I looked around the properties of IDTSCustomProperty90 and nothing sticks out.
Thanks
Mike
I have a custom component that takes in unicode stream and converts it to ascii text. However I would like to make my default string length and code page editable in the standard GUI editor. Right now I can set the default to 1000 characters, but when I try to change it, it says "Property value is not valid"
Any ideas?
Thanks!
Hi,
I recently came across a DTS made by an experienced DBA and was impressed by the use of Dynamic Properties DTS Task used. As I understood the DTS was generic and if I'm not mistaken, can be easily transferred to another server/machine on an AS IS basis and without having to change any of the properties (server name, login, password etc.) for the source or destination server.
This seems to be a really neat feature.
I tried to put this to use but am having problems regarding how to proceed... Unfortunately I have not been able to find any article either which addresses this particular request and takes a novice step by step so that this feature can be used.
Will appreciate any help.
Thanks
Hi,
DTS now has a dynamic properties task in sql 2000 which at a first glance is pretty cool. However i do not know if it would sort out a particular issue I have.
I need to have a generic DTS package that pulls data from different locations based on the project selected by a user. The source is a btrieve database
The name of the tables change with the project, so for example the F24 project would have its source table named F24TACT and the IFO source would have its source table named IFOTACT.
I need to be able to dynamically retrieve the table name from a local SQL table and assign this name to the data pump for the data extraction..
I don' t know if this is achievable in DTS. I can retrieve the value into a look up variable but how can i set it at runtime ?
thanks
Tony
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
Hello,I am building a packge where an FTP task needs to pull down a singlefileevery day from a specific location. The location will only have theone file.The file name will be different every day. A sample of one of the filenameslooks like this:CDNSC.CDNSC.SC00015.04012007The file names will be different every day, as the last eight digitsrepresent the date of the data in the file. The source files will belocatedin a subdirectory called 'outgoing'.My first approach to this has been to use a Dyamic Properties Task tosetthe SourceFileName of the FTP Task. I tried using a sting GlobalVariableexpressed as *.*.*.* thinking a wildcard would work, but it didn't.What approaches can I take so that the Source Filename in the FTP taskwilldymanically update each day to get the one file?Thank you for your help!cdun2
View 1 Replies View RelatedHi 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,
Hi ,
What is the equivalent of DTS Dynamic Properties task into SSIS ?
How do I convert this task to SSIS ?
Thanks,
Vikas
HI,
How can I programmatically change the properties of Send Mail task using Script Task. I want to change the From, To and Attachment parameters of the Send Mail task.
If the Script Task can't do it, Is there any alternative to do?
Thanks
Hi All,
I am working on a SSIS package which is using a Script task, now I have all the connection properties set up in the .NET script using connection strings, what do I need to do if I have to set this up using a config file or something else which is more secure (I dont want to leave the connection information in the script). Please Advice.
Thanks
Hi,
I've created a Custom Data Flow Component and added some Custom Properties.
I want the user to set the contents using an expression. I did some research and come up with the folowing:
Code Snippet
IDTSCustomProperty90 SourceTableProperty = ComponentMetaData.CustomPropertyCollection.New();
SourceTableProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
SourceTableProperty.Name = "SourceTable";
But it doesn't work, if I enter @[System:ackageName] in the field. It comes out "@[System:ackageName]" instead of the actual package name.
I'm also unable to find how I can tell the designer to show the Expression editor. I would like to see the elipses (...) next to my field.
Any help would be greatly appreciated!
Thank you
I have a Dynamic propeties task in dts 2000 that process/executes a global variable.
The global variable basically executes a bat file.
How do i set this up in ssis. The migration failed to properly convert this task.
Please help.
Thank you.
Hello All Experts,
I have created one custom task with PropertyGrid Control and two button on it. I have everything under one class library project.
Problem I am facing is when i load task and clik on Edit I can not see those properties into that GUI and even functionlity of those two buttons (OK and Cancel) not working but I am able to see those properties in default property window.
If I create this GUI as a seperate window application then I am able to see those properties in GUI and buttons also working but in SSIS I am not able to load the task.
After reading on internet about SSIS they suggest to create everything under one project which I did.
Basically I am trying to populate connection managers like Source Connection and Destination Connection when I load this task and there are much more backend functionlity but at first step i m stuck and not able to see those properties in GUI.
Please help and give your input on it. I was following "Increment Task" example given by MSDN.
If you need more info let me know.
Thanks
I am trying to read in a flat file, transform the fields and store into a destination database.
In DTS, this works using Transform Data Task Properties. I define the columns and then have a VB script on the Transformations tab that changes any bad data.
Is there a way to do this in SSIS that I can define the column transformations and re-use my VB scripts?
Linda
I would like to store the file properties like (title, created by , created time, modified time) and load in to a audit table along with file name. I know how to design this process inside a foreach lookup with script task and execute sql task.
I'm trying to achieve this by editing my existing code which captures the most recent file based on its file properties.Â
public void Main()
{
// TODO: Add your code here
var directory = new DirectoryInfo(Dts.Variables["User::Csv_Source"].Value.ToString());
FileInfo[] files = directory.GetFiles("*.csv");
DateTime lastModified = DateTime.MinValue;
[Code] ....
Does anyone know what the different values are (and what they mean) on the "Database Locking Mode" column off of the advanced button on a microsoft access connection within a DTS package on SQL server 7.0 ?
It's a 4 byte signed integer type with a default value of zero
Cheers,
Duncan
Hello,
Can I read and write the extended properties with Access?
If it€™s possible is better create a project (ADP) or a classic MDB?
When I try with this query in the query panel (in a MDB)
SELECT objtype, objname, name, value
FROM fn_listextendedproperty
(NULL, 'schema', 'Person', 'table', default, NULL, NULL)
I have this error message:
The size of a field is too long
If I try with an ADP, when I ask a new query in design view, I have a message for some roblems between the Access version (2003) and SQL Server version (2005) and I can€™t save the query.
Tanks.
I am not able to right click to execute a stored procedure or right click to access the stored procedure properties using Management Studio. Any ideas on what is causing this or if it is supposed to be this way?
Any help is much appreciated.
I'm programmatically loading a package that was created with VS 2005. The last task in the package is a Send Mail Task. It has two properties, FileAttachments and ToLine, which are set to expressions whose values come directly from package variables. The package runs in debug and non-debug (under VS) correctly.
View 3 Replies View RelatedIf, in an SSIS package, you put an instance of an 'Execute SQL Task' task in the Control Flow, in the Properties window, you can see the properties of the task, for example CodePage.
If you double click on the task, the Execute SQL Task Editor appears, with several of the properties which are also in the Properties window, including CodePage.
If, in the Editor, you update the value of CodePage, then click OK, the value of CodePage in the Properties window is updated immediately.
I have written a custom SSIS task, which also has the same properties in the Properties window and in the Editor. The Editor also has an OK button. When OK is clicked, the values of the task properties are updated. An example property is FolderToArchive. If I open the Editor, change the value of FolderToArchive and click the OK button, the value of FolderToArchive in the Properties window is NOT immediately updated.
If, however, I select the FolderToArchive field in the Propertiesd window, it is then updated with the value I entered in the Editor.
How do I get my task to update the values in the Properties window, after changing a value in the Editor, when I click the OK button?
I would have thought I would need something like, in pseudo-code,
   Task.Parent.PropertiesWindow.Refresh
   where task is of type Microsoft.SqlServer.Dts.Runtime.Task and Task.Parent is of type Microsoft.SqlServer.Dts.Runtime.Package.
I implemented a custom source adaptor. I want to be able to associate custom properties with each of the output columns. I want them to be passed downsteam. The idea is to be able to retrieve these information in a downstream custom transformations of ours and process the various columns accordingly. How do I go about doing this?I noticed that the IDTSCustomProperty90 seems to have a local scope only.
View 1 Replies View RelatedHi
We have a list of 40-50 SSIS packages and could grow up to 100. Each one loads 5-20 tables. I’d like to write a program (probably another package utilizing "Script task/component") to:
1- List all the packages by name
2- List input and output tables and/or file systems for each package
3- List child packages and procedures that are referenced in each package
So I need to have access to the Package properties.
I would like to hear your idea/clue/tips and or any helpfull direction.
Thanks so much.
Hi!I have a user on my database that has only "select" access(db_datareader).Problem is, I also want him to also be able to create/update extendedproperties on tables or views, but without modifying the tables'schema.I played around with GRANT but apparently, a member of "db_datareader"cannot create/modify extended properties on an object if he's not theowner of this object. I tried making this user a member of"db_datawriter", but it didn't work.Nothing short of making him member of "db_ddladmin" worked... but thenthis is too much, the user can now alter to delete tables: i DON'T wantthat!Any ideas anyone? Cheers!Ben
View 3 Replies View RelatedHi,I am looking for an easy way to get all of the properties from a SQL 2000database much like the documenter does in MS Access. Any ideas?
View 1 Replies View Related
Hi,
I have created a report in reporting service 2005 and i have succesfully integrated the report in asp.net webpage.
But i want to know whether it is possible to access the methods and properties of (report)rdl file in the asp.net webpage.
If it is possible then please tell me how to implement it
Thanks and Regards
Altaf Hussain N
Hi. I have set up an instance of SQL 2005 on my local computer. I use MS SQL Server Management Studio to login into my 2005 instance via Windows login. When I expand "Stored Procedures" under "Programmability" of the Database I want and go to a stored procedure and right click, I don't see the "properities" selection.
I know it's there because I can get to it on other instances but I can't get to it on my local instance. I added my windows user to my Logins and then to my database users but it didn't help.
Can anyone please tell me what I need to do so I can access the properties of the stored procedures?
Thanks
Hi,
in one of our team projects users whom are members of the reporting services €œcontent manager€? role are not able to get onto the €œsql server€™s reporting services€? properties page of their team project. Instead of that page they receive the following error message:
The permissions granted to user '<UserName>' are insufficient for performing this operation. (rsAccessDenied)
With four eyes we double checked that the users really have the content manager role, we also tried all kinds of role combinations with no success.
The interesting thing is that as soon as they €˜only€™ in the Browser role they have access to that particular page €“ but as expected are unable to administer their team projects reporting services.
The reporting services forum doesn€™t really offer any answers to this problem, neither did goolge nor this forum.
Could anyone explain this behavior to me?
Pls let me know if you need additional information on this.
Thanks,
Tom
HI,
I need to open a File through File connection manager and want to assign these file properties to SSIS precreated varibale or Newly created varibale. I want to show file properties in Propertygrid. Properties grid will conatin File Propeties Column and SSIS varibale Combobox column. The combo box will contain New variable field. When user select New Variable field, then a new SSIS varibale window will open and we can able create New variable and that Newly created variable should add to that property comboBox.
For Instance if we create a new varibale Name "Creationdate" by clicking on New Varible in ComboBox, then that CreationDate variable should add to Property ComboBox in PropertyGrid. After adding when we select that variable Name "Creationdate" then that selected file Creation date should assign to SSIS varibale "Creationdate" field.
Any Comments or sample will help me.
Nitin
Hi JayH (or anyone). Another week...a new set of problems. I obviously need to learn .net syntax, but because of project deadlines in converting from DTS to SSIS it is hard for me to stop and do that. So, if someone could help me some easy syntax, I would really appreciate it.
In DTS, there was a VBScript that copied a set of flat files from one directory to an archive directory after modifying the file name. In SSIS, the directory and archive directory will be specified in the config file. So, I need a .net script that retrieves a file, renames it and copies it to a different directory.
Linda
Here is the old VBScript Code:
Public Sub Main()
Option Explicit
Function Main()
Dim MovementDataDir
Dim MovementArchiveDataDir
Dim MovementDataFile
Dim MovementArchiveDataFile
Dim FileNameRoot
Dim FileNameExtension, DecimalLocation
Dim CurMonth, CurDay
Dim FileApplicationDate
Dim fso ' File System Object
Dim folder
Dim FileCollection
Dim MovementFile
'======================================================================
'Create text strings of today's date to be appended to the archived file.
FileApplicationDate = Now
CurMonth = Month(FileApplicationDate)
CurDay = Day(FileApplicationDate)
If Len(CurMonth) = 1 Then
CurMonth = "0" & CurMonth
End If
If Len(CurDay) = 1 Then
CurDay = "0" & CurDay
End If
FileApplicationDate = CurMonth & CurDay & Year(FileApplicationDate)
'=====================================================================
' Set the movement data directory from the global variable.
MovementDataDir = DTSGlobalVariables("gsMovementDataDir").Value
MovementArchiveDataDir = DTSGlobalVariables("gsMovementDataArchiveDir").Value
fso = CreateObject("Scripting.FileSystemObject")
folder = fso.GetFolder(MovementDataDir)
FileCollection = folder.Files
' Loop through all files in the data directory.
For Each MovementFile In FileCollection
' Get the full path name of the current data file.
MovementDataFile = MovementDataDir & "" & MovementFile.Name
' Get the full path name of the archive data file.
MovementArchiveDataFile = MovementArchiveDataDir & "" & MovementFile.Name
DecimalLocation = InStr(1, MovementArchiveDataFile, ".")
FileNameExtension = Mid(MovementArchiveDataFile, DecimalLocation, Len(MovementArchiveDataFile) - DecimalLocation + 1)
FileNameRoot = Mid(MovementArchiveDataFile, 1, DecimalLocation - 1)
MovementArchiveDataFile = FileNameRoot & "_" & FileApplicationDate & FileNameExtension
If (fso.FileExists(MovementDataFile)) Then
fso.CopyFile(MovementDataFile, MovementArchiveDataFile)
' If the archive file was coppied, then delete the old copy.
If (fso.FileExists(MovementArchiveDataFile)) Then
fso.DeleteFile(MovementDataFile)
End If
End If
Next
fso = Nothing
folder = Nothing
FileCollection = Nothing
Main = DTSTaskExecResult_Success
End Function
I've been using SSIS for a few months now, and am comfortable with most of the tools, except for the script task and script component. I don't remember any VB code from college - is that the best place to start in order to fully understand how to code the script task and component? I need to understand the situations where/when I would use scripting, why, etc. I've used a few examples on the internet to do some simple tasks, but need to understand the code I'm implementing. Any direction would be much appreciated.