I am just starting to learn DTS.
If I set up in a DTS package a series of 5 independant processes, each of
which Deletes rows out of it's respective table and then loads their
respective flat file into those tables.... Will all of these run
concurrently? I have them all tied to the same connection object, but that
should not prevent them from running concurrently...wil it?Jim,
Yes it will. if they use the same connection, it will prevent them from
running concurrently.
You can notice it in DTS Designer, when moving huge amount of data between
source and target using the same connection. The transformation steps
(datapumps) will run in sequence no matter if they were setup to run
concurrently.
AMB
"Jim Heavey" wrote:
> I am just starting to learn DTS.
> If I set up in a DTS package a series of 5 independant processes, each of
> which Deletes rows out of it's respective table and then loads their
> respective flat file into those tables.... Will all of these run
> concurrently? I have them all tied to the same connection object, but that
> should not prevent them from running concurrently...wil it?
>|||http://www.codeproject.com/useritems/DTS__VBNET_.asp
Showing posts with label package. Show all posts
Showing posts with label package. Show all posts
Thursday, March 29, 2012
DTS Simple Question
I am just starting to learn DTS.
If I set up in a DTS package a series of 5 independant processes, each of
which Deletes rows out of it's respective table and then loads their
respective flat file into those tables.... Will all of these run
concurrently? I have them all tied to the same connection object, but that
should not prevent them from running concurrently...wil it?
Jim,
Yes it will. if they use the same connection, it will prevent them from
running concurrently.
You can notice it in DTS Designer, when moving huge amount of data between
source and target using the same connection. The transformation steps
(datapumps) will run in sequence no matter if they were setup to run
concurrently.
AMB
"Jim Heavey" wrote:
> I am just starting to learn DTS.
> If I set up in a DTS package a series of 5 independant processes, each of
> which Deletes rows out of it's respective table and then loads their
> respective flat file into those tables.... Will all of these run
> concurrently? I have them all tied to the same connection object, but that
> should not prevent them from running concurrently...wil it?
>
If I set up in a DTS package a series of 5 independant processes, each of
which Deletes rows out of it's respective table and then loads their
respective flat file into those tables.... Will all of these run
concurrently? I have them all tied to the same connection object, but that
should not prevent them from running concurrently...wil it?
Jim,
Yes it will. if they use the same connection, it will prevent them from
running concurrently.
You can notice it in DTS Designer, when moving huge amount of data between
source and target using the same connection. The transformation steps
(datapumps) will run in sequence no matter if they were setup to run
concurrently.
AMB
"Jim Heavey" wrote:
> I am just starting to learn DTS.
> If I set up in a DTS package a series of 5 independant processes, each of
> which Deletes rows out of it's respective table and then loads their
> respective flat file into those tables.... Will all of these run
> concurrently? I have them all tied to the same connection object, but that
> should not prevent them from running concurrently...wil it?
>
DTS Script trying to re-execute another task in package
I'm new to the whole DTS scene and not impressed with the info I'm finding. Here's what I'm trying to do:
I have two recordsets generated by ExecteSQLTasks (created as output global variables). While I'm looping through one recordset in a script I would like to do data-driven updates of the second recordset based on data in the first. This seemed easy at first. I thought I could just do a little hoochie coochie:
Dim objPackage
Dim objTask
Dim objCustomTask
Set objPackage = DTSGlobalVariables.Parent
For each objTask in objPackage.Tasks
If objTask.CustomTask.Description = "The description I gave it!" Then
objTask.Properties("SQLStatement").Value = "My new SQL statement"
objTask.Execute
End If
And voila! I'd have my new recordset established as a new value for the global variable. But no! It doesn't seem to work that way. What's up with this?
FYI, I also tried to apply filters to the original recordset using:
rs.Filter = " table.field = " & varMyDataNotAString
But that didn't work either. Grrrr!
Anybody out there doing things like this and getting them to work?
Thanks!Check SQLDTS (http://www.sqldts.com) website and search for GLOBAL VARIABLES which will show code examples to use so.
HTH|||Actually, I'm pretty sure this is where I originally got the code in my initial post. The issue is that using this code did not refresh my global variable (and hence my recordset). I could change the SQL statement, but either the Task doesn't actually execute with the
objTask.CustomTask.Execute
or somehow the global variable isn't refreshed.
I plan to continue playing with it today.
Thanks for your reply!
I have two recordsets generated by ExecteSQLTasks (created as output global variables). While I'm looping through one recordset in a script I would like to do data-driven updates of the second recordset based on data in the first. This seemed easy at first. I thought I could just do a little hoochie coochie:
Dim objPackage
Dim objTask
Dim objCustomTask
Set objPackage = DTSGlobalVariables.Parent
For each objTask in objPackage.Tasks
If objTask.CustomTask.Description = "The description I gave it!" Then
objTask.Properties("SQLStatement").Value = "My new SQL statement"
objTask.Execute
End If
And voila! I'd have my new recordset established as a new value for the global variable. But no! It doesn't seem to work that way. What's up with this?
FYI, I also tried to apply filters to the original recordset using:
rs.Filter = " table.field = " & varMyDataNotAString
But that didn't work either. Grrrr!
Anybody out there doing things like this and getting them to work?
Thanks!Check SQLDTS (http://www.sqldts.com) website and search for GLOBAL VARIABLES which will show code examples to use so.
HTH|||Actually, I'm pretty sure this is where I originally got the code in my initial post. The issue is that using this code did not refresh my global variable (and hence my recordset). I could change the SQL statement, but either the Task doesn't actually execute with the
objTask.CustomTask.Execute
or somehow the global variable isn't refreshed.
I plan to continue playing with it today.
Thanks for your reply!
DTS schedult problem
I created a DTS package to transfer a table to a text file. Every time when
I click start manually, it'll run and transfer the data to a local txt file
with no problem. But when I schedule this DTS package and run it from the
SQL Agent - Jobs. It always fail to run. I don't know why. Anybody can help
me?|
| I created a DTS package to transfer a table to a text file. Every time
when
| I click start manually, it'll run and transfer the data to a local txt
file
| with no problem. But when I schedule this DTS package and run it from the
| SQL Agent - Jobs. It always fail to run. I don't know why. Anybody can
help
| me?
--
We need more information to help you. What errors are returned when the job
fails? Post it and we'll give it a shot.
Thanks,
--
Eric Cardenas
SQL Server support|||JL
I think that you cerated DTS package on the workstation not on the server ,
then SQL Agent on your wokstation is running under differnent account than
server.
Check it out.
"JL" <ljmagzine@.hotmail.com> wrote in message
news:eGGt1AgvDHA.2304@.TK2MSFTNGP12.phx.gbl...
> I created a DTS package to transfer a table to a text file. Every time
when
> I click start manually, it'll run and transfer the data to a local txt
file
> with no problem. But when I schedule this DTS package and run it from the
> SQL Agent - Jobs. It always fail to run. I don't know why. Anybody can
help
> me?
>|||VXJpIGlzIGNvcnJlY3QuICBJIHJhbiBpbnRvIHRoaXMgcHJvYmxlbSBhbmQgaXQgaGFkIG1lIHN0
dW1wZWQgZm9yIGEgd2hpbGUuDQoNClRoZSBzb2x1dGlvbiBpcyB0byBjcmVhdGUgdGhlIERUUyBw
YWNrYWdlIGFuZCBKb2Igd2hpbGUgcGh5c2ljYWxseSBhdCB0aGUgU1FMIFNlcnZlciBib3guDQoN
Cg0KIkpMIiA8bGptYWd6aW5lQGhvdG1haWwuY29tPiB3cm90ZSBpbiBtZXNzYWdlIG5ld3M6ZUdH
dDFBZ3ZESEEuMjMwNEBUSzJNU0ZUTkdQMTIucGh4LmdibC4uLg0KPiBJIGNyZWF0ZWQgYSBEVFMg
cGFja2FnZSB0byB0cmFuc2ZlciBhIHRhYmxlIHRvIGEgdGV4dCBmaWxlLiBFdmVyeSB0aW1lIHdo
ZW4NCj4gSSBjbGljayBzdGFydCBtYW51YWxseSwgaXQnbGwgcnVuIGFuZCB0cmFuc2ZlciB0aGUg
ZGF0YSB0byBhIGxvY2FsIHR4dCBmaWxlDQo+IHdpdGggbm8gcHJvYmxlbS4gQnV0IHdoZW4gSSBz
Y2hlZHVsZSB0aGlzIERUUyBwYWNrYWdlIGFuZCBydW4gaXQgZnJvbSB0aGUNCj4gU1FMIEFnZW50
IC0gSm9icy4gSXQgYWx3YXlzIGZhaWwgdG8gcnVuLiBJIGRvbid0IGtub3cgd2h5LiBBbnlib2R5
IGNhbiBoZWxwDQo+IG1lPw0KPiANCj4gsqlsql
I click start manually, it'll run and transfer the data to a local txt file
with no problem. But when I schedule this DTS package and run it from the
SQL Agent - Jobs. It always fail to run. I don't know why. Anybody can help
me?|
| I created a DTS package to transfer a table to a text file. Every time
when
| I click start manually, it'll run and transfer the data to a local txt
file
| with no problem. But when I schedule this DTS package and run it from the
| SQL Agent - Jobs. It always fail to run. I don't know why. Anybody can
help
| me?
--
We need more information to help you. What errors are returned when the job
fails? Post it and we'll give it a shot.
Thanks,
--
Eric Cardenas
SQL Server support|||JL
I think that you cerated DTS package on the workstation not on the server ,
then SQL Agent on your wokstation is running under differnent account than
server.
Check it out.
"JL" <ljmagzine@.hotmail.com> wrote in message
news:eGGt1AgvDHA.2304@.TK2MSFTNGP12.phx.gbl...
> I created a DTS package to transfer a table to a text file. Every time
when
> I click start manually, it'll run and transfer the data to a local txt
file
> with no problem. But when I schedule this DTS package and run it from the
> SQL Agent - Jobs. It always fail to run. I don't know why. Anybody can
help
> me?
>|||VXJpIGlzIGNvcnJlY3QuICBJIHJhbiBpbnRvIHRoaXMgcHJvYmxlbSBhbmQgaXQgaGFkIG1lIHN0
dW1wZWQgZm9yIGEgd2hpbGUuDQoNClRoZSBzb2x1dGlvbiBpcyB0byBjcmVhdGUgdGhlIERUUyBw
YWNrYWdlIGFuZCBKb2Igd2hpbGUgcGh5c2ljYWxseSBhdCB0aGUgU1FMIFNlcnZlciBib3guDQoN
Cg0KIkpMIiA8bGptYWd6aW5lQGhvdG1haWwuY29tPiB3cm90ZSBpbiBtZXNzYWdlIG5ld3M6ZUdH
dDFBZ3ZESEEuMjMwNEBUSzJNU0ZUTkdQMTIucGh4LmdibC4uLg0KPiBJIGNyZWF0ZWQgYSBEVFMg
cGFja2FnZSB0byB0cmFuc2ZlciBhIHRhYmxlIHRvIGEgdGV4dCBmaWxlLiBFdmVyeSB0aW1lIHdo
ZW4NCj4gSSBjbGljayBzdGFydCBtYW51YWxseSwgaXQnbGwgcnVuIGFuZCB0cmFuc2ZlciB0aGUg
ZGF0YSB0byBhIGxvY2FsIHR4dCBmaWxlDQo+IHdpdGggbm8gcHJvYmxlbS4gQnV0IHdoZW4gSSBz
Y2hlZHVsZSB0aGlzIERUUyBwYWNrYWdlIGFuZCBydW4gaXQgZnJvbSB0aGUNCj4gU1FMIEFnZW50
IC0gSm9icy4gSXQgYWx3YXlzIGZhaWwgdG8gcnVuLiBJIGRvbid0IGtub3cgd2h5LiBBbnlib2R5
IGNhbiBoZWxwDQo+IG1lPw0KPiANCj4gsqlsql
DTS Scheduling Issue
I have a DTS package that runs without any problems when I run it manually, however, when I schedule the package at a certain time no output is produced.
I have checked the Agent and it says the job is executing, however, it seems to have hung.
The log tells me DTSStep CreateProcessTask3 started at the correct time but there is nothing after that.
How do I check this? or find out what is causing it to hang?
Thanks!Try running profiler at the time the job start starts executing to see how far the job get's. What user account is the job run under?|||Hey tr1na,
Many thanks for your response it is appreciated. SQL server is new to me so apologies for the lack of knowledge
I have never run profiler before, I have had a look and it doesnt look that simple to run and also, I couldnt see where I could call my DTS package.
Anyway, in the package log file it states DTSStep CreateProcessTask3 started at 07.32 and well thats it! I have read somewhere that this could be a security issue and I should be logged in as SYSADMIN? However, I am currently Remote Desktop to the SERVER, login through NT, the package owner is my USER ID, how can I log into EM as SYSADMIN and try scheduling a DTS package under SYSADMIN, do you think this could be the problem?
I am not sure how to check the LOGIN ID.|||To view which user the job is running as right click the job and select "view job history" then tick "show step details". You should then see
"Executed as user: SYD\blah" etc|||Thanks!
Well, unfortunately, the job does not complete because it hangs so there is no job history, however, when I cancelled to job it tells me that the job was cancelled prior to completion by my "USER ID". So I suspect this is the USER ID being used to start the job?
Perhaps I need someway to remote desktop but login as SYSADMIN?|||when you run the job manually are you logged in as your userid. if so check the job runs under your user id.|||there are differences of environment when u execute a dts by right-clicking and thru jobs. for example if u refer a server name as (local) in connection, during right-clicked execution the database will be selected from the server where EM is installed but during execution thru job it will connect to server from where the job is running. similarly any disk file path/reference to COM component also changes with the execution mode. and this is often the source of problem reported by u...|||OK. The job did not run manually when logged in by me, it does run manually when logged in to the database as "sa"
upalsen:
Thank you for your response, it is appreciated, how can I get round this problem, is it a matter of logging in as SYSADMIN, hopefully it is as something as simple as this?|||there r a few settings under properties context menu (right-click) of SQL Server Agent. by default users other than sysadmin r not allowed to run cmdexec etc command. check the Job-System tab and Connection tab for such user specific issues. also a job can be saved under user other than sa. i am not sure exactly where your job is hanging but u can try the above combinations and check the result.|||Thank you.
OK. Well the check box is checked "Only users with SysAdmin priviledges can run CmdExec etc..."
So I uncheck the box, then it asks me to "Enter the proxy account SQL agent will use to run jobs owned by non-administrators"
Does this mean I need to create a "dummy" account and does it need to be a member of the sysadmin group or have specific priviledges?
Thank you.|||u give the name & password of the local m/c administrator and enter the machine name as domain. if u r having a domain controller in your network u can specify the details of a domain account as well.
I have checked the Agent and it says the job is executing, however, it seems to have hung.
The log tells me DTSStep CreateProcessTask3 started at the correct time but there is nothing after that.
How do I check this? or find out what is causing it to hang?
Thanks!Try running profiler at the time the job start starts executing to see how far the job get's. What user account is the job run under?|||Hey tr1na,
Many thanks for your response it is appreciated. SQL server is new to me so apologies for the lack of knowledge
I have never run profiler before, I have had a look and it doesnt look that simple to run and also, I couldnt see where I could call my DTS package.
Anyway, in the package log file it states DTSStep CreateProcessTask3 started at 07.32 and well thats it! I have read somewhere that this could be a security issue and I should be logged in as SYSADMIN? However, I am currently Remote Desktop to the SERVER, login through NT, the package owner is my USER ID, how can I log into EM as SYSADMIN and try scheduling a DTS package under SYSADMIN, do you think this could be the problem?
I am not sure how to check the LOGIN ID.|||To view which user the job is running as right click the job and select "view job history" then tick "show step details". You should then see
"Executed as user: SYD\blah" etc|||Thanks!
Well, unfortunately, the job does not complete because it hangs so there is no job history, however, when I cancelled to job it tells me that the job was cancelled prior to completion by my "USER ID". So I suspect this is the USER ID being used to start the job?
Perhaps I need someway to remote desktop but login as SYSADMIN?|||when you run the job manually are you logged in as your userid. if so check the job runs under your user id.|||there are differences of environment when u execute a dts by right-clicking and thru jobs. for example if u refer a server name as (local) in connection, during right-clicked execution the database will be selected from the server where EM is installed but during execution thru job it will connect to server from where the job is running. similarly any disk file path/reference to COM component also changes with the execution mode. and this is often the source of problem reported by u...|||OK. The job did not run manually when logged in by me, it does run manually when logged in to the database as "sa"
upalsen:
Thank you for your response, it is appreciated, how can I get round this problem, is it a matter of logging in as SYSADMIN, hopefully it is as something as simple as this?|||there r a few settings under properties context menu (right-click) of SQL Server Agent. by default users other than sysadmin r not allowed to run cmdexec etc command. check the Job-System tab and Connection tab for such user specific issues. also a job can be saved under user other than sa. i am not sure exactly where your job is hanging but u can try the above combinations and check the result.|||Thank you.
OK. Well the check box is checked "Only users with SysAdmin priviledges can run CmdExec etc..."
So I uncheck the box, then it asks me to "Enter the proxy account SQL agent will use to run jobs owned by non-administrators"
Does this mean I need to create a "dummy" account and does it need to be a member of the sysadmin group or have specific priviledges?
Thank you.|||u give the name & password of the local m/c administrator and enter the machine name as domain. if u r having a domain controller in your network u can specify the details of a domain account as well.
dts scheduling
Hi
i am trying to schedule a dts package in sqlserver 2005 but i really didn't
find any options like(schedule)
can anyone tell me how to schedule a dts pakage in sqlserver 2005
ur help is really appreciated
thanks
Message posted via http://www.droptable.com
In step of SQL Server Job select type as
SQL Server Integration Service Package and go on..
Regards
Amish
*** Sent via Developersdex http://www.codecomments.com ***
i am trying to schedule a dts package in sqlserver 2005 but i really didn't
find any options like(schedule)
can anyone tell me how to schedule a dts pakage in sqlserver 2005
ur help is really appreciated
thanks
Message posted via http://www.droptable.com
In step of SQL Server Job select type as
SQL Server Integration Service Package and go on..
Regards
Amish
*** Sent via Developersdex http://www.codecomments.com ***
dts scheduling
Hi
i am trying to schedule a dts package in sqlserver 2005 but i really didn'
t
find any options like(schedule)
can anyone tell me how to schedule a dts pakage in sqlserver 2005
ur help is really appreciated
thanks
Message posted via http://www.droptable.comIn step of SQL Server Job select type as
SQL Server Integration Service Package and go on..
Regards
Amish
*** Sent via Developersdex http://www.codecomments.com ***
i am trying to schedule a dts package in sqlserver 2005 but i really didn'
t
find any options like(schedule)
can anyone tell me how to schedule a dts pakage in sqlserver 2005
ur help is really appreciated
thanks
Message posted via http://www.droptable.comIn step of SQL Server Job select type as
SQL Server Integration Service Package and go on..
Regards
Amish
*** Sent via Developersdex http://www.codecomments.com ***
dts scheduling
Hi
i am trying to schedule a dts package in sqlserver 2005 but i really didn't
find any options like(schedule)
can anyone tell me how to schedule a dts pakage in sqlserver 2005
ur help is really appreciated
thanks
--
Message posted via http://www.sqlmonster.comIn step of SQL Server Job select type as
SQL Server Integration Service Package and go on..
Regards
Amish
*** Sent via Developersdex http://www.developersdex.com ***
i am trying to schedule a dts package in sqlserver 2005 but i really didn't
find any options like(schedule)
can anyone tell me how to schedule a dts pakage in sqlserver 2005
ur help is really appreciated
thanks
--
Message posted via http://www.sqlmonster.comIn step of SQL Server Job select type as
SQL Server Integration Service Package and go on..
Regards
Amish
*** Sent via Developersdex http://www.developersdex.com ***
DTS Scheduled Time
How can I tell when a DTS package is scheduled to run?
If I right click on the name, and select "Schedule Package", it brings up
default data (12:00 am Daily), and I KNOW that's not the scheduled time.
ThanksThat is the mechanism by which you set up a scheduled job via SQL Agent. By alter the parameters of the screen to the schedule you desire and clicking on OK, the GIU will build a scheduled job under the AGent with the same name as the DTS package.|||Now I see it. Thanks
If I right click on the name, and select "Schedule Package", it brings up
default data (12:00 am Daily), and I KNOW that's not the scheduled time.
ThanksThat is the mechanism by which you set up a scheduled job via SQL Agent. By alter the parameters of the screen to the schedule you desire and clicking on OK, the GIU will build a scheduled job under the AGent with the same name as the DTS package.|||Now I see it. Thanks
DTS Scheduled Task Fails - Permission Denied
I searched the forum's threads on this, and while there were many results, none have helped so far.
I am running a DTS package that is an ACtiveX Script Task using VBScript. The script uses CreateObject() to create a FileSystemObject to copy an .MDB before importing the tables into SQL Server. I want to copy it because of Access' notoriety of corrupting, and this much data being pumped out of Access could force me to Compact & Repair. I would rather do that on a copy.
Function Main()
Dim FSO
Set FSO=CreateObject("Scripting.FileSystemObject")
FSO.CopyFile "\\<server>\<Share>\Data.mdb", "\\<server>\C$\DataCopy\Data.mdb"
Main = DTSTaskExecResult_Success
End Function
The DTS Package runs when I execute it from Ent. Manager, of course. It fails if scheduled, or course :(
I have set the Owner of the Scheduled Task to my domain account, which is also in the Adminstrators Group on the physical server with the SQL Server installation (Windows 2003 Server). I also did the unnecessary task of adding my domain account specifically to the destination folder, which is also Shared.
My sqlagent.exe service runs as SYSTEM on the server, so the SQLAgent should have no problem copying a file from one folder on the server to another.
The Scheduled Task fails with the common error:
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1
DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220482 (800403FE)
Error string: Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Permission denied
Error on Line 12
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500
Error Detail Records:
Error: -2147220482 (800403FE); Provider Error: 0 (0)
Error string: Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Permission denied
Error on Line 12
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1
DTSRun: Package execution complete.
I checked this MS KB Article (http://support.microsoft.com/kb/q298725/), but the instructions after opening DCOMcnfg.exe do not follow what is shown in the WMI window on Windows 2003 Server :rolleyes: (i.e. there is no "Default Security" tab to click.)
I am at a loss here. Thanks for your help.I'll bump this once.|||I'm still working on it so not sure if what I suspect is the issue... but have a dts package that download a file, based on SQL statement from as/400 to sql table... runs great when I run it myself within the enterprise manager but when I try to call from a stored procedure via my exe it fails to run.
What I've deduced is that when I run it within the enterprise manager that it is, in essence, running on the client... locally and using my dll's, etc... however, when I run via stored procedure through my exe it is trying to run on the server, herein lies the problem (really strong guess at this point)... the server is not set up to connect to the as/400 and that's where it bombs.
I'm guessing that your sql server is having issues talking to your access database in much the same way that mine is having issues talking to the as/400. I've got the client software installed now and will be testing my theory shortly. Hope this helps you with your issue.
b|||Is the access db on the same server as Sql server?
If so, do non unc paths work? Such as :
FSO.CopyFile "c:\somefolder\Data.mdb", "c\DataCopy\Data.mdb"|||.. I have a package as well.
The thing is that if I put in a msgbox to execute directly before the ".deletefile", the package will run, but if I outcomment It it fails...
I can on the other hand do an ".copyfile" instead...
I only have the problem with the ".deletefile"
I would also appriciate all help i can get on this.
// selander
Function Main()
Dim Eso, E, E1, Ec
Set Eso = CreateObject("Scripting.FileSystemObject")
Set E = Eso.GetFolder("\\CLUST\SYS\IMPORT\ENT\")
Set Ec = E.Files
For Each E1 in Ec
if ucase(E1.name) = ucase(DTSGlobalVariables("strFileName").Value)+".ZIP" then
msgbox "FUBAR"
Eso.DeleteFile "\\CLUST\SYS\IMPORT\ENT\" + E1.name , True
exit for
end if
Next
Main = DTSTaskExecResult_Success
End Functionsqlsql
I am running a DTS package that is an ACtiveX Script Task using VBScript. The script uses CreateObject() to create a FileSystemObject to copy an .MDB before importing the tables into SQL Server. I want to copy it because of Access' notoriety of corrupting, and this much data being pumped out of Access could force me to Compact & Repair. I would rather do that on a copy.
Function Main()
Dim FSO
Set FSO=CreateObject("Scripting.FileSystemObject")
FSO.CopyFile "\\<server>\<Share>\Data.mdb", "\\<server>\C$\DataCopy\Data.mdb"
Main = DTSTaskExecResult_Success
End Function
The DTS Package runs when I execute it from Ent. Manager, of course. It fails if scheduled, or course :(
I have set the Owner of the Scheduled Task to my domain account, which is also in the Adminstrators Group on the physical server with the SQL Server installation (Windows 2003 Server). I also did the unnecessary task of adding my domain account specifically to the destination folder, which is also Shared.
My sqlagent.exe service runs as SYSTEM on the server, so the SQLAgent should have no problem copying a file from one folder on the server to another.
The Scheduled Task fails with the common error:
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1
DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220482 (800403FE)
Error string: Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Permission denied
Error on Line 12
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500
Error Detail Records:
Error: -2147220482 (800403FE); Provider Error: 0 (0)
Error string: Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Permission denied
Error on Line 12
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1
DTSRun: Package execution complete.
I checked this MS KB Article (http://support.microsoft.com/kb/q298725/), but the instructions after opening DCOMcnfg.exe do not follow what is shown in the WMI window on Windows 2003 Server :rolleyes: (i.e. there is no "Default Security" tab to click.)
I am at a loss here. Thanks for your help.I'll bump this once.|||I'm still working on it so not sure if what I suspect is the issue... but have a dts package that download a file, based on SQL statement from as/400 to sql table... runs great when I run it myself within the enterprise manager but when I try to call from a stored procedure via my exe it fails to run.
What I've deduced is that when I run it within the enterprise manager that it is, in essence, running on the client... locally and using my dll's, etc... however, when I run via stored procedure through my exe it is trying to run on the server, herein lies the problem (really strong guess at this point)... the server is not set up to connect to the as/400 and that's where it bombs.
I'm guessing that your sql server is having issues talking to your access database in much the same way that mine is having issues talking to the as/400. I've got the client software installed now and will be testing my theory shortly. Hope this helps you with your issue.
b|||Is the access db on the same server as Sql server?
If so, do non unc paths work? Such as :
FSO.CopyFile "c:\somefolder\Data.mdb", "c\DataCopy\Data.mdb"|||.. I have a package as well.
The thing is that if I put in a msgbox to execute directly before the ".deletefile", the package will run, but if I outcomment It it fails...
I can on the other hand do an ".copyfile" instead...
I only have the problem with the ".deletefile"
I would also appriciate all help i can get on this.
// selander
Function Main()
Dim Eso, E, E1, Ec
Set Eso = CreateObject("Scripting.FileSystemObject")
Set E = Eso.GetFolder("\\CLUST\SYS\IMPORT\ENT\")
Set Ec = E.Files
For Each E1 in Ec
if ucase(E1.name) = ucase(DTSGlobalVariables("strFileName").Value)+".ZIP" then
msgbox "FUBAR"
Eso.DeleteFile "\\CLUST\SYS\IMPORT\ENT\" + E1.name , True
exit for
end if
Next
Main = DTSTaskExecResult_Success
End Functionsqlsql
DTS Scheduled job always fails
Hi,
I created and saved a DTS package to "SQL Server". When I "Execute Package" under the "Data Transformation -> Local Packages" folder in Enterprise Mgr, it executes perfectly.
However, when I Schedule it, the scheduled job Fails. The View Job History shows "The job failed. The Job was invoked by User sa. The last step to run was step 1 (ImportTest3)."
Could someone please help me with this? I don't understand why the manual execution works, but the scheduled job doesn't. Thanks for the help!Did you ever find a solution to this problem? I am having the same problem.
Originally posted by multiplex77
Hi,
I created and saved a DTS package to "SQL Server". When I "Execute Package" under the "Data Transformation -> Local Packages" folder in Enterprise Mgr, it executes perfectly.
However, when I Schedule it, the scheduled job Fails. The View Job History shows "The job failed. The Job was invoked by User sa. The last step to run was step 1 (ImportTest3)."
Could someone please help me with this? I don't understand why the manual execution works, but the scheduled job doesn't. Thanks for the help!|||You execute under different security contexts when executing a dts package manually versus scheduling it as a job. What is your dts script doing ?|||I set up a number of packages to process selected cubes in Analysis Services. The first job processes all dimensions and even that one fails.|||What message is returned for the failure ?|||ptcooper - In your case, you can execute the package and it succeeds - but when you schedule it as a job, it fails - is this true ?|||True, the problem is exactly as described by multiplex77. the only jobs that fail are the ones that are processing OLAP cubes/dimensions
Originally posted by rnealejr
ptcooper - In your case, you can execute the package and it succeeds - but when you schedule it as a job, it fails - is this true ?|||Who is the owner of the job ?|||The owner is sa and the password was specified when the DTS packages were saved. SLQ Server Agent is logged on as administrator.
Originally posted by rnealejr
Who is the owner of the job ?|||log the package and post the message(s) returned in the log.|||I don't see a way to log the package. I've never had to do that.
Originally posted by rnealejr
log the package and post the message(s) returned in the log.|||If the SQL Agent is running as the local administrator, is the local administrator a member of the local Olap Administrators group? Not sure if it defaults to this.|||The SQlSERVERAGENT does not log on as the local administrator. It logs on as a system wide administrator. We have been looking for the OLAP administrator group in the 'security' tab and the 'member of' tab for the administrator, but don't find it listed in any of the drop down lists. We have all the latest service packs installed.|||The Olap Administrators group is a local group created by the Analysis Services install. Anyone who is not a member of this group can not use the SQL Server tools to manage, modify, or otherwise peek at the cubes on that machine. Remember this only applies to the MMC tool. The OLAP Administrators group will not show up in the domain-wide dropdown lists, since it is local only to one machine. Does that help?|||Right click your "My Computer" -> Manage -> Local Users and Groups -> Groups -> Olap Admin ... Add the account that is used to run sql server agent.|||UPDATE: We discovered that the OLAP Administrator group had somehow been deleted from the Active directory. We tried re-installing service pack 3 for Analysis Services, recreating the OLAP Administrators group and setting the members, properties etc. None of this worked. We just re-installed Analysis Services and service pack 3 on the server and now the jobs will run from the workstation in EM|||The OLAP administrators group is not in AD. Never was. It is local to the Analysis Services machine.|||Our Analysis Services server is a Domain controller, and as such cannot use the Local Users.. snap in. You get directed to the Active Directory Users and Computers when you try opening it. (also ref. MS KB article 829738 'More Information')|||Once you installed it as a domain controller in ad, the local users/groups do not transfer - which is why the olap admin group never appeared. I am a little curious that other problems did not appear before this problem.
Thanks for posting the follow-up with the solution.
I created and saved a DTS package to "SQL Server". When I "Execute Package" under the "Data Transformation -> Local Packages" folder in Enterprise Mgr, it executes perfectly.
However, when I Schedule it, the scheduled job Fails. The View Job History shows "The job failed. The Job was invoked by User sa. The last step to run was step 1 (ImportTest3)."
Could someone please help me with this? I don't understand why the manual execution works, but the scheduled job doesn't. Thanks for the help!Did you ever find a solution to this problem? I am having the same problem.
Originally posted by multiplex77
Hi,
I created and saved a DTS package to "SQL Server". When I "Execute Package" under the "Data Transformation -> Local Packages" folder in Enterprise Mgr, it executes perfectly.
However, when I Schedule it, the scheduled job Fails. The View Job History shows "The job failed. The Job was invoked by User sa. The last step to run was step 1 (ImportTest3)."
Could someone please help me with this? I don't understand why the manual execution works, but the scheduled job doesn't. Thanks for the help!|||You execute under different security contexts when executing a dts package manually versus scheduling it as a job. What is your dts script doing ?|||I set up a number of packages to process selected cubes in Analysis Services. The first job processes all dimensions and even that one fails.|||What message is returned for the failure ?|||ptcooper - In your case, you can execute the package and it succeeds - but when you schedule it as a job, it fails - is this true ?|||True, the problem is exactly as described by multiplex77. the only jobs that fail are the ones that are processing OLAP cubes/dimensions
Originally posted by rnealejr
ptcooper - In your case, you can execute the package and it succeeds - but when you schedule it as a job, it fails - is this true ?|||Who is the owner of the job ?|||The owner is sa and the password was specified when the DTS packages were saved. SLQ Server Agent is logged on as administrator.
Originally posted by rnealejr
Who is the owner of the job ?|||log the package and post the message(s) returned in the log.|||I don't see a way to log the package. I've never had to do that.
Originally posted by rnealejr
log the package and post the message(s) returned in the log.|||If the SQL Agent is running as the local administrator, is the local administrator a member of the local Olap Administrators group? Not sure if it defaults to this.|||The SQlSERVERAGENT does not log on as the local administrator. It logs on as a system wide administrator. We have been looking for the OLAP administrator group in the 'security' tab and the 'member of' tab for the administrator, but don't find it listed in any of the drop down lists. We have all the latest service packs installed.|||The Olap Administrators group is a local group created by the Analysis Services install. Anyone who is not a member of this group can not use the SQL Server tools to manage, modify, or otherwise peek at the cubes on that machine. Remember this only applies to the MMC tool. The OLAP Administrators group will not show up in the domain-wide dropdown lists, since it is local only to one machine. Does that help?|||Right click your "My Computer" -> Manage -> Local Users and Groups -> Groups -> Olap Admin ... Add the account that is used to run sql server agent.|||UPDATE: We discovered that the OLAP Administrator group had somehow been deleted from the Active directory. We tried re-installing service pack 3 for Analysis Services, recreating the OLAP Administrators group and setting the members, properties etc. None of this worked. We just re-installed Analysis Services and service pack 3 on the server and now the jobs will run from the workstation in EM|||The OLAP administrators group is not in AD. Never was. It is local to the Analysis Services machine.|||Our Analysis Services server is a Domain controller, and as such cannot use the Local Users.. snap in. You get directed to the Active Directory Users and Computers when you try opening it. (also ref. MS KB article 829738 'More Information')|||Once you installed it as a domain controller in ad, the local users/groups do not transfer - which is why the olap admin group never appeared. I am a little curious that other problems did not appear before this problem.
Thanks for posting the follow-up with the solution.
DTS Scheduled Job
I am getting the following error whenever I execute
the job via sql server job agent. When I execute
the job package manually via dts the result is good.
Should I mount the physical drive to the main server?
Executed as user: card\sql. DTSRun: Loading... DTSRun:
Executing...
DTSRun OnStart: Copy Data from Results to S:\test.xls
Step DTSRun OnError: Copy Data from Results to
S:\test.xls Step,
Error = -2147467259 (80004005)
Error string: Error opening datafile: The system cannot
find the path specified.
Error source: Microsoft Data Transformation Services Flat
File Rowset Provider
Help file: DTSFFile.hlp Help context: 0
Error Detail Records: Error: 3 (3); Provider Error:
3 (3)
Error string: Error opening datafile: The system cannot
find the path specified.
Error source: Microsoft Data Transformation Services Flat
File Rowset Provider
Help file: DTSFFile.hlp Help context: 0
DTSRun OnFinish: Copy Data from Results to S:\test.xls
Step
DTSRun: Package execution complete. Process Exit Code
1.
The step failed.DTS is a client tool and thus when you run it from EM it's running
on your pc. If the server doesn't have an S drive then you'll get this
error. It's best to use UNC paths to avoid ambiguity. Also check out
INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q269074
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"L Dominguez" <ldominguez@.cardionet.com> wrote in message
news:02af01c34730$9a2823b0$a601280a@.phx.gbl...
I am getting the following error whenever I execute
the job via sql server job agent. When I execute
the job package manually via dts the result is good.
Should I mount the physical drive to the main server?
Executed as user: card\sql. DTSRun: Loading... DTSRun:
Executing...
DTSRun OnStart: Copy Data from Results to S:\test.xls
Step DTSRun OnError: Copy Data from Results to
S:\test.xls Step,
Error = -2147467259 (80004005)
Error string: Error opening datafile: The system cannot
find the path specified.
Error source: Microsoft Data Transformation Services Flat
File Rowset Provider
Help file: DTSFFile.hlp Help context: 0
Error Detail Records: Error: 3 (3); Provider Error:
3 (3)
Error string: Error opening datafile: The system cannot
find the path specified.
Error source: Microsoft Data Transformation Services Flat
File Rowset Provider
Help file: DTSFFile.hlp Help context: 0
DTSRun OnFinish: Copy Data from Results to S:\test.xls
Step
DTSRun: Package execution complete. Process Exit Code
1.
The step failed.
the job via sql server job agent. When I execute
the job package manually via dts the result is good.
Should I mount the physical drive to the main server?
Executed as user: card\sql. DTSRun: Loading... DTSRun:
Executing...
DTSRun OnStart: Copy Data from Results to S:\test.xls
Step DTSRun OnError: Copy Data from Results to
S:\test.xls Step,
Error = -2147467259 (80004005)
Error string: Error opening datafile: The system cannot
find the path specified.
Error source: Microsoft Data Transformation Services Flat
File Rowset Provider
Help file: DTSFFile.hlp Help context: 0
Error Detail Records: Error: 3 (3); Provider Error:
3 (3)
Error string: Error opening datafile: The system cannot
find the path specified.
Error source: Microsoft Data Transformation Services Flat
File Rowset Provider
Help file: DTSFFile.hlp Help context: 0
DTSRun OnFinish: Copy Data from Results to S:\test.xls
Step
DTSRun: Package execution complete. Process Exit Code
1.
The step failed.DTS is a client tool and thus when you run it from EM it's running
on your pc. If the server doesn't have an S drive then you'll get this
error. It's best to use UNC paths to avoid ambiguity. Also check out
INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q269074
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"L Dominguez" <ldominguez@.cardionet.com> wrote in message
news:02af01c34730$9a2823b0$a601280a@.phx.gbl...
I am getting the following error whenever I execute
the job via sql server job agent. When I execute
the job package manually via dts the result is good.
Should I mount the physical drive to the main server?
Executed as user: card\sql. DTSRun: Loading... DTSRun:
Executing...
DTSRun OnStart: Copy Data from Results to S:\test.xls
Step DTSRun OnError: Copy Data from Results to
S:\test.xls Step,
Error = -2147467259 (80004005)
Error string: Error opening datafile: The system cannot
find the path specified.
Error source: Microsoft Data Transformation Services Flat
File Rowset Provider
Help file: DTSFFile.hlp Help context: 0
Error Detail Records: Error: 3 (3); Provider Error:
3 (3)
Error string: Error opening datafile: The system cannot
find the path specified.
Error source: Microsoft Data Transformation Services Flat
File Rowset Provider
Help file: DTSFFile.hlp Help context: 0
DTSRun OnFinish: Copy Data from Results to S:\test.xls
Step
DTSRun: Package execution complete. Process Exit Code
1.
The step failed.
DTS runs great... but not from the job list
I have a DTS package that updates a database from a text file and it works
great when I run it manually from the DTS designer. However, I right click
on it and set it up to run automatically and it will not run. I went to
view the history of the job messages and it appears that the following is
the error for this task:
Executed as user: MAHC_SQL2\SYSTEM. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun
OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1,
Error = -2147467259 (80004005) Error string: Error opening datafile:
The network name cannot be found. Error source: Microsoft Data
Transformation Services Flat File Rowset Provider Help file:
DTSFFile.hlp Help context: 0 Error Detail Records: Error:
67 (43); Provider Error: 67 (43) Error string: Error opening
datafile: The network name cannot be found. Error source: Microsoft
Data Transformation Services Flat File Rowset Provider Help file:
DTSFFile.hlp Help context: 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
Can anyone give me any clues as to what the problem could be?
Schoo
Have a look at this KB that details the common reasons a scheduled DTS
package will fail
INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/default...;en-us;Q269074
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Schoo" <scott.schuman@.nospam.ma-hc.com> wrote in message
news:%23enkcwUrEHA.452@.TK2MSFTNGP09.phx.gbl...
>I have a DTS package that updates a database from a text file and it works
> great when I run it manually from the DTS designer. However, I right
> click
> on it and set it up to run automatically and it will not run. I went to
> view the history of the job messages and it appears that the following is
> the error for this task:
> Executed as user: MAHC_SQL2\SYSTEM. DTSRun: Loading... DTSRun:
> Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun
> OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1,
> Error = -2147467259 (80004005) Error string: Error opening datafile:
> The network name cannot be found. Error source: Microsoft Data
> Transformation Services Flat File Rowset Provider Help file:
> DTSFFile.hlp Help context: 0 Error Detail Records: Error:
> 67 (43); Provider Error: 67 (43) Error string: Error opening
> datafile: The network name cannot be found. Error source:
> Microsoft
> Data Transformation Services Flat File Rowset Provider Help file:
> DTSFFile.hlp Help context: 0 DTSRun OnFinish:
> DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
> Exit Code 1. The step failed.
> Can anyone give me any clues as to what the problem could be?
> Schoo
>
|||Hi Schoo,
Thanks for you post and I would love to show my gratidue for MVP Jasper
Smith's providing that great KB:Q269074
From your Error Message, I foudn the error was "The network name cannot be
found" so that please check whether there is anything wrong with your
source and target of database. Make sure they are "online", also please
check the permission issue. It seems strange and I need more information to
make further troubleshooting.
Would you please generate a small DTS package and see whether it works
fine? To get a better picture of the problem we would like to reproduct the
issue on our end. To expedite the process, please provide us the detailed
reproduce steps.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Schoo,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
great when I run it manually from the DTS designer. However, I right click
on it and set it up to run automatically and it will not run. I went to
view the history of the job messages and it appears that the following is
the error for this task:
Executed as user: MAHC_SQL2\SYSTEM. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun
OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1,
Error = -2147467259 (80004005) Error string: Error opening datafile:
The network name cannot be found. Error source: Microsoft Data
Transformation Services Flat File Rowset Provider Help file:
DTSFFile.hlp Help context: 0 Error Detail Records: Error:
67 (43); Provider Error: 67 (43) Error string: Error opening
datafile: The network name cannot be found. Error source: Microsoft
Data Transformation Services Flat File Rowset Provider Help file:
DTSFFile.hlp Help context: 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
Can anyone give me any clues as to what the problem could be?
Schoo
Have a look at this KB that details the common reasons a scheduled DTS
package will fail
INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/default...;en-us;Q269074
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Schoo" <scott.schuman@.nospam.ma-hc.com> wrote in message
news:%23enkcwUrEHA.452@.TK2MSFTNGP09.phx.gbl...
>I have a DTS package that updates a database from a text file and it works
> great when I run it manually from the DTS designer. However, I right
> click
> on it and set it up to run automatically and it will not run. I went to
> view the history of the job messages and it appears that the following is
> the error for this task:
> Executed as user: MAHC_SQL2\SYSTEM. DTSRun: Loading... DTSRun:
> Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun
> OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1,
> Error = -2147467259 (80004005) Error string: Error opening datafile:
> The network name cannot be found. Error source: Microsoft Data
> Transformation Services Flat File Rowset Provider Help file:
> DTSFFile.hlp Help context: 0 Error Detail Records: Error:
> 67 (43); Provider Error: 67 (43) Error string: Error opening
> datafile: The network name cannot be found. Error source:
> Microsoft
> Data Transformation Services Flat File Rowset Provider Help file:
> DTSFFile.hlp Help context: 0 DTSRun OnFinish:
> DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
> Exit Code 1. The step failed.
> Can anyone give me any clues as to what the problem could be?
> Schoo
>
|||Hi Schoo,
Thanks for you post and I would love to show my gratidue for MVP Jasper
Smith's providing that great KB:Q269074
From your Error Message, I foudn the error was "The network name cannot be
found" so that please check whether there is anything wrong with your
source and target of database. Make sure they are "online", also please
check the permission issue. It seems strange and I need more information to
make further troubleshooting.
Would you please generate a small DTS package and see whether it works
fine? To get a better picture of the problem we would like to reproduct the
issue on our end. To expedite the process, please provide us the detailed
reproduce steps.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Schoo,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
DTS run from .NET
My dts package has to pick up a file which is in a mapped drive of the
sql server... (system1)...
when I run it from code the execution ends up in an error... but if I
have the drive mapped (drive where I have the source file) onto the
web server (system2) the execution goes through fine.. I donot want to
have the drive mapped onto the web server.. how can I do this..Try and use the UNC path.
sql server... (system1)...
when I run it from code the execution ends up in an error... but if I
have the drive mapped (drive where I have the source file) onto the
web server (system2) the execution goes through fine.. I donot want to
have the drive mapped onto the web server.. how can I do this..Try and use the UNC path.
Joel Scavone|||I did use a full netwok path and even did add identity impersonate in
the web.config... but does not help..
The package runs fine when I register the sql server my machine and run
the package..
Sigh! Don't know what wrong
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
DTS return
I am trying to execute a DTS package which will update four tables on a central server from various souces (each source will insert records into all four tables).
We are running this code by calling a stored procedure from VB, which includes code to execute the DTS package. The problem I am having is the VB code continues on even though the package has completed.
SET @.SQLStr = 'DTSRun /S CENTRAL1 /N DTS_TEST_HU_MoveCenterScreens /E'
EXEC @.Result = Master.dbo.xp_cmdshell @.SQLStr
I return @.Result. I would have thought that the return value from the execution of the sql package would not return until it was completed. However, it returns right away. The package takes about 10 mintutes to run, but the return variable is populated in less than a second.
The next step of the process relies upon the dts's completion.
I am using SQL 2000 as my DB
Any thoughts?You should take a look at this article:
Execute DTS via Stored Procedure (http://www.databasejournal.com/features/mssql/article.php/1459181)
Have you thought about executing DTS from VB?|||Thank you for the response. While I was still trying to figure out what to do I started to explore VB and found the DTS object. We are now using that object and it has solved my problems.
As for the link, I was trying to utilize the return variable but my code continued to execute even if the dts package was not complete.
Originally posted by achorozy
You should take a look at this article:
Execute DTS via Stored Procedure (http://www.databasejournal.com/features/mssql/article.php/1459181)
Have you thought about executing DTS from VB?sqlsql
We are running this code by calling a stored procedure from VB, which includes code to execute the DTS package. The problem I am having is the VB code continues on even though the package has completed.
SET @.SQLStr = 'DTSRun /S CENTRAL1 /N DTS_TEST_HU_MoveCenterScreens /E'
EXEC @.Result = Master.dbo.xp_cmdshell @.SQLStr
I return @.Result. I would have thought that the return value from the execution of the sql package would not return until it was completed. However, it returns right away. The package takes about 10 mintutes to run, but the return variable is populated in less than a second.
The next step of the process relies upon the dts's completion.
I am using SQL 2000 as my DB
Any thoughts?You should take a look at this article:
Execute DTS via Stored Procedure (http://www.databasejournal.com/features/mssql/article.php/1459181)
Have you thought about executing DTS from VB?|||Thank you for the response. While I was still trying to figure out what to do I started to explore VB and found the DTS object. We are now using that object and it has solved my problems.
As for the link, I was trying to utilize the return variable but my code continued to execute even if the dts package was not complete.
Originally posted by achorozy
You should take a look at this article:
Execute DTS via Stored Procedure (http://www.databasejournal.com/features/mssql/article.php/1459181)
Have you thought about executing DTS from VB?sqlsql
Tuesday, March 27, 2012
Dts Record Count
I have a dts package that export data from a database to a text file. I could like to create a validation script to coun the data in the database that should be exported to file and then re-count again after the data is in the text file. Also count the number of columns that is exported.
Is there a way to do that within a dts package?
Help!
LystraYou ought to be able to do that using DTS Global Variables (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsprog/dtspapps_82nn.asp).
-PatP
Is there a way to do that within a dts package?
Help!
LystraYou ought to be able to do that using DTS Global Variables (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsprog/dtspapps_82nn.asp).
-PatP
DTS question...
I have a question...;
I have a DTS package for sincronice two DB, but I want to know if each time that my package runs, all the rows will be copied or only the news rows...Originally posted by ericka
I have a question...;
I have a DTS package for sincronice two DB, but I want to know if each time that my package runs, all the rows will be copied or only the news rows...
How did you make your dts package ?
If you just use a datapump all row will be copied into your table.
If you need a prelication system sql server as replication tools.sqlsql
I have a DTS package for sincronice two DB, but I want to know if each time that my package runs, all the rows will be copied or only the news rows...Originally posted by ericka
I have a question...;
I have a DTS package for sincronice two DB, but I want to know if each time that my package runs, all the rows will be copied or only the news rows...
How did you make your dts package ?
If you just use a datapump all row will be copied into your table.
If you need a prelication system sql server as replication tools.sqlsql
dts question, data pump task hangs when i try to edit it!
I'm trying to build a DTS package that will copy data from one server to another, and I'm using the latest teradata ODBC driver to connect to teradata. The problem is when i setup the dts package - DTS just sits there and hangs when i try to make a new data pump task.
Have you connected to your data source before doing this?|||hi,
Hey dude,
Any clues why this would occur? Also, in case you were curious, I am on sql server 2000 with service pack 4
Quote:
Originally Posted by catstevens
I'm trying to build a DTS package that will copy data from one server to another, and I'm using the latest teradata ODBC driver to connect to teradata. The problem is when i setup the dts package - DTS just sits there and hangs when i try to make a new data pump task.
Any clues why this would occur? Also, in case you were curious, I am on sql server 2000 with service pack 4
Have you connected to your data source before doing this?|||hi,
i kind of figured out what was wrong so in case anyone googles this - dts attempts to look at every table in the data source for every database. because the source connection i was trying to pump from had so many tables it hung for a good 3 minutes before the properties window opened.
it sped up a little when I performed the editing on the server itself (as opposed to a workstation using enterprise manager).
thanks|||
Quote:
Originally Posted by catstevens
hi,
i kind of figured out what was wrong so in case anyone googles this - dts attempts to look at every table in the data source for every database. because the source connection i was trying to pump from had so many tables it hung for a good 3 minutes before the properties window opened.
it sped up a little when I performed the editing on the server itself (as opposed to a workstation using enterprise manager).
thanks
Hey dude,
What do you mean by editing on the server. I am a newbie to the data world and am trying to do the same ..extracting the data from TeraData to SQL Server. Its hanging and I was never able to get this done.
Can you please help me here...
THanks,
Rahul
DTS Question - copy table from SQL server to non-SQL server
I'm breaking into SQL 2000, and have completed a DTS package that imports and massages a file and creates a table with about 200M records.
Now I want to add a process that will export the table to a directory on a Windows 2000 server, in a format such as Access, dBase, or someting else, to make the table available for users who do not have assces to SQL 2000.
I'm having a problem deciding which DTS 'Task' should be used. I've tried several, but haven't stumbled on the correct one, or if I have, haven't realized it because of error messages.
Any tips to get me started will be appreciated.
Thanks,
RandyFirst, add a connection object that specifies your preferred destination (Access, text, Excel, whatever). Then, holding down your CTRL key, select first the source connection (your server), then click on the destination connection (the one you just added). Next click on the data pump task (aka Transform Data Task; it's the black arrow pointing to the yellow...er...thingy).
Then define your source query (or table), your destination and any mapping changes you want to make.
Best of luck,
hmscott
...or, contact Brett Kaiser about how to do a BCP... ;-)sqlsql
Now I want to add a process that will export the table to a directory on a Windows 2000 server, in a format such as Access, dBase, or someting else, to make the table available for users who do not have assces to SQL 2000.
I'm having a problem deciding which DTS 'Task' should be used. I've tried several, but haven't stumbled on the correct one, or if I have, haven't realized it because of error messages.
Any tips to get me started will be appreciated.
Thanks,
RandyFirst, add a connection object that specifies your preferred destination (Access, text, Excel, whatever). Then, holding down your CTRL key, select first the source connection (your server), then click on the destination connection (the one you just added). Next click on the data pump task (aka Transform Data Task; it's the black arrow pointing to the yellow...er...thingy).
Then define your source query (or table), your destination and any mapping changes you want to make.
Best of luck,
hmscott
...or, contact Brett Kaiser about how to do a BCP... ;-)sqlsql
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
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
Subscribe to:
Posts (Atom)