Dts Run Problem

Hi all,

I can run my dts in a server which is on our network. And i send global variables and dts runs correctly.

But in another dts; the same method does not work. But i don't get any exception. I use Dts class of Framework (Interop.Dts.dll).

My ExecuteDts Function is :

public static void ExecuteDTS(string PackageName,params object[] DtsVariableValues) {string ServerName = ConnectionNames.OlapServerName;string ServerUserName = ConnectionNames.OlapUserName;string ServerPassword = ConnectionNames.OlapPassword;object pVarPersistStgOfHost =null; DTS.PackageClass DtsPackage =new DTS.PackageClass(); DtsPackage.LoadFromSQLServer(ServerName, ServerUserName, ServerPassword, DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default ,null,null,null, PackageName,ref pVarPersistStgOfHost);try {string[] DtsVariableNames =new String[DtsPackage.GlobalVariables.Count];int VariableIndex = 0; DtsPackage.UseTransaction =true;foreach (GlobalVariable globalin DtsPackage.GlobalVariables) { DtsVariableNames[VariableIndex] = global.Name; DtsPackage.GlobalVariables.Remove(global.Name); VariableIndex++; } VariableIndex = 0;foreach (String DtsVariableNamein DtsVariableNames) { DtsPackage.GlobalVariables.AddGlobalVariable(DtsVariableName, DtsVariableValues.GetValue(VariableIndex)); VariableIndex++; } DtsPackage.Execute(); }catch (Exception ex) { //Console.WriteLine(ex.Message); }finally { DtsPackage.UnInitialize(); DtsPackage =null; } }

Any idea?

Regards..

sourvil:

Hi all,

I can run my dts in a server which is on our network. And i send global variables and dts runs correctly.

But in another dts; the same method does not work. But i don't get any exception. I use Dts class of Framework (Interop.Dts.dll).

Can you give us more details about the two machines you are using?|||

My 2 dts are on the same machine, on the same sql server.

Hmm, i think also it would be permission issue but i could not solve it yet?

|||

I think if you give the DTC creater local admin previleges will solve the issue.

Good luck.

|||

I get the Step Error Code:80004005. Error opening datafile (it is a text source in my app) : Logon Failure: unknown user name or bad password. I think 'sa' login (for sql server 2000) does not have enough rights to edit that text file.

I could not solve it yet?

Regards..

|||

sourvil:

I get the Step Error Code:80004005. Error opening datafile (it is a text source in my app) : Logon Failure: unknown user name or bad password. I think 'sa' login (for sql server 2000) does not have enough rights to edit that text file.

I could not solve it yet?

Regards..

I think you typed the password incorrectly or Did you forget the sa password? (Try to login on the SQL Server, will you will be able to login successfully?).

Good luck..

|||

Thanks for your qucik reply. But other dts packages work correctly with this username/password. And also the first 2 steps (Activex Code and ExecuteSql Step) works fine. But in the third step, i read some data from Text File Source. And there is an accessable error in here. My SqlAgent's Username is a LocalAdmin in that shared folder's computer.

...

Step Error Description:Error opening datafile: Logon failure: unknown user name or bad password.

Step Error Code: 80004005

...

Any idea?

Regards..

|||

sourvil:

Thanks for your qucik reply. But other dts packages work correctly with this username/password. And also the first 2 steps (Activex Code and ExecuteSql Step) works fine. But in the third step, i read some data from Text File Source. And there is an accessable error in here. My SqlAgent's Username is a LocalAdmin in that shared folder's computer.

...

Step Error Description:Error opening datafile: Logon failure: unknown user name or bad password.

Step Error Code: 80004005

...

Any idea?

Regards..

Check out these links:
http://www.computerperformance.co.uk/Logon/code/code_80004005.htm
http://www.lazydba.com/sql/1__7259.html
http://tutorials.aspfaq.com/8000xxxxx-errors/80004005-errors.html

Good luck.

|||

Thanks for reply but my problm isn't solved yet. I read those articels. But my dts works fine from Designer, but it does not work from asp.net page.

I know that when i want to run dts from asp.net, SqlAgent's Domain Username runs instead of my current using login name. But dts cannot open the shared text file when i call it from asp.net page, and dts fails in that step.

Any other idea?

Regards..

|||

sourvil:

Thanks for reply but my problm isn't solved yet. I read those articels. But my dts works fine from Designer, but it does not work from asp.net page.

I know that when i want to run dts from asp.net, SqlAgent's Domain Username runs instead of my current using login name. But dts cannot open the shared text file when i call it from asp.net page, and dts fails in that step.

Any other idea?

Regards..

I guess it related to a permission on the folder where the filer is reside.
Make sure ASPNET user has the needed permissions on that folder (maybe this is the why its fail not sure).

Good luck.

|||

Ok, i will not solve it :(

But i used "master.xp_cmdshellDTSRUN" method and it worked. In fact, i do not want to use this method because of writing all Global Variable Names statically. But this solved my problem.

Thank you very muchCS4Ever,have a nice day..

Best regards.

|||

sourvil:

Ok, i will not solve it :(

But i used "master.xp_cmdshellDTSRUN" method and it worked. In fact, i do not want to use this method because of writing all Global Variable Names statically. But this solved my problem.

Thank you very muchCS4Ever,have a nice day..

Best regards.

Your are welcomesourvil.

Suggestion (will not solve the problem but will help you in the approach you decided to go with):

Have two DTS, one is the real one (has all the logic and transdormations) and the other have a task to run the first one.

This will help you a lot I guess,

Good luck.

DTS question

Is there a way to use Global variables in the 'Execute SQL task' in a DTS package? if no is there a way to?Yes, depends on what version of SQL Server you have. In SQL Server 2000, you can use question marks as place holders for parameters, then assign the global variables to the question marks using the parameters button in the execute sql screen.

If you are using sql7 then I found this to work. Add an active x script to the package that creates and sets the sql command of the execute sql task. when you create the sql command in the vb script, use the variables you want. Here is the syntax of the active x script.

Steve

'*********************************
' Visual Basic ActiveX Script
'*********************************

Function Main()

Dim oPkg, oExecSQL, sSQLStatement

sSQLStatement = "EXEC stpr_createtable " & DTSGlobalVariables("tableName").Value

Set oPkg = DTSGlobalVariables.Parent
Set oExecSQL = oPkg.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomTask

oExecSQL.SQLStatement = sSQLStatement

Set oExecSQL = Nothing
Set oPkg = Nothing

Main = DTSTaskExecResult_Success
End Function

DTS question

How do I use a global rowset variable in a SQL statement where a value should be IN ( ? )
where ? is mapped to the global rowset variableI have two different data sources.

I need to pull from source (1) to Souce (2).

The data pulled from souce(1) needs to be based on values in a table in source(2).

Select fields
from source(1)
where field1 in ( Table in source 2)

Is this possible with DTS?|||try this link
[BOL] Using Global Variables with DTS Packages
and

[BOL] Execute SQL Task look lower into the doc for "Populating a Single Global Variable with an Entire Rowset"|||I have the global variable (rowset) populated, but how do I use it in my where condition?

where field in ( ? ) I get an error when I map the parm to the global rowset variable.

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 Package, global variable, test vs live?

Are global variable parameters in a DTS package specific to "only" that one package and do not effect other packages?

I need to add a variable to a test package but am worried that if I add this parameter it might affect the live production Table. There are two different servers on different sides of the firewall, there are two different databases and there are two tables but with different names, and there are DTS packages but they reference different tables in the query code.

So is it ok to add the global variable which would allow this DTS to delete recent records from the "test" version and it wont do anything to my live version?By the way I did check Microsoft's website for more details on DTS and variables. It looks as thought they're saying it is specific to only that dts, table and database. But it would be nice to confirm this with someone out there more experienced.

http://msdn2.microsoft.com/en-us/library/aa933470(SQL.80).aspxsqlsql

DTS Package Fails after 1000 Rows......

I am having a DTS package which copies data from Foxpro DBF to Sql Server. This DTS package is called in a VB program and Global Variables for DTS is set inside the VB program based on which DTS package copies the Data.

The problem which i am facing is, after 1000 rows transferred using DTS package, DTS package fails and starts giving Errors.

Inside the VB program i have ensured that the DTS package is released properly.

Please help me out, need a solution to this very badly......

Thanks

Pranjal Sharma

This may be an extreme but may be relative. Blank records or special characters being filtered through VB can cause a halt in the transfer.

If it errors after exactly 1000 records every time, I'd look at the content of that record specifically. If it errors after approximately 1000 records, it maybe timing out.

Adamus

|||Posting the error message will help...

DTS Package Fails after 1000 Rows......

I am having a DTS package which copies data from Foxpro DBF to Sql Server. This DTS package is called in a VB program and Global Variables for DTS is set inside the VB program based on which DTS package copies the Data.

The problem which i am facing is, after 1000 rows transferred using DTS package, DTS package fails and starts giving Errors.

Inside the VB program i have ensured that the DTS package is released properly.

Please help me out, need a solution to this very badly......

Thanks

Pranjal Sharma

This may be an extreme but may be relative. Blank records or special characters being filtered through VB can cause a halt in the transfer.

If it errors after exactly 1000 records every time, I'd look at the content of that record specifically. If it errors after approximately 1000 records, it maybe timing out.

Adamus

|||Posting the error message will help...

DTS mapping global variable to column

Hi
I have doubt in DTS -i want 2 export text file into Database - which i can able to do it while running DTS it will prompt inputbox to enter the datetime of transferring which i should update in the datetime column of the table,which i will store this user input value in globalvariables- so how should i map to column.
i placed text object (source) and connection object and active x script object

thanks in advance
HrMay search SQL DTS (http://www.sqldts.com) website.

DTS Looping

Hi to everyone!
It's my first mission in DTS.
I fill global variables with e-mail addresses (maybe with empty string)
of my accounts.
If I have an e-mail(not empty), I need to create an excel file with
data to this account and send messages to him.
I can do it to some alone account, but I have a problem with looping
I saw an example from http://www.sqldts.com/Default.aspx?246
it helped me very much, but when I run it in the loop it dos not
working!
I think a problem in :
stpEnterLoop.DisableStep = False
stpFinished.DisableStep = True
stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting
or I do something wrong.
I have 5 ActiveX task. First - fill global variables.
Second starting a loop. Maybe the problem is that i call a second
ActiveX task from itselfe?
Help me please.
Thanks a lot!It's hard to say something without seeing the code (with dts always is the
same) but first of all I'd put a log for that DTS and I'll try to see what
happen.
--
Current location: Alicante (ES)
"Dima" wrote:

> Hi to everyone!
> It's my first mission in DTS.
> I fill global variables with e-mail addresses (maybe with empty string)
> of my accounts.
> If I have an e-mail(not empty), I need to create an excel file with
> data to this account and send messages to him.
> I can do it to some alone account, but I have a problem with looping
> I saw an example from http://www.sqldts.com/Default.aspx?246
> it helped me very much, but when I run it in the loop it dos not
> working!
> I think a problem in :
> stpEnterLoop.DisableStep = False
> stpFinished.DisableStep = True
> stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting
> or I do something wrong.
> I have 5 ActiveX task. First - fill global variables.
> Second starting a loop. Maybe the problem is that i call a second
> ActiveX task from itselfe?
> Help me please.
> Thanks a lot!
>

DTS Global Variables Behaviour with > 1 User

Hi all

I have a DTS package that users of the database can run which basically acts like a 'live update' (as the database is based on a values produced from another system) and it takes roughly 30 or so seconds to run...

The dts package is not going to have a particularly large hit rate but i am interested in knowing what will happen if a user (user 1) attempts to run the package when it is in already in use by another user (user 2) ?

The dts package is from a sp using the dtsrun utility (passes in global variables).

I know that a second DTS will start before the first has finished but what i am not sure about is what will happen to the Global Variables when the second DTS package starts - i.e. will starting a second instance of the package with different variables have an effect on the first DTS's global variables while it is running?

I hope the answer is "No Tom, it work fine!"DTS is a weird beast to describe, because there are so many different contexts. The short answer is that each of the instances of a DTS package will have their own global variables.

-PatP|||Pat

Thanks for the help. I really appreciate it.

DTS Global Variables

Hello,

In the properties for a DTS package, I have a string variable. I need to execute an update statement based on this variable. I was going to use Execute SQL, but I didn't know now to get a value from the variables section.

I store the value there because I will be using dtsrun or the DTS library to execute the package, which I can change this value upon execution. How do I execute an update based on the value?

BrianAre you saying that you need to use this string variable in your WHERE clause? If so, you should be able to use a question mark (?) where you need to use that string value, then use the Parameters button to tell DTS which global variable to use for the parameter.

Terri|||Hey,

That was correct. THanks.

Brian|||Cool! I have found DTS to be one of the most powerful yet frustrating tools to use. Could they make it any more obtuse? Took me 2 years to realize there was such thing as global variables, which then opened up a whole new world.

Anyway, Marcin Policht has written a nice series on SQL Server 2000 DTS. There are 12 parts to it, and part 5 is:SQL Server 2000 DTS Part 5 - DTS Designer Tasks and Global Variables. Definitely worth bookmarking.

Terri

DTS Global Variable Variant Type Mismatch

I am trying to declare a global variable in a DTS package for passing the recordset to the next stage Active Script . After declaration of the Global Variable and selecting datatype of the variable as OTHER ( Variant ) , when I try to save the DTS Package changes , it throws a Type Mismatch Error .

:confused: Please help me out .

Thanx
Arnie .You have to execute the package or otherwise cause it to set the type of the variable to what it's going to use (for recordsets it is 'dispatch' but you can't set it manually). Once the type changes, you can save it.

DTS global variable question

Hello,
I am having difficulty with global variables in DTS. Specifically
I would like to instantiate a global variable with a value obtained
from a SQL database. I have created the global variable through the
package properties section. Now i need to instantiate it with a value
from the database. Should i use an ActiveX Script Task to accomplish
this? If so can someone send me a link that shows the best way of
doing this?

Thanks,
BillyAn ActiveX script task will do the job but you could also use a
DynamicProperties task if you are using SQL 2000. This enables you to set
properties at run time without writing code.

See http://www.sqldts.com/default.aspx?205 for an ActiveX script for an
example as sell as other useful DTS info.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Billy Cormic" <billy_cormic@.hotmail.com> wrote in message
news:dd2f7565.0409261325.4dda82fd@.posting.google.c om...
> Hello,
> I am having difficulty with global variables in DTS. Specifically
> I would like to instantiate a global variable with a value obtained
> from a SQL database. I have created the global variable through the
> package properties section. Now i need to instantiate it with a value
> from the database. Should i use an ActiveX Script Task to accomplish
> this? If so can someone send me a link that shows the best way of
> doing this?
> Thanks,
> Billy

DTS global variable problems...

Hey all,

I've recently been attempting a transform data task with a custom query for the source. Using the query, i've attempted to use global params, but it only ever seems to work if there is only one item in the global var. If I return an entire resultset, I get a "EXCEPTION_ACCESS_VIOLATION" instead. I'm trying to use it like "SELECT * FROM whatever WHERE ID IN(?)"

I've pondered this problem for quite some time now and I am wondering if there is a workaround for it. I know it would take much too long to do the same thing in activeX with a transform, so I would rather do it this way if I could.

Thanks in advance,
-KilkaHmmm, I'm not sure why that shouldn't work.... how are you setting your global variable??

An alternative way would be to use an ActiveX task to set the query value (eg. instead of setting a global variable set the query for the transform data task.)|||I've created them when I specify the IN (?) parameter under the parameters button. I then fill them with some ID's from several xls spreadsheets. I know that this procedure is working correctly because I wrote some activeX to spit out the contents of the var, and the size of the var. Everything there is right on the money. Also, when I specified the global variables, I set them as type <empty>, which is what I think im supposed to do according to msdn. I'm thinking that maybe this has something to do with the IN and it's param, as I'm doing this for 1500 items or so.

Thanks rokslide,
-Kilka|||errghhh, you might be running into a length problem... I suspect that there is a maximum length on the sql query for the transform data task...

Could you put the id's into a temp table and then do a join or something?

Can you test it without using so may items? perhaps only 10 or something...
that way you can try and eliminate possible problems...|||No, I've tried that already too. I've reduced the resultset so it only has two elements and I still get the same problem. I've verified that there were only two elements with the activeX script.

-Kilka|||Also,

Just thought that I should mention my global variables both appear as type "Dispatch" under the package properties. I was also thinking that perhaps I should try using another statement other than IN(?) in my data transformation. Is there anything like IN that would give me the same results ?|||apart from using multiple or statements, no, not really unless you create a temp table and join on it...

if you don't try and set the sql using a global variable what happens. does it still fail or does it work, I'm beginning to wonder if the problem isn't somewhere else...|||Ok,

So i've done some more playing around with this, still to no avail. Deleting the global var should and does give me "Global Variable 'chid2' not found'". If I create the variable but don't set it, I get "Invalid character value for cast specification". Finally if I set the var, even if the resultset has just one element, I still get the EXCEPTION_ACCESS_VIOLATION.

This is the error from the log

Step 'Copy Data from Results to Results Step' failed

Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Provider generated code execution exception: EXCEPTION_ACCESS_VIOLATION
Step Error code: 80040005
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:700

I've looked up this error and made sure that the Transform Data Task is executing on the main thread. I think I've done this right. When I put the param in this task, and it's already been filled, I get "No value given for one or more parameters" when attempting to do a preview.

Also, i've noticed that specifying one param, such as id=? in my source query works fine, it's just a rowset that does not work.|||also, it's just come to my attention that I get the same error when running test under the Tranformations tab of the Transform Data Task properties. The type of tranformation is a copy column.

Thanks in advance,
-Kilka|||So it may not be the querying at all but sme problem with the transform itself. Perhaps columns of the wrong data types or something affecting things?

If you completely replace the whole global variable bit and replace it (temporarily) with a static value and then try and execute it what happens?|||I've already tried that with an IN(1,2,3,4,5,5) in my source query. That seems to have worked fine.

I'd rather be doing as little processing in activeX as possible, but it's starting to look like I'll have to. I can just the global vars fine with activeX, but the problem is going to be performance. If anyone can think of anything else, I would be glad to hear it.

I was also thinking about constructing my string in a activeX object before this transformation, but I'm not sure if I'm allowed to use exec in a source like that because DTS won't know what columns I'm selecting much less moving.

Come to think of it, do you think the problem might be that there are no "," between elements of the global var. I think I'm going to try that next.

Thanks again rokslide,
-Kilka|||here's a thought that may or may not work....

what happens if you add an execute sql stask to your dts package and get it to try and execute the sql statement that is in your data transformation task?

does it still have a problem?? if not you could slip the execute sql task n before your transformation and get it to populate a temp table, then you can have your transformation execute a query that joins to the temp table to determine it's records...

just a thought...|||Yeah, it turns out I'm not in a position to do that. I can't create a temp table. I think my solution will be to cycle through the records using an activeX transformation and only copy the ones I need. I know this is a slow and stupid way to do it...single threaded too :( However, thanks for the help.

Cheers,
-kilka|||So now, I've taken to using a string to use my IN clause. however, there is some wierdness.

Assuming 445 is legit:
If I have 0,0,0,0,445 in the IN clause, I get the correct resultset
If I have 445,0, I get nothing
If I have 0,1,0,0,445 in the IN clause, I get nothing.
If I have 445 in the IN clause, it works fine..

Can anyone shed any light on this problem ?

-Kilka|||Can you post the entire sql statement??|||So I figured it out, got things to work dynamically.

So upon further investigation i've discovered that every data transformation object has a datapump task associated with it. The datapump task can have the source sql statement altered under the disconnected edit tool. I also found out that this variable can be edited using the following VB code:

' 205 (Change SourceSQLStatement)
Option Explicit

Function Main()
Dim oPkg, oDataPump, sSQLStatement

' Build new SQL Statement

sSQLStatement = "select * from <table> where ID IN(" & DTSGlobalVariables("instring2").value & ") OR ID IN (" & DTSGlobalVariables("instring3").value & ")"

' Get reference to the DataPump Task
Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask

' Assign SQL Statement to Source of DataPump
oDataPump.SourceSQLStatement = sSQLStatement

' Clean Up
Set oDataPump = Nothing
Set oPkg = Nothing

Main = DTSTaskExecResult_Success
End Function

I would run this after I built the instring global variables. I then run the activeX task and finally the transformation task. I'm going to post a big howto at some point when I get time as well.

At present however, I'm now stuck with the fact that I can't go:
DTSDestination("CellPhone") = Left(DTSSource("CellPhone"),Len(DTSSource("CellPhone"))-4)

in the transformation. When I hit parse, it works, however when I hit test/execute the package, I get "Invalid procedure call or argument:'Left'" I know for a fact is the second argument that's causing the problem, but can't figure out why this wouldn't be allowed.

Thanks,
-Kilka|||Hi there,

Actually that was kinda what I was meaning, or something similar,... with your lastest problem you need to check for cellphone values that are less then 4 long. if a value is less then 4 (and possibly 4 as well) your call will fail.

Cheers,
Roko