I have a bunch of Activexscripts in my DTS package This DTS package is going
to be schadulaed to run automatically .In my activeX code I use ADO
connections in those connection strings I used Integrated Security.Will it
be problematci when the DTS package becomes schaduled?

Which type of authentication should I use for my ado codes?

Thanks

I would ask myself why I was using ADO code inside DTS. Is there something DTS is not fulfilling?

As for authentication. This is by far the best article

http://support.microsoft.com/?kbid=269074

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


"Ray5531" <RayAll (AT) microsft (DOT) com> wrote

Yes,I'd like to get a count of one table in my activeX code and then based
on that count I would execute different packages .then after executing each
package I should Insert into a table and when the package is done I'd like
to delete the record from database,with this mechanism I control that not 2
instances of the same package is run at the same time (because of some
restrictions I have).

I know that I can use "Execute SQL task" ,but the problem is that I can not
run this task from within my ActiveX ,I can only set its sql statement and
let t run in the normal workflow which is not what I want .I'd like to get
the result right away and use it in ActiveX script.

Makes sense?

Thanks
"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Not really.

You can assign the return value from your

SELECT COUNT(*).......

to a Global Variable and use it.

You then use Workflow to decide what to do after that.

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


"Ray5531" <RayAll (AT) microsft (DOT) com> wrote

I know that I can assign return value from SELECT COUNT(*)....... to a
global variable and use it later,but I need that value in ActiveX script one
step before the "Execute Sql Take" gets executed in the normal
workflow,that's why I need something I can execute rather than just
assigning its sql statement and execute it later.I need the result in my
Activex Script.
Here is the code

For Each file In coll
set cnn = CreateObject("ADODB.Connection")
strConnection = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Data
Source=" & DTSGlobalVariables("gDatabaseServer").Value & ";" & _
" Initial Catalog=" &
DTSGlobalVariables("gCatalogName").Value
cnn.Open strConnection
strSQL = "SELECT COUNT(CONFIGID) AS countActive FROM
IF_CONFIG_SETTINGS WHERE (IFCODE =" & _
iIFCode & ")"
Set rs = cnn.Execute(strSQL)
************************************************** ************************************************** ****************
'EXIT from function if there is another instance of the same package
is running
IF rs.Fields("countActive") > 0 THEN
'Clean Up
Set cnn = Nothing
Set rs = Nothing
Main = DTSTaskExecResult_Success
EXIT FUNCTION
END IF
'*****INSERT INTO CONFIG
FILE********************************************** ****************************************

strSQL =Empty
strSQL ="INSERT IF_CONFIG_SETTINGS(FILE_ID,IFCODE) VALUES (" &
DTSGlobalVariables("gFileID").Value & "," & iIFCode & ")"
cnn.Execute(strSQL)
'************************************************* ************************************************** **************************
sServer = TRIM( DTSGlobalVariables("gDatabaseServer").Value)
sUID = ""
sPWD = ""
iSecurity = DTSSQLStgFlag_UseTrustedConnection
sPkgPWD = ""

SELECT CASE iIFCode
CASE 1
sPkgName = "C1TransformPackage"
CASE 4
sPkgName = "C4TransformPackage"
CASE 41
sPkgName = "C4TransformPackageHistory"
END SELECT

' Load Child Package
oPkg.LoadFromSQLServer sServer, sUID, sPWD, iSecurity , sPkgPWD, "", "",
sPkgName

oPkg.GlobalVariables("gCSVFilePath").Value=
DTSGlobalVariables("gLocalImportFolder").Value & "\" & file
oPkg.GlobalVariables("gFileID").Value= "11400"

oPkg.Execute
' Now check for errors in the Child Package
For Each oStep In oPkg.Steps
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
Main = DTSTaskExecResult_Failure
End If
Next

fso.MoveFile DTSGlobalVariables("gLocalImportFolder").Value & "\" &
file, DTSGlobalVariables("gLocalMoveFolder").Value & "\" & file

'*****DELETE FROM CONFIG
FILE********************************************** **************************************
strSQL ="DELETE IF_CONFIG_SETTINGS WHERE FILE_ID='" &
DTSGlobalVariables("gFileID").Value & "' AND IFCODE=" & iIFCode
cnn.Execute(strSQL)
'************************************************* ************************************************** ************************

' Clean Up
Set cnn = Nothing
Set rs = Nothing

EXIT FOR ' THIS PROCESS IS INTENDED TO BE RUN ONLY ONCE FOR NOW
Next




Thanks for your help

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

You are creating a new ADODB Connection on every iteration over the files.

If you must use ADO, are you sure you do not want to use an ExecuteSQL task to know what to do?, then if you use a trusted
connection then it will use, when scheduled, the runner of the package



--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


"Ray5531" <RayAll (AT) microsft (DOT) com> wrote

As yuo can see there is no more than one iteration for the the collection ,I
only process the first file in the collection and then Exit the FOR
statement.
The package ,when is schaduled,is run under the context of the owner.I can
set up SQL Agent proxy account to be owner and I'll give appropriate
Windows/NT and also database related permissions to operate well.
How about that?

activeX.
Look ,in my case I need to extract some information from the file name which
I'm processing ,and this information is used as a parameter for ExecuteSQL
task to retrieve another piece of information which I need again in my
ActiveX task,so I can't say that there is Activex task first and ExecuteSQL
task as the second steps ,they are somehow mixed together in my case,that's
why I cannot use ExecuteSQL task .I should be able to make the query
dynamicaaly and get the result from it right away.

Makes sense?



Thanks Allen for following up this thread.

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

If the job is owned by a sysadmin then it will use the SQL Server Agent service account when executed

If you are going to use the ActiveX approach then the above is all you need.

For the ExecuteSQL task approach I would do

Active Script Task - Grab the File Name into a GV. Set the SQLStatement property of the following ExecuteSQL Task
ExecuteSQL Task - Grab the Count of rows in the table matching the condition into a GV
Active Script Task - Check the value of the GV holding the rowcount

1. If > 0 then using workflow enable an ExecuteSQL task that does your INSERT having built up the statement in this task
2. If < 0 check the value of the GV holding the value of the filename and execute the correct package.



--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


"Ray5531" <RayAll (AT) microsft (DOT) com> wrote

Thanks Allan,

So you mean **Integrated Security=SSPI** uses SQL Server Agent Service
account if the schadulaed job is owned by someone who is in sysadmin role.Do
I undrestand you clearly?

Thanks

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

You do

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


"Ray5531" <Ray5531 (AT) microsoft (DOT) com> wrote