Hi,
I have a DTS named "Rahul_Test" where i have a global variale named "Rahul" INteher (1 Byte). N trying to assign this variable from SQL statement. Here is the syntax that I am writing.
exec master..xp_cmdshell 'DTSRUN -E -S142.102.27.222 -Usa -Psa -N"Rahul_Test" -ARahul:8="aaa"'
After executing the syntax I am getting the following message...
DTSRun: Loading...
DTSRun: Executing...
DTSRun: Package execution complete.
NULL
But the global variable "Rahul" doesnot get changed to 225.
Kindly help me out in this regard.
Thanks in advance.
Rahul Jhaone qn,
"Rahul" is integer or string data type?|||Rahul is a string data type|||Have tried even with this syntax....... still it failed with the same output
exec master..xp_cmdshell 'DTSRUN -E -S142.102.27.222 -Usa -Psa -N"Rahul_Test" -A"Rahul:8"="225"'|||Rahul is String Data Type. By Mistake it was written as Integer while posting the issue........
Showing posts with label global. Show all posts
Showing posts with label global. Show all posts
Sunday, February 26, 2012
Friday, February 24, 2012
DTS: Problems calling packages from package.
(SQL Server 2000, SP3a)
Hello all!
I have a DTS package, that I'll call "A", which calls another package "B". In A's Global
Variable (GV) space, I'm defining the following variables:
SourceServer
SourceDatabase
SourceUser
SourcePassword
DestinationServer
DestinationDatabase
DestinationUser
DestinationPassword
I also define these same GVs in B.
I have A define the values for the GVs and I believe that they should "propagate" to B by
placing these variables in the "Outer Package Global Variables" tab.
However, when I try to change a value from A, it's like B "remembers" the last successful
value that was used (it's not picking up the changed value).
Am I doing something wrong with respect to GVs and packages calling other packages?
Thanks!
John PetersonJohn
I think you will be better to put the question to DTS.newsgroup and also
look at www.sqldts.com .
Personally , I have been calling DTS Package from stored procedure which is
accepted a parameter for GV.
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OSDjL$ETEHA.2480@.TK2MSFTNGP10.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I have a DTS package, that I'll call "A", which calls another package "B".
In A's Global
> Variable (GV) space, I'm defining the following variables:
> SourceServer
> SourceDatabase
> SourceUser
> SourcePassword
> DestinationServer
> DestinationDatabase
> DestinationUser
> DestinationPassword
> I also define these same GVs in B.
> I have A define the values for the GVs and I believe that they should
"propagate" to B by
> placing these variables in the "Outer Package Global Variables" tab.
> However, when I try to change a value from A, it's like B "remembers" the
last successful
> value that was used (it's not picking up the changed value).
> Am I doing something wrong with respect to GVs and packages calling other
packages?
> Thanks!
> John Peterson
>|||Thanks Uri...however, it *seems* like it's not working correctly in my case. :-(
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O3NZtGFTEHA.2944@.tk2msftngp13.phx.gbl...
> John
> I think you will be better to put the question to DTS.newsgroup and also
> look at www.sqldts.com .
> Personally , I have been calling DTS Package from stored procedure which is
> accepted a parameter for GV.
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OSDjL$ETEHA.2480@.TK2MSFTNGP10.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > I have a DTS package, that I'll call "A", which calls another package "B".
> In A's Global
> > Variable (GV) space, I'm defining the following variables:
> >
> > SourceServer
> > SourceDatabase
> > SourceUser
> > SourcePassword
> > DestinationServer
> > DestinationDatabase
> > DestinationUser
> > DestinationPassword
> >
> > I also define these same GVs in B.
> >
> > I have A define the values for the GVs and I believe that they should
> "propagate" to B by
> > placing these variables in the "Outer Package Global Variables" tab.
> >
> > However, when I try to change a value from A, it's like B "remembers" the
> last successful
> > value that was used (it's not picking up the changed value).
> >
> > Am I doing something wrong with respect to GVs and packages calling other
> packages?
> >
> > Thanks!
> >
> > John Peterson
> >
> >
>|||A better question might be, what exactly do the two tabs do? That is, if I want to
propagate my out (caller) package's GVs to the inner (callee), do I do that from the Outer
Package tab? Or should that be done from the Inner Package tab?
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eyt8UOFTEHA.2692@.TK2MSFTNGP09.phx.gbl...
> Thanks Uri...however, it *seems* like it's not working correctly in my case. :-(
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O3NZtGFTEHA.2944@.tk2msftngp13.phx.gbl...
> > John
> > I think you will be better to put the question to DTS.newsgroup and also
> > look at www.sqldts.com .
> >
> > Personally , I have been calling DTS Package from stored procedure which is
> > accepted a parameter for GV.
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:OSDjL$ETEHA.2480@.TK2MSFTNGP10.phx.gbl...
> > > (SQL Server 2000, SP3a)
> > >
> > > Hello all!
> > >
> > > I have a DTS package, that I'll call "A", which calls another package "B".
> > In A's Global
> > > Variable (GV) space, I'm defining the following variables:
> > >
> > > SourceServer
> > > SourceDatabase
> > > SourceUser
> > > SourcePassword
> > > DestinationServer
> > > DestinationDatabase
> > > DestinationUser
> > > DestinationPassword
> > >
> > > I also define these same GVs in B.
> > >
> > > I have A define the values for the GVs and I believe that they should
> > "propagate" to B by
> > > placing these variables in the "Outer Package Global Variables" tab.
> > >
> > > However, when I try to change a value from A, it's like B "remembers" the
> > last successful
> > > value that was used (it's not picking up the changed value).
> > >
> > > Am I doing something wrong with respect to GVs and packages calling other
> > packages?
> > >
> > > Thanks!
> > >
> > > John Peterson
> > >
> > >
> >
> >
>|||From this article: http://www.winnetmag.com/SQLServer/Article/ArticleID/19747/19747.html
<Quote>
Inside the Execute Package task, you can easily pass global variables to the child
package, as Figure 1 shows. If you click the Inner Package Global Variables tab in the
Execute Package task, DTS scans the child package for any global variables that it
expects, and you can set them to your own values. Inner global variables let you set the
global variables for the child package from the Execute Package task. Inner global
variables are useful when you're calling auditing packages and you want to pass to the
child package certain errors based on events in the parent package. Outer global variables
send global variables from the parent package to the child package. If a global variable
exists in the child package, DTS updates the child's global variable to the parent's
value. If the global variable doesn't exist in the child package, DTS creates it.
</Quote>
It would *seem* that my understanding of this is accurate. Though, clearly I'm still
doing something wrong...
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OvKJxQFTEHA.2692@.TK2MSFTNGP09.phx.gbl...
> A better question might be, what exactly do the two tabs do? That is, if I want to
> propagate my out (caller) package's GVs to the inner (callee), do I do that from the
Outer
> Package tab? Or should that be done from the Inner Package tab?
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:eyt8UOFTEHA.2692@.TK2MSFTNGP09.phx.gbl...
> > Thanks Uri...however, it *seems* like it's not working correctly in my case. :-(
> >
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:O3NZtGFTEHA.2944@.tk2msftngp13.phx.gbl...
> > > John
> > > I think you will be better to put the question to DTS.newsgroup and also
> > > look at www.sqldts.com .
> > >
> > > Personally , I have been calling DTS Package from stored procedure which is
> > > accepted a parameter for GV.
> > >
> > >
> > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > news:OSDjL$ETEHA.2480@.TK2MSFTNGP10.phx.gbl...
> > > > (SQL Server 2000, SP3a)
> > > >
> > > > Hello all!
> > > >
> > > > I have a DTS package, that I'll call "A", which calls another package "B".
> > > In A's Global
> > > > Variable (GV) space, I'm defining the following variables:
> > > >
> > > > SourceServer
> > > > SourceDatabase
> > > > SourceUser
> > > > SourcePassword
> > > > DestinationServer
> > > > DestinationDatabase
> > > > DestinationUser
> > > > DestinationPassword
> > > >
> > > > I also define these same GVs in B.
> > > >
> > > > I have A define the values for the GVs and I believe that they should
> > > "propagate" to B by
> > > > placing these variables in the "Outer Package Global Variables" tab.
> > > >
> > > > However, when I try to change a value from A, it's like B "remembers" the
> > > last successful
> > > > value that was used (it's not picking up the changed value).
> > > >
> > > > Am I doing something wrong with respect to GVs and packages calling other
> > > packages?
> > > >
> > > > Thanks!
> > > >
> > > > John Peterson
> > > >
> > > >
> > >
> > >
> >
> >
>
Hello all!
I have a DTS package, that I'll call "A", which calls another package "B". In A's Global
Variable (GV) space, I'm defining the following variables:
SourceServer
SourceDatabase
SourceUser
SourcePassword
DestinationServer
DestinationDatabase
DestinationUser
DestinationPassword
I also define these same GVs in B.
I have A define the values for the GVs and I believe that they should "propagate" to B by
placing these variables in the "Outer Package Global Variables" tab.
However, when I try to change a value from A, it's like B "remembers" the last successful
value that was used (it's not picking up the changed value).
Am I doing something wrong with respect to GVs and packages calling other packages?
Thanks!
John PetersonJohn
I think you will be better to put the question to DTS.newsgroup and also
look at www.sqldts.com .
Personally , I have been calling DTS Package from stored procedure which is
accepted a parameter for GV.
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OSDjL$ETEHA.2480@.TK2MSFTNGP10.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I have a DTS package, that I'll call "A", which calls another package "B".
In A's Global
> Variable (GV) space, I'm defining the following variables:
> SourceServer
> SourceDatabase
> SourceUser
> SourcePassword
> DestinationServer
> DestinationDatabase
> DestinationUser
> DestinationPassword
> I also define these same GVs in B.
> I have A define the values for the GVs and I believe that they should
"propagate" to B by
> placing these variables in the "Outer Package Global Variables" tab.
> However, when I try to change a value from A, it's like B "remembers" the
last successful
> value that was used (it's not picking up the changed value).
> Am I doing something wrong with respect to GVs and packages calling other
packages?
> Thanks!
> John Peterson
>|||Thanks Uri...however, it *seems* like it's not working correctly in my case. :-(
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O3NZtGFTEHA.2944@.tk2msftngp13.phx.gbl...
> John
> I think you will be better to put the question to DTS.newsgroup and also
> look at www.sqldts.com .
> Personally , I have been calling DTS Package from stored procedure which is
> accepted a parameter for GV.
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OSDjL$ETEHA.2480@.TK2MSFTNGP10.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > I have a DTS package, that I'll call "A", which calls another package "B".
> In A's Global
> > Variable (GV) space, I'm defining the following variables:
> >
> > SourceServer
> > SourceDatabase
> > SourceUser
> > SourcePassword
> > DestinationServer
> > DestinationDatabase
> > DestinationUser
> > DestinationPassword
> >
> > I also define these same GVs in B.
> >
> > I have A define the values for the GVs and I believe that they should
> "propagate" to B by
> > placing these variables in the "Outer Package Global Variables" tab.
> >
> > However, when I try to change a value from A, it's like B "remembers" the
> last successful
> > value that was used (it's not picking up the changed value).
> >
> > Am I doing something wrong with respect to GVs and packages calling other
> packages?
> >
> > Thanks!
> >
> > John Peterson
> >
> >
>|||A better question might be, what exactly do the two tabs do? That is, if I want to
propagate my out (caller) package's GVs to the inner (callee), do I do that from the Outer
Package tab? Or should that be done from the Inner Package tab?
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eyt8UOFTEHA.2692@.TK2MSFTNGP09.phx.gbl...
> Thanks Uri...however, it *seems* like it's not working correctly in my case. :-(
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O3NZtGFTEHA.2944@.tk2msftngp13.phx.gbl...
> > John
> > I think you will be better to put the question to DTS.newsgroup and also
> > look at www.sqldts.com .
> >
> > Personally , I have been calling DTS Package from stored procedure which is
> > accepted a parameter for GV.
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:OSDjL$ETEHA.2480@.TK2MSFTNGP10.phx.gbl...
> > > (SQL Server 2000, SP3a)
> > >
> > > Hello all!
> > >
> > > I have a DTS package, that I'll call "A", which calls another package "B".
> > In A's Global
> > > Variable (GV) space, I'm defining the following variables:
> > >
> > > SourceServer
> > > SourceDatabase
> > > SourceUser
> > > SourcePassword
> > > DestinationServer
> > > DestinationDatabase
> > > DestinationUser
> > > DestinationPassword
> > >
> > > I also define these same GVs in B.
> > >
> > > I have A define the values for the GVs and I believe that they should
> > "propagate" to B by
> > > placing these variables in the "Outer Package Global Variables" tab.
> > >
> > > However, when I try to change a value from A, it's like B "remembers" the
> > last successful
> > > value that was used (it's not picking up the changed value).
> > >
> > > Am I doing something wrong with respect to GVs and packages calling other
> > packages?
> > >
> > > Thanks!
> > >
> > > John Peterson
> > >
> > >
> >
> >
>|||From this article: http://www.winnetmag.com/SQLServer/Article/ArticleID/19747/19747.html
<Quote>
Inside the Execute Package task, you can easily pass global variables to the child
package, as Figure 1 shows. If you click the Inner Package Global Variables tab in the
Execute Package task, DTS scans the child package for any global variables that it
expects, and you can set them to your own values. Inner global variables let you set the
global variables for the child package from the Execute Package task. Inner global
variables are useful when you're calling auditing packages and you want to pass to the
child package certain errors based on events in the parent package. Outer global variables
send global variables from the parent package to the child package. If a global variable
exists in the child package, DTS updates the child's global variable to the parent's
value. If the global variable doesn't exist in the child package, DTS creates it.
</Quote>
It would *seem* that my understanding of this is accurate. Though, clearly I'm still
doing something wrong...
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OvKJxQFTEHA.2692@.TK2MSFTNGP09.phx.gbl...
> A better question might be, what exactly do the two tabs do? That is, if I want to
> propagate my out (caller) package's GVs to the inner (callee), do I do that from the
Outer
> Package tab? Or should that be done from the Inner Package tab?
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:eyt8UOFTEHA.2692@.TK2MSFTNGP09.phx.gbl...
> > Thanks Uri...however, it *seems* like it's not working correctly in my case. :-(
> >
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:O3NZtGFTEHA.2944@.tk2msftngp13.phx.gbl...
> > > John
> > > I think you will be better to put the question to DTS.newsgroup and also
> > > look at www.sqldts.com .
> > >
> > > Personally , I have been calling DTS Package from stored procedure which is
> > > accepted a parameter for GV.
> > >
> > >
> > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > news:OSDjL$ETEHA.2480@.TK2MSFTNGP10.phx.gbl...
> > > > (SQL Server 2000, SP3a)
> > > >
> > > > Hello all!
> > > >
> > > > I have a DTS package, that I'll call "A", which calls another package "B".
> > > In A's Global
> > > > Variable (GV) space, I'm defining the following variables:
> > > >
> > > > SourceServer
> > > > SourceDatabase
> > > > SourceUser
> > > > SourcePassword
> > > > DestinationServer
> > > > DestinationDatabase
> > > > DestinationUser
> > > > DestinationPassword
> > > >
> > > > I also define these same GVs in B.
> > > >
> > > > I have A define the values for the GVs and I believe that they should
> > > "propagate" to B by
> > > > placing these variables in the "Outer Package Global Variables" tab.
> > > >
> > > > However, when I try to change a value from A, it's like B "remembers" the
> > > last successful
> > > > value that was used (it's not picking up the changed value).
> > > >
> > > > Am I doing something wrong with respect to GVs and packages calling other
> > > packages?
> > > >
> > > > Thanks!
> > > >
> > > > John Peterson
> > > >
> > > >
> > >
> > >
> >
> >
>
DTS: Inserting the contents of a global variable into a table via a loop
Hello,
I am wondering if anyone can help me. I am fairly new to DTS and am having some difficulty getting something to work. I will explain below:
I have one table (table_a) which contains a listing of contact details. I want to copy the data from table_a to table_b. The tricky part is that I want to insert certain items into table_b depending on a conditional statement on the items in table_a. (ie. If value in table_a.column_a is 1 then insert "good" into table_b.column_a, etc)
I have done a lot of looking and found that an ActiveX Script Task is the answer. So I now have an Execute SQL Task that selects all the data from table_a into a global variable recordset, an ActiveX Script Task that loops through the recordset, determines the necessary values and modifies the SQL Statement of a temporary Execute SQL Task with the required INSERT statment (as explained in this article: http://www.databasejournal.com/features/mssql/article.php/1461581).
I run it and it all runs fine except that no data is inserted into the table!!! The SQL Statement in the temp Task does get modified with the INSERT statement and inserts the data fine when forced to run. But it appears that the temp Task is not being executed within the ActiveX Script.
Can anybody tell me how to force a step to run in this loop-based scenario, or if there is a much better way I could do this.
Thanks for any help. LeeIf I understand what you are trying to do...
Inputvalue = 10
if Inputvalue < 10, Output Value = 20, else Output Value = 30.
If you are trying to make those types of changes, then you can do some simple Transforms.
With two data sources, you can add a Transform Data Task.
You can modify (delete, then add) an activex transform within the Transform task that will allow you to modify outgoing data based on the data coming in.
If you need more help on this, see books online, ActiveX Script, then choose DTS.|||Thanks, yes. I was just looking into that as I received your post and it does indeed look like the ActiveX Transform option is the way to go.
I'm impressed you could decipher what I was after!
Thanks for the help.
I am wondering if anyone can help me. I am fairly new to DTS and am having some difficulty getting something to work. I will explain below:
I have one table (table_a) which contains a listing of contact details. I want to copy the data from table_a to table_b. The tricky part is that I want to insert certain items into table_b depending on a conditional statement on the items in table_a. (ie. If value in table_a.column_a is 1 then insert "good" into table_b.column_a, etc)
I have done a lot of looking and found that an ActiveX Script Task is the answer. So I now have an Execute SQL Task that selects all the data from table_a into a global variable recordset, an ActiveX Script Task that loops through the recordset, determines the necessary values and modifies the SQL Statement of a temporary Execute SQL Task with the required INSERT statment (as explained in this article: http://www.databasejournal.com/features/mssql/article.php/1461581).
I run it and it all runs fine except that no data is inserted into the table!!! The SQL Statement in the temp Task does get modified with the INSERT statement and inserts the data fine when forced to run. But it appears that the temp Task is not being executed within the ActiveX Script.
Can anybody tell me how to force a step to run in this loop-based scenario, or if there is a much better way I could do this.
Thanks for any help. LeeIf I understand what you are trying to do...
Inputvalue = 10
if Inputvalue < 10, Output Value = 20, else Output Value = 30.
If you are trying to make those types of changes, then you can do some simple Transforms.
With two data sources, you can add a Transform Data Task.
You can modify (delete, then add) an activex transform within the Transform task that will allow you to modify outgoing data based on the data coming in.
If you need more help on this, see books online, ActiveX Script, then choose DTS.|||Thanks, yes. I was just looking into that as I received your post and it does indeed look like the ActiveX Transform option is the way to go.
I'm impressed you could decipher what I was after!
Thanks for the help.
Sunday, February 19, 2012
DTS!! Plese help!
I've been stuck with this for a week and I seriously need help!
I have a DTS package with only 2 tasks, an ExecuteSqlTask that loads global variables and a TransformDataTask.
The TransformDataTask "source" is a query over one table, and the "destination" is another table. The transformation itself is an ActivexScript, written in VBscript, that performs several checks over source data and retrieves "data warehouse keys" from the DW tables using lookups.
So far, everything seems ok.
If I test the transformation it works fine, and produces a text output that (for testing purposes) I have tried to insert into the destination table, and this also works!!!!
But when I try to execute the package via Enterprise Manager the execution halts. I've set "Row Count Step" to 1, in order to guess wich line had the problem. This number changes but independetly from sorting data. Execution halts when less than 100 rows have been processed!!!! It actually doesn't halt, it says "Running" but stays like that for even days!!!!!!
Ok, I' ll send the code.
All the lookups "retrieve" values, except "actualiza_valores" that performs an update.
Any ideas would be apreciated!!!!
thanks in advance
lorena
Function Main()
Dim ASIGNADA
Dim CONFIRMADA
Dim NO_REALIZADA
Dim ULTERIOR
Dim PRIMERA_VEZ
Dim PROGRAMADA
Dim NO_PROGRAMADA
Dim NUEVA
Dim REPETIDA
Dim fecha
Dim TIEMPO_ESPERA
Dim cantAnio
Dim edad
Dim msg
ASIGNADA=0
CONFIRMADA=0
NO_REALIZADA=0
ULTERIOR = 0
PRIMERA_VEZ=0
NUEVA = 0
REPETIDA=0
TIEMPO_ESPERA= 0
PROGRAMADA = 0
NO_PROGRAMADA = 0
cantAnio = 0
msg="hola"
'Hay que ver si hay una consulta para el mismo paciente, profesional, servicio y fecha.
'Si es asi se actualizan las medidas de ese hecho.
'El tiempo de espera sera el promedio de los tiempos de espera
fecha = DTSLookups("get_id_fecha").Execute(DTSSource("fecha_consulta"))
servicio =DTSLookups("get_id_servicio").Execute(DTSSource("servicio_clave"))
If IsNull(DTSSource("profesional_clave")) Then
msg="El profesional llego NULL"
DTSLookups("set_entrada_log").Execute(msg)
profesional = DTSLookups("get_id_profesional").Execute(-1)
Else
profesional = DTSLookups("get_id_profesional").Execute(DTSSource("profesional_clave"))
End If
If IsEmpty(profesional) Then
msg="El profesional "&CStr(DTSSource("profesional_clave"))&" no esta en la dimension"
' DTSPackageLog.WriteStringToLog msg
profesional = DTSLookups("get_id_profesional").Execute(-1)
End If
cliente=DTSLookups("get_id_cliente").Execute(DTSSource("cliente_clave"))
If IsEmpty(cliente) Then
' DTSPackageLog.WriteStringToLog "El cliente"&CStr(DTSSource("cliente_clave"))&" no esta en la dimension"
cliente = DTSLookups("get_id_cliente").Execute(-1)
End If
cantAnio = DTSLookups("cant_consultas_ao").Execute(cliente, servicio)
If (cantAnio>0) Then
REPETIDA = 1
NUEVA=0
Else
NUEVA = 1
REPETIDA=0
End If
'confirmada, asignada, nopresentada
If StrComp( DTSSource("COD_ESTADO"),DTSLookups("get_cit").Execute())=0 Then
ASIGNADA=1
ElseIf StrComp( DTSSource("COD_ESTADO"),DTSLookups("get_visit").Execute())=0 Then
CONFIRMADA=1
ElseIf StrComp( DTSSource("COD_ESTADO"),DTSLookups("get_np").Execute())=0 Then
NO_REALIZADA=1
End If
If IsNull(DTSSource("TIPO_CEX")) Then
ULTERIOR = 0
PRIMERA_VEZ=0
ElseIf CInt(DTSSource("TIPO_CEX"))=CInt(DTSLookups("get_id_prim_vez").Execute()) Then
PRIMERA_VEZ=1
ElseIf CInt(DTSSource("TIPO_CEX"))=CInt(DTSLookups("get_id_ult").Execute()) Then
ULTERIOR = 1
End If
If IsNull(DTSSource("TIEMPO_ESPERA")) Then
TIEMPO_ESPERA=-1
Else
TIEMPO_ESPERA=DTSSource("TIEMPO_ESPERA")
End If
If DTSLookups("existe_consulta_dia").Execute(cliente, profesional,servicio,fecha) = 0 Then
'NO HAY EN LA BASE UNA CONSULTA PARA ESE CLIENTE EN ESE SERVICIO Y FECHA
DTSDestination("CLIENTE_ID") = cliente
DTSDestination("PROFESIONAL_ID") = profesional
If (DTSSource("edad")<=0) Then
DTSDestination("GR_ETAREO_ID") = DTSLookups("get_id_gr_etareo").Execute(0,0)
Else
If IsEmpty(DTSLookups("get_id_gr_etareo").Execute(CInt(DTSSource("edad")),CInt(DTSSource("edad")))) Then
DTSPackageLog.WriteStringToLog "No sabe calcular para "&CStr(DTSSource("edad"))
Else
DTSDestination("GR_ETAREO_ID") = DTSLookups("get_id_gr_etareo").Execute(CInt(DTSSource("edad")),CInt(DTSSource("edad")))
End If
End If
If IsNull(DTSSource("ICD_COD")) Then
DTSDestination("DIAGNOS_ID") = DTSLookups("get_id_diagnos").Execute(-1)
Else
DTSDestination("DIAGNOS_ID") = DTSLookups("get_id_diagnos").Execute(DTSSource("ICD_COD"))
End If
DTSDestination("SERVICIOS_ID")= servicio
DTSDestination("FECHA_ID") = fecha
If IsNull(DTSSource("MOTIVO_CONS_CLAVE")) Then
DTSDestination("MOTIVO_CONS_ID")=DTSLookups("get_id_motivo_cons").Execute(1)
Else
DTSDestination("MOTIVO_CONS_ID") = DTSLookups("get_id_motivo_cons").Execute(DTSSource("MOTIVO_CONS_CLAVE"))
End If
If IsNull(DTSSource("PROFESIONAL_DERIV_CLAVE")) Then
DTSDestination("PROFESIONAL_DERIVADOR_ID")=DTSLookups("get_id_profesional").Execute(-1)
Else
DTSDestination("PROFESIONAL_DERIVADOR_ID")=DTSLookups("get_id_profesional").Execute(DTSSource("PROFESIONAL_DERIV_CLAVE"))
End If
DTSDestination("TIEMPO_ESPERA") = TIEMPO_ESPERA
DTSDestination("CONSULTAS_CONFIRMADAS") = CONFIRMADA
DTSDestination("CONSULTAS_ASIGNADAS") = ASIGNADA
DTSDestination("CONSULTAS_NO_REALIZADAS")=NO_REALIZADA
DTSDestination("CONSULTAS_PRIMERA_VEZ") = PRIMERA_VEZ
DTSDestination("CONSULTAS_ULTERIORES")=ULTERIOR
DTSDestination("CONSULTAS_NUEVAS")=NUEVA
DTSDestination("CONSULTAS_REPETIDAS")=REPETIDA
DTSDestination("CONSULTAS_PROGRAMADAS")=PROGRAMADA
DTSDestination("CONSULTAS_NO_PROGRAMADAS")=NO_PROGRAMADA
DTSDestination("cant_consult") = 1
msg="Inserto "&CStr(cliente)&" profesional "&CStr(profesional)&" fecha "&CStr(fecha)&" servicio "&CStr(servicio)
'MsgBox(msg)
Main = DTSTransformStat_OK
Else
'HAY EN LA BASE, SE ACTUALIZA
a= DTSLookups("actualiza_valores").Execute(TIEMPO_ESPERA, CONFIRMADA,ASIGNADA,NO_REALIZADA,PROGRAMADA,NO_PRO GRAMADA,PRIMERA_VEZ,ULTERIOR,NUEVA,REPETIDA,CLIENT E,PROFESIONAL,SERVICIO,FECHA)
msg="Actualizo "&CStr(cliente)&" profesional "&CStr(profesional)&" fecha "&CStr(fecha)&" servicio "&CStr(servicio)
'MsgBox(msg)
Main = DTSTransformStat_SkipRow
End If
End FunctionI have just looked through the code and it all looks fine.
I suspect your problem lies elsewhere. Perhaps in the data you are processing or in the target table you are going to.
I'm not sure what it could be though. Perhaps some sort of lock being generated on the table or database. I really don't know.
It's possible that your MDAC installation is corrupted (it can cause some very strange behaviour). If possible try installing MDAC 2.8 and see if that helps. It won't hurt that is for sure.
Sorry I couldn't be of more help.
I have a DTS package with only 2 tasks, an ExecuteSqlTask that loads global variables and a TransformDataTask.
The TransformDataTask "source" is a query over one table, and the "destination" is another table. The transformation itself is an ActivexScript, written in VBscript, that performs several checks over source data and retrieves "data warehouse keys" from the DW tables using lookups.
So far, everything seems ok.
If I test the transformation it works fine, and produces a text output that (for testing purposes) I have tried to insert into the destination table, and this also works!!!!
But when I try to execute the package via Enterprise Manager the execution halts. I've set "Row Count Step" to 1, in order to guess wich line had the problem. This number changes but independetly from sorting data. Execution halts when less than 100 rows have been processed!!!! It actually doesn't halt, it says "Running" but stays like that for even days!!!!!!
Ok, I' ll send the code.
All the lookups "retrieve" values, except "actualiza_valores" that performs an update.
Any ideas would be apreciated!!!!
thanks in advance
lorena
Function Main()
Dim ASIGNADA
Dim CONFIRMADA
Dim NO_REALIZADA
Dim ULTERIOR
Dim PRIMERA_VEZ
Dim PROGRAMADA
Dim NO_PROGRAMADA
Dim NUEVA
Dim REPETIDA
Dim fecha
Dim TIEMPO_ESPERA
Dim cantAnio
Dim edad
Dim msg
ASIGNADA=0
CONFIRMADA=0
NO_REALIZADA=0
ULTERIOR = 0
PRIMERA_VEZ=0
NUEVA = 0
REPETIDA=0
TIEMPO_ESPERA= 0
PROGRAMADA = 0
NO_PROGRAMADA = 0
cantAnio = 0
msg="hola"
'Hay que ver si hay una consulta para el mismo paciente, profesional, servicio y fecha.
'Si es asi se actualizan las medidas de ese hecho.
'El tiempo de espera sera el promedio de los tiempos de espera
fecha = DTSLookups("get_id_fecha").Execute(DTSSource("fecha_consulta"))
servicio =DTSLookups("get_id_servicio").Execute(DTSSource("servicio_clave"))
If IsNull(DTSSource("profesional_clave")) Then
msg="El profesional llego NULL"
DTSLookups("set_entrada_log").Execute(msg)
profesional = DTSLookups("get_id_profesional").Execute(-1)
Else
profesional = DTSLookups("get_id_profesional").Execute(DTSSource("profesional_clave"))
End If
If IsEmpty(profesional) Then
msg="El profesional "&CStr(DTSSource("profesional_clave"))&" no esta en la dimension"
' DTSPackageLog.WriteStringToLog msg
profesional = DTSLookups("get_id_profesional").Execute(-1)
End If
cliente=DTSLookups("get_id_cliente").Execute(DTSSource("cliente_clave"))
If IsEmpty(cliente) Then
' DTSPackageLog.WriteStringToLog "El cliente"&CStr(DTSSource("cliente_clave"))&" no esta en la dimension"
cliente = DTSLookups("get_id_cliente").Execute(-1)
End If
cantAnio = DTSLookups("cant_consultas_ao").Execute(cliente, servicio)
If (cantAnio>0) Then
REPETIDA = 1
NUEVA=0
Else
NUEVA = 1
REPETIDA=0
End If
'confirmada, asignada, nopresentada
If StrComp( DTSSource("COD_ESTADO"),DTSLookups("get_cit").Execute())=0 Then
ASIGNADA=1
ElseIf StrComp( DTSSource("COD_ESTADO"),DTSLookups("get_visit").Execute())=0 Then
CONFIRMADA=1
ElseIf StrComp( DTSSource("COD_ESTADO"),DTSLookups("get_np").Execute())=0 Then
NO_REALIZADA=1
End If
If IsNull(DTSSource("TIPO_CEX")) Then
ULTERIOR = 0
PRIMERA_VEZ=0
ElseIf CInt(DTSSource("TIPO_CEX"))=CInt(DTSLookups("get_id_prim_vez").Execute()) Then
PRIMERA_VEZ=1
ElseIf CInt(DTSSource("TIPO_CEX"))=CInt(DTSLookups("get_id_ult").Execute()) Then
ULTERIOR = 1
End If
If IsNull(DTSSource("TIEMPO_ESPERA")) Then
TIEMPO_ESPERA=-1
Else
TIEMPO_ESPERA=DTSSource("TIEMPO_ESPERA")
End If
If DTSLookups("existe_consulta_dia").Execute(cliente, profesional,servicio,fecha) = 0 Then
'NO HAY EN LA BASE UNA CONSULTA PARA ESE CLIENTE EN ESE SERVICIO Y FECHA
DTSDestination("CLIENTE_ID") = cliente
DTSDestination("PROFESIONAL_ID") = profesional
If (DTSSource("edad")<=0) Then
DTSDestination("GR_ETAREO_ID") = DTSLookups("get_id_gr_etareo").Execute(0,0)
Else
If IsEmpty(DTSLookups("get_id_gr_etareo").Execute(CInt(DTSSource("edad")),CInt(DTSSource("edad")))) Then
DTSPackageLog.WriteStringToLog "No sabe calcular para "&CStr(DTSSource("edad"))
Else
DTSDestination("GR_ETAREO_ID") = DTSLookups("get_id_gr_etareo").Execute(CInt(DTSSource("edad")),CInt(DTSSource("edad")))
End If
End If
If IsNull(DTSSource("ICD_COD")) Then
DTSDestination("DIAGNOS_ID") = DTSLookups("get_id_diagnos").Execute(-1)
Else
DTSDestination("DIAGNOS_ID") = DTSLookups("get_id_diagnos").Execute(DTSSource("ICD_COD"))
End If
DTSDestination("SERVICIOS_ID")= servicio
DTSDestination("FECHA_ID") = fecha
If IsNull(DTSSource("MOTIVO_CONS_CLAVE")) Then
DTSDestination("MOTIVO_CONS_ID")=DTSLookups("get_id_motivo_cons").Execute(1)
Else
DTSDestination("MOTIVO_CONS_ID") = DTSLookups("get_id_motivo_cons").Execute(DTSSource("MOTIVO_CONS_CLAVE"))
End If
If IsNull(DTSSource("PROFESIONAL_DERIV_CLAVE")) Then
DTSDestination("PROFESIONAL_DERIVADOR_ID")=DTSLookups("get_id_profesional").Execute(-1)
Else
DTSDestination("PROFESIONAL_DERIVADOR_ID")=DTSLookups("get_id_profesional").Execute(DTSSource("PROFESIONAL_DERIV_CLAVE"))
End If
DTSDestination("TIEMPO_ESPERA") = TIEMPO_ESPERA
DTSDestination("CONSULTAS_CONFIRMADAS") = CONFIRMADA
DTSDestination("CONSULTAS_ASIGNADAS") = ASIGNADA
DTSDestination("CONSULTAS_NO_REALIZADAS")=NO_REALIZADA
DTSDestination("CONSULTAS_PRIMERA_VEZ") = PRIMERA_VEZ
DTSDestination("CONSULTAS_ULTERIORES")=ULTERIOR
DTSDestination("CONSULTAS_NUEVAS")=NUEVA
DTSDestination("CONSULTAS_REPETIDAS")=REPETIDA
DTSDestination("CONSULTAS_PROGRAMADAS")=PROGRAMADA
DTSDestination("CONSULTAS_NO_PROGRAMADAS")=NO_PROGRAMADA
DTSDestination("cant_consult") = 1
msg="Inserto "&CStr(cliente)&" profesional "&CStr(profesional)&" fecha "&CStr(fecha)&" servicio "&CStr(servicio)
'MsgBox(msg)
Main = DTSTransformStat_OK
Else
'HAY EN LA BASE, SE ACTUALIZA
a= DTSLookups("actualiza_valores").Execute(TIEMPO_ESPERA, CONFIRMADA,ASIGNADA,NO_REALIZADA,PROGRAMADA,NO_PRO GRAMADA,PRIMERA_VEZ,ULTERIOR,NUEVA,REPETIDA,CLIENT E,PROFESIONAL,SERVICIO,FECHA)
msg="Actualizo "&CStr(cliente)&" profesional "&CStr(profesional)&" fecha "&CStr(fecha)&" servicio "&CStr(servicio)
'MsgBox(msg)
Main = DTSTransformStat_SkipRow
End If
End FunctionI have just looked through the code and it all looks fine.
I suspect your problem lies elsewhere. Perhaps in the data you are processing or in the target table you are going to.
I'm not sure what it could be though. Perhaps some sort of lock being generated on the table or database. I really don't know.
It's possible that your MDAC installation is corrupted (it can cause some very strange behaviour). If possible try installing MDAC 2.8 and see if that helps. It won't hurt that is for sure.
Sorry I couldn't be of more help.
DTS with ASP.NET Question
Hi There,
I need some help with passing global variabe: I try this way but does not work??
my code:
1 Dim DTSPack As New DTS.Package
2
3 Try
4
5 DTSPack.LoadFromSQLServer(SrName, UName, PWD, DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "", "", PkgName)
6 DTSPack.GlobalVariables.Item("ExcelPath").Value = InpFileExcel.PostedFile.FileName.ToString
7 DTSPack.Execute()
8
9 Catch ExecDTS As Exception
10 lbl1.Text = ExecDTS.Message
11 End Try
Is it DTS (2000 ) or SSIS (2005). ?
Wednesday, February 15, 2012
DTS time out when run from SP global var
Hi
I run a DTS that gets global variable that says to which table to enter the text file. no problem when i run it inside the DTS. sometimes problem when i run it in the "local packages" frame but all the time i have a "time out" problem when i run it from SP. every time the number that written for the variable is like written in the defult for the DTS it runs in 1 second. when the value is the other one (say have file 1 and 2 and 1 is defult and i put 2 in the SP) i get 17 second and the process doesnt import the file at all!!! i use in the SP the sp_OACreate, sp_OAMethod, sp_OASetProperty with v. 97 and in the package in workflow properties of the current step i do If DTSGlobalVariables("FileToRun").Value <> 1 Then Main = DTSStepScriptResult_DontExecuteTask else Main = DTSStepScriptResult_ExecuteTask for both files. (actually should do 7).
any idea what is the problem as it runs good in the DTS frame cant be that its programming big mistake.
Thx
InonIs the SP running inside a transaction and locking the table? The dts package will get a new connection and be blocked by that lock.|||Originally posted by nigelrivett
Is the SP running inside a transaction and locking the table? The dts package will get a new connection and be blocked by that lock.
No the SP doesnt touch the table only the DTS fill the table.
I run a DTS that gets global variable that says to which table to enter the text file. no problem when i run it inside the DTS. sometimes problem when i run it in the "local packages" frame but all the time i have a "time out" problem when i run it from SP. every time the number that written for the variable is like written in the defult for the DTS it runs in 1 second. when the value is the other one (say have file 1 and 2 and 1 is defult and i put 2 in the SP) i get 17 second and the process doesnt import the file at all!!! i use in the SP the sp_OACreate, sp_OAMethod, sp_OASetProperty with v. 97 and in the package in workflow properties of the current step i do If DTSGlobalVariables("FileToRun").Value <> 1 Then Main = DTSStepScriptResult_DontExecuteTask else Main = DTSStepScriptResult_ExecuteTask for both files. (actually should do 7).
any idea what is the problem as it runs good in the DTS frame cant be that its programming big mistake.
Thx
InonIs the SP running inside a transaction and locking the table? The dts package will get a new connection and be blocked by that lock.|||Originally posted by nigelrivett
Is the SP running inside a transaction and locking the table? The dts package will get a new connection and be blocked by that lock.
No the SP doesnt touch the table only the DTS fill the table.
Subscribe to:
Posts (Atom)