DTS Script trying to re-execute another task in package

I'm new to the whole DTS scene and not impressed with the info I'm finding. Here's what I'm trying to do:

I have two recordsets generated by ExecteSQLTasks (created as output global variables). While I'm looping through one recordset in a script I would like to do data-driven updates of the second recordset based on data in the first. This seemed easy at first. I thought I could just do a little hoochie coochie:

Dim objPackage
Dim objTask
Dim objCustomTask

Set objPackage = DTSGlobalVariables.Parent

For each objTask in objPackage.Tasks
If objTask.CustomTask.Description = "The description I gave it!" Then
objTask.Properties("SQLStatement").Value = "My new SQL statement"
objTask.Execute
End If

And voila! I'd have my new recordset established as a new value for the global variable. But no! It doesn't seem to work that way. What's up with this?

FYI, I also tried to apply filters to the original recordset using:

rs.Filter = " table.field = " & varMyDataNotAString

But that didn't work either. Grrrr!

Anybody out there doing things like this and getting them to work?

Thanks!Check SQLDTS (http://www.sqldts.com) website and search for GLOBAL VARIABLES which will show code examples to use so.

HTH|||Actually, I'm pretty sure this is where I originally got the code in my initial post. The issue is that using this code did not refresh my global variable (and hence my recordset). I could change the SQL statement, but either the Task doesn't actually execute with the

objTask.CustomTask.Execute

or somehow the global variable isn't refreshed.

I plan to continue playing with it today.

Thanks for your reply!

Dts Record Count

I have a dts package that export data from a database to a text file. I could like to create a validation script to coun the data in the database that should be exported to file and then re-count again after the data is in the text file. Also count the number of columns that is exported.

Is there a way to do that within a dts package?

Help!

LystraYou ought to be able to do that using DTS Global Variables (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsprog/dtspapps_82nn.asp).

-PatP

DTS question

Say I would use a ActiveX vbScript to query an LDAP server.

If i then would use that script to run in a DTS package. Witch steps do I have to take? Do i need to make a connection with the database, how do I create links.

I not looking for an out of the box solution, just maybe some good links to good sites witch explain this problem / question. My search wasn't that successfull

Greetings,
GodofredoYour DTS may contain nothing but an ActiveX script. You create connections inside the script using ADO, RDO, or whatever else you think you can use for it.|||yes but i want to fill up a table with the information retrieved by the ActiveX script|||you could just create a job that runs your activex script by querying ldap
and you could make that available to the next available step in the job. you dont really have to run dts. BUt if you want to, SQL Server has access to the ADSI oledb provider . in other words you could link a server to active directory and run a query based on your appropriate LDAP architecture.|||This is what a call a straightforward answer

Thanks,
Godofredo|||Hey, that's because he teaches this stuff...

Scott does DTS and LDAP...|||"at the same time baby!!!!"

is this thing on ?? 1.. 2.. 3 ...sqlsql

DTS problem: ActiveX Script Task Properties dialog box

My ActiveX Script Task Properties dialog box has the "functions" window
maximized and I
can no longer get at my code.
I can not find any way to resize it.
Please ...
If anyone has come across this and can help...
it will be much appreciated.
I've never had this happen before.
Thanks in advance,
bob mcclellanDid you find out the fix for that dialog box problem?|||I went to C:\Program Files\Microsoft SQL Server\80\Tools\Binn directory
and Registered ALL dll's
*** Sent via Developersdex http://www.examnotes.net ***

DTS package with unexpected issues

I've got a DTS package that runs an active-x script. The script is
simple - it runs a stored procedure and saves the results to a CSV
file. I kept getting this error message when trying to run it saying
that the recordset object I was using could not be used when closed.
Well, it didn't make a whole lot of sense to me as to why that was
happening, and it doesn't realte to my question except to give you a
sense of what I'm trying to do. After spending an inordinate amount of
time on that... I decided to just create a SQL Server connection
object and an Excel Object and then use a transformation to load the
query results. Simple enough, or so I thought. So in the
transformation object under the Source tab, I typed in the query to run
the Stored Procedure:

Declare @.S nvarchar(30)
Declare @.E nvarchar(30)
SET @.S = Convert(nvarchar(30), GetDate()-1, 101) + ' 12 AM'
SET @.E = Convert(nvarchar(30), GetDate()-1, 101) + ' 11:59:59 PM'
exec CTI_REPORT_Q_ACTIVITY_DETAIL @.S, @.E

And then I clicked on the preview button. I got the message that no
rowset was returned. In a way, that explains the issue with the
Active-X script. BUT, I know darn well it returns data. It returns
438 rows of data when I run this in Query Analyser.

So, here's my question...how could that be? Is there some issue that
DTS packages have with temporary tables? I do use a couple in the
Stored Procedure. Without having to post the stored procedure and
tables, etc. could someone let me know if they've run into something
like this before?

Thanks,
Jenniferjennifer1970@.hotmail.com wrote:

> Declare @.S nvarchar(30)
> Declare @.E nvarchar(30)
> SET @.S = Convert(nvarchar(30), GetDate()-1, 101) + ' 12 AM'
> SET @.E = Convert(nvarchar(30), GetDate()-1, 101) + ' 11:59:59 PM'
> exec CTI_REPORT_Q_ACTIVITY_DETAIL @.S, @.E

I've never used a stored procedure in DTS, but i presume that if any
error occurs in SP, DTS won't catch it.
Try taking source from SP and execute it DTS.
Change:
exec CTI_REPORT_Q_ACTIVITY_DETAIL @.S, @.E
with procedure code

Dts package that imports difference

Assuming you are using an Execute SQL task, you can use a script like the
one below:
SET IDENTITY_INSERT Database2.dbo.MyTable ON
GO
INSERT INTO Database2.dbo.MyTable
(
ID,
MyData
)
SELECT
ID,
MyData
FROM Database1.dbo.MyTable AS t1
WHERE NOT EXISTS
(
SELECT *
FROM Database2.dbo.MyTable AS t2
WHERE t2.ID = t1.ID
)
Hope this helps.
Dan Guzman
SQL Server MVP
"italic" <hugur@.hotmail.com> wrote in message
news:%23M7cQ36kHHA.4624@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have to server and two different database on them. I want to import one
> table from database1 to database2. But I want to import only difference
> between two table.I want to also keep identity column value. I created a
> dts package but I don't know how can I put a condition like "where myid
> not in(Select myid from database2.table)" and where can I put Set
> IDENTITY_INSERT mytable ON?
> Thanks in advance,
>
>
in DTS,
first you have to get the lastet ID (select max(id) from destination)
then you have to dynamically create the select statement (Select * from
source where id > maxid)
(or you have to use a parametrized query where the parameter will be the ID)
and finally load the data.
"italic" <hugur@.hotmail.com> wrote in message
news:Ogw1D77kHHA.2272@.TK2MSFTNGP02.phx.gbl...
>I don't think you can connect from "Execute SQL task" to both server. You
>need to select connection(server) first. Am I wrong?
> Thanks
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:51021AEF-D9B2-4FA4-AD0E-3CA7F1C1C889@.microsoft.com...
>
|||>I don't think you can connect from "Execute SQL task" to both server. You
>need to select connection(server) first. Am I wrong?
You are correct. It was unclear to me from your message that the databases
were on different servers.
If you have a lot of changes, I suggest you copy the data from the source
table into a staging table on the target databse server and then use an
Execute SQL task.
Hope this helps.
Dan Guzman
SQL Server MVP
"italic" <hugur@.hotmail.com> wrote in message
news:Ogw1D77kHHA.2272@.TK2MSFTNGP02.phx.gbl...
>I don't think you can connect from "Execute SQL task" to both server. You
>need to select connection(server) first. Am I wrong?
> Thanks
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:51021AEF-D9B2-4FA4-AD0E-3CA7F1C1C889@.microsoft.com...
>
|||Hi
"italic" wrote:

> I don't think you can connect from "Execute SQL task" to both server. You
> need to select connection(server) first. Am I wrong?
> Thanks
You can create a linked server and reference that in your query, or as you
have a second connection to the destination server you could use a query
(task) to retrieve the highest identity from the destination server then use
that in a global variable as the start of the information retrieved from the
second server.
See http://www.sqldts.com/280.aspx
http://www.sqldts.com/205.aspx
The second method would not necessarily be transactionally consistent,
therefore you should not be changing the destination table when this is
running.
John
|||Hi
"italic" wrote:

> Linked Server is not allowed. I can try the second way.
> Thanks
>
You would need a 4 part name! What error do you get?
John
sqlsql

DTS Package scripting

Is there any way to script DTS Packages? :confused:I don't think there is as they are not compiled entirely of t-sql, it could be VB script etc.sqlsql

DTS package Scheduled Job does not run correctly

Hi
I have a DTS package that is running a VB script that reads all the
files from a folder one by one and processes them. After a file is
processed - file is read and the content entered in a table in one of
the databases - the file is moved to a different location.
The DTS package runs wonderfully if I run it manually (right click
Execute Package).
I scheduled this DTS package to run every hour (right click Schedule
Package and a job was created).
The corresponding job finishes successfull, my files are even moved to
a different location but no data is entered in my table.
The SQL Agent runs under an account that is system admin for our
network.
The job is owned by the same account.
Please help.
Thanks,
CarlyAre you using workflow, in your DTS package. If you are trying to execute any
step after inserting data into table try to do it 'OnSuccess' workflow and
see if it gets executed successfully.
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Carly" wrote:
> Hi
> I have a DTS package that is running a VB script that reads all the
> files from a folder one by one and processes them. After a file is
> processed - file is read and the content entered in a table in one of
> the databases - the file is moved to a different location.
> The DTS package runs wonderfully if I run it manually (right click
> Execute Package).
> I scheduled this DTS package to run every hour (right click Schedule
> Package and a job was created).
> The corresponding job finishes successfull, my files are even moved to
> a different location but no data is entered in my table.
> The SQL Agent runs under an account that is system admin for our
> network.
> The job is owned by the same account.
> Please help.
> Thanks,
> Carly
>

DTS package prob

I wrote next ActiveX script. But it doesn't work. Can somebody tell me what i'm doing wrong. It should retrieve the mail and Assoc-NT-Account, but so far i only get errors?

Another question: should I explicitly write this to a table? If yes, how?

************************************************** ******************** ' Visual Basic ActiveX Script '************************************************* **********************

Function Main() Main = DTSTaskExecResult_Success

strServerName = "**********" set oConn = CreateObject("ADODB.Connection") set oCommand = CreateObject("ADODB.Command") set oRS = CreateObject("ADODB.RecordSet")

oConn.Provider("ADsDSOObject") on error resume next oConn.Properties("User ID") = "*********" oConn.Properties("User Name") = "*******" oConn.Properties("Password")="*********" oConn.Properties("Encrypt Password")= True oConn.Open "Ads Provider"

set oCommand.ActiveConnectection = oConn

strQuery =" Select Assoc-NT-Account, mail from LDAP://servername/o=orgName/ou=OrgUnit where objectClass = 'person' order by cn"

oCommand.CommandText = strQuery oCommand.Properties("Page Size") = 99

set oRS.sort ="cn"

i = 0 While not oRS.eof vObjectClass=oRS.Fields("objectClass") bShow = oRS.Fields("mail") > " " if bShow then oRS.Fields("mail") oRS.Fields("Assoc-NT-Account") End If oRS.MoveNext i=i+1 wend End FunctionCan you tell us what error you're getting?|||Yes, it would help to assess and refer to http://www.sqldts.com for reference on the active-x scripts.|||ActiveX can't create object ADODB :(|||I think you need to register the ActiveX components (OCX) on every workstation. You can download Setup/Install-Maker-Programs which are doing this job for you.

And ensure other PCs have the same version of the MDAC_TYP and http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/vamsgCantCreateObject.asp for reference.|||He's attempting to run the ActiveX component from an ActiveX Script in a DTS package. If this is running as a job from SQL Server, then you don't need ADO on anything other then the SQL Server, and that should be installed by default. If you're executing the ActiveX Script in a DTS Package directly (as opposed to a job), then you have to have ADO on your desktop, which should also be installed by default.

Godofredo, does it tell you exactly which line it fails on? And I'm assuming your real code does not have the run-on lines as in the example originally posted, am I correct?|||No the run lines are due to formatting when copying the code... sorry for that.

It fails on the | set oRS = CreateObject("ADODB.RecordSet") |- part the part between the two pipes. Is it that what you mean?|||Hmmm...

Try replacing this line:
oConn.Provider("ADsDSOObject")
With this:
oConn.Provider = "ADSDSOObject"

Second, I don't know about the "User Name" property. In my connections to LDAP, I simply use "User ID"

Third, I don't think you need to specify "Ads Provider" when you open the connection. Just leave it as:

oConn.Open

For reference, here's some code I've written for connecting to LDAP. You may find it useful:

'We need to make ADO objects to execute a search against Active Directory
set oConn = CreateObject("ADODB.Connection")
set oCommand = CreateObject("ADODB.Command")
set oRS = CreateObject("ADODB.Recordset")

'We have to use the AD Provider to search against AD using ADO
oConn.Provider = "ADSDSOObject"
oConn.Properties("User ID") = strADUserID 'Username of the account with access to *search* AD
oConn.Properties("Password") = strADPassword 'Password of the above username
oConn.Open 'Open connection to the provider
set oCommand.ActiveConnection = oConn 'Set the active connection

'Execute a SQL query through the AD Provider to the AD server to find our the username that was typed in
'ADNamespace = LDAP: (for AD), WINNT: can also be used to authenticate against a NT Domain
'ADServer = Server or IP to the AD server
'ADBaseDN = What AD container do we start searching from
'objectClass = "user" This tells the search to ignore everything except for "user" objects
'sAMAccountName = This is the user ID we want to find
strQuery= "SELECT distinguishedName FROM '" & strADNamespace & "//" & strADServer & "/" & strADBaseDN & "' WHERE objectClass='user' AND sAMAccountName='" & strUser & "'"
oCommand.CommandText = strQuery
oCommand.Properties("Size Limit") = 1 'We only want one result - and there should be only one.
oCommand.Properties("Timeout") = 30 'Time out if it takes to long
set oRS = oCommand.Execute 'Execute the query

dts package active x script

I want to rename my file in the active x script in my DTS package and archive it in a archive folder. My code is archiving the file in the archive, but I need to rename the file too so that it has filename_today's date. I wrote the code like this

set fso = CREATEOBJECT("Scripting.FileSystemObject")

fso.MoveFile DTSGlobalVariables("FileFullName").Value ,DTSGlobalVariables("ArchiveLocation").Value

before this step I want to write

fso.renamefile(DTSGlobalVariables("FileFullName").Value DTSGlobalVariables("FileFullName").Value & now)

and after this step I want to do

fso.MoveFile DTSGlobalVariables("FileFullName").Value ,DTSGlobalVariables("ArchiveLocation").Value

so in the archive folder I can have the date of the file that has been archived.

when i trying to do this

fso.renamefile(DTSGlobalVariables("FileFullName").Value DTSGlobalVariables("FileFullName").Value & now)

I am getting an error
Cannot use parantheses while calling a sub.

Please let me know how can I do this

Off the top of my head, the first thing I would try would be this:
fso.renamefile DTSGlobalVariables("FileFullName").Value DTSGlobalVariables("FileFullName").Value & now
|||I tried this, but now I am getting the error
Object doesn't support this property or method: 'fso.RenameFile'
Please let me know how can I fix this
Any help will be greatly appreciated|||

I figured it out.
Thanks

DTS Job ActiveX Script Help

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!

J827I would create the text file as another data source. You can then grab the first line, and put the date into the global variable or something, then run an activeX script to compare the global variable to the current date.

Does that kinda achieve what you are after?

DTS Help

Hey guys,
In my DTS package I have an ActiveX Script that basicaly checkes if records exist in a table something like this:
Const adOpenKeyset = 1
Const adLockOptimistic = 3

Function FindRec()

Dim objConn
Dim sql
Dim rs

Dim Success

Success = 0

sqlconn = "DSN=SAConn;UID=PSI_Admin;PWD=psiadmin001;DATABASE= SA"

Set objConn = CreateObject("ADODB.Connection")
objConn.Open sqlconn

sql = "SELECT NAM FROM tblUnassignedNAM "

Set rs = CreateObject("ADODB.RecordSet")
rs.Open sql, objConn, adOpenKeyset, adLockOptimistic

If rs.EOF = False Then

Success = 1

End If

rs.Close
Set rs = Nothing

objConn.Close
Set objConn = Nothing

If Success = 1 Then
FindRec = DTSStepExecResult_Success
ElseIf Success = 0 Then
FindRec = DTSStepExecResult_Failure
End If

End Function

If you notice at the end I say
If Success = 1 Then
FindRec = DTSStepExecResult_Success
ElseIf Success = 0 Then
FindRec = DTSStepExecResult_Failure
End If

So if there are no records it should fail and do something else, but the process does not fail it's successful. Why is that?You are simply returning the numeric value of the errors. You are not throwing an error.

DTS Global Variable Variant Type Mismatch

I am trying to declare a global variable in a DTS package for passing the recordset to the next stage Active Script . After declaration of the Global Variable and selecting datatype of the variable as OTHER ( Variant ) , when I try to save the DTS Package changes , it throws a Type Mismatch Error .

:confused: Please help me out .

Thanx
Arnie .You have to execute the package or otherwise cause it to set the type of the variable to what it's going to use (for recordsets it is 'dispatch' but you can't set it manually). Once the type changes, you can save it.