Sql 2000 Dynamic Properties Task
Feb 26, 2001
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
View 2 Replies
ADVERTISEMENT
Jun 28, 2007
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.
View 11 Replies
View Related
Jan 10, 2005
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
View 3 Replies
View Related
Apr 4, 2007
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 Related
Oct 11, 2006
Hi ,
What is the equivalent of DTS Dynamic Properties task into SSIS ?
How do I convert this task to SSIS ?
Thanks,
Vikas
View 1 Replies
View Related
Nov 7, 2007
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
View 5 Replies
View Related
Mar 9, 2007
hi,
How do you migrate dynamic properties? I think successful rate is 0% in my tests. Does anyone have an example to show me?
View 3 Replies
View Related
Feb 7, 2007
Since 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?
View 1 Replies
View Related
Sep 11, 2007
Im 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?
View 5 Replies
View Related
Feb 20, 2007
Hi 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
View 2 Replies
View Related
Apr 17, 2007
Hi,
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
View 11 Replies
View Related
Mar 27, 2006
I'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
View 23 Replies
View Related
Jun 15, 2007
Hi,
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
View 1 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
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
Jun 30, 2006
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
View 1 Replies
View Related
Sep 5, 2006
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
View 3 Replies
View Related
Nov 13, 2007
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 Replies
View Related
Apr 29, 2008
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
View 6 Replies
View Related
May 3, 2007
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
View 16 Replies
View Related
May 14, 2015
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] ....
View 3 Replies
View Related
Nov 26, 2005
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 Related
May 25, 2015
If, 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.
View 9 Replies
View Related
May 15, 2006
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 Related
Jul 6, 2007
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
View 2 Replies
View Related
Jul 15, 2001
A query to get the Table and Column Description fields in SQL Server 2000.
SELECT so.name, sc.name, sc.xtype, sc.length, sc.colid,
tableprop.value as TableDescription,
colprop.value as ColDescription
FROM
sysobjects so
INNER JOIN syscolumns sc ON sc.id = so.id
INNER JOIN sysproperties tableprop ON tableprop.id = so.id and tableprop.type = 3
INNER JOIN sysproperties colprop ON colprop.id = sc.id and colprop.type = 4
and colprop.smallid = sc.colid
WHERE so.type='u' and so.name = 'TableName'
Order by sc.ColID
Damian
View 2 Replies
View Related
May 7, 2007
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
View 6 Replies
View Related
Jul 20, 2005
Hi,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
Nov 29, 2004
I have looked on google and haven't found a query (as of yet) to perform this function.
Essentially I am using VB.NET with Excel and have a mapping between a worksheet and a table in my database. I wrote an import function to pull the data out of excel and put it into SQL Server but I want to try catching errors before i do that.
What is the SQL query to get column sizes from a table. Meaning in a table I have column1 that is allowed a size of int(5). How do i retrieve that information from a query opposed to just looking at it in SQL Server EM??
any help would be appreciated
thanks
View 5 Replies
View Related
Oct 29, 2007
Hi - 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 Related
Sep 21, 2006
Hi everyone,
For first time I'm testing this task and surprisingly, when I try "Edit Package" option:
1)The DTS host failed to load or save the package properly
2)The selected package cannot be opened
3)Error HRESULT E_FAIL has been returned from a call to a COM component
But after these messages you can see all the tasks but they haven't name!!
It seem as if RCW mechanism has failed between managed and unmanaged coded-partially.
I don't dare to follow doing more stuff, I don't know if that package is well-loaded or not from there. ?¿
Any guidance or idea about this?
View 5 Replies
View Related
Mar 16, 2015
I have an ODBC connection string that is working fine with the following properties:
Database="XXXXXXX",Network="YYYYYY"; strangely no server is specified in the string, but it is specified in the ODBC Connection file.
I am trying to do a new server registration in SSMS for this database.However, I don't understand where the network spec is placed.
Under Registered server name I've tried:
YYYYYYXXXXX
When I browse the server for the database instance list, I receive "network path was not found".
I even tried:"XXXXXXX",Network="YYYYYY" for the registered server name.Same error message.
What am I doing wrong ?
View 1 Replies
View Related
May 21, 2008
What is equivalent to dynamic property task of dts2000 in SSIS 2005;
Thanks
View 1 Replies
View Related
