DTS Workflow problems

I have created a stored procedure, but returns a recordset if sorten conditions are met.
The stored procedure can return no recordset as well. Then, I have created a DTS that executes
this stored procedure. What I want is the following. If the stored procedure does not return anything, I want the DTS not to execute.
My DTS consists of: Connection > Destination File.

This is my ActiveX transformation:

Function Main()
DTSDestination("Study_Area_ID") = DTSSource("Study_Area_ID")
DTSDestination("Entered_Month_Dt") = DTSSource("Entered_Month_Dt")
DTSDestination("Applies_To_Month_Dt") = DTSSource("Applies_To_Month_Dt")
DTSDestination("Support_Type_Cd") = DTSSource("Support_Type_Cd")
DTSDestination("LSS_Payment_Amt") = DTSSource("LSS_Payment_Amt")
DTSDestination("LSS_Calc_Payment_Amt") = DTSSource("LSS_Calc_Payment_Amt")
DTSDestination("Eligibility_Ind") = DTSSource("Eligibility_Ind")

Main = DTSTransformStat_OK
End FunctionOriginally posted by awegrzyn
I have created a stored procedure, but returns a recordset if sorten conditions are met.
The stored procedure can return no recordset as well. Then, I have created a DTS that executes
this stored procedure. What I want is the following. If the stored procedure does not return anything, I want the DTS not to execute.
My DTS consists of: Connection > Destination File.

This is my ActiveX transformation:

Function Main()
DTSDestination("Study_Area_ID") = DTSSource("Study_Area_ID")
DTSDestination("Entered_Month_Dt") = DTSSource("Entered_Month_Dt")
DTSDestination("Applies_To_Month_Dt") = DTSSource("Applies_To_Month_Dt")
DTSDestination("Support_Type_Cd") = DTSSource("Support_Type_Cd")
DTSDestination("LSS_Payment_Amt") = DTSSource("LSS_Payment_Amt")
DTSDestination("LSS_Calc_Payment_Amt") = DTSSource("LSS_Calc_Payment_Amt")
DTSDestination("Eligibility_Ind") = DTSSource("Eligibility_Ind")

Main = DTSTransformStat_OK
End Function

Use the following active x transformation task. Make sure you add all of the source and destination colums. I do this a lot please let me know if you need help. jon@.oratu.com

'************************************************* *********************
' Visual Basic Transformation Script
'************************************************* ***********************

' Copy each source column to the destination column
Function Main()

if var is DTSSource("Study_Area_ID") and DTSSource("Entered_Month_Dt") then
Main = DTSTransformStat_SkipRow
else

DTSDestination("Study_Area_ID") = DTSSource("Study_Area_ID")
DTSDestination("Entered_Month_Dt") = DTSSource("Entered_Month_Dt")
DTSDestination("Applies_To_Month_Dt") = DTSSource("Applies_To_Month_Dt")
DTSDestination("Support_Type_Cd") = DTSSource("Support_Type_Cd")
DTSDestination("LSS_Payment_Amt") = DTSSource("LSS_Payment_Amt")
DTSDestination("LSS_Calc_Payment_Amt") = DTSSource("LSS_Calc_Payment_Amt")
DTSDestination("Eligibility_Ind") = DTSSource("Eligibility_Ind")

main = Main = DTSTransformStat_OK
end if

End Function|||Hi,

I'll try this at work tomorrow and let you know. Thanks for the help.

Andrzej|||Hi,

Your code failed on line 1 (object required) during execution
This is what i had:

Function Main()
IF var is DTSSource("Study_Area_ID") and DTSSource("Entered_Month_Dt") THEN
Main = DTSTransformStat_SkipRow
ELSE
DTSDestination("Study_Area_ID") = DTSSource("Study_Area_ID")
DTSDestination("Entered_Month_Dt") = DTSSource("Entered_Month_Dt")
DTSDestination("Applies_To_Month_Dt") = DTSSource("Applies_To_Month_Dt")
DTSDestination("Support_Type_Cd") = DTSSource("Support_Type_Cd")
DTSDestination("LSS_Payment_Amt") = DTSSource("LSS_Payment_Amt")
DTSDestination("LSS_Calc_Payment_Amt") = DTSSource("LSS_Calc_Payment_Amt")
DTSDestination("Eligibility_Ind") = DTSSource("Eligibility_Ind")

Main = DTSTransformStat_OK
END IF
End Function

DTS to Lotus notes

DTS to Lotus notes

I must import/export some data from SQL Server to Lotus Notes.
I downloaded and installed NotesSQL ODBC drivers.
I met no problem importing from a Lotus Notes view to an SQL Server table: I used DTS and everything succeded.
But I can't do the reverse process (exporting from SQL Server to Lotus Notes): DTS keeps on giving an error without description and it fails.
Does anybody know how to export a table or a view from SQL Server to Lotus Notes using DTS?

Thanks in advance.Are you trying to setup a daily process, or is it a 1 time thing?

Do you have Notespump or Notestrix?

And as much as they protest, Lotus Notes is NOT a database...

Sorry...I got carried away...

And what do you mean...are you trying 2 way replication?|||Originally posted by Brett Kaiser
Are you trying to setup a daily process, or is it a 1 time thing?

Do you have Notespump or Notestrix?

And as much as they protest, Lotus Notes is NOT a database...

Sorry...I got carried away...

And what do you mean...are you trying 2 way replication?

Thanks for the reply. This would be a weekly process that I would like to setup and schedule via DTS. I would agree that notes is not a database by definition. I would just like to automate this if possible. It is a very small dataset based on an SQL query. Do I have a bad version of NotesSQL that only works one way?! I don't have Notespump or Notestrix. Wouldn't those apps be a bit overkill? Thanks! - JB|||I haven't done this...but I'm going to try it...got a link for the driver?

Also, how do you authenticate?

Maybe it's a permissions issue...|||Originally posted by Brett Kaiser
I haven't done this...but I'm going to try it...got a link for the driver?

Also, how do you authenticate?

Maybe it's a permissions issue...

The site is: www.lotus.com/notessql

The owner of the DB has given me read/write
permission on the DB. Each time DTS attempts
to read or write to the .nsf, I am prompted for
my lotus notes password and it is accepted.|||Still looking

Found this

http://www.lotus.com/products/rnext.nsf/873769A79D9C5B2285256A0800720B96/F8BA31E9F7BDEC3A85256C4700667625?OpenDocument

Does it have to be installed on the server, or can it be installed on the client?

I guess where ever DTS is running, no?|||Originally posted by Brett Kaiser
Still looking

Found this

http://www.lotus.com/products/rnext.nsf/873769A79D9C5B2285256A0800720B96/F8BA31E9F7BDEC3A85256C4700667625?OpenDocument

Does it have to be installed on the server, or can it be installed on the client?

I guess where ever DTS is running, no?

Yes. I think you would want to install it on the server. You will then be permitted to setup an odbc connection there to an nsf file.|||You can install on the client...just did it...

Bam shows up in my source selection list...

Now I gotta find a notes server to link to...

Did you see the user manual after the install?|||I've reviewed the "help" html doc a bit. It seems to talk more about extracting data to another DB.|||The site is: www.lotus.com/notessql

...Each time DTS attempts to read or write to the .nsf, I am prompted for
my lotus notes password and it is accepted.

Don't want this to happen every time? Go into your registry under HKLM->Software->ODBC->ODBC.INI, then find the entry for your connection. Add a new string called SuppressUID and set it to 1. You won't get prompted anymore. You do have to do this for all of your Notes connections though.