DTS and stored procedure

Hello there,

I have been reading through Online Books and many postings and still
am not sure how I should go about doing this.

I have SQL Server 2000 table tblENTRY and an AS400 table TKMAST.
TKMAST receives data and is maintained by an outside source. I need
to get the received data from TKMAST into tblENTRY. I can obtain the
data easily using the DTS. But what I can't figure out is how to
insert or update data in tblENTRY.

So, at this point I have a DTS package that creates a table using this
statement:
CREATE TABLE [FromTKMAST] (
[TKFILR] char (3) NOT NULL,
[TKENT#] numeric (7,0) NOT NULL,
[TKCKDG] numeric (1,0) NOT NULL,
[TKCLCD] char (6) NOT NULL,
[TKFILE] numeric (6,0) NOT NULL,
[TKDENY] numeric (2,0) NOT NULL,
[TKDENM] numeric (2,0) NOT NULL,
[TKDEND] numeric (2,0) NOT NULL
)

[TKENT#] is the primary key

I was thinking that I should do a trigger on FromTKMAST to update or
insert into tblENTRY but I feel like that is a slow and poor
solution. I feel like there is a capability in the DTS that I don't
see or understand. Is there a way to execute a stored procedure from
a DTS package? It seems like this must be a common task.

Breaking it down, first I need to get the data (dts package), then I
need to run a stored procedure that will take that data and insert or
update it in tblENTRY. This process needs to be automated...it needs
to check for new or updated records in TKMAST every five minutes or
so. It seems like such a basic operation, but I can't figure out
where and how I go about doing this. Where would I put the stored
procedure, if that is how I should do it? Any help would be greatly
appreciated.Have you read the Books Online that comes with SQLserver, there is a
chapter Using ActiveX Script in DTS. It looks like the solution. I
have a similar task but have not get time to do it.|||I think u r looking for DTS - EXECUTE SQL TASK, you can run any query
using this which run through query analyzer.

-Hayat
www.mysticssoft.com

phantomtoe@.yahoo.com (Rowan) wrote in message news:<4bbf8d70.0310021110.3096f1ff@.posting.google.com>...
> Hello there,
> I have been reading through Online Books and many postings and still
> am not sure how I should go about doing this.
> I have SQL Server 2000 table tblENTRY and an AS400 table TKMAST.
> TKMAST receives data and is maintained by an outside source. I need
> to get the received data from TKMAST into tblENTRY. I can obtain the
> data easily using the DTS. But what I can't figure out is how to
> insert or update data in tblENTRY.
> So, at this point I have a DTS package that creates a table using this
> statement:
> CREATE TABLE [FromTKMAST] (
> [TKFILR] char (3) NOT NULL,
> [TKENT#] numeric (7,0) NOT NULL,
> [TKCKDG] numeric (1,0) NOT NULL,
> [TKCLCD] char (6) NOT NULL,
> [TKFILE] numeric (6,0) NOT NULL,
> [TKDENY] numeric (2,0) NOT NULL,
> [TKDENM] numeric (2,0) NOT NULL,
> [TKDEND] numeric (2,0) NOT NULL
> )
> [TKENT#] is the primary key
> I was thinking that I should do a trigger on FromTKMAST to update or
> insert into tblENTRY but I feel like that is a slow and poor
> solution. I feel like there is a capability in the DTS that I don't
> see or understand. Is there a way to execute a stored procedure from
> a DTS package? It seems like this must be a common task.
> Breaking it down, first I need to get the data (dts package), then I
> need to run a stored procedure that will take that data and insert or
> update it in tblENTRY. This process needs to be automated...it needs
> to check for new or updated records in TKMAST every five minutes or
> so. It seems like such a basic operation, but I can't figure out
> where and how I go about doing this. Where would I put the stored
> procedure, if that is how I should do it? Any help would be greatly
> appreciated.

DTS - Looping in a Data DrivenQuery

Hi Guys

I am reading a table one record at a time. Within this record a field can contain multiple values.
The delimiter is a ^. The data comes from a Pick legacy system
The data looks like this :-
chargeable_item_cd quantity text_1
I77C1^I77C2 1^1 PLATES /SCREWS^1.3MM SET

I want to extract the multiple values from this field and insert a record for each set of values.
I can unravel the data easy enough. The problem I have is how to loop within a DTS Activex
Script to store each of the values extracted from the field before moving onto the next record.
Is it possible or am I better of using a SQL task an taking a fraction of the time. (I am resisting
this as my boss doesnt like SQL code)
I am doing this within a data driven query.

Thanks in advance.

Cheers
Chris

The code I have so far looks like this(but doesnt work). It gives a "No query specification returnedby transform status".
'************************************************* **********
' Visual Basic Transformation Script
'************************************************* **********

' Copy each source column to the destination column
Function Main()
DTSDestination("DHB_Key") = DTSLookups("DHB Lookup").Execute(DTSGlobalVariables("DHB_Code").Value)
DTSDestination("Health_Encounter_Theatre_Key") = DTSSource("rule_violtd_cd")

DTSDestination("Patient_Key") = DTSLookups("Patient Lookup").Execute(left(DTSSource("admit_id"), 7))

DTSDestination("Patient_Care_Episode_Key") = _
DTSLookups("Patient Care Lookup").Execute( DTSDestination("Patient_Key"), _
"*T" + DTSSource("hosp_cd") + DTSSource("scout_sheet_nbr"), _
DTSDestination("DHB_Key"))

DTSDestination("Health_Encounter_Key") = DTSLookups("Hlth Encntr Lookup").Execute( DTSDestination("DHB_Key"), _
DTSDestination("Patient_Key"), _
DTSDestination("Patient_Care_Episode_Key"), _
"TH")
' This piece of code does it for multi field values for charagble items to individual values that can be stored in the database

' Copy each source column to the destination column
DIM string1, string2, string3, sitem, sqty, sdescript, quantity, descript
string1 = DTSSource("chargeable_item_cd")
string2 = DTSSource("quantity")

Do While InStr( string1 , "^") > 0
sitem = left(string1, InStr( string1 , "^") -1 )
sqty = left(string2, InStr( string2 , "^") -1 )
sdescript = left(string3, InStr( string3 , "^") -1 )

DTSDestination("Chargeable_Items_Key") = DTSLookups("Chargeable Items Lookup").Execute(sitem)
DTSDestination("Quantity") = sqty
DTSDestination("Description_Of_Item") = sdescript

If IsNull(sqty) Then
quantity = 0
Else
quantity = sqty
End If

If IsNull(sdescript) Then
descript = "X"
Else
descript = sdescript
End If

If NOT IsNull(DTSDestination("Chargeable_Items_Key")) Then
If DTSLookups("Item Exists Lookup").Execute(DTSDestination("DHB_Key"),_
DTSDestination("Health_Encounter_Theatre_Key"),_
DTSDestination("Patient_Key"),_
DTSDestination("Patient_Care_Episode_Key"),_
DTSDestination("Health_Encounter_Key"),_
DTSDestination("Chargeable_Items_Key"),_
quantity,_
descript) = 0 Then
Main = DTSTransformstat_InsertQuery
Else
Main = DTSTransformStat_SkipRow
End If
Main = DTSTransformStat_OK
End If

string1 = Mid( string1 , InStr( string1 , "^") + 1, Len( string1 ) )
string2 = Mid( string2 , InStr( string2 , "^") + 1, Len( string2 ) )
string3 = Mid( string3 , InStr( string3 , "^") + 1, Len( string3 ) )
loop

Main = DTSTransformStat_OK
End Functionokie, this is what I would do...
write 2 sub routines......

Sub InsertItems(strItems)
Dim arrItems, iItem
arrItems = split(strItems,"^")
for iItem = 0 to uBound(arrItems)
ExecuteSQL("insert into itemTable (column) values (" & arrItems(iItem) & ")"
next
End Sub

Sub ExecuteSQL(strSQL)
On Error Resume Next
Dim oConn, oRs, strConn
Set oConn = CreateObject("ADODB.Connection")
Set oRs = CreateObject("ADODB.Recordset")

strConn = "Your connection string goes here"
oConn.ConnectionString = strConn
oConn.ConnectionTimeout = 30
oConn.Open
oConn.execute(stqSQL
set oConn = nothing
On Error Goto 0
End Sub

in your main function call InsertItems and pass it the value of chargeable_item_cd (assuming I got the name right)

Make sense??|||Hi Rokslide

I take it that this will work inside the Activex Script Transformation Properties portion of a data driven query.

It is nearly knock off time Friday afternoon in NZ so I will try it on Monday morning
.
Cheers
Chris|||Hi Chris,

Your Activex Script can have as many extra functions or sub routines as you like with no problems providing there is only 1 main function.

Nearly knowing off time?? It's only 2pm! ;) It is Friday though and the lead in to a long weekend so... ;)

Where are you working in Christchurch? I used to live and work there myself. :)|||Hi

I am based at Princess Margret Hospital by under the Port Hills but
I live way out in the country about 10 kms the other side of Rangiora
on a 52 acre farmlet.

Cheers|||Cool. :) I used to work for the Council there and then started a company with a friend. Before living in Christchurch I was living out in Cheviot.

Mmmmm country air... ;)

Let me know if you need any more help with that DTS package.

DTC - Transaction context in use by another session

Hi

I have a master package that executes a series of sub packages. The master package is run from a SQL Agent job. The packages are reading from and writing to two databases on the same instance of SQL Server 2005.

If I set the Execute Package Tasks for the sub packages without any precedence constraints between them and set TransactionOption = Required at the master package level (and supported from there downwards) I get the following errors.

The event log shows me:

The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00A "Unable to enlist in the transaction.".

Running a SQL Profiler trace shows me:

Error: 3910, Severity: 16, State: 2
Transaction context in use by another session.

This problem is well documented and seems to go back to DTS in SQL Server 7.0 . . . see http://support.microsoft.com/?scid=kb;en-us;279857&spid=2852&sid=150

I can get round it by setting precedence between the sub packages - making them run one at a time solves the problem. But then we don't get the performance benefits of running the packages concurrently. Does anyone have any other solutions.

TIA . . . Ed

Ed,

are using the RTM or SP1 version?

There was a fix in SP1 that might address your problem.

Thanks.

|||

Hi Bob

We tried SP1 but reverted because there were some issues with it and we couldn't afford the time to play around with it.

I can leave the packages running in series for now and try running them in parallel with SP1 installed when I have time to experiment.

Thanks . . . Ed

|||

I'm getting the same error messages in almost the same scenario, but I have SP1 applied. In my case, I have a sub-package that starts its own distributed transaction and it has multiple data flows that run concurrently within the same container component. Sometimes it runs through just fine. Other times, one or another of the data flows will die with this error. It's unpredictable which one or if it will fail at all. If I sequence the data flows, the problem goes away.

Is there an alternative solution besides the one in SP1? What was the one in SP1?

Thanks,

Joe

|||

Are you using a configured common connection between the two (or more) packages? As a guess, try to make the connection string slightly different for each by appending the ";Application Name=?" property with the package name where the ? is.

Under a slightly different scenario I was able to get the same thing happening and this corrected my problem. I was using a shared config file to configure the connections and when I added the Application Name part it went away.

|||

None of my packages are using configured connections. They each have their own connection manager defined within their package. I do intend to make them configured in the future, so I'll keep that suggestion in mind.

Also, I can get the error to occur when executing only one of the subpackages at a time. They each are starting their own transaction. It's the data flows that have problems running concurrently.

DTC - Transaction context in use by another session

Hi

I have a master package that executes a series of sub packages. The master package is run from a SQL Agent job. The packages are reading from and writing to two databases on the same instance of SQL Server 2005.

If I set the Execute Package Tasks for the sub packages without any precedence constraints between them and set TransactionOption = Required at the master package level (and supported from there downwards) I get the following errors.

The event log shows me:

The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00A "Unable to enlist in the transaction.".

Running a SQL Profiler trace shows me:

Error: 3910, Severity: 16, State: 2
Transaction context in use by another session.

This problem is well documented and seems to go back to DTS in SQL Server 7.0 . . . see http://support.microsoft.com/?scid=kb;en-us;279857&spid=2852&sid=150

I can get round it by setting precedence between the sub packages - making them run one at a time solves the problem. But then we don't get the performance benefits of running the packages concurrently. Does anyone have any other solutions.

TIA . . . Ed

Ed,

are using the RTM or SP1 version?

There was a fix in SP1 that might address your problem.

Thanks.

|||

Hi Bob

We tried SP1 but reverted because there were some issues with it and we couldn't afford the time to play around with it.

I can leave the packages running in series for now and try running them in parallel with SP1 installed when I have time to experiment.

Thanks . . . Ed

|||

I'm getting the same error messages in almost the same scenario, but I have SP1 applied. In my case, I have a sub-package that starts its own distributed transaction and it has multiple data flows that run concurrently within the same container component. Sometimes it runs through just fine. Other times, one or another of the data flows will die with this error. It's unpredictable which one or if it will fail at all. If I sequence the data flows, the problem goes away.

Is there an alternative solution besides the one in SP1? What was the one in SP1?

Thanks,

Joe

|||

Are you using a configured common connection between the two (or more) packages? As a guess, try to make the connection string slightly different for each by appending the ";Application Name=?" property with the package name where the ? is.

Under a slightly different scenario I was able to get the same thing happening and this corrected my problem. I was using a shared config file to configure the connections and when I added the Application Name part it went away.

|||

None of my packages are using configured connections. They each have their own connection manager defined within their package. I do intend to make them configured in the future, so I'll keep that suggestion in mind.

Also, I can get the error to occur when executing only one of the subpackages at a time. They each are starting their own transaction. It's the data flows that have problems running concurrently.