Re: which authentication mechanism should I use?

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: DTS Error EXCEPTION_ACCESS_VIOLATION
    ... >>We have some DTS jobs that runs every night called via a ... >>Package ... If this is no help, then please describe how you execute the package, ... DTS - http://www.sqldts.com PASS - the definitive, global community for SQL Server professionals ...
    (microsoft.public.sqlserver.dts)
  • Re: ETL rejects records intermittently
    ... The data source is the SQL SERVER 2005 table. ... We first dump the data from ... When I execute the SSIS package ...
    (microsoft.public.sqlserver.dts)
  • Re: which authentication mechanism should I use?
    ... global variable and use it later,but I need that value in ActiveX script one ... workflow,that's why I need something I can execute rather than just ... 'EXIT from function if there is another instance of the same package ... >>> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ...
    (microsoft.public.sqlserver.dts)
  • Re: DTS and MSDE
    ... that does the calling of the package as the package runs from the place calling ... I will presume that the package is stored on a SQL Server somewhere. ... The above error is not appearing the in the vb app (removed error ... I've even used the "sa" login to execute the dts pkg and still ...
    (microsoft.public.sqlserver.dts)
  • Re: Export to Text File Fails from Server Box
    ... If you are on WKSTN1 and you pick up a package from SVR1 and press execute, ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. ... >I should have specified in my question that in re-creating the text> connection, I did point in to a file local to the SQL Server. ... When I click on the>>> Destination tab, there is an execute button. ...
    (microsoft.public.sqlserver.dts)