DTS newbie help...

Question

DTS newbie help...

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

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

Calvin Lawson SSChampion Points: 11030 More actions · Answer 1

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

Oblio Leitch SSCrazy Points: 2137 More actions · Answer 2

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

kailash sahu SSC Veteran Points: 249 More actions · Answer 3

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!!!

Oblio Leitch SSCrazy Points: 2137 More actions · Answer 4

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?

dzebrowitz SSC Journeyman Points: 87 More actions · Answer 5

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.

dzebrowitz SSC Journeyman Points: 87 More actions · Answer 6

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

Oblio Leitch SSCrazy Points: 2137 More actions · Answer 7

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

dzebrowitz SSC Journeyman Points: 87 More actions · Answer 8

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

kailash sahu SSC Veteran Points: 249 More actions · Answer 9

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!!!