I'm attempting to write my first activex script. Not familiar with VB script so bear with what might seem like a simple question.
I'm trying to use an activex script in a dts to do a transformation. I have all the columns as straight copies except for one which I'm doing an activex script. What I'm trying to do is if the date is less than 1900-01-01 I want to default it to 1900-01-01. Every once in awhile a date comes across from the source file with a date of 0001-01-01 which sql doesn't like so I want to default it to 1900-01-01 instead. The script I've created parces fine but when I run the dts I get an error that the activex script encountered an invalid data value for source column LLAI. I don't understand why. any suggestions would be greatly appreciated.
Here is the script.
Function Main()
If DTSSource("LLAI") >= "1900-01-01" Then
DTSDestination("LastActivityISODate") = DTSSource("LLAI")
Else
DTSDestination("LastActivityISODate") = "1900-01-01"
End If
I have to put some ActiveX code in the first step of a DTS package, which will search thru all the files in some folder and if it finds a file with filename starting with "Test" (like TestFile.txt), the script will rename it and then use it for transforming data to SQL tables.
In VB Dir$ function could have been used, what should I use in ActiveX?
Hello, I have a DTS package that has an ActiveX task. This task works with access to create databases and push out data to them. The problem is, I'm getting an error trying to establish a connection to the SQL Server database as such: set objConn = Server.CreateObject("ADODB.Connection") I get this as a resolution: http://support.microsoft.com/default.aspx?scid=kb;en-us;201740 But I wasn't sure since I'm not dealing with ASP. I'm trying to run the task as myself manually, not scheduled also, for testing purposes. Any ideas? Thanks.
Am having trouble writing to a table on the SQL 7 Server database, using a DTS ActiveX script.
When I try a .ADDNEW function, the following error comes up. "The opperation required by the application is not supported by the provider."
The line preceding the .ADDNEW are as follows. ----- SET Conn=CreateObject("ADODB.Connection") SET RS = CreateObject("ADODB.RecordSet") Conn.ConnectionString = "PROVIDER=SQLOLEDB;DATABASE=DataIn;User ID=sa;Password=" Conn.Open RS.Open sqlSites, Conn ----
I am getting following error message due to which i am not able to access database
Microsoft OLE DB Provider for ODBC Drivers error '80040e4d' [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed- User: _ Reason: Not defined as a valid user of a trusted SQL Server connection. /myhome/adduser.asp, line 28
I using ODBC DSN with NT authentication. I am accessing Data from SQLServer 6.5 using ActiveX DLL. I used ADO to connect to the database in DLL
I ran this activeX script with my DTS package. For debugging purpose I included the message box. The message is showing the actual name of the file but the the file name is not changing to the name display in the message box.
set objFSO = CreateObject("Scripting.FileSystemObject") strFullNm = DTSGlobalVariables("FilePathRoot").value & "IMS_ALL_DONATIONS.txt"
'check to see if file exist and then concatenate the file if objFSO.FileExists(strFullNm) then strFullNm = DTSGlobalVariables("FilePathRoot").value & " " & ms_date & "IMS_ALL_DONATIONS.txt" MsgBox "This is the new filename: " & strFullNm else MsgBox "File does not exist" end if
set objFSO = nothing Main = DTSTaskExecResult_Success End Function
I have a DTS Package Job that needs to pre-check a txt file (see below) with a 'Date' in it. TO compare it with the current Date (execution Date -> today). If they match, move on to the next step and fail otherwise. I don't know how to create an ActiveX script to do this kind of comparison.
----------------------------------------------------------------------- Volume Unit Referred SBR Used Recfm SSNE BlkSz Dsorg Dsname 5GSL4B 6760 2005/03/09 1065535 FB 3000 27000 PS 'AAS3P.QT.SECMRK.ZXWSDB.FULL.UNPACKED' -----------------------------------------------------------------------
I am tying to run this DTS: the job has an activex part which uses : "Set fso = CreateObject("Scripting.FileSystemObject")" this row gives me the following error: "ActiveX Scripting encountered a Run Time Error during the execution of the script. " the dts worked when I run it local on my station but when I try running it on the server this error comes can u help??? 10x
i need to loop through all the table(currently iam doing it using global variable) and send mail to the agents with messagetext as there orderno(s) I figured to send mail when an agent has one order but iam stuck when the agent has more than 1 order . Can you anyone please help /advice any better solution.
Gurus I have a DTS running in Sql Server 2000 and it is a Activex script.Now i want to migrate this DTS in 2005?Can you please explain the steps.. My problem is when i execute this package in 2005 in busines intelligence studio it turns red.. What does that mean?Itz a simple script which calls some URL
I migrated the DTS from 2000, and the migrated SSIS which includes ActiveX script is 11KB, takes 00:00.125 running. I rewrote the SSIS using some new features provided in BIDS, and the new package is 50KB, takes 00:00:6.016 running. Is that normal or maybe because of the efficiency of my code?
I'm getting a little confused about what is supported in MS-SQL ActiveX andwhat is Visual Basic and what is VBScript. Can someone please point me to awebsite, recommend a book or if I've missed it where in the MS-SQL help thatdeals with the ActiveX supported in SQL. I'm not looking to do web pagesASP, ect. I'm looking to use the ActiveX as add on funtionality to supportthe Stored Procs I write.Thanks,-p
hi all I'm working on a complicated activex script which is a part of a DTS package. Are there some debuggers around that could help make the task easier? Specifically I'm looking for an ActiveX script debugger. Can somebody point me to a tutorial or something? Regards
hey guys i have a code that looks something like this set mySourceConn = CreateObject("ADODB.Connection") mySourceConn.Open = "Provider=SQLOLEDB;Server=(local);Database=server_name;User Id=sa;Password=saPWD"
this works fine but the problem that i'm having is we do not want to hard code the password in the connection string. so i was wondering if there is anyway to access the DTS Connection object from the ActiveX. i was able to get the object but it is in connection2 datatype and i am not familiar of using this datatype. Code below returned the connection2 object 'DTS_Conn is the name of the object i specified in the DTS'DTS_Conn --> SQL Server Connection objectDTSGlobalVariables.Parent.Connections("DTS_Conn") can i use this connection2 object to run the sql statement and get the recordset?
I have been trying to create, then call an activeX object. I use sp_OAcreate and then sp_OAmethod to create the object, the call the method within. I have registered the DLL but I can not get it to work. I used the examples within T-SQL help but it doesn't seem to work. I am calling from within a sp within the northwind db. I need to pass 4 parms to the method. Here is the proc.
CREATE PROCEDURE SIMPLE2_SP (@CUSTOMERID nVARCHAR(25) = NULL, @ORDERID INT = NULL) AS
DECLARE @BLOB nVARCHAR(1200), @PARM1 nVARCHAR(25), @SF INT, @pOBJ INT
/***Make sure that the customerid is specified, if not, get out. sfogli 7-17-00 ***/ IF (@CUSTOMERID is NULL) OR (@CUSTOMERID = '') BEGIN RAISERROR('Must specify a CUSTOMER name.',-1,-1) RETURN (1) END
/***Hardcode the path, customerid, and orderid for testing purposes. sfogli 7-17-00. ***/ --SELECT @BLOB = 'PATH=C:REPORT.HTM;CUSTOMERID=' + @CUSTOMERID + ';' + 'ORDERID=' + CONVERT(VARCHAR(5),@ORDERID)+ ';' SELECT@PARM1 = 'PATH=C:REPORT.HTM;'
/***Make sure that the orderid is specified, if not, raise message. sfogli 7-17-00 ***/ IF (@ORDERID is NULL) OR (@ORDERID = '') BEGIN RAISERROR('To continue process, please specify an ORDERID and retry.',-1,-1) PRINT @BLOB RETURN (1) END
I'm looking for a way to get the name of the server on which the DTS package lives.
I copy packages between servers. The problem is that everytime a package is copied to different server, I have to change the reference in the connection strings to point to the new server name. I'd like to find an automatic way to interrogate the server name where the package currently lives and dynamically change connection strings from within an ActiveX task. That would cut maintenance way down.
Hello, Does anyone know how I add the SQLDMO library to enable me to reference it in DTS Active X scripts? I do not want to install VB on the server to do this.
I've been looking on-line for about a day now and can't find what I'm looking for. I don't know much about ActiveX and even less about using it in a DTS package. If you guys could help me out be telling me what to use/give a few minimal examples I can figure the rest out. I just can't find enough info on the web to do what I want.
I'll describe what I've got, what I'm trying to do with it, and we'll see what you guys think.
What I've got: A table that stores To, Subject, MsgText, MsgHTML, and FileAttachment locations.
What I'd Like to do: Write out the info in the table into a text file and then copy it to the Pickup folder to be sent. Now, I know DTS has an e-mail task, but the Dynaprops/e-mail task are being flaky. The information in the E-Mail doesn't always seem to update like I'd like it.
How I was planning on doing it: Write an ActiveX script to Create the E-mail, store it in the DB, and then Write it out to the txt file to be sent.
I think this'd be a good way to do it, but then again I could be wrong. Maybe use something else? Please help. I could use it.
I am attempting to export a records to an excel file using DTS and then use activex to rename the file to include the date it was run. To do this i just move the file.
However when the script is run, it fails saying that permission is denied on the "move" line. I have seen other reports saying that permission is denied when using the "CreateObject" however i have implemented the changes it says, ie/ adding the SQL Agent login using the DCOMcnfg utility and renaming the DefaultAccessPermission key in the registry, with no success. It also doesnt seem to fail on that line anyway.
I have also added all the groups and usernames with full control to the security of the folder the file is in to no avail either.
Hi, i transferred some data from a table to a txt file in DTS (Using some sort of SQT Tasks for updates and deletes.) Before i send it to another computer using FTP, i want to convert the txt file to cvs. Can you please tell me what code i can use? Thanks.
I have a DTS package that downloads files via FTP, then processes each file. A few tasks are done when each file is processed: (a) a holding table is truncated and 1 blank record is inserted into the holding table, (b) the XML data in the file is inserted into the holding table via TextCopy.exe, (c) the XML data is parsed using OPENXML and inserted into 2 additional holding tables, and (d) the XML file is archived to another directory. After (a), (b), (c), and (d) are completed, the DTS package loops back and executes (a), (b), (c), and (d) for the other remaining files.
It all worked flawlessly in testing, until I commented out a MsgBox line in the ActiveX task for item (b) above. Without the MsgBox command, the other tasks (b) and (c) don't appear to execute, though I can see that the looping is working, since the source files get moved to the archive location (in step (d)).
Attached is a screenshot of the DTS package (it can also be viewed at http://www.nmwildlife.org/images/DTS_screenshot.gif).
I think that the MsgBox issue is a red herring; in other words, I'm thinking that when I click the OK button on the MsgBox, there might be something about the return code which allows the tasks to be executed properly. However, I'm not a VBScript expert, so can't figure out where the problem lies or how to fix it.
Here's the code for the "Import w/ShellCmd" ActiveX task:
Function Main() Dim objShell Dim strPath Dim strCmd
Set objShell = CreateObject("WScript.Shell") objShell.Run strCmd Set objShell = nothing
MsgBox ""
Main = DTSTaskExecResult_Success End Function
And here's the code for the "Begin Loop" ActiveX task:
Option Explicit
Function Main()
dim pkg dim stpEnterLoop dim stpFinished
set pkg = DTSGlobalVariables.Parent set stpEnterLoop = pkg.Steps("DTSStep_DTSExecuteSQLTask_2") 'Start loop at the "Truncate dc_XML" task set stpFinished = pkg.Steps("DTSStep_DTSActiveScriptTask_5")
' We want to continue with the loop only of there are more than 1 text file in the directory. ' If the function ShouldILoop returns true then we disable the step that takes us out of the package and continue processing
if ShouldILoop = True then stpEnterLoop.DisableStep = False stpFinished.DisableStep = True stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting else stpEnterLoop.DisableStep =True stpFinished.DisableStep = False stpFinished.ExecutionStatus = DTSStepExecStat_Waiting End if
Main = DTSTaskExecResult_Success End Function
Function ShouldILoop
dim fso dim fil dim fold dim pkg dim counter
set pkg = DTSGlobalVariables.Parent set fso = CREATEOBJECT("Scripting.FileSystemObject")
set fold = fso.GetFolder(DTSGlobalVariables("gv_FileLocation").Value)
counter = fold.files.count
'So long as there is more than 1 file carry on
if counter >= 1 then
for each fil in fold.Files DTSGlobalVariables("gv_FileFullName").Value = fil.path ShouldILoop = CBool(True) Next
else ShouldILoop = CBool(False) End if
End Function
The goal is to get the DTS package to run without having to manually click OK on the MsgBox; that way, I can schedule it to run automatically.
Any help would be greatly appreciated. Thanks in advance!
Does anyone know the best way to handle this type of situation?
A file coming into a directory based on the date filename042707 I use the fileexist stored procedure to check for the existence. I use xp_cmdshell(sp)... stored procedure to rename the file so that it just has filename instead of the date.... I can not use variables within the xp_cmdshell to replace the date... everyday the filename would change to filename042807,filename042907 etc....
Basically I know how to copy the file to another directory if it exists... then I want to strip the right 8 characters off... rename just to that but with the rename i have to know what the file will be named for that day ... excuse the grammar just somewhat tired... any suggestions... please thanks time for sleep
Is there a way to manipulate the Access Object Model via ActiveX script. What I am trying to do is create a DTS package that uses a ActiveX script to manipulate a Access MDB to import and Excel file into the Access MDB and then in the end export the Access table data to a SQL Server Table.
Below is a code snippet that is throwing the following error:Error Source: Microsoft DTS PackageError Description: Error Code: 0Error Source= Microsoft VBScript runtime errorError Description: Object required: 'Server'Error on line 13In my code line 13 is the following:Set Cnxn = Server.CreateObject("ADODB.connection")I did set up a Microsoft OLE DB connection in my DTS package for thisActiveX Task. I'm new to VBScript and ActiveX. Any help would beappreciated.Thanks,-p'************************************************* *********************' Visual Basic ActiveX Script'************************************************* ***********************Function Main()Main = DTSTaskExecResult_Success' connection, command and recordset variablesDim Cnxn, strCnxn' create and open connectionSet Cnxn = Server.CreateObject("ADODB.connection")strCnxn = "data source=Pluto;initial catalog=Stats;User Id=sa;password=;"Cnxn.Open strCnxnEnd Function
I have a DTS package which executes an ActiveX control written in vbscript. I need to move the ActiveX code out of the DTS environment. The code is too big to execute in a Sql Server Job Step as type "ActiveX Script." Do I have any other easy options?
Can you assist me with converting the code below to VB ?
Function Main() Dim objExcel Set objExcel = CreateObject("Excel.Application") objExcel.Workbooks.Open "C:FTPOUTGOINGFTP_MarkOff.xls",,,,"" objExcel.Workbooks(1).SaveAs "C:FTPOUTGOINGFTPMarkoff.xls",,"password" objExcel.Workbooks.Close Set objExcel = Nothing Main = DTSTaskExecResult_Success End Function
