Pardon my ignorance of VB Script, but I am attempting to refine a DTS package (importing data from a text file) so that only records that contain the value '3' in column 5 will be imported. Thanks in advance.
View 1 RepliesHi,
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.
found it
View 3 Replies View RelatedHey,
I have MS SQL database.
I have procedure:
code:--------------------------------------------------------------------------------
CREATE PROCEDURE dbo.Reg_DropTable
@ModuleId varchar(10)
AS
declare @TableName varchar, @kiek numeric
set @TableName = 'reg_'+@ModuleId
begin
DROP TABLE @TableName <- HERE I GOT ERROR
end
GO
--------------------------------------------------------------------------------
I got error when using variable with tables names.
How to do this?
Ps. Number is send to this function and it must drop table with name Reg_[That number]
How do I use ActiveX script in DTS to do a 'Save As' on an excel file and
convert that to a tab delimited text file ?
Help is much appreciated.
Hayes
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?
Thanks a lot for your help
hello friends, i heard from my colleagues that xml and activex are necessary for DBA
is it so? how could we use in our daily activities?
can any one provide me good links for this concepts please?
thank you very much.
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.
Hi there,
Can I be able to call a Activex DLL from Sp or from triggers.
Sachi
Looking for a answer..
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
Main = DTSTransformStat_OK
End Function
Hi,
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
----
Q - Whats wrong ?
I get this error msg when I run my ActiveX script in a DTS package.
Err number: 429
Err Message: ActiveX component can't create object
When I Set crApplication = CreateObject("CrystalRuntime.Application.9")
if Err.Number <>0 then
'I get the message here
ne one know what this is about? I'm running this package on SQL server 2000 with Admistrative access
Hi all,
I have a problem with a SQL Agent JOB on SQL 2K SP3 installation (on Win2K Server).
SQL Agent is running under a domain account with local Admin privileges and SA privileges in SQL server.
A job with an ActiveXScript step fails with "Permission denied: 'CreateObject line..."
If I execute the same ActiveXScript as cmdexec step (calling CSript with the VBScript as parameter) it works without any problem.
The ActiveXScript for reference:
Dim oXls
Dim oWorkBook
Dim oSheet
Set oXLS = CreateObject ("Excel.Application")
oXls.Visible = True
oXls.DisplayAlerts = True
Set oWorkBook = oXLS.WorkBooks.Add
Set oSheet = oWorkBook.ActiveSheet
oSheet.Name = "Test AG"
oWorkBook.SaveAs "C:TempTestAg.Xls"
oWorkBook.Close
Set oSheet = Nothing
Set oWorkBook = Nothing
oXls.Quit
Set oXlS = Nothing
Does anybody have any idea why approach 1 fails while approach 2 does work happily?
Hi all,
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
Can u help me out ?
Thanks & Regards
Rajanikanth
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.
Function Main()
ms_year = year(date())
ms_month = month(date())
ms_day = day(date())
ms_date = ms_year & "_" & ms_month &"_" & ms_day & "_"
dim objFSO, strFullNm
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'
-----------------------------------------------------------------------
Thank you for any suggestion!
J827
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
Hi gurus,
I am trying a accomplish a mail task which gets its information from the tables. iam using DTS[Activex and send email tasks].
table info:
agentid agentmail orderno
1 xx@hh vinet
2 xy@hh husk
1 xx@hh husk
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.
Thanks
Cindy
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
Please help
Regards
Nitin
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?
thanks.
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
View 6 Replies View RelatedHi,
Is there a source I can go to for ActiveX documentation? I just need to be able to translate some things.
For example:
On Error Resume Next
What does this do within the ActiveX script?
Thanks
I am moving data from Access to SQL Server.
I have a term date field (datetime) whose source values in Access are '1/1/1700'
I am using an Active X Transformation task to change those values to '1/1/1753'
Here is my code
If DTSSource("TERM_DATE").value < '1/1/1753'
then DTSDestination("TERM_DATE").value = '1/1/1753'
else DTSDestination("TERM_DATE") = DTSSource("TERM_DATE")
can't figure out whats wrong
Mike
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
View 1 Replies View Relatedhey 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?
Thanks in advance for your help...
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;'
/***Append BLOB with revelant data from customer table, sfogli 7-17-00.
***/
SELECT @BLOB = (SELECT'CustomerID=' + CUSTOMERID + ';' +
'CompanyName=' + COMPANYNAME + ';' +
'ContactName=' + CONTACTNAME + ';' +
'ContactTitle=' + CONTACTTITLE + ';' +
'Address=' + ADDRESS + ';' +
'City=' + CITY + ';' +
--Contains null values'Region=' + REGION + ';' +
'PostalCode=' + POSTALCODE + ';' +
'Country=' + COUNTRY + ';' +
'Phone=' + PHONE + ';' +
'Fax=' + FAX + ';'
FROMCUSTOMERS
WHERECUSTOMERID = @CUSTOMERID)
/***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
/***Append BLOB with revelant data from orders table, sfogli 7-17-00.
***/
SELECT@BLOB = @BLOB + (SELECT'OrderID=' + CONVERT(VARCHAR(5),ORDERID) + ';' +
'CustomerID=' + CUSTOMERID + ';' +
'EmployeeID=' + CONVERT(VARCHAR(7),EMPLOYEEID) + ';' +
'OrderDate=' + CONVERT(VARCHAR(15),ORDERDATE) + ';'
/***'RequiredDate=' + REQUIREDDATE + ';' +
'ShippedDate=' + SHIPPEDDATE + ';' +
'ShipVia=' + SHIPVIA + ';' +
'Freight=' + FREIGHT + ';' +
'ShipName=' + SHIPNAME + ';' +
'ShipAddress=' + SHIPADDRESS + ';' +
'ShipCity=' + SHIPCITY + ';' +
'ShipRegion=' + SHIPREGION + ';' +
'ShipPostalCode=' + SHIPPOSTALCODE + ';' +
'ShipCountry=' + SHIPCOUNTRY + ';'
***/
FROMORDERS
WHEREORDERID = CONVERT(VARCHAR(5),@ORDERID))
EXEC @SF = SP_OACREATE "PMIDocHandler.HTMLDocs", @pOBJ OUT
IF @SF <> 0 GOTO ERR
--(sPath, sCustomer, sContractID, sKeyValuePairs)
EXEC @SF = SP_OAMETHOD @pOBJ, "CreateDocument" ,@PARM1, @CUSTOMERID, @ORDERID, @BLOB
IF @SF <> 0 GOTO ERR
ERR:
RAISERROR('THIS IS ONLY A TEST.',16,1)
--SELECT @BLOB AS 'VARIABLES PASSED'
RETURN
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.
Regards,
John Thorpe
Hi everybody.
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.
Your help is more than appreciated.
Thanks!
-Me
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.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Main = DTSTaskExecResult_Success
Set fso = CreateObject("Scripting.FileSystemObject")
'
Set bFile = fso.GetFile("c:DTS reportsoutput.XLS")
aYear = year(now)
aMonth = month(now)
aDay = day(now)
filename = "output" &aYear & aMonth & aDay & ".xls"
bFile.move ("c:DTS Reports"&filename)
'fso.MoveFile "c:DTS Reportsoutput.xls", "c:DTS Reports"&filename
Set fso = Nothing
End Function
Does anyone know what kind of permissions the folder the file is in must have in order for this script to work? Any help would be greatly appreciated.
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
strPath = CSTR(DTSGlobalVariables("gv_FileFullName").Value)
strCmd = """C:Program FilesMicrosoft SQL ServerMSSQLBinnTextCopy.exe"" /S ""GROVER"" /U sa /P """" /D TESTProlaw /T dc_XML /C myXML /F " & _
strPath & _
" /W ""WHERE 1=1"" /I /Z"
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.
Any ideas?