DTS Parametirized ExecuteSQlTask

Hi,

I have a DTS whit several SQl tasks that executes a stored procedure. The result of this execution is stored in an output parameter as global variable.

The problem is if i manually launch the DTS package, it works with no problems but after i schedule the job, i received an error in the SQl tasks i said before. What can i do to fix it?

The Error is:
-----
Executed as user: SCCCOL1\sqlservices. ...t: DTSStep_DTSActiveScriptTask_4 DTSRun OnFinish:
DTSStep_DTSActiveScriptTask_4 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_26 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_26 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_4 DTSRun OnError: DTSStep_DTSExecuteSQLTask_4, Error = -2147220421 (8004043B) Error string: The task reported failure on execution. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 700 Error Detail Records: Error: -2147220421 (8004043B); Provider Error: 0 (0) Error string: The task reported failure on execution. Er... Process Exit Code 1. The step failed.When you schedule a dts task as a job, the permissions used for this job varies. Who is the owner of the job, what login is being used for the sql server agent service and what is the step doing when it fails ?|||Originally posted by rnealejr
When you schedule a dts task as a job, the permissions used for this job varies. Who is the owner of the job, what login is being used for the sql server agent service and what is the step doing when it fails ?

Hi,

Thanks for ur answer. The owner of my job is an windows authenticated user how has administrator permissions over the server, but user used to run sqlserver agent is default user: SqlServices. However in the properties, i've configured the connections like windows authentication ( i supuse that it's using my administrative account to run jobs).

In a previous post, i found that i have to doing bigger the login time-out for SQL Agent. I did it and i rebuild the package into one new with a connection with clear specifications to the server. i mean that before i've reference to [local] server and after i changed it to [NAMESERVER] SQL on my network. I scheduled this package and it works.

is it a bug of SQl Server? why Agent SQl works with a form and not with another?

Thanks,
Maritzita

dts parameters, more values in one parameter

hy all,

i'm using the object transform data task from a server (where i'm not dbo) to another server (where i'm dbo).

i'm getting data from a table. i need to select just some records from this table. i need to use a query like this one below...

select * from {table_name} where operationedate in ('20070101', '20070205', '20060524')

... cause everytime i run the dts the operationdate field must be filtered on different date.

so i tried to use the parameter from a global variables. i've tried lots of things but everytime i failed.

i used to try:

select * from {table_name} where operationedate in (?)

but it doesnt work!

any one can understand what i try to explain and even help me?

bye

nicola

Have you tried using a SSIS variable with an expression to build query dinamically?

There are a lot of post about it. You would need the set the EvaluateAsExpression porperty of the variable as TRUE and then build the expression. The expression will use another variable that has the coma delimited list of values with the dates you care about.

|||

Build two variables: varInClause (string) & varSQLStatement (string)

For the value of varInClause, you can type your dates including the quotes and commas: '20070101','20070205',...

Set EvaluateAsExpresstion to true on the varSQLStatement variable. In the expressions box, type this as your expression:

"select * from table_name where operationdate in (" + @.{User::varInClause} + ")"

Then, use the variable, varSQLStatement, in your OLE DB source.

|||

thank you very much for answered so soon

but maybe i'm still in trouble

i forgot you to say i'm working in SQL Server 2000

i think that is possible to use SSIS only in SQL Server 2005

i'm right or not?

bye

nicola

|||Oh, well, that is a very important piece of information because this is not a DTS forum. If you're working with DTS, you need to post that question on the DTS newsgroup.

http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg|||

thank you very much

i'm sorry for time you loose with my problem

bye

nicola

dts parameter

I am running the DTS as follows,

SET @.CMD = 'dtsrun /S '+@.server+' /U '+@.user+' /P '+@.pass+' /N '+@.dtsn+' /A MyBinaryID:19 = '+@.MyBinaryID

EXECUTE master..xp_cmdshell @.CMD

This does not return all the data correctly.MyBinaryIDis a Binary(8) field, any idea how should I pass MyBinaryID and define what type of parameter in the DTS?

In the DTS package, set the Global Variable MyBinary to Integer type, and map it to the parameter used in your query. For example I use such query in a Execute SQL Task in DTS:

UPDATE tbl_DTS
SET name='xxx'
WHERE CONVERT(BIGINT,MyBinaryID)= ?

And I map the MyBinary Global Variable to Parameter1. Declare @.MyBinaryID as BIGINT in T-SQL and assign a proper value to it (of cource the value of @.MyBinary need to be some value converted from the BINARY(8) data). Then set the @.CMD as following:

SET @.CMD = 'dtsrun /S'+@.server+' /U'+@.user+' /P'+@.pass+' /N'+@.dtsn+' /A MyBinaryID= '
+CONVERT(VARCHAR,@.MyBinaryID)

EXEC master..xp_cmdshell @.CMD

DTS package with parameters

Hi,
I want to create DTS package which can accept a parameter and export / import data which match the parameter value. is it possible? if yes , can you please tell me the steps.
thanks in advance.
Satya - do not refer me to SQLDTS.com , not much help there .
RamI am not clear on what you are trying to do...

What are the source and destination connections in both the import and export scenario.

Are you trying to conditionally import or export data based on some Parameter? How are you executing the package?

DTS packages can take input parameters and return paramters via Package Variables (inner and outer). You can use an Execute package task to pass parameters to a nested DTS package which contains your logic for import or export. Depending on the complexity of your process you could create one master package (to manage the process) and two "child" packages: one for Import and one for export.

See books online for more info on the Execute Package Task...

CPNsqlsql

DTS Package Date Parameter

Hi

I'm using a DTS package to move data from one database to another, as you do.
The Transform Data task between the two connections uses a SQL statement which includes a hardcoded date variable in the criteria.
" where entrydate = '31 Oct 2003' "

Is there anyway to pass a parameter or variable to the dts package, like you can to a stored procedure?

I thought of having another statement to get a datevalue from another table, but DTS doesn't seem to like multiple sql statements in the Transform Data Tasks.
My last resort option is to use the getdate() function, but this would reduce my flexibility too much.

Thanks
Danyou can use global variable in the DTS package. One step in the package can save date in this variable and then the transform data task can read that.

activex syntax to read the value:
var_value = DTSGlobalVariables("DTS_var").value|||Theres a couple of steps for this I don't understand.
Should the tasks in the process be as follows?:
1: ActiveX script task to
get the date value from a table using sql, and saves it as a globalvariable 'Var1'?
2: Transform Data task which uses global variable 'Var1' in the sql statement?

If so:
How do you use sql in the ActiveX task?
And how do you reference the global variable in the Transform data sql statement?|||In the transformation use activex script and run the sql in activex and assign the result of the sql to the DTSDestination("Col1").|||Originally posted by vmlal
In the transformation use activex script and run the sql in activex and assign the result of the sql to the DTSDestination("Col1").

run sql in activex:

Dim oSQLServer
Dim strSQLServer
Dim Query
Dim Messages

Set oSQLServer = CreateObject("SQLDMO.SQLServer")
strSQLServer = "ServerName"
' use integrated sec and connect
oSQLServer.LoginSecure = TRUE
oSQLServer.Connect strSQLServer

strQuery = "select col1,col2 from blah"
'Run the query
set Query = oSQLServer.ExecuteWithResults(strQuery)
Messages = Query.GetColumnString(1,1) & " " & Query.GetColumnString(1,2)|||I've got a lot to learn about ActiveX for DTS, thanks for pointing me into the right direction.

Thanks for your help rohitkumar and vmlal!