Re: which authentication mechanism should I use?

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@xxxxxxxxxxxx> wrote in message news:etSgCQDTFHA.3556@xxxxxxxxxxxxxxxxxxxxxxx
> 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?
>
>> If you must use ADO, are you sure you do not want to use an ExecuteSQL task to know what to do?,
> I'd love to use ExecuteSQL task but I don't know how to execute it from my 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@xxxxxxxxxxxxxxxxxx> wrote in message news:%23FKy4oCTFHA.228@xxxxxxxxxxxxxxxxxxxxxxx
>> 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@xxxxxxxxxxxx> wrote in message news:e0I775BTFHA.3152@xxxxxxxxxxxxxxxxxxxxxxx
>>>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@xxxxxxxxxxxxxxxxxx> wrote in message news:%23lzKt0BTFHA.2840@xxxxxxxxxxxxxxxxxxxxxxx
>>>> 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@xxxxxxxxxxxx> wrote in message news:Od39XrBTFHA.580@xxxxxxxxxxxxxxxxxxxxxxx
>>>>> 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@xxxxxxxxxxxxxxxxxx> wrote in message news:eTRxjjBTFHA.2560@xxxxxxxxxxxxxxxxxxxxxxx
>>>>>>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@xxxxxxxxxxxx> wrote in message news:%23vkAjbBTFHA.1896@xxxxxxxxxxxxxxxxxxxxxxx
>>>>>>>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
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>


.

Relevant Pages

  • Re: Missing Global Variable parameter mapping
    ... Does that work everytime. ... The ExecuteSQL task is just ... > problems later in the package because it thinks it should be copying zero ...
    (microsoft.public.sqlserver.dts)
  • Re: DTS from C#
    ... Did you install an unhandled exception handler to catch exceptions that do ... > I have a DTS package that works perfectly fine from within Enterprise ... It has an ExecuteSQL task to clear a table, ...
    (microsoft.public.sqlserver.dts)
  • Re: Data Transfer from Oracle to SQLSRV is very slow
    ... I tried to put the Select statement in an ExecuteSQL task, ... that ExecuteSQL doesn't wait the results to fire Success ... but just syntax validation. ... >> The source is a quite simple SELECT on several tables with inner joins ...
    (microsoft.public.sqlserver.dts)
  • Re: which authentication mechanism should I use?
    ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... Set the SQLStatement property of the following ExecuteSQL Task ... >> Allan Mitchell MCSE,MCDBA, ... >>> I'd love to use ExecuteSQL task but I don't know how to execute it from my activeX. ...
    (microsoft.public.sqlserver.dts)
  • Re: which authentication mechanism should I use?
    ... account if the schadulaed job is owned by someone who is in sysadmin role.Do ... > If the job is owned by a sysadmin then it will use the SQL Server Agent ... > If you are going to use the ActiveX approach then the above is all you ... > property of the following ExecuteSQL Task ...
    (microsoft.public.sqlserver.dts)