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
Showing posts with label running. Show all posts
Showing posts with label running. Show all posts
Thursday, March 29, 2012
Sunday, March 25, 2012
DTS Problem
Try removing it from your server, running a reg clean then
putting it back on and registering it.
Peter
"A man is never more truthful than when he acknowledges
himself a liar."
Mark Twain
>--Original Message--
>I'm getting the error message
>Server.CreateObject Failed
>when running a dts package. Been searching around, it
recommended
>re-registering the 5 dts*.dll files on the system.
>All but one re-registered without a problem, however, the
dtswiz.dll file
>didn't - I got this error message
>"dtswiz.dll was loaded, but the DllRegisterServer entry
point was not found.
>This file can not be registered."
>
>Thanks for any help.
>
>
>.
>
Thanks for getting back to me peter
Been looking for info on running a registry clean - any advice?
Rich
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:0baf01c49ff0$10833c20$a601280a@.phx.gbl...[vbcol=seagreen]
> Try removing it from your server, running a reg clean then
> putting it back on and registering it.
> Peter
> "A man is never more truthful than when he acknowledges
> himself a liar."
> Mark Twain
>
> recommended
> dtswiz.dll file
> point was not found.
putting it back on and registering it.
Peter
"A man is never more truthful than when he acknowledges
himself a liar."
Mark Twain
>--Original Message--
>I'm getting the error message
>Server.CreateObject Failed
>when running a dts package. Been searching around, it
recommended
>re-registering the 5 dts*.dll files on the system.
>All but one re-registered without a problem, however, the
dtswiz.dll file
>didn't - I got this error message
>"dtswiz.dll was loaded, but the DllRegisterServer entry
point was not found.
>This file can not be registered."
>
>Thanks for any help.
>
>
>.
>
Thanks for getting back to me peter
Been looking for info on running a registry clean - any advice?
Rich
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:0baf01c49ff0$10833c20$a601280a@.phx.gbl...[vbcol=seagreen]
> Try removing it from your server, running a reg clean then
> putting it back on and registering it.
> Peter
> "A man is never more truthful than when he acknowledges
> himself a liar."
> Mark Twain
>
> recommended
> dtswiz.dll file
> point was not found.
DTS Problem
I'm getting the error message
Server.CreateObject Failed
when running a dts package. Been searching around, it recommended
re-registering the 5 dts*.dll files on the system.
All but one re-registered without a problem, however, the dtswiz.dll file
didn't - I got this error message
"dtswiz.dll was loaded, but the DllRegisterServer entry point was not found.
This file can not be registered."
Thanks for any help.Try removing it from your server, running a reg clean then
putting it back on and registering it.
Peter
"A man is never more truthful than when he acknowledges
himself a liar."
Mark Twain
>--Original Message--
>I'm getting the error message
>Server.CreateObject Failed
>when running a dts package. Been searching around, it
recommended
>re-registering the 5 dts*.dll files on the system.
>All but one re-registered without a problem, however, the
dtswiz.dll file
>didn't - I got this error message
>"dtswiz.dll was loaded, but the DllRegisterServer entry
point was not found.
>This file can not be registered."
>
>Thanks for any help.
>
>
>.
>|||Thanks for getting back to me peter
Been looking for info on running a registry clean - any advice?
Rich
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:0baf01c49ff0$10833c20$a601280a@.phx.gbl...
> Try removing it from your server, running a reg clean then
> putting it back on and registering it.
> Peter
> "A man is never more truthful than when he acknowledges
> himself a liar."
> Mark Twain
>
>>--Original Message--
>>I'm getting the error message
>>Server.CreateObject Failed
>>when running a dts package. Been searching around, it
> recommended
>>re-registering the 5 dts*.dll files on the system.
>>All but one re-registered without a problem, however, the
> dtswiz.dll file
>>didn't - I got this error message
>>"dtswiz.dll was loaded, but the DllRegisterServer entry
> point was not found.
>>This file can not be registered."
>>
>>Thanks for any help.
>>
>>
>>.
Server.CreateObject Failed
when running a dts package. Been searching around, it recommended
re-registering the 5 dts*.dll files on the system.
All but one re-registered without a problem, however, the dtswiz.dll file
didn't - I got this error message
"dtswiz.dll was loaded, but the DllRegisterServer entry point was not found.
This file can not be registered."
Thanks for any help.Try removing it from your server, running a reg clean then
putting it back on and registering it.
Peter
"A man is never more truthful than when he acknowledges
himself a liar."
Mark Twain
>--Original Message--
>I'm getting the error message
>Server.CreateObject Failed
>when running a dts package. Been searching around, it
recommended
>re-registering the 5 dts*.dll files on the system.
>All but one re-registered without a problem, however, the
dtswiz.dll file
>didn't - I got this error message
>"dtswiz.dll was loaded, but the DllRegisterServer entry
point was not found.
>This file can not be registered."
>
>Thanks for any help.
>
>
>.
>|||Thanks for getting back to me peter
Been looking for info on running a registry clean - any advice?
Rich
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:0baf01c49ff0$10833c20$a601280a@.phx.gbl...
> Try removing it from your server, running a reg clean then
> putting it back on and registering it.
> Peter
> "A man is never more truthful than when he acknowledges
> himself a liar."
> Mark Twain
>
>>--Original Message--
>>I'm getting the error message
>>Server.CreateObject Failed
>>when running a dts package. Been searching around, it
> recommended
>>re-registering the 5 dts*.dll files on the system.
>>All but one re-registered without a problem, however, the
> dtswiz.dll file
>>didn't - I got this error message
>>"dtswiz.dll was loaded, but the DllRegisterServer entry
> point was not found.
>>This file can not be registered."
>>
>>Thanks for any help.
>>
>>
>>.
Thursday, March 22, 2012
DTS performance
Hi,
Can anyone advise me of a quick way to estimate the time taken by DTS to
import a table (24 columns x 700,000 rows) from JD Edwards (running on
AS400) into SQL Server (new table and no manipulation involved)?
Many thanks,
Steve"Steve McDonald" <ajones1@.nsw.bigpond.net.au> wrote in message news:<TBbRa.7157$wU5.924@.news-server.bigpond.net.au>...
> Hi,
> Can anyone advise me of a quick way to estimate the time taken by DTS to
> import a table (24 columns x 700,000 rows) from JD Edwards (running on
> AS400) into SQL Server (new table and no manipulation involved)?
> Many thanks,
> Steve
There are plenty of factors which may vary from one environment to
another, so the most reliable way to find out would simply be to test
it. In theory, you could work out the volume of data (average row
length x number of rows), then divide that by your network speed, add
some time for DTS overhead etc., but in practice I think you wouldn't
get a very accurate answer.
Simon
DTS Parsing Delimited Record
I am running SQLServer 2000 and am parsing a delimited file. Each line
is delimited with a crlf, but each line also ends with a tilda (~) that
I do not want to process. Each line does not have the same number of
elements, but every line ends with a tilda. Is there a way to set the
input file parser to strip off the tilda (that is, define the end of
line marker to be ~+cr+lf ?
Thanks,
greg lindstromhi,
why don't use each line as 'fixed field' from the beginning till the end and
then, using VbScript and substring do what you want.
--
current location: alicante (es)
"tuba_ranger" wrote:
> I am running SQLServer 2000 and am parsing a delimited file. Each line
> is delimited with a crlf, but each line also ends with a tilda (~) that
> I do not want to process. Each line does not have the same number of
> elements, but every line ends with a tilda. Is there a way to set the
> input file parser to strip off the tilda (that is, define the end of
> line marker to be ~+cr+lf ?
> Thanks,
> greg lindstrom
>
is delimited with a crlf, but each line also ends with a tilda (~) that
I do not want to process. Each line does not have the same number of
elements, but every line ends with a tilda. Is there a way to set the
input file parser to strip off the tilda (that is, define the end of
line marker to be ~+cr+lf ?
Thanks,
greg lindstromhi,
why don't use each line as 'fixed field' from the beginning till the end and
then, using VbScript and substring do what you want.
--
current location: alicante (es)
"tuba_ranger" wrote:
> I am running SQLServer 2000 and am parsing a delimited file. Each line
> is delimited with a crlf, but each line also ends with a tilda (~) that
> I do not want to process. Each line does not have the same number of
> elements, but every line ends with a tilda. Is there a way to set the
> input file parser to strip off the tilda (that is, define the end of
> line marker to be ~+cr+lf ?
> Thanks,
> greg lindstrom
>
dts parameter
I am running the DTS as follows,
SET @.CMD = 'dtsrun /S '+@.server+' /U '+@.user+' /P '+@.pass+' /N '+@.dtsn+' /A MyBinaryID:19 = '+@.MyBinaryID
EXECUTE master..xp_cmdshell @.CMD
This does not return all the data correctly.MyBinaryIDis a Binary(8) field, any idea how should I pass MyBinaryID and define what type of parameter in the DTS?
In the DTS package, set the Global Variable MyBinary to Integer type, and map it to the parameter used in your query. For example I use such query in a Execute SQL Task in DTS:
UPDATE tbl_DTS
SET name='xxx'
WHERE CONVERT(BIGINT,MyBinaryID)= ?
And I map the MyBinary Global Variable to Parameter1. Declare @.MyBinaryID as BIGINT in T-SQL and assign a proper value to it (of cource the value of @.MyBinary need to be some value converted from the BINARY(8) data). Then set the @.CMD as following:
SET @.CMD = 'dtsrun /S'+@.server+' /U'+@.user+' /P'+@.pass+' /N'+@.dtsn+' /A MyBinaryID= '
+CONVERT(VARCHAR,@.MyBinaryID)
EXEC master..xp_cmdshell @.CMD
dts packages take a long time to run
Hi there
I'm running a DTS package that imports data from various Excel spreadsheets
into a SQL Server database. Running the package from the command prompt, I
get information on each data pump task including each 1,000 rows imported
successfully. When all rows have been imported from a particular worksheet
(65536), DTS takes quite a bit of time before it initiates the data pump
task for the next worksheet. I notice that the CPU falls idle during this
time, but DTSrun.exe's memory usage remains high for some time afterward.
This apparent idle time more than doubles the total amount taken to complete
this DTS package. Do you know why this is so? I thought it might be time
taken to build the indexes on each imported worksheet (I need to create each
table from scratch), or alternatively the time taken to establish
communication with Excel. Any other ideas, and a suggestion on how to pick
up the pace?!
Best regards
Loane
You can use profiler to monitor what is going on - looking
at what the process is doing especially at the end of the
import and after the import, what the durations are for
different steps and processes in the package.
You could also monitor it more "manually" by querying
sysprocesses and watching the wait types, activities, etc.
If you are running SQL 2000, sp3 you can also use fn_get_sql
to see what exactly it's executing at different points.
-Sue
On Mon, 2 May 2005 22:26:21 +0200, "Loane Sharp"
<look_sharp_not@.hotmail.com> wrote:
>Hi there
>I'm running a DTS package that imports data from various Excel spreadsheets
>into a SQL Server database. Running the package from the command prompt, I
>get information on each data pump task including each 1,000 rows imported
>successfully. When all rows have been imported from a particular worksheet
>(65536), DTS takes quite a bit of time before it initiates the data pump
>task for the next worksheet. I notice that the CPU falls idle during this
>time, but DTSrun.exe's memory usage remains high for some time afterward.
>This apparent idle time more than doubles the total amount taken to complete
>this DTS package. Do you know why this is so? I thought it might be time
>taken to build the indexes on each imported worksheet (I need to create each
>table from scratch), or alternatively the time taken to establish
>communication with Excel. Any other ideas, and a suggestion on how to pick
>up the pace?!
>Best regards
>Loane
>
|||thanks, will try this one, it's been really niggling me
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:3end71hhr8drpdqhs7kg8d4k6d99415lo3@.4ax.com...
> You can use profiler to monitor what is going on - looking
> at what the process is doing especially at the end of the
> import and after the import, what the durations are for
> different steps and processes in the package.
> You could also monitor it more "manually" by querying
> sysprocesses and watching the wait types, activities, etc.
> If you are running SQL 2000, sp3 you can also use fn_get_sql
> to see what exactly it's executing at different points.
> -Sue
> On Mon, 2 May 2005 22:26:21 +0200, "Loane Sharp"
> <look_sharp_not@.hotmail.com> wrote:
>
I'm running a DTS package that imports data from various Excel spreadsheets
into a SQL Server database. Running the package from the command prompt, I
get information on each data pump task including each 1,000 rows imported
successfully. When all rows have been imported from a particular worksheet
(65536), DTS takes quite a bit of time before it initiates the data pump
task for the next worksheet. I notice that the CPU falls idle during this
time, but DTSrun.exe's memory usage remains high for some time afterward.
This apparent idle time more than doubles the total amount taken to complete
this DTS package. Do you know why this is so? I thought it might be time
taken to build the indexes on each imported worksheet (I need to create each
table from scratch), or alternatively the time taken to establish
communication with Excel. Any other ideas, and a suggestion on how to pick
up the pace?!
Best regards
Loane
You can use profiler to monitor what is going on - looking
at what the process is doing especially at the end of the
import and after the import, what the durations are for
different steps and processes in the package.
You could also monitor it more "manually" by querying
sysprocesses and watching the wait types, activities, etc.
If you are running SQL 2000, sp3 you can also use fn_get_sql
to see what exactly it's executing at different points.
-Sue
On Mon, 2 May 2005 22:26:21 +0200, "Loane Sharp"
<look_sharp_not@.hotmail.com> wrote:
>Hi there
>I'm running a DTS package that imports data from various Excel spreadsheets
>into a SQL Server database. Running the package from the command prompt, I
>get information on each data pump task including each 1,000 rows imported
>successfully. When all rows have been imported from a particular worksheet
>(65536), DTS takes quite a bit of time before it initiates the data pump
>task for the next worksheet. I notice that the CPU falls idle during this
>time, but DTSrun.exe's memory usage remains high for some time afterward.
>This apparent idle time more than doubles the total amount taken to complete
>this DTS package. Do you know why this is so? I thought it might be time
>taken to build the indexes on each imported worksheet (I need to create each
>table from scratch), or alternatively the time taken to establish
>communication with Excel. Any other ideas, and a suggestion on how to pick
>up the pace?!
>Best regards
>Loane
>
|||thanks, will try this one, it's been really niggling me
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:3end71hhr8drpdqhs7kg8d4k6d99415lo3@.4ax.com...
> You can use profiler to monitor what is going on - looking
> at what the process is doing especially at the end of the
> import and after the import, what the durations are for
> different steps and processes in the package.
> You could also monitor it more "manually" by querying
> sysprocesses and watching the wait types, activities, etc.
> If you are running SQL 2000, sp3 you can also use fn_get_sql
> to see what exactly it's executing at different points.
> -Sue
> On Mon, 2 May 2005 22:26:21 +0200, "Loane Sharp"
> <look_sharp_not@.hotmail.com> wrote:
>
dts packages take a long time to run
Hi there
I'm running a DTS package that imports data from various Excel spreadsheets
into a SQL Server database. Running the package from the command prompt, I
get information on each data pump task including each 1,000 rows imported
successfully. When all rows have been imported from a particular worksheet
(65536), DTS takes quite a bit of time before it initiates the data pump
task for the next worksheet. I notice that the CPU falls idle during this
time, but DTSrun.exe's memory usage remains high for some time afterward.
This apparent idle time more than doubles the total amount taken to complete
this DTS package. Do you know why this is so? I thought it might be time
taken to build the indexes on each imported worksheet (I need to create each
table from scratch), or alternatively the time taken to establish
communication with Excel. Any other ideas, and a suggestion on how to pick
up the pace?!
Best regards
LoaneYou can use profiler to monitor what is going on - looking
at what the process is doing especially at the end of the
import and after the import, what the durations are for
different steps and processes in the package.
You could also monitor it more "manually" by querying
sysprocesses and watching the wait types, activities, etc.
If you are running SQL 2000, sp3 you can also use fn_get_sql
to see what exactly it's executing at different points.
-Sue
On Mon, 2 May 2005 22:26:21 +0200, "Loane Sharp"
<look_sharp_not@.hotmail.com> wrote:
>Hi there
>I'm running a DTS package that imports data from various Excel spreadsheets
>into a SQL Server database. Running the package from the command prompt, I
>get information on each data pump task including each 1,000 rows imported
>successfully. When all rows have been imported from a particular worksheet
>(65536), DTS takes quite a bit of time before it initiates the data pump
>task for the next worksheet. I notice that the CPU falls idle during this
>time, but DTSrun.exe's memory usage remains high for some time afterward.
>This apparent idle time more than doubles the total amount taken to complet
e
>this DTS package. Do you know why this is so? I thought it might be time
>taken to build the indexes on each imported worksheet (I need to create eac
h
>table from scratch), or alternatively the time taken to establish
>communication with Excel. Any other ideas, and a suggestion on how to pick
>up the pace?!
>Best regards
>Loane
>|||thanks, will try this one, it's been really niggling me
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:3end71hhr8drpdqhs7kg8d4k6d99415lo3@.
4ax.com...
> You can use profiler to monitor what is going on - looking
> at what the process is doing especially at the end of the
> import and after the import, what the durations are for
> different steps and processes in the package.
> You could also monitor it more "manually" by querying
> sysprocesses and watching the wait types, activities, etc.
> If you are running SQL 2000, sp3 you can also use fn_get_sql
> to see what exactly it's executing at different points.
> -Sue
> On Mon, 2 May 2005 22:26:21 +0200, "Loane Sharp"
> <look_sharp_not@.hotmail.com> wrote:
>
>
I'm running a DTS package that imports data from various Excel spreadsheets
into a SQL Server database. Running the package from the command prompt, I
get information on each data pump task including each 1,000 rows imported
successfully. When all rows have been imported from a particular worksheet
(65536), DTS takes quite a bit of time before it initiates the data pump
task for the next worksheet. I notice that the CPU falls idle during this
time, but DTSrun.exe's memory usage remains high for some time afterward.
This apparent idle time more than doubles the total amount taken to complete
this DTS package. Do you know why this is so? I thought it might be time
taken to build the indexes on each imported worksheet (I need to create each
table from scratch), or alternatively the time taken to establish
communication with Excel. Any other ideas, and a suggestion on how to pick
up the pace?!
Best regards
LoaneYou can use profiler to monitor what is going on - looking
at what the process is doing especially at the end of the
import and after the import, what the durations are for
different steps and processes in the package.
You could also monitor it more "manually" by querying
sysprocesses and watching the wait types, activities, etc.
If you are running SQL 2000, sp3 you can also use fn_get_sql
to see what exactly it's executing at different points.
-Sue
On Mon, 2 May 2005 22:26:21 +0200, "Loane Sharp"
<look_sharp_not@.hotmail.com> wrote:
>Hi there
>I'm running a DTS package that imports data from various Excel spreadsheets
>into a SQL Server database. Running the package from the command prompt, I
>get information on each data pump task including each 1,000 rows imported
>successfully. When all rows have been imported from a particular worksheet
>(65536), DTS takes quite a bit of time before it initiates the data pump
>task for the next worksheet. I notice that the CPU falls idle during this
>time, but DTSrun.exe's memory usage remains high for some time afterward.
>This apparent idle time more than doubles the total amount taken to complet
e
>this DTS package. Do you know why this is so? I thought it might be time
>taken to build the indexes on each imported worksheet (I need to create eac
h
>table from scratch), or alternatively the time taken to establish
>communication with Excel. Any other ideas, and a suggestion on how to pick
>up the pace?!
>Best regards
>Loane
>|||thanks, will try this one, it's been really niggling me
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:3end71hhr8drpdqhs7kg8d4k6d99415lo3@.
4ax.com...
> You can use profiler to monitor what is going on - looking
> at what the process is doing especially at the end of the
> import and after the import, what the durations are for
> different steps and processes in the package.
> You could also monitor it more "manually" by querying
> sysprocesses and watching the wait types, activities, etc.
> If you are running SQL 2000, sp3 you can also use fn_get_sql
> to see what exactly it's executing at different points.
> -Sue
> On Mon, 2 May 2005 22:26:21 +0200, "Loane Sharp"
> <look_sharp_not@.hotmail.com> wrote:
>
>
DTS Packages (.dtsx) and Automation through C#
I have spent the last couple of weeks going through about 15-20 different examples/tutorials on running DTS packages from inside a C# Windows app (and even asp.net). I must have a mental block, because I can't seem to make it click in my head. I have hundreds of DTS packages saved as .dtsx files. I can double click on them and run them perfectly. What I need to do is be able to run them conditionally from inside a windows app. Could anyone point me to a tutorial or example that could help me with this? I've even looked at some VB source to see if I could work it out, but I keep running up against a wall. I'd appreciate any help anyone can provide.
I just realized, if there is a better forum for me to post this in, please let me know that also.
Wednesday, March 21, 2012
DTS package with inputs
Hi I am using mssql 2000 and have been performing a data port manually that
consists of running a query on an sql database (using a date range as an
input) and then connecting to an Oracle database and appending a table in the
Oracle database with the data from the SQL query. The SQL query writes the
data to a table and I then use access to connect to this table and the
destination table in the Oracle database and run a simple query inside of
access to append the data into the Oracle table. I would like to create a
dts package that does this but the initial query requires 2 input parameters,
start date and end date, anyhow just wondering if you can still build a dts
package that when run from Enterprise manager would prompt for the date
inputs and then perform the sql query as well as the data append to the
Oracle table.
Thanks.
Paul G
Software engineer.
Hi Paul
You can set global variables on the command line such as (this has wrapped):
DECLARE @.datefrom CHAR(10)
DECLARE @.dateto CHAR(10)
DECLARE @.cmd varchar(2200)
DECLARE @.cmdroot varchar(2000)
DECLARE @.stat int
SET @.cmdroot = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "' + DB_NAME()
+ ' Monthly Export" '
SELECT@.dateto =
CONVERT(CHAR(10),DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)),121),
@.datefrom =
CONVERT(CHAR(10),DATEADD(m,-1,DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME))),121)
SET @.cmd = @.cmdroot + ' /A "DateFrom":"7"="' + @.datefrom + '" /A
"DateTo":"7"="' + @.dateto + '"'
EXEC @.stat = master..xp_cmdshell @.cmd
SET @.stat = COALESCE(@.stat,@.@.ERROR)
John
"Paul" wrote:
> Hi I am using mssql 2000 and have been performing a data port manually that
> consists of running a query on an sql database (using a date range as an
> input) and then connecting to an Oracle database and appending a table in the
> Oracle database with the data from the SQL query. The SQL query writes the
> data to a table and I then use access to connect to this table and the
> destination table in the Oracle database and run a simple query inside of
> access to append the data into the Oracle table. I would like to create a
> dts package that does this but the initial query requires 2 input parameters,
> start date and end date, anyhow just wondering if you can still build a dts
> package that when run from Enterprise manager would prompt for the date
> inputs and then perform the sql query as well as the data append to the
> Oracle table.
> Thanks.
> --
> Paul G
> Software engineer.
|||Hi thanks for the response, for some reason I did not receive the
notification of replies. So would you place the variable declarations as
well as the provided code in the actual SQL job as the command in a step, or
would this be placed in one of the queries of the dts package?
Paul G
Software engineer.
"John Bell" wrote:
[vbcol=seagreen]
> Hi Paul
> You can set global variables on the command line such as (this has wrapped):
> DECLARE @.datefrom CHAR(10)
> DECLARE @.dateto CHAR(10)
> DECLARE @.cmd varchar(2200)
> DECLARE @.cmdroot varchar(2000)
> DECLARE @.stat int
> SET @.cmdroot = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "' + DB_NAME()
> + ' Monthly Export" '
> SELECT@.dateto =
> CONVERT(CHAR(10),DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)),121),
> @.datefrom =
> CONVERT(CHAR(10),DATEADD(m,-1,DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME))),121)
> SET @.cmd = @.cmdroot + ' /A "DateFrom":"7"="' + @.datefrom + '" /A
> "DateTo":"7"="' + @.dateto + '"'
> EXEC @.stat = master..xp_cmdshell @.cmd
> SET @.stat = COALESCE(@.stat,@.@.ERROR)
> John
> "Paul" wrote:
|||Hi Paul
The values would need to be global variables and to assign them withing the
job would require an activeX task. It is easier and probably a more flexible
design if you have a job that you pass the dates required to it. Then if at
some point you need a different date range or run it manually then it is easy
to do.
I would put the code into a stored procedure and then run that from the SQL
Agent job step.
If you are using stored procedures withing the DTS package then you can have
parameters that maped to the global variables see
http://www.sqldts.com/234.aspx
John
"Paul" wrote:
[vbcol=seagreen]
> Hi thanks for the response, for some reason I did not receive the
> notification of replies. So would you place the variable declarations as
> well as the provided code in the actual SQL job as the command in a step, or
> would this be placed in one of the queries of the dts package?
> --
> Paul G
> Software engineer.
>
> "John Bell" wrote:
|||ok thanks for the additional information. I will probably have to run this
manually for some time as it is necessary to perform some manual data checks
first. Eventually hope to automate and schedule the task. Will take a look
at the provided link.
Paul G
Software engineer.
"John Bell" wrote:
[vbcol=seagreen]
> Hi Paul
> The values would need to be global variables and to assign them withing the
> job would require an activeX task. It is easier and probably a more flexible
> design if you have a job that you pass the dates required to it. Then if at
> some point you need a different date range or run it manually then it is easy
> to do.
> I would put the code into a stored procedure and then run that from the SQL
> Agent job step.
> If you are using stored procedures withing the DTS package then you can have
> parameters that maped to the global variables see
> http://www.sqldts.com/234.aspx
>
> John
> "Paul" wrote:
consists of running a query on an sql database (using a date range as an
input) and then connecting to an Oracle database and appending a table in the
Oracle database with the data from the SQL query. The SQL query writes the
data to a table and I then use access to connect to this table and the
destination table in the Oracle database and run a simple query inside of
access to append the data into the Oracle table. I would like to create a
dts package that does this but the initial query requires 2 input parameters,
start date and end date, anyhow just wondering if you can still build a dts
package that when run from Enterprise manager would prompt for the date
inputs and then perform the sql query as well as the data append to the
Oracle table.
Thanks.
Paul G
Software engineer.
Hi Paul
You can set global variables on the command line such as (this has wrapped):
DECLARE @.datefrom CHAR(10)
DECLARE @.dateto CHAR(10)
DECLARE @.cmd varchar(2200)
DECLARE @.cmdroot varchar(2000)
DECLARE @.stat int
SET @.cmdroot = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "' + DB_NAME()
+ ' Monthly Export" '
SELECT@.dateto =
CONVERT(CHAR(10),DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)),121),
@.datefrom =
CONVERT(CHAR(10),DATEADD(m,-1,DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME))),121)
SET @.cmd = @.cmdroot + ' /A "DateFrom":"7"="' + @.datefrom + '" /A
"DateTo":"7"="' + @.dateto + '"'
EXEC @.stat = master..xp_cmdshell @.cmd
SET @.stat = COALESCE(@.stat,@.@.ERROR)
John
"Paul" wrote:
> Hi I am using mssql 2000 and have been performing a data port manually that
> consists of running a query on an sql database (using a date range as an
> input) and then connecting to an Oracle database and appending a table in the
> Oracle database with the data from the SQL query. The SQL query writes the
> data to a table and I then use access to connect to this table and the
> destination table in the Oracle database and run a simple query inside of
> access to append the data into the Oracle table. I would like to create a
> dts package that does this but the initial query requires 2 input parameters,
> start date and end date, anyhow just wondering if you can still build a dts
> package that when run from Enterprise manager would prompt for the date
> inputs and then perform the sql query as well as the data append to the
> Oracle table.
> Thanks.
> --
> Paul G
> Software engineer.
|||Hi thanks for the response, for some reason I did not receive the
notification of replies. So would you place the variable declarations as
well as the provided code in the actual SQL job as the command in a step, or
would this be placed in one of the queries of the dts package?
Paul G
Software engineer.
"John Bell" wrote:
[vbcol=seagreen]
> Hi Paul
> You can set global variables on the command line such as (this has wrapped):
> DECLARE @.datefrom CHAR(10)
> DECLARE @.dateto CHAR(10)
> DECLARE @.cmd varchar(2200)
> DECLARE @.cmdroot varchar(2000)
> DECLARE @.stat int
> SET @.cmdroot = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "' + DB_NAME()
> + ' Monthly Export" '
> SELECT@.dateto =
> CONVERT(CHAR(10),DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)),121),
> @.datefrom =
> CONVERT(CHAR(10),DATEADD(m,-1,DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME))),121)
> SET @.cmd = @.cmdroot + ' /A "DateFrom":"7"="' + @.datefrom + '" /A
> "DateTo":"7"="' + @.dateto + '"'
> EXEC @.stat = master..xp_cmdshell @.cmd
> SET @.stat = COALESCE(@.stat,@.@.ERROR)
> John
> "Paul" wrote:
|||Hi Paul
The values would need to be global variables and to assign them withing the
job would require an activeX task. It is easier and probably a more flexible
design if you have a job that you pass the dates required to it. Then if at
some point you need a different date range or run it manually then it is easy
to do.
I would put the code into a stored procedure and then run that from the SQL
Agent job step.
If you are using stored procedures withing the DTS package then you can have
parameters that maped to the global variables see
http://www.sqldts.com/234.aspx
John
"Paul" wrote:
[vbcol=seagreen]
> Hi thanks for the response, for some reason I did not receive the
> notification of replies. So would you place the variable declarations as
> well as the provided code in the actual SQL job as the command in a step, or
> would this be placed in one of the queries of the dts package?
> --
> Paul G
> Software engineer.
>
> "John Bell" wrote:
|||ok thanks for the additional information. I will probably have to run this
manually for some time as it is necessary to perform some manual data checks
first. Eventually hope to automate and schedule the task. Will take a look
at the provided link.
Paul G
Software engineer.
"John Bell" wrote:
[vbcol=seagreen]
> Hi Paul
> The values would need to be global variables and to assign them withing the
> job would require an activeX task. It is easier and probably a more flexible
> design if you have a job that you pass the dates required to it. Then if at
> some point you need a different date range or run it manually then it is easy
> to do.
> I would put the code into a stored procedure and then run that from the SQL
> Agent job step.
> If you are using stored procedures withing the DTS package then you can have
> parameters that maped to the global variables see
> http://www.sqldts.com/234.aspx
>
> John
> "Paul" wrote:
DTS package with inputs
Hi I am using mssql 2000 and have been performing a data port manually that
consists of running a query on an sql database (using a date range as an
input) and then connecting to an Oracle database and appending a table in the
Oracle database with the data from the SQL query. The SQL query writes the
data to a table and I then use access to connect to this table and the
destination table in the Oracle database and run a simple query inside of
access to append the data into the Oracle table. I would like to create a
dts package that does this but the initial query requires 2 input parameters,
start date and end date, anyhow just wondering if you can still build a dts
package that when run from Enterprise manager would prompt for the date
inputs and then perform the sql query as well as the data append to the
Oracle table.
Thanks.
--
Paul G
Software engineer.Hi Paul
You can set global variables on the command line such as (this has wrapped):
DECLARE @.datefrom CHAR(10)
DECLARE @.dateto CHAR(10)
DECLARE @.cmd varchar(2200)
DECLARE @.cmdroot varchar(2000)
DECLARE @.stat int
SET @.cmdroot = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "' + DB_NAME()
+ ' Monthly Export" '
SELECT @.dateto =CONVERT(CHAR(10),DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)),121),
@.datefrom =CONVERT(CHAR(10),DATEADD(m,-1,DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME))),121)
SET @.cmd = @.cmdroot + ' /A "DateFrom":"7"="' + @.datefrom + '" /A
"DateTo":"7"="' + @.dateto + '"'
EXEC @.stat = master..xp_cmdshell @.cmd
SET @.stat = COALESCE(@.stat,@.@.ERROR)
John
"Paul" wrote:
> Hi I am using mssql 2000 and have been performing a data port manually that
> consists of running a query on an sql database (using a date range as an
> input) and then connecting to an Oracle database and appending a table in the
> Oracle database with the data from the SQL query. The SQL query writes the
> data to a table and I then use access to connect to this table and the
> destination table in the Oracle database and run a simple query inside of
> access to append the data into the Oracle table. I would like to create a
> dts package that does this but the initial query requires 2 input parameters,
> start date and end date, anyhow just wondering if you can still build a dts
> package that when run from Enterprise manager would prompt for the date
> inputs and then perform the sql query as well as the data append to the
> Oracle table.
> Thanks.
> --
> Paul G
> Software engineer.|||Hi thanks for the response, for some reason I did not receive the
notification of replies. So would you place the variable declarations as
well as the provided code in the actual SQL job as the command in a step, or
would this be placed in one of the queries of the dts package?
--
Paul G
Software engineer.
"John Bell" wrote:
> Hi Paul
> You can set global variables on the command line such as (this has wrapped):
> DECLARE @.datefrom CHAR(10)
> DECLARE @.dateto CHAR(10)
> DECLARE @.cmd varchar(2200)
> DECLARE @.cmdroot varchar(2000)
> DECLARE @.stat int
> SET @.cmdroot = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "' + DB_NAME()
> + ' Monthly Export" '
> SELECT @.dateto => CONVERT(CHAR(10),DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)),121),
> @.datefrom => CONVERT(CHAR(10),DATEADD(m,-1,DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME))),121)
> SET @.cmd = @.cmdroot + ' /A "DateFrom":"7"="' + @.datefrom + '" /A
> "DateTo":"7"="' + @.dateto + '"'
> EXEC @.stat = master..xp_cmdshell @.cmd
> SET @.stat = COALESCE(@.stat,@.@.ERROR)
> John
> "Paul" wrote:
> > Hi I am using mssql 2000 and have been performing a data port manually that
> > consists of running a query on an sql database (using a date range as an
> > input) and then connecting to an Oracle database and appending a table in the
> > Oracle database with the data from the SQL query. The SQL query writes the
> > data to a table and I then use access to connect to this table and the
> > destination table in the Oracle database and run a simple query inside of
> > access to append the data into the Oracle table. I would like to create a
> > dts package that does this but the initial query requires 2 input parameters,
> > start date and end date, anyhow just wondering if you can still build a dts
> > package that when run from Enterprise manager would prompt for the date
> > inputs and then perform the sql query as well as the data append to the
> > Oracle table.
> > Thanks.
> > --
> > Paul G
> > Software engineer.|||Hi Paul
The values would need to be global variables and to assign them withing the
job would require an activeX task. It is easier and probably a more flexible
design if you have a job that you pass the dates required to it. Then if at
some point you need a different date range or run it manually then it is easy
to do.
I would put the code into a stored procedure and then run that from the SQL
Agent job step.
If you are using stored procedures withing the DTS package then you can have
parameters that maped to the global variables see
http://www.sqldts.com/234.aspx
John
"Paul" wrote:
> Hi thanks for the response, for some reason I did not receive the
> notification of replies. So would you place the variable declarations as
> well as the provided code in the actual SQL job as the command in a step, or
> would this be placed in one of the queries of the dts package?
> --
> Paul G
> Software engineer.
>
> "John Bell" wrote:
> > Hi Paul
> >
> > You can set global variables on the command line such as (this has wrapped):
> >
> > DECLARE @.datefrom CHAR(10)
> > DECLARE @.dateto CHAR(10)
> > DECLARE @.cmd varchar(2200)
> > DECLARE @.cmdroot varchar(2000)
> > DECLARE @.stat int
> >
> > SET @.cmdroot = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "' + DB_NAME()
> > + ' Monthly Export" '
> >
> > SELECT @.dateto => > CONVERT(CHAR(10),DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)),121),
> > @.datefrom => > CONVERT(CHAR(10),DATEADD(m,-1,DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME))),121)
> >
> > SET @.cmd = @.cmdroot + ' /A "DateFrom":"7"="' + @.datefrom + '" /A
> > "DateTo":"7"="' + @.dateto + '"'
> >
> > EXEC @.stat = master..xp_cmdshell @.cmd
> > SET @.stat = COALESCE(@.stat,@.@.ERROR)
> >
> > John
> >
> > "Paul" wrote:
> >
> > > Hi I am using mssql 2000 and have been performing a data port manually that
> > > consists of running a query on an sql database (using a date range as an
> > > input) and then connecting to an Oracle database and appending a table in the
> > > Oracle database with the data from the SQL query. The SQL query writes the
> > > data to a table and I then use access to connect to this table and the
> > > destination table in the Oracle database and run a simple query inside of
> > > access to append the data into the Oracle table. I would like to create a
> > > dts package that does this but the initial query requires 2 input parameters,
> > > start date and end date, anyhow just wondering if you can still build a dts
> > > package that when run from Enterprise manager would prompt for the date
> > > inputs and then perform the sql query as well as the data append to the
> > > Oracle table.
> > > Thanks.
> > > --
> > > Paul G
> > > Software engineer.|||ok thanks for the additional information. I will probably have to run this
manually for some time as it is necessary to perform some manual data checks
first. Eventually hope to automate and schedule the task. Will take a look
at the provided link.
--
Paul G
Software engineer.
"John Bell" wrote:
> Hi Paul
> The values would need to be global variables and to assign them withing the
> job would require an activeX task. It is easier and probably a more flexible
> design if you have a job that you pass the dates required to it. Then if at
> some point you need a different date range or run it manually then it is easy
> to do.
> I would put the code into a stored procedure and then run that from the SQL
> Agent job step.
> If you are using stored procedures withing the DTS package then you can have
> parameters that maped to the global variables see
> http://www.sqldts.com/234.aspx
>
> John
> "Paul" wrote:
> > Hi thanks for the response, for some reason I did not receive the
> > notification of replies. So would you place the variable declarations as
> > well as the provided code in the actual SQL job as the command in a step, or
> > would this be placed in one of the queries of the dts package?
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "John Bell" wrote:
> >
> > > Hi Paul
> > >
> > > You can set global variables on the command line such as (this has wrapped):
> > >
> > > DECLARE @.datefrom CHAR(10)
> > > DECLARE @.dateto CHAR(10)
> > > DECLARE @.cmd varchar(2200)
> > > DECLARE @.cmdroot varchar(2000)
> > > DECLARE @.stat int
> > >
> > > SET @.cmdroot = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "' + DB_NAME()
> > > + ' Monthly Export" '
> > >
> > > SELECT @.dateto => > > CONVERT(CHAR(10),DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)),121),
> > > @.datefrom => > > CONVERT(CHAR(10),DATEADD(m,-1,DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME))),121)
> > >
> > > SET @.cmd = @.cmdroot + ' /A "DateFrom":"7"="' + @.datefrom + '" /A
> > > "DateTo":"7"="' + @.dateto + '"'
> > >
> > > EXEC @.stat = master..xp_cmdshell @.cmd
> > > SET @.stat = COALESCE(@.stat,@.@.ERROR)
> > >
> > > John
> > >
> > > "Paul" wrote:
> > >
> > > > Hi I am using mssql 2000 and have been performing a data port manually that
> > > > consists of running a query on an sql database (using a date range as an
> > > > input) and then connecting to an Oracle database and appending a table in the
> > > > Oracle database with the data from the SQL query. The SQL query writes the
> > > > data to a table and I then use access to connect to this table and the
> > > > destination table in the Oracle database and run a simple query inside of
> > > > access to append the data into the Oracle table. I would like to create a
> > > > dts package that does this but the initial query requires 2 input parameters,
> > > > start date and end date, anyhow just wondering if you can still build a dts
> > > > package that when run from Enterprise manager would prompt for the date
> > > > inputs and then perform the sql query as well as the data append to the
> > > > Oracle table.
> > > > Thanks.
> > > > --
> > > > Paul G
> > > > Software engineer.
consists of running a query on an sql database (using a date range as an
input) and then connecting to an Oracle database and appending a table in the
Oracle database with the data from the SQL query. The SQL query writes the
data to a table and I then use access to connect to this table and the
destination table in the Oracle database and run a simple query inside of
access to append the data into the Oracle table. I would like to create a
dts package that does this but the initial query requires 2 input parameters,
start date and end date, anyhow just wondering if you can still build a dts
package that when run from Enterprise manager would prompt for the date
inputs and then perform the sql query as well as the data append to the
Oracle table.
Thanks.
--
Paul G
Software engineer.Hi Paul
You can set global variables on the command line such as (this has wrapped):
DECLARE @.datefrom CHAR(10)
DECLARE @.dateto CHAR(10)
DECLARE @.cmd varchar(2200)
DECLARE @.cmdroot varchar(2000)
DECLARE @.stat int
SET @.cmdroot = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "' + DB_NAME()
+ ' Monthly Export" '
SELECT @.dateto =CONVERT(CHAR(10),DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)),121),
@.datefrom =CONVERT(CHAR(10),DATEADD(m,-1,DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME))),121)
SET @.cmd = @.cmdroot + ' /A "DateFrom":"7"="' + @.datefrom + '" /A
"DateTo":"7"="' + @.dateto + '"'
EXEC @.stat = master..xp_cmdshell @.cmd
SET @.stat = COALESCE(@.stat,@.@.ERROR)
John
"Paul" wrote:
> Hi I am using mssql 2000 and have been performing a data port manually that
> consists of running a query on an sql database (using a date range as an
> input) and then connecting to an Oracle database and appending a table in the
> Oracle database with the data from the SQL query. The SQL query writes the
> data to a table and I then use access to connect to this table and the
> destination table in the Oracle database and run a simple query inside of
> access to append the data into the Oracle table. I would like to create a
> dts package that does this but the initial query requires 2 input parameters,
> start date and end date, anyhow just wondering if you can still build a dts
> package that when run from Enterprise manager would prompt for the date
> inputs and then perform the sql query as well as the data append to the
> Oracle table.
> Thanks.
> --
> Paul G
> Software engineer.|||Hi thanks for the response, for some reason I did not receive the
notification of replies. So would you place the variable declarations as
well as the provided code in the actual SQL job as the command in a step, or
would this be placed in one of the queries of the dts package?
--
Paul G
Software engineer.
"John Bell" wrote:
> Hi Paul
> You can set global variables on the command line such as (this has wrapped):
> DECLARE @.datefrom CHAR(10)
> DECLARE @.dateto CHAR(10)
> DECLARE @.cmd varchar(2200)
> DECLARE @.cmdroot varchar(2000)
> DECLARE @.stat int
> SET @.cmdroot = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "' + DB_NAME()
> + ' Monthly Export" '
> SELECT @.dateto => CONVERT(CHAR(10),DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)),121),
> @.datefrom => CONVERT(CHAR(10),DATEADD(m,-1,DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME))),121)
> SET @.cmd = @.cmdroot + ' /A "DateFrom":"7"="' + @.datefrom + '" /A
> "DateTo":"7"="' + @.dateto + '"'
> EXEC @.stat = master..xp_cmdshell @.cmd
> SET @.stat = COALESCE(@.stat,@.@.ERROR)
> John
> "Paul" wrote:
> > Hi I am using mssql 2000 and have been performing a data port manually that
> > consists of running a query on an sql database (using a date range as an
> > input) and then connecting to an Oracle database and appending a table in the
> > Oracle database with the data from the SQL query. The SQL query writes the
> > data to a table and I then use access to connect to this table and the
> > destination table in the Oracle database and run a simple query inside of
> > access to append the data into the Oracle table. I would like to create a
> > dts package that does this but the initial query requires 2 input parameters,
> > start date and end date, anyhow just wondering if you can still build a dts
> > package that when run from Enterprise manager would prompt for the date
> > inputs and then perform the sql query as well as the data append to the
> > Oracle table.
> > Thanks.
> > --
> > Paul G
> > Software engineer.|||Hi Paul
The values would need to be global variables and to assign them withing the
job would require an activeX task. It is easier and probably a more flexible
design if you have a job that you pass the dates required to it. Then if at
some point you need a different date range or run it manually then it is easy
to do.
I would put the code into a stored procedure and then run that from the SQL
Agent job step.
If you are using stored procedures withing the DTS package then you can have
parameters that maped to the global variables see
http://www.sqldts.com/234.aspx
John
"Paul" wrote:
> Hi thanks for the response, for some reason I did not receive the
> notification of replies. So would you place the variable declarations as
> well as the provided code in the actual SQL job as the command in a step, or
> would this be placed in one of the queries of the dts package?
> --
> Paul G
> Software engineer.
>
> "John Bell" wrote:
> > Hi Paul
> >
> > You can set global variables on the command line such as (this has wrapped):
> >
> > DECLARE @.datefrom CHAR(10)
> > DECLARE @.dateto CHAR(10)
> > DECLARE @.cmd varchar(2200)
> > DECLARE @.cmdroot varchar(2000)
> > DECLARE @.stat int
> >
> > SET @.cmdroot = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "' + DB_NAME()
> > + ' Monthly Export" '
> >
> > SELECT @.dateto => > CONVERT(CHAR(10),DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)),121),
> > @.datefrom => > CONVERT(CHAR(10),DATEADD(m,-1,DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME))),121)
> >
> > SET @.cmd = @.cmdroot + ' /A "DateFrom":"7"="' + @.datefrom + '" /A
> > "DateTo":"7"="' + @.dateto + '"'
> >
> > EXEC @.stat = master..xp_cmdshell @.cmd
> > SET @.stat = COALESCE(@.stat,@.@.ERROR)
> >
> > John
> >
> > "Paul" wrote:
> >
> > > Hi I am using mssql 2000 and have been performing a data port manually that
> > > consists of running a query on an sql database (using a date range as an
> > > input) and then connecting to an Oracle database and appending a table in the
> > > Oracle database with the data from the SQL query. The SQL query writes the
> > > data to a table and I then use access to connect to this table and the
> > > destination table in the Oracle database and run a simple query inside of
> > > access to append the data into the Oracle table. I would like to create a
> > > dts package that does this but the initial query requires 2 input parameters,
> > > start date and end date, anyhow just wondering if you can still build a dts
> > > package that when run from Enterprise manager would prompt for the date
> > > inputs and then perform the sql query as well as the data append to the
> > > Oracle table.
> > > Thanks.
> > > --
> > > Paul G
> > > Software engineer.|||ok thanks for the additional information. I will probably have to run this
manually for some time as it is necessary to perform some manual data checks
first. Eventually hope to automate and schedule the task. Will take a look
at the provided link.
--
Paul G
Software engineer.
"John Bell" wrote:
> Hi Paul
> The values would need to be global variables and to assign them withing the
> job would require an activeX task. It is easier and probably a more flexible
> design if you have a job that you pass the dates required to it. Then if at
> some point you need a different date range or run it manually then it is easy
> to do.
> I would put the code into a stored procedure and then run that from the SQL
> Agent job step.
> If you are using stored procedures withing the DTS package then you can have
> parameters that maped to the global variables see
> http://www.sqldts.com/234.aspx
>
> John
> "Paul" wrote:
> > Hi thanks for the response, for some reason I did not receive the
> > notification of replies. So would you place the variable declarations as
> > well as the provided code in the actual SQL job as the command in a step, or
> > would this be placed in one of the queries of the dts package?
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "John Bell" wrote:
> >
> > > Hi Paul
> > >
> > > You can set global variables on the command line such as (this has wrapped):
> > >
> > > DECLARE @.datefrom CHAR(10)
> > > DECLARE @.dateto CHAR(10)
> > > DECLARE @.cmd varchar(2200)
> > > DECLARE @.cmdroot varchar(2000)
> > > DECLARE @.stat int
> > >
> > > SET @.cmdroot = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "' + DB_NAME()
> > > + ' Monthly Export" '
> > >
> > > SELECT @.dateto => > > CONVERT(CHAR(10),DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)),121),
> > > @.datefrom => > > CONVERT(CHAR(10),DATEADD(m,-1,DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME))),121)
> > >
> > > SET @.cmd = @.cmdroot + ' /A "DateFrom":"7"="' + @.datefrom + '" /A
> > > "DateTo":"7"="' + @.dateto + '"'
> > >
> > > EXEC @.stat = master..xp_cmdshell @.cmd
> > > SET @.stat = COALESCE(@.stat,@.@.ERROR)
> > >
> > > John
> > >
> > > "Paul" wrote:
> > >
> > > > Hi I am using mssql 2000 and have been performing a data port manually that
> > > > consists of running a query on an sql database (using a date range as an
> > > > input) and then connecting to an Oracle database and appending a table in the
> > > > Oracle database with the data from the SQL query. The SQL query writes the
> > > > data to a table and I then use access to connect to this table and the
> > > > destination table in the Oracle database and run a simple query inside of
> > > > access to append the data into the Oracle table. I would like to create a
> > > > dts package that does this but the initial query requires 2 input parameters,
> > > > start date and end date, anyhow just wondering if you can still build a dts
> > > > package that when run from Enterprise manager would prompt for the date
> > > > inputs and then perform the sql query as well as the data append to the
> > > > Oracle table.
> > > > Thanks.
> > > > --
> > > > Paul G
> > > > Software engineer.
DTS Package with Dynamic Properties running Analysis Services Processing
Hi,
I wasn't sure of the location of this as it spans quite a few areas.
I have created a DTS package with Dynamic Parameters. The package is going to process the Dimensions of a cube therefore I have a dynamic property :
DimFolder -> String -> myServer\myCube\DimFolder
Which sets the TreeKey of the Analysis Services Processing Task to set the DimFolder to the correct server.
When I click on the parameters task and run is it works fine. If I then click on all the individual tasks within the DTS they all work fine as well.
However, when I click the play button to run the whole package is one go, I get
"Need to run the object to perform this operations. Code execution exception: EXCEPTION_ACCESS_VIOLATION".
I can't understand how this can happen as clicking each individual task and running each individual task works fine.
Any suggestions...
Jayne
Hi Jayne,
You may be encountering the problem described below, so try setting the Workflow Poperties of the Dynamic Properties task to "Execute on Main Thread":
http://support.microsoft.com/default.aspx?scid=kb;en-us;282966
>>
DTS Package Fails with Dynamic Properties Task and OLAP Processing Task
View products that this article applies to.
This article was previously published under Q282966
SYMPTOMS
When you process a Data Transformation Services (DTS) package that contains both a Dynamic Properties task and an OLAP Processing task, the DTS package may fail during execution of the Dynamic Properties task with the following error message:
1 task(s) failed during execution
When you double-click the failed Dynamic Properties Task, the following error dialog is displayed:Code execution exception: EXCEPTION_ACCESS_VIOLATION
CAUSE
The Dynamic Property task is used to set properties on other tasks, and the Analysis Services/OLAP processing task is a Single Threaded Apartment (STA) component. When the Dynamic Property task tries to set the properties on the STA based component, the Dynamic Property task must run on the main thread to set the properties of the STA based task.
>>
|||Also you may find that you have to use a scripting task rather than the dynamic properties task. Because of some binding issues I found when I was writing SSABI that I had to use a script task and walk the object model by-hand that the dynamic properties task returned errors , even when it was set to run on the main thread. You mileage may vary, but if the main thread setting doesn't work, try using a script task.
_-_-_ Dave
Monday, March 19, 2012
DTS package Scheduled Job does not run correctly
Hi
I have a DTS package that is running a VB script that reads all the
files from a folder one by one and processes them. After a file is
processed - file is read and the content entered in a table in one of
the databases - the file is moved to a different location.
The DTS package runs wonderfully if I run it manually (right click
Execute Package).
I scheduled this DTS package to run every hour (right click Schedule
Package and a job was created).
The corresponding job finishes successfull, my files are even moved to
a different location but no data is entered in my table.
The SQL Agent runs under an account that is system admin for our
network.
The job is owned by the same account.
Please help.
Thanks,
CarlyAre you using workflow, in your DTS package. If you are trying to execute any
step after inserting data into table try to do it 'OnSuccess' workflow and
see if it gets executed successfully.
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Carly" wrote:
> Hi
> I have a DTS package that is running a VB script that reads all the
> files from a folder one by one and processes them. After a file is
> processed - file is read and the content entered in a table in one of
> the databases - the file is moved to a different location.
> The DTS package runs wonderfully if I run it manually (right click
> Execute Package).
> I scheduled this DTS package to run every hour (right click Schedule
> Package and a job was created).
> The corresponding job finishes successfull, my files are even moved to
> a different location but no data is entered in my table.
> The SQL Agent runs under an account that is system admin for our
> network.
> The job is owned by the same account.
> Please help.
> Thanks,
> Carly
>
I have a DTS package that is running a VB script that reads all the
files from a folder one by one and processes them. After a file is
processed - file is read and the content entered in a table in one of
the databases - the file is moved to a different location.
The DTS package runs wonderfully if I run it manually (right click
Execute Package).
I scheduled this DTS package to run every hour (right click Schedule
Package and a job was created).
The corresponding job finishes successfull, my files are even moved to
a different location but no data is entered in my table.
The SQL Agent runs under an account that is system admin for our
network.
The job is owned by the same account.
Please help.
Thanks,
CarlyAre you using workflow, in your DTS package. If you are trying to execute any
step after inserting data into table try to do it 'OnSuccess' workflow and
see if it gets executed successfully.
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Carly" wrote:
> Hi
> I have a DTS package that is running a VB script that reads all the
> files from a folder one by one and processes them. After a file is
> processed - file is read and the content entered in a table in one of
> the databases - the file is moved to a different location.
> The DTS package runs wonderfully if I run it manually (right click
> Execute Package).
> I scheduled this DTS package to run every hour (right click Schedule
> Package and a job was created).
> The corresponding job finishes successfull, my files are even moved to
> a different location but no data is entered in my table.
> The SQL Agent runs under an account that is system admin for our
> network.
> The job is owned by the same account.
> Please help.
> Thanks,
> Carly
>
DTS Package problem...
Hi all
SQL 2k (dev.ed.)
I've got a problem with a DTS package. I have it running at 9am every
morning to copy DB tables/stored procs etc from a server other side of
the world to my local server. This has been working well for a couple
months now...
I had to reinstall windows and the local DB server and now I can't get
the DTS package working again.
I have it set up to copy from src to dest as normal - it seems to copy
all the tables, index, stored procs, functions, triggers... the only
thing that it is NOT coping is the actual DATA in the TABLES.
I have checked & double checked and the check box IS marked to copy
data, I've tried both sub options of replace & Append data, but no
luck...
Anyone got ANY ideas on why this is happening?
Thanks
Al
Harag
First, you can SQL Server Profiler to see what is going on during the
package's execution.
Second, would it be much better to backup/restore the database on
destination server rather to copy tables/store procedures?
"Harag" <haragREMOVECAPITALS@.softhome.net> wrote in message
news:ohftk0toad0rska9eaktlgjhflsq5bi84d@.4ax.com...
> Hi all
> SQL 2k (dev.ed.)
> I've got a problem with a DTS package. I have it running at 9am every
> morning to copy DB tables/stored procs etc from a server other side of
> the world to my local server. This has been working well for a couple
> months now...
> I had to reinstall windows and the local DB server and now I can't get
> the DTS package working again.
> I have it set up to copy from src to dest as normal - it seems to copy
> all the tables, index, stored procs, functions, triggers... the only
> thing that it is NOT coping is the actual DATA in the TABLES.
> I have checked & double checked and the check box IS marked to copy
> data, I've tried both sub options of replace & Append data, but no
> luck...
> Anyone got ANY ideas on why this is happening?
> Thanks
> Al
|||On Mon, 20 Sep 2004 14:44:07 +0200, "Uri Dimant" <urid@.iscar.co.il>
wrote:
>Harag
>First, you can SQL Server Profiler to see what is going on during the
>package's execution.
Hmm, How do I do that with the profiler? I'm kinda new to that and not
really sure on what to do with it.
>Second, would it be much better to backup/restore the database on
>destination server rather to copy tables/store procedures?
the DB on the other side of the world is a 3rd party host (they host
the website) so they have they own backups etc. But I like to take a
daily copy of the tables and a weekly copy of the stored procs for my
own backups... I can also use "live" data for further testing of new
features to the site rather than made up data.
Thanks again.
Al.
>
>"Harag" <haragREMOVECAPITALS@.softhome.net> wrote in message
>news:ohftk0toad0rska9eaktlgjhflsq5bi84d@.4ax.com.. .
>
|||On Mon, 20 Sep 2004 14:14:08 +0100, Harag
<haragREMOVECAPITALS@.softhome.net> wrote:
>On Mon, 20 Sep 2004 14:44:07 +0200, "Uri Dimant" <urid@.iscar.co.il>
>wrote:
>
>Hmm, How do I do that with the profiler? I'm kinda new to that and not
>really sure on what to do with it.
>
ok had a quick play with Profiler - setting for standard then ran the
DTS package... all seems ok to me in the profiler except for one area
where I think the problem is:
it has the following lines:
use master exec sp_dboption N'OnlineBackup', N'select into/bulkcopy',
N'true' use [OnlineBackup] checkpoint
In between these 2 lines there is 7 rows that all say "use
OnlineBackup"... thats it, nothing else. (btw there is currently 7
tables)
use master exec sp_dboption N'OnlineBackup', N'select into/bulkcopy',
N'false' use [OnlineBackup] checkpoint
[vbcol=seagreen]
>the DB on the other side of the world is a 3rd party host (they host
>the website) so they have they own backups etc. But I like to take a
>daily copy of the tables and a weekly copy of the stored procs for my
>own backups... I can also use "live" data for further testing of new
>features to the site rather than made up data.
>Thanks again.
>Al.
SQL 2k (dev.ed.)
I've got a problem with a DTS package. I have it running at 9am every
morning to copy DB tables/stored procs etc from a server other side of
the world to my local server. This has been working well for a couple
months now...
I had to reinstall windows and the local DB server and now I can't get
the DTS package working again.
I have it set up to copy from src to dest as normal - it seems to copy
all the tables, index, stored procs, functions, triggers... the only
thing that it is NOT coping is the actual DATA in the TABLES.
I have checked & double checked and the check box IS marked to copy
data, I've tried both sub options of replace & Append data, but no
luck...
Anyone got ANY ideas on why this is happening?
Thanks
Al
Harag
First, you can SQL Server Profiler to see what is going on during the
package's execution.
Second, would it be much better to backup/restore the database on
destination server rather to copy tables/store procedures?
"Harag" <haragREMOVECAPITALS@.softhome.net> wrote in message
news:ohftk0toad0rska9eaktlgjhflsq5bi84d@.4ax.com...
> Hi all
> SQL 2k (dev.ed.)
> I've got a problem with a DTS package. I have it running at 9am every
> morning to copy DB tables/stored procs etc from a server other side of
> the world to my local server. This has been working well for a couple
> months now...
> I had to reinstall windows and the local DB server and now I can't get
> the DTS package working again.
> I have it set up to copy from src to dest as normal - it seems to copy
> all the tables, index, stored procs, functions, triggers... the only
> thing that it is NOT coping is the actual DATA in the TABLES.
> I have checked & double checked and the check box IS marked to copy
> data, I've tried both sub options of replace & Append data, but no
> luck...
> Anyone got ANY ideas on why this is happening?
> Thanks
> Al
|||On Mon, 20 Sep 2004 14:44:07 +0200, "Uri Dimant" <urid@.iscar.co.il>
wrote:
>Harag
>First, you can SQL Server Profiler to see what is going on during the
>package's execution.
Hmm, How do I do that with the profiler? I'm kinda new to that and not
really sure on what to do with it.
>Second, would it be much better to backup/restore the database on
>destination server rather to copy tables/store procedures?
the DB on the other side of the world is a 3rd party host (they host
the website) so they have they own backups etc. But I like to take a
daily copy of the tables and a weekly copy of the stored procs for my
own backups... I can also use "live" data for further testing of new
features to the site rather than made up data.
Thanks again.
Al.
>
>"Harag" <haragREMOVECAPITALS@.softhome.net> wrote in message
>news:ohftk0toad0rska9eaktlgjhflsq5bi84d@.4ax.com.. .
>
|||On Mon, 20 Sep 2004 14:14:08 +0100, Harag
<haragREMOVECAPITALS@.softhome.net> wrote:
>On Mon, 20 Sep 2004 14:44:07 +0200, "Uri Dimant" <urid@.iscar.co.il>
>wrote:
>
>Hmm, How do I do that with the profiler? I'm kinda new to that and not
>really sure on what to do with it.
>
ok had a quick play with Profiler - setting for standard then ran the
DTS package... all seems ok to me in the profiler except for one area
where I think the problem is:
it has the following lines:
use master exec sp_dboption N'OnlineBackup', N'select into/bulkcopy',
N'true' use [OnlineBackup] checkpoint
In between these 2 lines there is 7 rows that all say "use
OnlineBackup"... thats it, nothing else. (btw there is currently 7
tables)
use master exec sp_dboption N'OnlineBackup', N'select into/bulkcopy',
N'false' use [OnlineBackup] checkpoint
[vbcol=seagreen]
>the DB on the other side of the world is a 3rd party host (they host
>the website) so they have they own backups etc. But I like to take a
>daily copy of the tables and a weekly copy of the stored procs for my
>own backups... I can also use "live" data for further testing of new
>features to the site rather than made up data.
>Thanks again.
>Al.
DTS Package problem...
Hi all
SQL 2k (dev.ed.)
I've got a problem with a DTS package. I have it running at 9am every
morning to copy DB tables/stored procs etc from a server other side of
the world to my local server. This has been working well for a couple
months now...
I had to reinstall windows and the local DB server and now I can't get
the DTS package working again.
I have it set up to copy from src to dest as normal - it seems to copy
all the tables, index, stored procs, functions, triggers... the only
thing that it is NOT coping is the actual DATA in the TABLES.
I have checked & double checked and the check box IS marked to copy
data, I've tried both sub options of replace & Append data, but no
luck...
Anyone got ANY ideas on why this is happening?
Thanks
AlHarag
First, you can SQL Server Profiler to see what is going on during the
package's execution.
Second, would it be much better to backup/restore the database on
destination server rather to copy tables/store procedures?
"Harag" <haragREMOVECAPITALS@.softhome.net> wrote in message
news:ohftk0toad0rska9eaktlgjhflsq5bi84d@.4ax.com...
> Hi all
> SQL 2k (dev.ed.)
> I've got a problem with a DTS package. I have it running at 9am every
> morning to copy DB tables/stored procs etc from a server other side of
> the world to my local server. This has been working well for a couple
> months now...
> I had to reinstall windows and the local DB server and now I can't get
> the DTS package working again.
> I have it set up to copy from src to dest as normal - it seems to copy
> all the tables, index, stored procs, functions, triggers... the only
> thing that it is NOT coping is the actual DATA in the TABLES.
> I have checked & double checked and the check box IS marked to copy
> data, I've tried both sub options of replace & Append data, but no
> luck...
> Anyone got ANY ideas on why this is happening?
> Thanks
> Al|||On Mon, 20 Sep 2004 14:44:07 +0200, "Uri Dimant" <urid@.iscar.co.il>
wrote:
>Harag
>First, you can SQL Server Profiler to see what is going on during the
>package's execution.
Hmm, How do I do that with the profiler? I'm kinda new to that and not
really sure on what to do with it.
>Second, would it be much better to backup/restore the database on
>destination server rather to copy tables/store procedures?
the DB on the other side of the world is a 3rd party host (they host
the website) so they have they own backups etc. But I like to take a
daily copy of the tables and a weekly copy of the stored procs for my
own backups... I can also use "live" data for further testing of new
features to the site rather than made up data.
Thanks again.
Al.
>
>"Harag" <haragREMOVECAPITALS@.softhome.net> wrote in message
>news:ohftk0toad0rska9eaktlgjhflsq5bi84d@.4ax.com...
>> Hi all
>> SQL 2k (dev.ed.)
>> I've got a problem with a DTS package. I have it running at 9am every
>> morning to copy DB tables/stored procs etc from a server other side of
>> the world to my local server. This has been working well for a couple
>> months now...
>> I had to reinstall windows and the local DB server and now I can't get
>> the DTS package working again.
>> I have it set up to copy from src to dest as normal - it seems to copy
>> all the tables, index, stored procs, functions, triggers... the only
>> thing that it is NOT coping is the actual DATA in the TABLES.
>> I have checked & double checked and the check box IS marked to copy
>> data, I've tried both sub options of replace & Append data, but no
>> luck...
>> Anyone got ANY ideas on why this is happening?
>> Thanks
>> Al
>|||On Mon, 20 Sep 2004 14:14:08 +0100, Harag
<haragREMOVECAPITALS@.softhome.net> wrote:
>On Mon, 20 Sep 2004 14:44:07 +0200, "Uri Dimant" <urid@.iscar.co.il>
>wrote:
>>Harag
>>First, you can SQL Server Profiler to see what is going on during the
>>package's execution.
>Hmm, How do I do that with the profiler? I'm kinda new to that and not
>really sure on what to do with it.
>
ok had a quick play with Profiler - setting for standard then ran the
DTS package... all seems ok to me in the profiler except for one area
where I think the problem is:
it has the following lines:
use master exec sp_dboption N'OnlineBackup', N'select into/bulkcopy',
N'true' use [OnlineBackup] checkpoint
In between these 2 lines there is 7 rows that all say "use
OnlineBackup"... thats it, nothing else. (btw there is currently 7
tables)
use master exec sp_dboption N'OnlineBackup', N'select into/bulkcopy',
N'false' use [OnlineBackup] checkpoint
>>Second, would it be much better to backup/restore the database on
>>destination server rather to copy tables/store procedures?
>the DB on the other side of the world is a 3rd party host (they host
>the website) so they have they own backups etc. But I like to take a
>daily copy of the tables and a weekly copy of the stored procs for my
>own backups... I can also use "live" data for further testing of new
>features to the site rather than made up data.
>Thanks again.
>Al.
>>
>>"Harag" <haragREMOVECAPITALS@.softhome.net> wrote in message
>>news:ohftk0toad0rska9eaktlgjhflsq5bi84d@.4ax.com...
>> Hi all
>> SQL 2k (dev.ed.)
>> I've got a problem with a DTS package. I have it running at 9am every
>> morning to copy DB tables/stored procs etc from a server other side of
>> the world to my local server. This has been working well for a couple
>> months now...
>> I had to reinstall windows and the local DB server and now I can't get
>> the DTS package working again.
>> I have it set up to copy from src to dest as normal - it seems to copy
>> all the tables, index, stored procs, functions, triggers... the only
>> thing that it is NOT coping is the actual DATA in the TABLES.
>> I have checked & double checked and the check box IS marked to copy
>> data, I've tried both sub options of replace & Append data, but no
>> luck...
>> Anyone got ANY ideas on why this is happening?
>> Thanks
>> Al
SQL 2k (dev.ed.)
I've got a problem with a DTS package. I have it running at 9am every
morning to copy DB tables/stored procs etc from a server other side of
the world to my local server. This has been working well for a couple
months now...
I had to reinstall windows and the local DB server and now I can't get
the DTS package working again.
I have it set up to copy from src to dest as normal - it seems to copy
all the tables, index, stored procs, functions, triggers... the only
thing that it is NOT coping is the actual DATA in the TABLES.
I have checked & double checked and the check box IS marked to copy
data, I've tried both sub options of replace & Append data, but no
luck...
Anyone got ANY ideas on why this is happening?
Thanks
AlHarag
First, you can SQL Server Profiler to see what is going on during the
package's execution.
Second, would it be much better to backup/restore the database on
destination server rather to copy tables/store procedures?
"Harag" <haragREMOVECAPITALS@.softhome.net> wrote in message
news:ohftk0toad0rska9eaktlgjhflsq5bi84d@.4ax.com...
> Hi all
> SQL 2k (dev.ed.)
> I've got a problem with a DTS package. I have it running at 9am every
> morning to copy DB tables/stored procs etc from a server other side of
> the world to my local server. This has been working well for a couple
> months now...
> I had to reinstall windows and the local DB server and now I can't get
> the DTS package working again.
> I have it set up to copy from src to dest as normal - it seems to copy
> all the tables, index, stored procs, functions, triggers... the only
> thing that it is NOT coping is the actual DATA in the TABLES.
> I have checked & double checked and the check box IS marked to copy
> data, I've tried both sub options of replace & Append data, but no
> luck...
> Anyone got ANY ideas on why this is happening?
> Thanks
> Al|||On Mon, 20 Sep 2004 14:44:07 +0200, "Uri Dimant" <urid@.iscar.co.il>
wrote:
>Harag
>First, you can SQL Server Profiler to see what is going on during the
>package's execution.
Hmm, How do I do that with the profiler? I'm kinda new to that and not
really sure on what to do with it.
>Second, would it be much better to backup/restore the database on
>destination server rather to copy tables/store procedures?
the DB on the other side of the world is a 3rd party host (they host
the website) so they have they own backups etc. But I like to take a
daily copy of the tables and a weekly copy of the stored procs for my
own backups... I can also use "live" data for further testing of new
features to the site rather than made up data.
Thanks again.
Al.
>
>"Harag" <haragREMOVECAPITALS@.softhome.net> wrote in message
>news:ohftk0toad0rska9eaktlgjhflsq5bi84d@.4ax.com...
>> Hi all
>> SQL 2k (dev.ed.)
>> I've got a problem with a DTS package. I have it running at 9am every
>> morning to copy DB tables/stored procs etc from a server other side of
>> the world to my local server. This has been working well for a couple
>> months now...
>> I had to reinstall windows and the local DB server and now I can't get
>> the DTS package working again.
>> I have it set up to copy from src to dest as normal - it seems to copy
>> all the tables, index, stored procs, functions, triggers... the only
>> thing that it is NOT coping is the actual DATA in the TABLES.
>> I have checked & double checked and the check box IS marked to copy
>> data, I've tried both sub options of replace & Append data, but no
>> luck...
>> Anyone got ANY ideas on why this is happening?
>> Thanks
>> Al
>|||On Mon, 20 Sep 2004 14:14:08 +0100, Harag
<haragREMOVECAPITALS@.softhome.net> wrote:
>On Mon, 20 Sep 2004 14:44:07 +0200, "Uri Dimant" <urid@.iscar.co.il>
>wrote:
>>Harag
>>First, you can SQL Server Profiler to see what is going on during the
>>package's execution.
>Hmm, How do I do that with the profiler? I'm kinda new to that and not
>really sure on what to do with it.
>
ok had a quick play with Profiler - setting for standard then ran the
DTS package... all seems ok to me in the profiler except for one area
where I think the problem is:
it has the following lines:
use master exec sp_dboption N'OnlineBackup', N'select into/bulkcopy',
N'true' use [OnlineBackup] checkpoint
In between these 2 lines there is 7 rows that all say "use
OnlineBackup"... thats it, nothing else. (btw there is currently 7
tables)
use master exec sp_dboption N'OnlineBackup', N'select into/bulkcopy',
N'false' use [OnlineBackup] checkpoint
>>Second, would it be much better to backup/restore the database on
>>destination server rather to copy tables/store procedures?
>the DB on the other side of the world is a 3rd party host (they host
>the website) so they have they own backups etc. But I like to take a
>daily copy of the tables and a weekly copy of the stored procs for my
>own backups... I can also use "live" data for further testing of new
>features to the site rather than made up data.
>Thanks again.
>Al.
>>
>>"Harag" <haragREMOVECAPITALS@.softhome.net> wrote in message
>>news:ohftk0toad0rska9eaktlgjhflsq5bi84d@.4ax.com...
>> Hi all
>> SQL 2k (dev.ed.)
>> I've got a problem with a DTS package. I have it running at 9am every
>> morning to copy DB tables/stored procs etc from a server other side of
>> the world to my local server. This has been working well for a couple
>> months now...
>> I had to reinstall windows and the local DB server and now I can't get
>> the DTS package working again.
>> I have it set up to copy from src to dest as normal - it seems to copy
>> all the tables, index, stored procs, functions, triggers... the only
>> thing that it is NOT coping is the actual DATA in the TABLES.
>> I have checked & double checked and the check box IS marked to copy
>> data, I've tried both sub options of replace & Append data, but no
>> luck...
>> Anyone got ANY ideas on why this is happening?
>> Thanks
>> Al
DTS Package Not Running When Schedlued
I have been having a problem where I can not get a scheduled DTS pachkage t
o
run. It fails at all launch intervals. The Job can be launched manually and
it will import the data just fine. I am trying to import foxpro files into
the SQL server. Is this a permissions issue? What persmissions are passed
from SQL to the other server I am trying to import from?
Thanks
--
MitchTry granting permissions to the folder that contains the import FoxPro files
to the Windows account that the SQL Server and SQL Server Agent services are
starting under.
HTH
Jerry
"Mitchm" <Mitchm@.discussions.microsoft.com> wrote in message
news:4D64AB57-815C-4A91-87C2-9B5AFF3D26F5@.microsoft.com...
>I have been having a problem where I can not get a scheduled DTS pachkage
>to
> run. It fails at all launch intervals. The Job can be launched manually
> and
> it will import the data just fine. I am trying to import foxpro files into
> the SQL server. Is this a permissions issue? What persmissions are passed
> from SQL to the other server I am trying to import from?
> Thanks
> --
> Mitch|||Running from the scheduler, your DTS package is going to run under the
context of the user that SQL Agent is running under. Check the
properties of SQL Agent, see what user the service is running as. That
user needs to have access to the share that your Foxpro database is on.
o
run. It fails at all launch intervals. The Job can be launched manually and
it will import the data just fine. I am trying to import foxpro files into
the SQL server. Is this a permissions issue? What persmissions are passed
from SQL to the other server I am trying to import from?
Thanks
--
MitchTry granting permissions to the folder that contains the import FoxPro files
to the Windows account that the SQL Server and SQL Server Agent services are
starting under.
HTH
Jerry
"Mitchm" <Mitchm@.discussions.microsoft.com> wrote in message
news:4D64AB57-815C-4A91-87C2-9B5AFF3D26F5@.microsoft.com...
>I have been having a problem where I can not get a scheduled DTS pachkage
>to
> run. It fails at all launch intervals. The Job can be launched manually
> and
> it will import the data just fine. I am trying to import foxpro files into
> the SQL server. Is this a permissions issue? What persmissions are passed
> from SQL to the other server I am trying to import from?
> Thanks
> --
> Mitch|||Running from the scheduler, your DTS package is going to run under the
context of the user that SQL Agent is running under. Check the
properties of SQL Agent, see what user the service is running as. That
user needs to have access to the share that your Foxpro database is on.
DTS Package Not Running When Schedlued
I have been having a problem where I can not get a scheduled DTS pachkage to
run. It fails at all launch intervals. The Job can be launched manually and
it will import the data just fine. I am trying to import foxpro files into
the SQL server. Is this a permissions issue? What persmissions are passed
from SQL to the other server I am trying to import from?
Thanks
--
MitchTry granting permissions to the folder that contains the import FoxPro files
to the Windows account that the SQL Server and SQL Server Agent services are
starting under.
HTH
Jerry
"Mitchm" <Mitchm@.discussions.microsoft.com> wrote in message
news:4D64AB57-815C-4A91-87C2-9B5AFF3D26F5@.microsoft.com...
>I have been having a problem where I can not get a scheduled DTS pachkage
>to
> run. It fails at all launch intervals. The Job can be launched manually
> and
> it will import the data just fine. I am trying to import foxpro files into
> the SQL server. Is this a permissions issue? What persmissions are passed
> from SQL to the other server I am trying to import from?
> Thanks
> --
> Mitch|||Running from the scheduler, your DTS package is going to run under the
context of the user that SQL Agent is running under. Check the
properties of SQL Agent, see what user the service is running as. That
user needs to have access to the share that your Foxpro database is on.
run. It fails at all launch intervals. The Job can be launched manually and
it will import the data just fine. I am trying to import foxpro files into
the SQL server. Is this a permissions issue? What persmissions are passed
from SQL to the other server I am trying to import from?
Thanks
--
MitchTry granting permissions to the folder that contains the import FoxPro files
to the Windows account that the SQL Server and SQL Server Agent services are
starting under.
HTH
Jerry
"Mitchm" <Mitchm@.discussions.microsoft.com> wrote in message
news:4D64AB57-815C-4A91-87C2-9B5AFF3D26F5@.microsoft.com...
>I have been having a problem where I can not get a scheduled DTS pachkage
>to
> run. It fails at all launch intervals. The Job can be launched manually
> and
> it will import the data just fine. I am trying to import foxpro files into
> the SQL server. Is this a permissions issue? What persmissions are passed
> from SQL to the other server I am trying to import from?
> Thanks
> --
> Mitch|||Running from the scheduler, your DTS package is going to run under the
context of the user that SQL Agent is running under. Check the
properties of SQL Agent, see what user the service is running as. That
user needs to have access to the share that your Foxpro database is on.
Sunday, March 11, 2012
DTS Package Error Executing in SQL Agent
Hi everyone,
I just got assigned to help out with a couple of problems with the running of DTS packages using the SQL Server Agent, this is the problem and a list of what I've done so far:
There are 4 packages that:
a) Deletes all records from a table and then populates it using a flat file that contains that day's transactions.
b) Creates a report in Excel
c) Creates a summary report in Excel
d) Does a), b) and c) for another table.
All these packages are scheduled to run weekdays starting at 8:00 am, and they were owned by sa. Last month we had a massive change of passwords thanks to an audit and unfortunately the dba left without letting us know that password. After the massive change, these packages started failing with errors like:
The job failed. The Job was invoked by Schedule 18 (DLYINVDT_XLS). The last step to run was step 1 (DLYINVDT_XLS).
The job failed. The Job was invoked by Schedule 22 (AGAR830_dts). The last step to run was step 1 (AGAR830_dts)
Yesterday I went into all the packages and changed the connection properties from user sa to another user that has permission over the tables and ran the 4 of them at least 10 times each, both using my personal laptop and directly in the server (just wanted to check remote and local, even though I know it really has nothing to do with the execution that its always local to the server).
Now, the funny thing is that I came in this morning and guess what.... yeap, the packages failed, I can run them manually with NO problem at all, but if I run them using the SQL Server Agent then they just will not run.
At the time I was writing this I found out the password for sa, so now I'm using it but unfortunately no change, the errors now are :
The job failed. The Job was invoked by User sa. The last step to run was step 1 (AGAR830_dts)
I'm an informix dba and I started training myself in sql a couple of months ago so I'm confortable with what I'm doing, but at this point in time I'm about to throw something to the screen :) Since I can execute the packages manually then the users are happy, but I would really like to find out why the Agent is not working right.
Any help will be GREATLY appreciated
Luis TorresHi,
To anyone interested... I solved the problem by recreating the DTS packages and rescheduling them, they work perfect now, would still like to know why the previous problem happened, but oh well, as long as its working now :)
Thanks
Luis Torres|||Generally, when Enterprise Manager creates a dtsrun job, the command is created with the DTS package ID. The ID goes with the previous version of the package. I usually change my jobs to run the package by name, so it always picks up the latest version.|||Thanks MCrowley it now makes sence on why it was failing :)
I just got assigned to help out with a couple of problems with the running of DTS packages using the SQL Server Agent, this is the problem and a list of what I've done so far:
There are 4 packages that:
a) Deletes all records from a table and then populates it using a flat file that contains that day's transactions.
b) Creates a report in Excel
c) Creates a summary report in Excel
d) Does a), b) and c) for another table.
All these packages are scheduled to run weekdays starting at 8:00 am, and they were owned by sa. Last month we had a massive change of passwords thanks to an audit and unfortunately the dba left without letting us know that password. After the massive change, these packages started failing with errors like:
The job failed. The Job was invoked by Schedule 18 (DLYINVDT_XLS). The last step to run was step 1 (DLYINVDT_XLS).
The job failed. The Job was invoked by Schedule 22 (AGAR830_dts). The last step to run was step 1 (AGAR830_dts)
Yesterday I went into all the packages and changed the connection properties from user sa to another user that has permission over the tables and ran the 4 of them at least 10 times each, both using my personal laptop and directly in the server (just wanted to check remote and local, even though I know it really has nothing to do with the execution that its always local to the server).
Now, the funny thing is that I came in this morning and guess what.... yeap, the packages failed, I can run them manually with NO problem at all, but if I run them using the SQL Server Agent then they just will not run.
At the time I was writing this I found out the password for sa, so now I'm using it but unfortunately no change, the errors now are :
The job failed. The Job was invoked by User sa. The last step to run was step 1 (AGAR830_dts)
I'm an informix dba and I started training myself in sql a couple of months ago so I'm confortable with what I'm doing, but at this point in time I'm about to throw something to the screen :) Since I can execute the packages manually then the users are happy, but I would really like to find out why the Agent is not working right.
Any help will be GREATLY appreciated
Luis TorresHi,
To anyone interested... I solved the problem by recreating the DTS packages and rescheduling them, they work perfect now, would still like to know why the previous problem happened, but oh well, as long as its working now :)
Thanks
Luis Torres|||Generally, when Enterprise Manager creates a dtsrun job, the command is created with the DTS package ID. The ID goes with the previous version of the package. I usually change my jobs to run the package by name, so it always picks up the latest version.|||Thanks MCrowley it now makes sence on why it was failing :)
Friday, March 9, 2012
DTS Package as a job
I tired to run a DTS package as a job and the job failed right away with the following message in the history of the job.
I'm running the DTS package using the dtsrun cmd in the job.
The error message:
DTSRun: Loading... Error: -2147221005 (800401F3); Provider Error: 0 (0) Error string: Invalid class string Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 713. Process Exit Code 1. The step failed.
W the f_is worng with it.... sorry this is urgentThat means that some object in your dts package has not been registered or is corrupt - Did this package run successfully from the dts designer ?|||Well I have activex scripts, dynamic propeties task, sql tasks in the package. I ran perfect on the development server as a job but on the uat server it gives that error. what r they ways around this?|||Did you copy the package from the development server to this one ? Also, did you try running the package from the dts designer on this server ?|||Originally posted by rnealejr
Did you copy the package from the development server to this one ? Also, did you try running the package from the dts designer on this server ?
Yes i coped the package from dev to uat. no i didnt becuase the pakage is built to be protable ie the connection data sources use (local) so they dont haveto be changed once they r promoted to different enivornments.... so if the package is run from the designer it wont use the uat server but my local server... but if its run as a job it runs on the uat server...|||I would test the package and run it on the actual sql server uat through the dts designer. This will eliminate any issues between the differences in how sql server executes a job versus a dts package.|||Originally posted by rnealejr
I would test the package and run it on the actual sql server uat through the dts designer. This will eliminate any issues between the differences in how sql server executes a job versus a dts package.
I ran the package on the actual server through dts designer and it works fine. but running it as a job still gives me the invalid class string error.
Plz advise.|||Is the owner of the job a sa within sql server ? If it is, what is the login/permissions of the account that is used for the agent service ?|||job owner is sa
login/permissions are administrative rights (all access)
generally what can generate this error?|||This might be related to permissions in the registry. What login is used to run the sql server agent service ? And what permissions does it have on this server ?|||The sql server agent uses system login. I'm guessing the system login has all the rights etc
fyi : it's running SQL7.0, Mdac version 2.5.4403.12
Microsoft SQL Server 7.00 - 7.00.1077 (Intel X86) Sep 6 2002 15:10:15 Copyright (c) 1988-2002 Microsoft Corporation Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)|||You created the package using SQL 2000 Enterprise manager which has objects like dynamic properties task, that do not exist on SQL 7. You will be able to execute it through enterprise manager on your PC, but not as a job on the SQL 7 Server.|||Thank you!... never realized that
ne alternatives to dynamic task properties in sql7 ?|||Yes, use Active X scripts to assign properties to objects. Like this: Well you'll have to format it better, the small text box messed it up.
Option Explicit
Function Main()
Dim oPKG
Dim oCustomTask
Set oPKG = DTSGlobalVariables.Parent
oPKG.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask.Properties.Item("DestinationObjectName").Value = DTSGlobalVariables("fundingTableName").Value
oPKG.Tasks("DTSTask_DTSDataPumpTask_2").CustomTask.Properties.Item("SourceObjectName").Value = DTSGlobalVariables("tableName").Value
oPKG.Tasks("DTSTask_DTSDataPumpTask_2").CustomTask.Properties.Item("DestinationObjectName").Value = DTSGlobalVariables("exportPath").Value
oPKG.Tasks("DTSTask_DTSDataPumpTask_3").CustomTask.Properties.Item("DestinationObjectName").Value = DTSGlobalVariables("tableName").Value
Set oPKG = Nothing
Main = DTSTaskExecResult_Success
End Function|||Nice catch shicks.|||No Problem, Anytime.|||Say i have to set the data source of a connection using active x... how would i do that?
for eg: the data source of the connection should equal to a global variable.|||This should do it
Function Main()
Dim oPKG
Dim oConnection
Set oPKG = DTSGlobalVariables.Parent
For Each oConnection in oPKG.Connections
If oConnection.ProviderID = "SQLOLEDB" Then
oConnection.DataSource =DTSGlobalVariables("server").Value
oConnection.Catalog = DTSGlobalVariables("database").Value
oConnection.UserID = DTSGlobalVariables("username").Value
oConnection.Password = DTSGlobalVariables("password").Value
End If
Next
Set oConnection = Nothing
Set oPKG = Nothing
Main = DTSTaskExecResult_Success
End Function|||Life saver!
thx
ne good resources where i could learn more about ActiveX scripts?
thx again|||I think it's VB script, then I just open the dynamic properties task and pick and object and get the property names. I am sure there must be a more efficient way to view the DTS classes, but it works for me. Maybe look for a DTS programming book. I don't know of any off the top of my head, but I am sure they are out there.
I'm running the DTS package using the dtsrun cmd in the job.
The error message:
DTSRun: Loading... Error: -2147221005 (800401F3); Provider Error: 0 (0) Error string: Invalid class string Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 713. Process Exit Code 1. The step failed.
W the f_is worng with it.... sorry this is urgentThat means that some object in your dts package has not been registered or is corrupt - Did this package run successfully from the dts designer ?|||Well I have activex scripts, dynamic propeties task, sql tasks in the package. I ran perfect on the development server as a job but on the uat server it gives that error. what r they ways around this?|||Did you copy the package from the development server to this one ? Also, did you try running the package from the dts designer on this server ?|||Originally posted by rnealejr
Did you copy the package from the development server to this one ? Also, did you try running the package from the dts designer on this server ?
Yes i coped the package from dev to uat. no i didnt becuase the pakage is built to be protable ie the connection data sources use (local) so they dont haveto be changed once they r promoted to different enivornments.... so if the package is run from the designer it wont use the uat server but my local server... but if its run as a job it runs on the uat server...|||I would test the package and run it on the actual sql server uat through the dts designer. This will eliminate any issues between the differences in how sql server executes a job versus a dts package.|||Originally posted by rnealejr
I would test the package and run it on the actual sql server uat through the dts designer. This will eliminate any issues between the differences in how sql server executes a job versus a dts package.
I ran the package on the actual server through dts designer and it works fine. but running it as a job still gives me the invalid class string error.
Plz advise.|||Is the owner of the job a sa within sql server ? If it is, what is the login/permissions of the account that is used for the agent service ?|||job owner is sa
login/permissions are administrative rights (all access)
generally what can generate this error?|||This might be related to permissions in the registry. What login is used to run the sql server agent service ? And what permissions does it have on this server ?|||The sql server agent uses system login. I'm guessing the system login has all the rights etc
fyi : it's running SQL7.0, Mdac version 2.5.4403.12
Microsoft SQL Server 7.00 - 7.00.1077 (Intel X86) Sep 6 2002 15:10:15 Copyright (c) 1988-2002 Microsoft Corporation Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)|||You created the package using SQL 2000 Enterprise manager which has objects like dynamic properties task, that do not exist on SQL 7. You will be able to execute it through enterprise manager on your PC, but not as a job on the SQL 7 Server.|||Thank you!... never realized that
ne alternatives to dynamic task properties in sql7 ?|||Yes, use Active X scripts to assign properties to objects. Like this: Well you'll have to format it better, the small text box messed it up.
Option Explicit
Function Main()
Dim oPKG
Dim oCustomTask
Set oPKG = DTSGlobalVariables.Parent
oPKG.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask.Properties.Item("DestinationObjectName").Value = DTSGlobalVariables("fundingTableName").Value
oPKG.Tasks("DTSTask_DTSDataPumpTask_2").CustomTask.Properties.Item("SourceObjectName").Value = DTSGlobalVariables("tableName").Value
oPKG.Tasks("DTSTask_DTSDataPumpTask_2").CustomTask.Properties.Item("DestinationObjectName").Value = DTSGlobalVariables("exportPath").Value
oPKG.Tasks("DTSTask_DTSDataPumpTask_3").CustomTask.Properties.Item("DestinationObjectName").Value = DTSGlobalVariables("tableName").Value
Set oPKG = Nothing
Main = DTSTaskExecResult_Success
End Function|||Nice catch shicks.|||No Problem, Anytime.|||Say i have to set the data source of a connection using active x... how would i do that?
for eg: the data source of the connection should equal to a global variable.|||This should do it
Function Main()
Dim oPKG
Dim oConnection
Set oPKG = DTSGlobalVariables.Parent
For Each oConnection in oPKG.Connections
If oConnection.ProviderID = "SQLOLEDB" Then
oConnection.DataSource =DTSGlobalVariables("server").Value
oConnection.Catalog = DTSGlobalVariables("database").Value
oConnection.UserID = DTSGlobalVariables("username").Value
oConnection.Password = DTSGlobalVariables("password").Value
End If
Next
Set oConnection = Nothing
Set oPKG = Nothing
Main = DTSTaskExecResult_Success
End Function|||Life saver!
thx
ne good resources where i could learn more about ActiveX scripts?
thx again|||I think it's VB script, then I just open the dynamic properties task and pick and object and get the property names. I am sure there must be a more efficient way to view the DTS classes, but it works for me. Maybe look for a DTS programming book. I don't know of any off the top of my head, but I am sure they are out there.
DTS Package - Execute Sql Task
Hello
Could someone please tell me what constitutes a "success" and a "failure"
result when running an "Execute Sql" task within a DTS package.
For instance, within my DTS package, I have an Execute Sql task which simply
runs a stored procedure and returns a scalar value which is the number of
records in a table (ie SELECT COUNT(1) FROM TABLENAME).
Now if that value is below a predermined value (global variable), I want the
Execute Sql task to report "success" and then carry on to execute an ActiveX
task using the "On Success" constraint within the package workflow. If the
value is not below the predetermined value, I wantthe Execute Sql task to
report "failure" and just stop there.
I hope this isn't confusing.
Regards
Peter
--== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet News=
=--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--In your Excecute SQL task, you need to evaluate the scalar value returned
from the stored proc and, if desired, raise an error. This will force the
DTS workflow to the 'failure' route.
Something like this:
if @.var >= @.predeterminedvalue
RAISERROR ('Value was too high',16,1)
Then put an ActiveX script in the failure workflow and make the script one
line:
Main = DTSTaskExecResult_Failure
If you want the package to end after this, then just make sure there are no
other tasks in the workflow after the ActiveX script AND there are no tasks
that run without a predecessor.
"Peter" <spam@.dwstech.com> wrote in message
news:1139320625_5345@.sp6iad.superfeed.net...
> Hello
> Could someone please tell me what constitutes a "success" and a "failure"
> result when running an "Execute Sql" task within a DTS package.
> For instance, within my DTS package, I have an Execute Sql task which
> simply runs a stored procedure and returns a scalar value which is the
> number of records in a table (ie SELECT COUNT(1) FROM TABLENAME).
> Now if that value is below a predermined value (global variable), I want
> the Execute Sql task to report "success" and then carry on to execute an
> ActiveX task using the "On Success" constraint within the package
> workflow. If the value is not below the predetermined value, I wantthe
> Execute Sql task to report "failure" and just stop there.
> I hope this isn't confusing.
> Regards
> Peter
>
> --== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet
> News==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+
> Newsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption
> =--|||Excellent VC
That is kinda what I was thinking but just wasn't sure if that was an
accepted method.
I could also, of course, put the whole thing in an ActiveX script but then
it gets nasty because I would have to create connection and recordset
objects etc just to return the one scalar (rowcount).
Thanks
Peter
"VC" <me@.here.com> wrote in message
news:bpqdnbU5164MJHXenZ2dnUVZ_tGdnZ2d@.co
mcast.com...
> In your Excecute SQL task, you need to evaluate the scalar value returned
> from the stored proc and, if desired, raise an error. This will force the
> DTS workflow to the 'failure' route.
> Something like this:
> if @.var >= @.predeterminedvalue
> RAISERROR ('Value was too high',16,1)
> Then put an ActiveX script in the failure workflow and make the script one
> line:
> Main = DTSTaskExecResult_Failure
> If you want the package to end after this, then just make sure there are
> no other tasks in the workflow after the ActiveX script AND there are no
> tasks that run without a predecessor.
>
> "Peter" <spam@.dwstech.com> wrote in message
> news:1139320625_5345@.sp6iad.superfeed.net...
>
>
--== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet News=
=--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||Peter - Yes, putting the whole thing in an ActiveX script is possible, but I
personally think that is a bad design. I hate the idea of a DTS package
sitting right on the SQL Server running a script that creates an ADO
connection to the SQL Server itself in order to execute a simple SQL. It is
just a lot of unnecessary overhead.
"Peter" <spam@.dwstech.com> wrote in message
news:1139325919_5391@.sp6iad.superfeed.net...
> Excellent VC
> That is kinda what I was thinking but just wasn't sure if that was an
> accepted method.
> I could also, of course, put the whole thing in an ActiveX script but then
> it gets nasty because I would have to create connection and recordset
> objects etc just to return the one scalar (rowcount).
> Thanks
> Peter
> "VC" <me@.here.com> wrote in message
> news:bpqdnbU5164MJHXenZ2dnUVZ_tGdnZ2d@.co
mcast.com...
>
> --== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet
> News==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+
> Newsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption
> =--
Could someone please tell me what constitutes a "success" and a "failure"
result when running an "Execute Sql" task within a DTS package.
For instance, within my DTS package, I have an Execute Sql task which simply
runs a stored procedure and returns a scalar value which is the number of
records in a table (ie SELECT COUNT(1) FROM TABLENAME).
Now if that value is below a predermined value (global variable), I want the
Execute Sql task to report "success" and then carry on to execute an ActiveX
task using the "On Success" constraint within the package workflow. If the
value is not below the predetermined value, I wantthe Execute Sql task to
report "failure" and just stop there.
I hope this isn't confusing.
Regards
Peter
--== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet News=
=--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--In your Excecute SQL task, you need to evaluate the scalar value returned
from the stored proc and, if desired, raise an error. This will force the
DTS workflow to the 'failure' route.
Something like this:
if @.var >= @.predeterminedvalue
RAISERROR ('Value was too high',16,1)
Then put an ActiveX script in the failure workflow and make the script one
line:
Main = DTSTaskExecResult_Failure
If you want the package to end after this, then just make sure there are no
other tasks in the workflow after the ActiveX script AND there are no tasks
that run without a predecessor.
"Peter" <spam@.dwstech.com> wrote in message
news:1139320625_5345@.sp6iad.superfeed.net...
> Hello
> Could someone please tell me what constitutes a "success" and a "failure"
> result when running an "Execute Sql" task within a DTS package.
> For instance, within my DTS package, I have an Execute Sql task which
> simply runs a stored procedure and returns a scalar value which is the
> number of records in a table (ie SELECT COUNT(1) FROM TABLENAME).
> Now if that value is below a predermined value (global variable), I want
> the Execute Sql task to report "success" and then carry on to execute an
> ActiveX task using the "On Success" constraint within the package
> workflow. If the value is not below the predetermined value, I wantthe
> Execute Sql task to report "failure" and just stop there.
> I hope this isn't confusing.
> Regards
> Peter
>
> --== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet
> News==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+
> Newsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption
> =--|||Excellent VC
That is kinda what I was thinking but just wasn't sure if that was an
accepted method.
I could also, of course, put the whole thing in an ActiveX script but then
it gets nasty because I would have to create connection and recordset
objects etc just to return the one scalar (rowcount).
Thanks
Peter
"VC" <me@.here.com> wrote in message
news:bpqdnbU5164MJHXenZ2dnUVZ_tGdnZ2d@.co
mcast.com...
> In your Excecute SQL task, you need to evaluate the scalar value returned
> from the stored proc and, if desired, raise an error. This will force the
> DTS workflow to the 'failure' route.
> Something like this:
> if @.var >= @.predeterminedvalue
> RAISERROR ('Value was too high',16,1)
> Then put an ActiveX script in the failure workflow and make the script one
> line:
> Main = DTSTaskExecResult_Failure
> If you want the package to end after this, then just make sure there are
> no other tasks in the workflow after the ActiveX script AND there are no
> tasks that run without a predecessor.
>
> "Peter" <spam@.dwstech.com> wrote in message
> news:1139320625_5345@.sp6iad.superfeed.net...
>
>
--== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet News=
=--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||Peter - Yes, putting the whole thing in an ActiveX script is possible, but I
personally think that is a bad design. I hate the idea of a DTS package
sitting right on the SQL Server running a script that creates an ADO
connection to the SQL Server itself in order to execute a simple SQL. It is
just a lot of unnecessary overhead.
"Peter" <spam@.dwstech.com> wrote in message
news:1139325919_5391@.sp6iad.superfeed.net...
> Excellent VC
> That is kinda what I was thinking but just wasn't sure if that was an
> accepted method.
> I could also, of course, put the whole thing in an ActiveX script but then
> it gets nasty because I would have to create connection and recordset
> objects etc just to return the one scalar (rowcount).
> Thanks
> Peter
> "VC" <me@.here.com> wrote in message
> news:bpqdnbU5164MJHXenZ2dnUVZ_tGdnZ2d@.co
mcast.com...
>
> --== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet
> News==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+
> Newsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption
> =--
Subscribe to:
Posts (Atom)