I have a package that has 12 data pump tasks all executing in parallel.
It is transferring raw data from an AS400 DW to a MSSQLSvr Staging area.
Each pump task on completion assigns values to a set of global variables, then having done this passes these as parameters to a sproc which inserts them into a table.
This seems to work for 4 or 5 of the pump tasks but, the rest of the rows in the table are all the same because the remaining pump tasks are all executing before the sprocs.
Is there a way to make sure that the entire set of job steps completes, before starting another job set of steps while still keeping them running in parallel.
I had wondered if there was a way to use the PumpComplete phase of each pump step to fire off the sproc, but can't see how you execute the step.
Any ideas would be much appreciated.Darren Green suggested
Why not take a simper approach and only populate your progress list as
tasks start executing. You could drive this quite happily off events.
To determine order of execution you would need to enumerate all steps as
constraints are held by the task they go to, not from.
For each step, enumerate the PrecedenceConstraints collection, to get
the PrecedenceConstraint objects. The StepName is the preceding step, So
if a step as no PrecedenceConstraints it is the start step. Not sure
that this guaranteed to 100 accurate either as in theory you can change
the basis and result to in effect be a "On Preceeding Step Not Run", and
have a circular reference, but I suspect DTS itself may have the same
problem as you in this case, so probably not worth worrying about for
the start step, but perfectly valid elsewhere.
Showing posts with label executing. Show all posts
Showing posts with label executing. Show all posts
Sunday, March 11, 2012
DTS Package Execution Context
I have some DTS Packages that are no longer executing successfully. They were yesterday they are not today.
They are reporting an error when this line is called...
Set objConn = CreateObject("ADODB.Connection")
The error is...
The remote server machine does not exist or is unavailable.
Now this error occurs when I execute the package, but if I execute the individual task that makes this call no error is raised. There is only one task in the package and all previous lines in the task are just setting up variables and the like.
I suspect that the problem has occured due to the installation of MS Office on the machine running SQL Server. I think that perhaps a dll was overwritten that should not have been.
Has anyone else had this problem and found a solution? What is the difference in the execution context when you execute task instead of execute package?
Anyone able to help me out?
Cheers,
RokslideHowdy
Seach for this phrase within Books On Line :
package execution permissions
I think it will point you in the right direction...
Cheers,
SG.|||I have had a look through the online books. They tell you about the permissions that the package executes with but not the task,..
I would have thought they would be the same, but from what I am seeing it would appear that they are not...|||A couple of questions:
Is this a vb dts package or a sql dts package ? If it is a sql dts package, is this occuring in an activex scripting task ? Have you tried recreating a package from scratch to see if the error appears ? Did the office installation update your mdac ?|||The package is a sql dts package and the code is in an activex scripting task.
If I recreate the package the problem still appears.
I think it is possible that the installation of office did update mdac, but I don't know for sure as I didn't do the install. The MDAC version tool tells me that we are using MDAC 2.7 at the moment...|||Have you restarted sql server since the installation ? Have you installed the service pack for mdac ? Will this error appear in any attempt to CreateObject() such as Recordset or Scripting.FileSystemObject ? If you have visual basic - save this package as a visual basic module and try it from the vb ide.|||I can create any object except for ADODB objects from the looks of things.
I can restart the server but I believe it has been rebooted after the office install...|||I would apply the service pack for mdac. When you are attempting to execute the package what login are you using (and with what permissions) ? When you execute the task (and it succeeds) and you execute the package (and it fails) are you using the same login ? What is the login used for the sql server and agent services ? Also, whoever installed office, under what login was it installed ?|||Also, try to execute the vbscript code in office and see if the same error occurs. And if you have vb installed, try creating a connection object using early binding first (setting the reference for the ado object) and if that succeeds try the late-binding method (createobject). Also, try logging in as an Administrator (or Domain Admin) and as a last resort temporarily change the login for sql service to the Administrator account (to eliminate any permissions issues).|||If I open the Package and click the execute package button it fail, if I then right click on the task and go "Execute Task" it succeeds...
I will try out some of the other things you have suggested and get back to you. :)|||If the mdac sp does not help, then I would start to test permissions issues (also turn on logging for the package) - modifying the sqlagent/sql server services to run as administrator and creating the package as administrator. Lastly reinstalling the mdac.|||It looks like it was the MDAC installation that was corrupted.
INstalled MDAC 2.8 and all my troubles have gone away. Of course I had to prove to support that this was the problem before they would let me install it... ;)
Thanks for all the help. :)
They are reporting an error when this line is called...
Set objConn = CreateObject("ADODB.Connection")
The error is...
The remote server machine does not exist or is unavailable.
Now this error occurs when I execute the package, but if I execute the individual task that makes this call no error is raised. There is only one task in the package and all previous lines in the task are just setting up variables and the like.
I suspect that the problem has occured due to the installation of MS Office on the machine running SQL Server. I think that perhaps a dll was overwritten that should not have been.
Has anyone else had this problem and found a solution? What is the difference in the execution context when you execute task instead of execute package?
Anyone able to help me out?
Cheers,
RokslideHowdy
Seach for this phrase within Books On Line :
package execution permissions
I think it will point you in the right direction...
Cheers,
SG.|||I have had a look through the online books. They tell you about the permissions that the package executes with but not the task,..
I would have thought they would be the same, but from what I am seeing it would appear that they are not...|||A couple of questions:
Is this a vb dts package or a sql dts package ? If it is a sql dts package, is this occuring in an activex scripting task ? Have you tried recreating a package from scratch to see if the error appears ? Did the office installation update your mdac ?|||The package is a sql dts package and the code is in an activex scripting task.
If I recreate the package the problem still appears.
I think it is possible that the installation of office did update mdac, but I don't know for sure as I didn't do the install. The MDAC version tool tells me that we are using MDAC 2.7 at the moment...|||Have you restarted sql server since the installation ? Have you installed the service pack for mdac ? Will this error appear in any attempt to CreateObject() such as Recordset or Scripting.FileSystemObject ? If you have visual basic - save this package as a visual basic module and try it from the vb ide.|||I can create any object except for ADODB objects from the looks of things.
I can restart the server but I believe it has been rebooted after the office install...|||I would apply the service pack for mdac. When you are attempting to execute the package what login are you using (and with what permissions) ? When you execute the task (and it succeeds) and you execute the package (and it fails) are you using the same login ? What is the login used for the sql server and agent services ? Also, whoever installed office, under what login was it installed ?|||Also, try to execute the vbscript code in office and see if the same error occurs. And if you have vb installed, try creating a connection object using early binding first (setting the reference for the ado object) and if that succeeds try the late-binding method (createobject). Also, try logging in as an Administrator (or Domain Admin) and as a last resort temporarily change the login for sql service to the Administrator account (to eliminate any permissions issues).|||If I open the Package and click the execute package button it fail, if I then right click on the task and go "Execute Task" it succeeds...
I will try out some of the other things you have suggested and get back to you. :)|||If the mdac sp does not help, then I would start to test permissions issues (also turn on logging for the package) - modifying the sqlagent/sql server services to run as administrator and creating the package as administrator. Lastly reinstalling the mdac.|||It looks like it was the MDAC installation that was corrupted.
INstalled MDAC 2.8 and all my troubles have gone away. Of course I had to prove to support that this was the problem before they would let me install it... ;)
Thanks for all the help. :)
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 :)
DTS package doesnt execute
I'm pulling my hair out. After several attempts I got the sp_OAMethod
to execute without error. Unfortunately the DTS package isn't
executing. It also isn't returning any error. What could I be doing
wrong? Any help would be appreciated.
to execute without error. Unfortunately the DTS package isn't
executing. It also isn't returning any error. What could I be doing
wrong? Any help would be appreciated.
This is the
EXEC @.hr=sp_OACreate 'DTS.Package', @.oPKG OUTPUT
IF @.hr<>0
BEGIN
EXEC sp_OAGetErrorInfo @.oPKG,@.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RAISERROR (@.desc,16,1)
RETURN
END
EXEC @.hr=sp_OAMethod
@.oPKG,'LoadFromSQLServer',NULL,@.ServerName='CAMDEV 0',@.PackageName='TestPkg',@.Flags=256
IF @.hr<>0
BEGIN
EXEC sp_OAGetErrorInfo @.oPKG,@.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RAISERROR (@.desc,16,1)
RETURN
END
--Execute the pkg
EXEC @.hr=sp_OAMethod @.oPKG,'Execute'
IF @.hr<>0
BEGIN
EXEC sp_OAGetErrorInfo @.oPKG,@.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
print @.desc
RAISERROR (@.desc,16,1)
RETURN
ENDYour code runs a package correctly when I tried it, but I suspect
there's probably an error within the package itself - it's being
Executed correctly, but then one particular step is failing. I would
enable package logging and an error file (if you haven't already), and
see what that shows.
Since DTS is client-side, even if you've tested the package
successfully from your workstation in EM, there may be problems when
you execute it from the server:
http://support.microsoft.com/defaul...kb;en-us;269074
Simon
Friday, February 24, 2012
DTS job not executing all tasks
SQL Server 2000
I created DTS package with several step.
When I run manually, all steps work OK.
When I schedule as job, only the first step runs. The next two steps
do not run.
The log simply says step ...xyz.. did not run
The owner of the job is the same as my regular XP login anme which is
also the same name I run it as manually.
Any ideas?
Thanks
jeffWhat is the login used to start the services of SQL agent.
from
Doller|||>What is the login used to start the services of SQL agent.
system account
I have more specifics about the problem.
I scheduled and ran only the package that makes up the first step of
the previously mentioned package.
This first step (package) runs a command file (DOS .bat) that ftp's
to a remote machine and does an "mget filename".
When this single package is executed manually it all works. I look in
the local machine folder and there is the file.
When run as a scheduled job the log shows the ftp mget getting the
file. However, the file does not exist on the local machine when the
job finishes.
As a matter of fact, if I manually execute the scheduled job, it also
shows that is gets file, but file does not exist on local machine.
jeff
On 19 Apr 2006 19:58:53 -0700, "doller" <sufianarif@.gmail.com> wrote:
>What is the login used to start the services of SQL agent.
>from
>Doller
I created DTS package with several step.
When I run manually, all steps work OK.
When I schedule as job, only the first step runs. The next two steps
do not run.
The log simply says step ...xyz.. did not run
The owner of the job is the same as my regular XP login anme which is
also the same name I run it as manually.
Any ideas?
Thanks
jeffWhat is the login used to start the services of SQL agent.
from
Doller|||>What is the login used to start the services of SQL agent.
system account
I have more specifics about the problem.
I scheduled and ran only the package that makes up the first step of
the previously mentioned package.
This first step (package) runs a command file (DOS .bat) that ftp's
to a remote machine and does an "mget filename".
When this single package is executed manually it all works. I look in
the local machine folder and there is the file.
When run as a scheduled job the log shows the ftp mget getting the
file. However, the file does not exist on the local machine when the
job finishes.
As a matter of fact, if I manually execute the scheduled job, it also
shows that is gets file, but file does not exist on local machine.
jeff
On 19 Apr 2006 19:58:53 -0700, "doller" <sufianarif@.gmail.com> wrote:
>What is the login used to start the services of SQL agent.
>from
>Doller
DTS job not executing all tasks
SQL Server 2000
I created DTS package with several step.
When I run manually, all steps work OK.
When I schedule as job, only the first step runs. The next two steps
do not run.
The log simply says step ...xyz.. did not run
The owner of the job is the same as my regular XP login anme which is
also the same name I run it as manually.
Any ideas?
Thanks
jeffWhat is the login used to start the services of SQL agent.
from
Doller|||>What is the login used to start the services of SQL agent.
system account
I have more specifics about the problem.
I scheduled and ran only the package that makes up the first step of
the previously mentioned package.
This first step (package) runs a command file (DOS .bat) that ftp's
to a remote machine and does an "mget filename".
When this single package is executed manually it all works. I look in
the local machine folder and there is the file.
When run as a scheduled job the log shows the ftp mget getting the
file. However, the file does not exist on the local machine when the
job finishes.
As a matter of fact, if I manually execute the scheduled job, it also
shows that is gets file, but file does not exist on local machine.
jeff
On 19 Apr 2006 19:58:53 -0700, "doller" <sufianarif@.gmail.com> wrote:
>What is the login used to start the services of SQL agent.
>from
>Doller
I created DTS package with several step.
When I run manually, all steps work OK.
When I schedule as job, only the first step runs. The next two steps
do not run.
The log simply says step ...xyz.. did not run
The owner of the job is the same as my regular XP login anme which is
also the same name I run it as manually.
Any ideas?
Thanks
jeffWhat is the login used to start the services of SQL agent.
from
Doller|||>What is the login used to start the services of SQL agent.
system account
I have more specifics about the problem.
I scheduled and ran only the package that makes up the first step of
the previously mentioned package.
This first step (package) runs a command file (DOS .bat) that ftp's
to a remote machine and does an "mget filename".
When this single package is executed manually it all works. I look in
the local machine folder and there is the file.
When run as a scheduled job the log shows the ftp mget getting the
file. However, the file does not exist on the local machine when the
job finishes.
As a matter of fact, if I manually execute the scheduled job, it also
shows that is gets file, but file does not exist on local machine.
jeff
On 19 Apr 2006 19:58:53 -0700, "doller" <sufianarif@.gmail.com> wrote:
>What is the login used to start the services of SQL agent.
>from
>Doller
DTS issues...
When executing a DTS Package from an ASP page, the steps of the package are executed in the wrong order, but it's the same wrong order every time. Any ideas?Cross post...
http://www.dbforums.com/t993006.html
Stay with your original thread...
http://www.dbforums.com/t993006.html
Stay with your original thread...
DTS issues...
When executing a DTS Package from an ASP page, the steps of the package are executed in the wrong order, but it's the same wrong order every time. Any ideas?What doe sthat mean?
They could thread out, depending on how you coded it?
Where in Jersey?
http://members.tripod.com/~mugsy11/heyrube.html|||how does it run locally?|||Meaning that the steps in the package are coded correctly. I create a table, then populate the new table, then export to an excel file. Running it from Enterprise Manager works like a champ every time, but when running it from an ASP page, it always tries to populate before creating.
Now after trying to tweek a few things, my code is screwy and I'm get server errors. I'll get it sorted out and post the exact errors I was getting.|||Does it have to be excel?
Can it be csv?
How about a sproc...
Even if it needed to be a xls, you could use a com object I guess...
I've seen DTS do some weird stuff...especially if this is a versioned package...
Can you recreate a brand new package and try it?
I know this is a stupid question, but you do have workflow steps in there...right?
I'd still go with a sproc....|||Originally posted by Brett Kaiser
Does it have to be excel?
Can it be csv?
How about a sproc...
Even if it needed to be a xls, you could use a com object I guess...
I've seen DTS do some weird stuff...especially if this is a versioned package...
Can you recreate a brand new package and try it?
I know this is a stupid question, but you do have workflow steps in there...right?
I'd still go with a sproc....
Yeah, the workflow steps are in there, and you just answered my next question. I was thinking about calling a sproc to execute it, so now that you said that, I'm going to give that a try.
And I'm in the south Jersey area by the way. Erial to be exact....about 5 miles south of Deptford.|||When you said south...you ain't lion
How far to AC?
http://www.mapquest.com/maps/map.adp?country=US&countryid=250&addtohistory=&address=&city=Erial+&state=NJ&zipcode=&submit=Get+Map
Flyer fan?
Come on, you can admit it...
Go Devils...|||Oh, and let me ask...
Do you or someone you know own a confederate flag?|||Originally posted by Brett Kaiser
When you said south...you ain't lion
How far to AC?
http://www.mapquest.com/maps/map.adp?country=US&countryid=250&addtohistory=&address=&city=Erial+&state=NJ&zipcode=&submit=Get+Map
Flyer fan?
Come on, you can admit it...
Go Devils...
I'm 20 minutes or so from AC depending on how heavy my foot is that day. And I'm not a fan for ANY Philly team. I'm from the midwest originally, so that's where my loyalties are. And as far as hockey goes, I'm more of a Redwings fan than anything I guess, but the Rangers are pseudo-cool.|||A transplant TO NJ?
There's a switch...
btw, in your sproc I'd use xp_cmdshell and ftp to get the data out...
How/ why are you creating this table?
What the data source?|||Yeah. Was in the Navy for 9 years and got dropped off on the east coast and just stayed. It's a long story. And every long story involves a woman, so I'll leave it at that for now.
Every month, our affiliates require a report(xls) that contains referrals, items purchased, costs and prices, etc. It's a different query for every affiliate and until recently, the person in charge of this was running each query in QA and then saving the results to an xls file. When this duty was passed to me, I quickly decided that the was NO way that I was going to do it all manually like that. So I put together the DTS package that works just fine for me because I have all the permission and rights that a person could have, so I would just run the package once a month.
Soon after, the powers that be, who also have large plush offices and are technical idiots, decided that they wanted to be able to check this data when and where ever they want to, so I added a link to our intranet, and that's where my problem started.|||Yeah, I set that up for 1 project...
Each report has it's own sproc...
Each report is in a report table which contains report names, sproc names, if it's batch or online (batch save it to a file location, online is saved to their local through a browswer)
The browser executes a job when they select a report that runs the sproc.
That's important becuase we found if the sproc took a whil, the browser would hang...basically started another independant thread...
The report sprocs would create csv files with a header...like..
Select '"'+LTrim(RTrim(IsNull(SignOffEntity,'')))+'"'
+',"'+LTrim(RTrim(IsNull(LedgerSumAmount,'')))+'"'
+',"'+LTrim(RTrim(IsNull(LedgerNumOfRows,'')))+'"'
From
( Select 0 As SQLGroup, 'ATS Sign-Off Entity' As SignOffEntity
, 'Ledger Balance' As LedgerSumAmount, 'Ledger Count' As LedgerNumOfRows
Union All
Select 1 As SQLGroup, a.SignOffEntity
, Convert(Char(15),Sum(IsNull(b.Amount,0))) As LedgerSumAmount
, Convert(Char(15),Count(b.Entity)) As LedgerNumOfRows
From ATS_SignOff_Entity a (NoLock)
Left Join (Select Entity, IsNull(Amount,0) As Amount
From Ledger_Detail c (NoLock)
Where Account_Type In ('4', '5', '6', '7')
Union All
Select Entity, IsNull(Convert(money,BOY),0) As Amount
From tblAcct_LedgerBalance d (NoLock)) b
On a.Entity = b.Entity
Group by a.SignOffEntity
Union All
Select 2 As SQLGroup, 'TOTAL'
, Convert(Char(15),Sum(IsNull(b.Amount,0))) As LedgerSumAmount
, Convert(Char(15),Count(b.Entity)) As LedgerNumOfRows
From ATS_SignOff_Entity a (NoLock)
Left Join (Select Entity, IsNull(Amount,0) As Amount
From Ledger_Detail c (NoLock)
Where Account_Type In ('4', '5', '6', '7')
Union All
Select Entity, IsNull(Convert(money,BOY),0) As Amount
From tblAcct_LedgerBalance d ) b
On a.Entity = b.Entity
) As A
Notice that it's just 1 column, because all of the columns are concatenated...notice too the column conversions to char where needed...
Also notice the trailer or total row...they're kept in the correct order by the SQLGroup Column...
They could thread out, depending on how you coded it?
Where in Jersey?
http://members.tripod.com/~mugsy11/heyrube.html|||how does it run locally?|||Meaning that the steps in the package are coded correctly. I create a table, then populate the new table, then export to an excel file. Running it from Enterprise Manager works like a champ every time, but when running it from an ASP page, it always tries to populate before creating.
Now after trying to tweek a few things, my code is screwy and I'm get server errors. I'll get it sorted out and post the exact errors I was getting.|||Does it have to be excel?
Can it be csv?
How about a sproc...
Even if it needed to be a xls, you could use a com object I guess...
I've seen DTS do some weird stuff...especially if this is a versioned package...
Can you recreate a brand new package and try it?
I know this is a stupid question, but you do have workflow steps in there...right?
I'd still go with a sproc....|||Originally posted by Brett Kaiser
Does it have to be excel?
Can it be csv?
How about a sproc...
Even if it needed to be a xls, you could use a com object I guess...
I've seen DTS do some weird stuff...especially if this is a versioned package...
Can you recreate a brand new package and try it?
I know this is a stupid question, but you do have workflow steps in there...right?
I'd still go with a sproc....
Yeah, the workflow steps are in there, and you just answered my next question. I was thinking about calling a sproc to execute it, so now that you said that, I'm going to give that a try.
And I'm in the south Jersey area by the way. Erial to be exact....about 5 miles south of Deptford.|||When you said south...you ain't lion
How far to AC?
http://www.mapquest.com/maps/map.adp?country=US&countryid=250&addtohistory=&address=&city=Erial+&state=NJ&zipcode=&submit=Get+Map
Flyer fan?
Come on, you can admit it...
Go Devils...|||Oh, and let me ask...
Do you or someone you know own a confederate flag?|||Originally posted by Brett Kaiser
When you said south...you ain't lion
How far to AC?
http://www.mapquest.com/maps/map.adp?country=US&countryid=250&addtohistory=&address=&city=Erial+&state=NJ&zipcode=&submit=Get+Map
Flyer fan?
Come on, you can admit it...
Go Devils...
I'm 20 minutes or so from AC depending on how heavy my foot is that day. And I'm not a fan for ANY Philly team. I'm from the midwest originally, so that's where my loyalties are. And as far as hockey goes, I'm more of a Redwings fan than anything I guess, but the Rangers are pseudo-cool.|||A transplant TO NJ?
There's a switch...
btw, in your sproc I'd use xp_cmdshell and ftp to get the data out...
How/ why are you creating this table?
What the data source?|||Yeah. Was in the Navy for 9 years and got dropped off on the east coast and just stayed. It's a long story. And every long story involves a woman, so I'll leave it at that for now.
Every month, our affiliates require a report(xls) that contains referrals, items purchased, costs and prices, etc. It's a different query for every affiliate and until recently, the person in charge of this was running each query in QA and then saving the results to an xls file. When this duty was passed to me, I quickly decided that the was NO way that I was going to do it all manually like that. So I put together the DTS package that works just fine for me because I have all the permission and rights that a person could have, so I would just run the package once a month.
Soon after, the powers that be, who also have large plush offices and are technical idiots, decided that they wanted to be able to check this data when and where ever they want to, so I added a link to our intranet, and that's where my problem started.|||Yeah, I set that up for 1 project...
Each report has it's own sproc...
Each report is in a report table which contains report names, sproc names, if it's batch or online (batch save it to a file location, online is saved to their local through a browswer)
The browser executes a job when they select a report that runs the sproc.
That's important becuase we found if the sproc took a whil, the browser would hang...basically started another independant thread...
The report sprocs would create csv files with a header...like..
Select '"'+LTrim(RTrim(IsNull(SignOffEntity,'')))+'"'
+',"'+LTrim(RTrim(IsNull(LedgerSumAmount,'')))+'"'
+',"'+LTrim(RTrim(IsNull(LedgerNumOfRows,'')))+'"'
From
( Select 0 As SQLGroup, 'ATS Sign-Off Entity' As SignOffEntity
, 'Ledger Balance' As LedgerSumAmount, 'Ledger Count' As LedgerNumOfRows
Union All
Select 1 As SQLGroup, a.SignOffEntity
, Convert(Char(15),Sum(IsNull(b.Amount,0))) As LedgerSumAmount
, Convert(Char(15),Count(b.Entity)) As LedgerNumOfRows
From ATS_SignOff_Entity a (NoLock)
Left Join (Select Entity, IsNull(Amount,0) As Amount
From Ledger_Detail c (NoLock)
Where Account_Type In ('4', '5', '6', '7')
Union All
Select Entity, IsNull(Convert(money,BOY),0) As Amount
From tblAcct_LedgerBalance d (NoLock)) b
On a.Entity = b.Entity
Group by a.SignOffEntity
Union All
Select 2 As SQLGroup, 'TOTAL'
, Convert(Char(15),Sum(IsNull(b.Amount,0))) As LedgerSumAmount
, Convert(Char(15),Count(b.Entity)) As LedgerNumOfRows
From ATS_SignOff_Entity a (NoLock)
Left Join (Select Entity, IsNull(Amount,0) As Amount
From Ledger_Detail c (NoLock)
Where Account_Type In ('4', '5', '6', '7')
Union All
Select Entity, IsNull(Convert(money,BOY),0) As Amount
From tblAcct_LedgerBalance d ) b
On a.Entity = b.Entity
) As A
Notice that it's just 1 column, because all of the columns are concatenated...notice too the column conversions to char where needed...
Also notice the trailer or total row...they're kept in the correct order by the SQLGroup Column...
DTS Issues
Hi,
I am using Data transformation services to transform my data, from one
data model to another.
My issue is executing some of the steps take a very long time(hours), I
am doing so activex processing, but nothing too funky, I end having to
stop the server with no errors logged.
Any clues, ideas.
Thanks
JackHow much data are you transferring?
Is it a straight copy of data from one place to another?
You may wish to describe each step in your DTS process, including and SQL and ActiveX processing that is used - otherwise we can't give you much of a help!
It may also be worth telling us what you want your DTS to actually be doing, as there may be more efficient ways to do this in the first place :)
Kind regards,
George|||There is a asbout 200 mb of data 40 tables, all the data is being transformed forom one data model to another,
the tasks balk on 4 tables.
table a - 1 table 90000+ records lots of data this would be 30% of the db 3 table joing into 1
table b - 1 table 1228 records nothing much- 3 table join into 1
table c - 1 table 40000+ records nothing much as well 2 table join into 1
table d - 1 table 25000+ records nothing much this table is the same as table b - 3 table join merge into 1
although the one table with the bulk of tha data 40% transforms properly with no issues 225000+ records
I am testing for null on every field that could be null.
The data is being transformed from MSSQL to MSSQL.
All the transformation tests work correctly, and parse correctly
I have created separate packages for the remaining tables with still the same problems.
Let me know if you need other details
Jack|||What version of SQLServer?
You need to identify which steps are taking so much time, via
package logging, or logging from your ActiveX, or profiling.
Usually this is caused by SQL inefficiency, locking, and/or transactional recording. Are you using NOLOCK hints where possible?
Your db logging level can slow things down too - Recovery Model 'full' vs. 'simple' adds a lot of weight. You can use bcp to avoid this.|||I am using MSSQL 2000 sp3.
I am using the package loggin and it's shows nothing other than started at??? it never completes
I will add the activex logging. not sure waht you mean by profiling.
Can I transform the data with a BCP.
Thanks
Jack|||Right the DTS logging doesn't seem to checkpoint until completion so it's often unreliable.
So try this...and probably easier than activex logging ...if you're still trying to identify the step, then log to the database - i.e. create an Audit table, and before each step, log the current time and step # using a straight insert/update. This way you can control the logging.
It also sounds like you haven't looked at the running processes while your DTS is running - see Ent Mgr under Management -> Current Activity -> Locks/ Object and Locks/Process ID - that will probably show you some locking you didn't expect and tells you exactly your problem.
You can also use the SQL Profiler tool to watch batch-by-batch what the db is doing, when all else fails; but the previous steps should be enough.
You're into the hard part - getting it to work well! Good luck -|||I will give those a try,
the activex looging seems to be reliable, I am testing on the first step and the last step and the log file says it's progressing.
I may just be a little impatient I've been working on this for 5 straight days and getting tired, 90% the way though I appreciate the tips.
Thanks
Jack|||When you check for null, what do you do, exclude it from your export, or put a dummy value in (e.g. "n/a" )?
I bet this will be causing performance issues either way if you are implementing the check using ActiveX.
The good news is that this can be easily (and not to mention - efficiently) remedied in SQL. The bad news is that it may require to re-write some of your DTS.|||After all said and done, it was a lookup query that was bogging the package.
Put the lookup into it's own package to run after
Everything works great.
Thanks for the assistance
Jack|||Right before I am to deploy the application, the DTS is crapping out.
I have some rogue characters that I need to remove before the data gets transformed.
The problem is I have lost the field.
the field is DTSSource("l_text") and it is empty, when I execute, I am doing the activex file logging and the file is empty as well. But when I test the ransformation everything works great.
Any Assitance would be appreciated
Jack
I am using Data transformation services to transform my data, from one
data model to another.
My issue is executing some of the steps take a very long time(hours), I
am doing so activex processing, but nothing too funky, I end having to
stop the server with no errors logged.
Any clues, ideas.
Thanks
JackHow much data are you transferring?
Is it a straight copy of data from one place to another?
You may wish to describe each step in your DTS process, including and SQL and ActiveX processing that is used - otherwise we can't give you much of a help!
It may also be worth telling us what you want your DTS to actually be doing, as there may be more efficient ways to do this in the first place :)
Kind regards,
George|||There is a asbout 200 mb of data 40 tables, all the data is being transformed forom one data model to another,
the tasks balk on 4 tables.
table a - 1 table 90000+ records lots of data this would be 30% of the db 3 table joing into 1
table b - 1 table 1228 records nothing much- 3 table join into 1
table c - 1 table 40000+ records nothing much as well 2 table join into 1
table d - 1 table 25000+ records nothing much this table is the same as table b - 3 table join merge into 1
although the one table with the bulk of tha data 40% transforms properly with no issues 225000+ records
I am testing for null on every field that could be null.
The data is being transformed from MSSQL to MSSQL.
All the transformation tests work correctly, and parse correctly
I have created separate packages for the remaining tables with still the same problems.
Let me know if you need other details
Jack|||What version of SQLServer?
You need to identify which steps are taking so much time, via
package logging, or logging from your ActiveX, or profiling.
Usually this is caused by SQL inefficiency, locking, and/or transactional recording. Are you using NOLOCK hints where possible?
Your db logging level can slow things down too - Recovery Model 'full' vs. 'simple' adds a lot of weight. You can use bcp to avoid this.|||I am using MSSQL 2000 sp3.
I am using the package loggin and it's shows nothing other than started at??? it never completes
I will add the activex logging. not sure waht you mean by profiling.
Can I transform the data with a BCP.
Thanks
Jack|||Right the DTS logging doesn't seem to checkpoint until completion so it's often unreliable.
So try this...and probably easier than activex logging ...if you're still trying to identify the step, then log to the database - i.e. create an Audit table, and before each step, log the current time and step # using a straight insert/update. This way you can control the logging.
It also sounds like you haven't looked at the running processes while your DTS is running - see Ent Mgr under Management -> Current Activity -> Locks/ Object and Locks/Process ID - that will probably show you some locking you didn't expect and tells you exactly your problem.
You can also use the SQL Profiler tool to watch batch-by-batch what the db is doing, when all else fails; but the previous steps should be enough.
You're into the hard part - getting it to work well! Good luck -|||I will give those a try,
the activex looging seems to be reliable, I am testing on the first step and the last step and the log file says it's progressing.
I may just be a little impatient I've been working on this for 5 straight days and getting tired, 90% the way though I appreciate the tips.
Thanks
Jack|||When you check for null, what do you do, exclude it from your export, or put a dummy value in (e.g. "n/a" )?
I bet this will be causing performance issues either way if you are implementing the check using ActiveX.
The good news is that this can be easily (and not to mention - efficiently) remedied in SQL. The bad news is that it may require to re-write some of your DTS.|||After all said and done, it was a lookup query that was bogging the package.
Put the lookup into it's own package to run after
Everything works great.
Thanks for the assistance
Jack|||Right before I am to deploy the application, the DTS is crapping out.
I have some rogue characters that I need to remove before the data gets transformed.
The problem is I have lost the field.
the field is DTSSource("l_text") and it is empty, when I execute, I am doing the activex file logging and the file is empty as well. But when I test the ransformation everything works great.
Any Assitance would be appreciated
Jack
Subscribe to:
Posts (Atom)