DTS Dynamic Properties Task To SSIS
Oct 11, 2006Hi ,
What is the equivalent of DTS Dynamic Properties task into SSIS ?
How do I convert this task to SSIS ?
Thanks,
Vikas
ADVERTISEMENT
Dynamic Properties Task In DTS 2000, Need To Convert It To SSIS
Jun 28, 2007I 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.
How To Use The Dynamic Properties DTS Task
Jan 10, 2005Hi,
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
Sql 2000 Dynamic Properties Task
Feb 26, 2001Hi,
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
Need Assistance With Dynamic Properties And FTP Task
Apr 4, 2007Hello,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 RelatedNew 2005 SSIS Task: File Properties Task
Nov 7, 2007A 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
SSIS Equivalent For Dynamic Properties Global Variable Example
Apr 17, 2007Hi,
I have a dts package that currently uses a dynamic properties task to set the values of global variables. Each variable is based on the value of a query to the database.
I am in the process of migrating this dts package to SSIS but cannot find an equivalent function. I have looked at property expressions but cannot get this working the same way.
Any help would be appreciated.
Thanks
Lyn
How To Convert Dynamic Properties To SSIS Package Config?
Mar 27, 2006I'm currently trying to convert over packages from SQL 2000 to SQL 2005. The biggest obstacle at the start has to do with converting my "Dynamic Properties" control. I use it to read an .ini file and load the user name and password to my connections. With 2000 it's nice and easy 1 file 3 lines. While trying to convert it I€™ve had nothing but problems. I've tried the Registry Entry but it forces everything into the Current User and I can't use that I need local machine. There is no documentation and I can't have a xml file with 1000 different username and password settings because 1 person has to update that file with the new passwords. Any help would be GREATLY appreciated.
Thank You
Jerry
SSIS Scripts Task - Connection Properties
Sep 5, 2006Hi 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
Custom Task With PropertyGrid Control SSIS - Not Able To See Properties In GUI
Apr 29, 2008Hello 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
SSIS Equivalent To DTS Transform Data Task Properties
May 3, 2007I 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
Integration Services :: Updating SSIS Task Standard Properties Window
May 25, 2015If, 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.
Reg:- Assign File Properties To SSIS Varibales(Custom Task) In Property Grid Progrmatically.
Jul 6, 2007HI,
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
Moving Files (split From An Existing Thread-SSIS Equivalent To DTS Transform Data Task Properties)
May 7, 2007Hi 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
Integration Services :: Dynamic Password In FTP Task In SSIS?
Dec 31, 2013I can set almost all properties of FTP task and FTP connection manager using expressions option. In that option, I don't see that I can set FTP PASSWORD using a variable. How do I set the password dynamically?
View 2 Replies View RelatedMigrate Dynamic Properties
Mar 9, 2007hi,
How do you migrate dynamic properties? I think successful rate is 0% in my tests. Does anyone have an example to show me?
Dynamic Logging Properties In MS SQL DTS Packages
Feb 7, 2007Since I can't seem tofind the Microsoft SQL 2000 forum, I will post this here:
I currently have logging enable on several of my packages.
However, we are still in development of our packages and are reaching upwards
of 100 and logging will eventually need to be active on all of them. In
production, there will still be a development server and a production server,
both with different server names and user id/pwd.
I am looking for a way to dynamically change the logon information for the
logging so that we do not have to have someone go through and manually change
the options. I have tried using Dynamic Properties Task, but this only works on
the 2nd run of the package.
-----
As a second question: can anyone explain to me why the errordescription field
in sysdtssteplog is cut short?
DTS Old Style And Dynamic Properties Tasks
Sep 11, 2007Im using embedded SS2000 DTS packages in SSIS. These legacy packages use Dynamic Properties Tasks to pick up connection settings from a named ini file. The name of that ini file needs to change occasionally. To date the only way we have of doing this is resetting all the dynamic properties to point at the new ini file. Unfortuntely there are a lot of connections and properties and there is no budget to recreate these packages in SSIS. There must be an easier way?!
Does anyone know how to quickly change Dynamic Properties Tasks to look at a new ini?
Migrating DTS Packages Question: Dynamic Properties
Feb 20, 2007Hi guys,
After DTS Migration is "successful", I opened up dynamic properties step but I saw codes were all commented out inside the public class ScriptMain. Since I am not familiar with Microsoft.SqlServer.Dts.Runtime, I'm not sure what methods I should use as there aren't much examples available online. How do I do the following in SISS way?
' Add your code here
' Source Type = 2
' Global variable = glvTrade
' Destination = 'Connections';'Trade';'OLEDBProperties';'Data Source';'Properties';'Value'
Regards
Commented Dynamic Properties -- SQL2000 To SQL2005 Mgration
Jun 15, 2007Hi,
After DTS Migration is "successful", I opened up dynamic properties step but I saw codes were all commented out inside the public class ScriptMain. Since I am not familiar with Microsoft.SqlServer.Dts.Runtime, I'm not sure what methods I should use as there aren't much examples available online. How do I do the following in SISS way?
' Source(Type = 2)
' Global variable = SPLastDate
' Destination = 'Tasks';'DTSTask_DTSExecuteSQLTask_7';'Properties';'SQLStatement'
How can i convert this code for ssis ?
Does anyone have any solution ? kind of urgency..
Thanks
Yasemin
DTS Task Properties And Global Variables
Mar 2, 2004Hey 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
How To Read Dynamically Sql Task Properties??
Apr 25, 2007Hi 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,
Change Email Task Properties
Jun 30, 2006HI,
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
Can I Access Another Component Properties From A Script Task
Nov 13, 2007I 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 Replies View RelatedIntegration Services :: File Properties Script Task
May 14, 2015I 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] ....
Runtime Errors In Send Mail Task - Properties With Expressions Failing
Nov 26, 2005I'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 RelatedHow Do I Propagate Custom Properties To Downstream Transforms In A Data Flow Task
May 15, 2006I 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 RelatedSSIS - Custom Properties For Derived And Other Transformations
May 10, 2006Hi,
I saw some thing called custom properties for the "Derived transformation" in the msdn site. I tried to use them in a simple package, but I am getting an error as "can't write to derivedoutputcolumnname.friendlyexpression". Friendly expression is one of the custom properties available for the derived transformation output columns.
The steps I followed to get to this error are as follows:
1) Get data from a table using OLEDB Source. Suppose I am getting firstName, LastName etc.
2) Derived column input is values from the above OLEDB Source.
3) I have added a new column called "Concatenated name" which is concatenated value of first and last names.
4) Then in the properties editor of this data flow task in expressions option I clicked on ellipse available. I got an editor for property expression, which contained two columns called "Property" and "Expression". Property column contains dropdown with friendly expressions propety for the derived columns and expression column is a text box, where in we can enter expression to be evaluated for the corresponding friendly expression property.
5) Now when I click on OK and try to debug it gives an error as "Can't write to concatenatedname.friendlyexpresiion".
If anybody has already faced this problem and solved it please let me know, because I am struck here a long time.
Thanks&Regards,
Sreekanth Ammisetty
Can Custom Properties In A SSIS Component Be Disabled
Feb 26, 2008Is 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
Building Expression Like Properties In Custom SSIS Tasks
Oct 22, 2007
Ive been using SSIS for a month or two and now find I need to create some custom tasks to perform some performance logging. in the the overloaded ProviderComponentProperties section I am trying to create a property which has the same look as the Expressions properties you find elsewhere (Little + on the left and a group of sub properties when expanded).
Ive have played with creating a IDTSCustomPropertyCollection90 collection then adding my sub properties to it but I cant seem to then add my new collection to the ComponentMetaData.CustomPropertiesCollection.
Im assuming the Expressions parameter is a collection added to the properties collection but I cant figure out how. Any help would be much appreciated.
Migrating DTS 2000 To SSIS ; Modifying OLE DB Connection Properties
Oct 29, 2007Hi - Im migrating packages in dts 2000 to SSIS 2005 using package migrator wizard. It loads a CSV to a table in DB. I need to change the server and file location paths after migration. While changing this, im encountering error - "the acquireconnection method call to connectionmanager failed
View 1 Replies View RelatedSSIS Task Transfer SQL Server Objects Task And Default Constraints On Tables
Feb 21, 2008
I am using the "Transfer SQL Server Objects Task" to copy some tables from database A to database B including data.
The tables, primary key constraints, Foreign key, data and all transfers nicely except for "DEFAULT CONSTRAINTS" on the tables.
I have failed to find any option in the "Transfer SQL Server Objects Task" task to explicitly say "copy default constraints". So I guess logically it should happen automatically but it doesn't. I hope it is not a bug :-)
Any option anyone knows will help.
Thanks.
SSIS (Integration Services) Transfer SQL Server Objects Task: This Task Can Not Participate In A Transaction
Feb 1, 2007In short, does the €œTransfer SQL Server Objects Task€? support distributed transactions?
In trying to use a €œTransfer SQL Server Objects Task€? in a container using a transaction on the container. The task is set to support the transaction. It is setup to copy table data from several tables from a non-domain server (sql server 2000) to a domain-based server (sql server 2005). I get an error stating, €œThis task can not participate in a transaction€?.
I am wondering if it means exactly what it says €“ this task in SSIS can€™t participate at all. Or does it mean that it won€™t in this scenario for some reason. I attempted a simple copy of data from mssql 2005 to mssql 2005 (same server) and the task still failed). MSDTC appears to be running properly on my machine and such (I can do a simple distributed transaction across linked server to the 2000 server in Query Analyzer (QA)). Also, MSDTC appears to be working on both servers with distributed transaction query tests in QA.
Here€™s the error info€¦
SSIS package "Development BusinessContacts and Products Migration.dtsx" starting.
Information: 0x4001100A at Copy BusinessContacts Data: Starting distributed transaction for this container.
Error: 0xC002F319 at Copy BusinessContacts database table data 1, Transfer SQL Server Objects Task: This task can not participate in a transaction.
Task failed: Copy BusinessContacts database table data 1
Information: 0x4001100C at Copy BusinessContacts database table data 1: Aborting the current distributed transaction.
Information: 0x4001100C at Copy BusinessContacts Data: Aborting the current distributed transaction.
SSIS package "Development BusinessContacts and Products Migration.dtsx" finished: Failure.
The program '[4700] Development BusinessContacts and Products Migration.dtsx: DTS' has exited with code 0 (0x0).