Post reply

DTS newbie help...

  • June 22, 2004 at 12:21 pm

    #511495

    Most likely your administrator is smart and locked it down (imagine someone running "del c:\* /F /Q").  You've got very precise instructions on how to set the job up and schedule, so you should do that.

    cl

    Signature is NULL

  • June 23, 2004 at 7:28 pm

    #511689

    Well, I hope that someone locked it down - is there any way to tell?

    No, EXEC master.dbo.xp_cmdshell 'dir c:\' doesn't work.

    The sp_start_job thing is fantastic; that's exactly what I needed.

    Now, I have one final hurdle. I've got everything working - the Excel file uploading to a table with a data pump. Then the workflow splits between updates and inserts, and finally it truncates the upload table.

    What I also need to do is add the new ids to a matching table. The problem is, those ids are identity fields assigned by the destination table (even though I'm using an email address field for determining if a record exists).

    Can anyone suggest a way for me to insert from staging to destination, acquire a new identity number, then insert that into a matching table (possibly multiple times)? Then all I'll need to do is drive a stake through it!

    TIA

  • June 24, 2004 at 10:08 am

    #511809

    if i understand you correctly...you are trying to get the identity field value from one table and trying to insert it to the other matching tables....right?

    if so, you can get the current identity value of any table using IDENT_CURRENT('DatabaseName.dbo.TableName')

    Hope this help!!!

  • June 24, 2004 at 11:39 am

    #511826

    Well, unfortunately, no, because I'm doing a bulk insert and need to return all newly created idents.

    However, I think I've over-come the issue by reselecting back from the table I've inserted into.

    Now, I have an ActiveX question:

    I've been looking at the stuff on sqldts.com, and I want to use ActiveX to control the workflow conditions of a single task. I only want the task to execute if there's data in a table - how do I do that? Here's what I have so far:

    Function Main()

	Dim oRS

	' Build new SQL Statement
	sSQLStatement = "SELECT * FROM source_cats_import1"

' Now, magically the SQL statement executes, and...

	' Check we have some records in our Recordset
	If oRS.Value.BOF Then
		' No records found, stop workflow
		Main = DTSStepScriptResult_DontExecuteTask
	Else
		' We have records so continue
		Main = DTSStepScriptResult_ExecuteTask
	End If

	Set oRS = Nothing

End Function

    I think this will work, but I don't know how to execute SQL from ActiveX. Can anyone give me suggestions?

  • June 24, 2004 at 11:45 am

    #511830

    What kind of task do you want to execute? 

    If the task can be completed with a T-SQL statement, then you can use a T-SQL IF statement to either execute it or not, based on the rowcount.

    I've also done this same type of thing with a Data Transformation Task... you can use a SQL IF statement in there.  But, if the rowcount is zero, you still have to return an "empty" result set with all of the field names.  If you don't return the field names, the task will blow up.

  • June 24, 2004 at 12:02 pm

    #511835

    Example for a "conditional" Data Transformation Task:

    IF (SELECT Count(*) from table_A) >  0
    BEGIN

      SELECT    A,B,C,D

      FROM my_table
    END

    ELSE
    BEGIN

     --This Query does not return any data

        SELECT     A,B,C,D  

        FROM my_table
        WHERE A IS  NULL 
     
    -- Assumming that A is a primary key and A is never NULL... this query will never return results but will provide an empty result set with the field names.
    END

  • June 24, 2004 at 3:41 pm

    #511883

    I suppose I could do that. Do you know, though, how to execute a query through ActiveX?

  • June 24, 2004 at 4:11 pm

    #511884

    Yes... you can do it as shown below.  Or, you can also use a Exec SQL task to do the count, and put the result into a global variable.  You can then reference the global variable in the activex script.

    But here you go, this is calling a UDF.  You can use any TSQL statement as long as it returns only one value:

    Function fGet_Transfer_Pricing(parCalendar_DT)

    Dim connODS 

    'Instantiate the ADO Database Connection Objects

    set connODS = CreateObject("ADODB.Connection")

     

    'set the connection properties to point to the ODS database

    connODS.Open = "Provider=SQLOLEDB.1;Data Source=server_name;Initial Catalog=database_name;user id ='sa';password='password'"

     Dim rsResult

     Dim strSQLCmd

     

     'Instantiate the ADO Database Connection Objects

     set rsResult = CreateObject("ADODB.Recordset")

    '**************************************************************************************

    '* Get Annualized Transfer Pricing Rate 

    strSQLCmd = "EXEC ods_allocations.dbo.usp_Alloc_Annualized_Transfer_Pricing_Rate '" & cstr(parCalendar_DT) &"'"

    '* Using TSQL Query, populate recordset from ODS Database

    rsResult.Open  strSQLCmd, connODS

    fGet_Transfer_Pricing  = rsResult.Fields(0).Value
    Set rsResult = Nothing

     '**************************************************************************************

    End Function

  • June 25, 2004 at 9:35 am

    #511986

    Hi end-user,

    With all due respect, i feel it would be better and beneficial for others also, if you continue posting your questions in seperate threads rather than posting in the same.

    Cheers!!!

     

     

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply