Hi All,
I want to design a DTS Package that will read an EXCEL Document (One Data
Source) and ONE SQL Server (2nd Data Source) and Execute one Query which
will have a JOIN from Both the source and Export the result to another Excel
Document.
How Can I perform that using DTS?
I have took 3 Connections 1) SQL Server 2) Excel -> These tow for Source
And 3) Excel Connection for Export the Result.
My Requirement is to get the value from One of the column from one of the
Sheet and use that values to get a Joined Record from TWO tables of SQL
Server.
Ex: -
Sheet2$ : Having Column "EmployeeID" with 100 rows.
IN SQL Server I have 2 Tables. 1) Employee 2) Dept.
I want to export the LIST of the Departments for the Employee that are in
the Excel Sheet2.
Please Suggest how can I do that or any Better solution using DTS.
Thanks
PrabhatYou could use OPENDATASOURCE
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended
properties=Excel 5.0')...xactions
Or you can create a linked server of the source XL spreadsheet from the
SQL Server. You then query that and export to XL destination.
You cannot use the Excel connections to do this ........Yet.
Allan
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:not_a_mail@.hotmail.com:
> Hi All,
> I want to design a DTS Package that will read an EXCEL Document (One Data
> Source) and ONE SQL Server (2nd Data Source) and Execute one Query which
> will have a JOIN from Both the source and Export the result to another Exc
el
> Document.
> How Can I perform that using DTS?
> I have took 3 Connections 1) SQL Server 2) Excel -> These tow for Source
> And 3) Excel Connection for Export the Result.
> My Requirement is to get the value from One of the column from one of the
> Sheet and use that values to get a Joined Record from TWO tables of SQL
> Server.
> Ex: -
> Sheet2$ : Having Column "EmployeeID" with 100 rows.
> IN SQL Server I have 2 Tables. 1) Employee 2) Dept.
> I want to export the LIST of the Departments for the Employee that are in
> the Excel Sheet2.
> Please Suggest how can I do that or any Better solution using DTS.
>
> Thanks
> Prabhat|||306397 How To Use Excel with SQL Server Linked Servers and Distributed
Queries
http://support.microsoft.com/?id=306397
-Doug
--
Douglas Laudenschlager
Microsoft SQL Server documentation team
Redmond, Washington, USA
This posting is provided "AS IS" with no warranties, and confers no rights.
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:%23iSD4RHXFHA.3464@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I want to design a DTS Package that will read an EXCEL Document (One Data
> Source) and ONE SQL Server (2nd Data Source) and Execute one Query which
> will have a JOIN from Both the source and Export the result to another
> Excel
> Document.
> How Can I perform that using DTS?
> I have took 3 Connections 1) SQL Server 2) Excel -> These tow for Source
> And 3) Excel Connection for Export the Result.
> My Requirement is to get the value from One of the column from one of the
> Sheet and use that values to get a Joined Record from TWO tables of SQL
> Server.
> Ex: -
> Sheet2$ : Having Column "EmployeeID" with 100 rows.
> IN SQL Server I have 2 Tables. 1) Employee 2) Dept.
> I want to export the LIST of the Departments for the Employee that are in
> the Excel Sheet2.
> Please Suggest how can I do that or any Better solution using DTS.
>
> Thanks
> Prabhat
>|||"Douglas Laudenschlager [MS]" <douglasl@.online.microsoft.com> wrote in
message news:OOnZmB$XFHA.2884@.tk2msftngp13.phx.gbl...
> 306397 How To Use Excel with SQL Server Linked Servers and Distributed
> Queries
> http://support.microsoft.com/?id=306397
> -Doug
> --
> Douglas Laudenschlager
> Microsoft SQL Server documentation team
> Redmond, Washington, USA
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:%23iSD4RHXFHA.3464@.TK2MSFTNGP10.phx.gbl...
Data
the
in
>
Showing posts with label execute. Show all posts
Showing posts with label execute. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
DTS fails on Stored Procedure warning
Hi,
I am calling a stored proc from a DTS package in SQL 2000 using Execute SQL
step & when I try to execute it from DTS, I get 'failed on execution', but
when I run the stored proc manually, it runs fine, but gives a couple of
warnings:
Warning: Null value is eliminated by an aggregate or other SET operation.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Any ideas why this happens?
Thanks,
Mo
you can setup an option when you call a dbcc command
generally there is a "no_msg" (or an option like this; read the BOL) which
disabled output except in case of errors.
"Mo" <Mo@.discussions.microsoft.com> wrote in message
news:72063C0B-8791-4666-90A0-8C7C645C2ADF@.microsoft.com...
> Hi,
> I am calling a stored proc from a DTS package in SQL 2000 using Execute
> SQL
> step & when I try to execute it from DTS, I get 'failed on execution', but
> when I run the stored proc manually, it runs fine, but gives a couple of
> warnings:
> Warning: Null value is eliminated by an aggregate or other SET operation.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Any ideas why this happens?
> Thanks,
> Mo
>
sql
I am calling a stored proc from a DTS package in SQL 2000 using Execute SQL
step & when I try to execute it from DTS, I get 'failed on execution', but
when I run the stored proc manually, it runs fine, but gives a couple of
warnings:
Warning: Null value is eliminated by an aggregate or other SET operation.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Any ideas why this happens?
Thanks,
Mo
you can setup an option when you call a dbcc command
generally there is a "no_msg" (or an option like this; read the BOL) which
disabled output except in case of errors.
"Mo" <Mo@.discussions.microsoft.com> wrote in message
news:72063C0B-8791-4666-90A0-8C7C645C2ADF@.microsoft.com...
> Hi,
> I am calling a stored proc from a DTS package in SQL 2000 using Execute
> SQL
> step & when I try to execute it from DTS, I get 'failed on execution', but
> when I run the stored proc manually, it runs fine, but gives a couple of
> warnings:
> Warning: Null value is eliminated by an aggregate or other SET operation.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Any ideas why this happens?
> Thanks,
> Mo
>
sql
DTS fails on Stored Procedure warning
Hi,
I am calling a stored proc from a DTS package in SQL 2000 using Execute SQL
step & when I try to execute it from DTS, I get 'failed on execution', but
when I run the stored proc manually, it runs fine, but gives a couple of
warnings:
Warning: Null value is eliminated by an aggregate or other SET operation.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Any ideas why this happens?
Thanks,
Moyou can setup an option when you call a dbcc command
generally there is a "no_msg" (or an option like this; read the BOL) which
disabled output except in case of errors.
"Mo" <Mo@.discussions.microsoft.com> wrote in message
news:72063C0B-8791-4666-90A0-8C7C645C2ADF@.microsoft.com...
> Hi,
> I am calling a stored proc from a DTS package in SQL 2000 using Execute
> SQL
> step & when I try to execute it from DTS, I get 'failed on execution', but
> when I run the stored proc manually, it runs fine, but gives a couple of
> warnings:
> Warning: Null value is eliminated by an aggregate or other SET operation.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Any ideas why this happens?
> Thanks,
> Mo
>
I am calling a stored proc from a DTS package in SQL 2000 using Execute SQL
step & when I try to execute it from DTS, I get 'failed on execution', but
when I run the stored proc manually, it runs fine, but gives a couple of
warnings:
Warning: Null value is eliminated by an aggregate or other SET operation.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Any ideas why this happens?
Thanks,
Moyou can setup an option when you call a dbcc command
generally there is a "no_msg" (or an option like this; read the BOL) which
disabled output except in case of errors.
"Mo" <Mo@.discussions.microsoft.com> wrote in message
news:72063C0B-8791-4666-90A0-8C7C645C2ADF@.microsoft.com...
> Hi,
> I am calling a stored proc from a DTS package in SQL 2000 using Execute
> SQL
> step & when I try to execute it from DTS, I get 'failed on execution', but
> when I run the stored proc manually, it runs fine, but gives a couple of
> warnings:
> Warning: Null value is eliminated by an aggregate or other SET operation.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Any ideas why this happens?
> Thanks,
> Mo
>
DTS fails at customer site with "Too many columns", works locally
I am having the most baffling problem with DTS... :confused:
I have a set of ActiveX transforms that execute on my customers flat transaction data files, destination a single database table. Since they switched to a new method of generating the flat file using SAS, the DTS package mysteriously will fail at a couple select records. The error is always the same, and turning on error logging in DTS yielded this:
Step 'DTSStep_DTSDataPumpTask_1' failed
Step Error Source: Microsoft Data Transformation Services Flat File Rowset Provider
Step Error Description:Too many columns found in the current row; non-whitespace characters were found after the last defined column's data.
Step Error code: 80043013
Step Error Help File: DTSFFile.hlp
Step Error Help Context ID:0
Step Execution Started: 11/16/2004 6:37:51 PM
Step Execution Completed: 11/16/2004 6:39:39 PM
Total Step Execution Time: 107.415 seconds
Progress count in Step: 515000
The exact same file parses all the way through on my laptop, with the same DTS package. Tests have revealed no strange characters or whitespaces in the data file, not at that record (running a Test... on any of the active x transforms will fail at row 515186 always, until that row is deleted and it fails on some subsequent row - this iteration went on at the customer site until about 5 rows were deleted this month and it finally worked), not at any other records. My database and the customer database are both using the same, default character set.
The only microsoft KB article referencing anything resembling my problem is
http://support.microsoft.com/default.aspx?scid=kb;en-us;292588
but this does not hold because I am not specifying fixed width, but rather comma delimited.
If anyone has any ideas about what other environmental variables are coming into play here, please let me know - I'm at the end of my rope. I believe we are both patched up to SQL 2000 SP3. They have an XP client connecting to a 2003 server; I have an XP client/server. Neither machine has the NLS_LANG environment variable set.This may not be helpful...but have you considered just using a stored procedure instead?|||What happens to that row when you try to import the file into access? If you create an extra column at the top of the flat file, it should insert whatevers in that column for the five offending rows right? Once you get it into a table query it with a NOT NULL. It might give you a clue as to what the offending characters are.
If your stuck with the file then you might just have to use the insertfail phase to make the pump task skip to the next record when it finds an offending row. Read up on multiphase to find out exactly how you'd do this.
Sorry can't help you more.|||Modify the DTS package to use an Execute Process Task and then use bcp.
-or-Use Execute SQL Task and the Bulk Insert Transact-SQL command.|||...the file imports fine here with the exact same DTS package, so I don't want to modify it to address a problem that isn't really the problem. IN other words, there is nothing to indicate there is anything actually wrong with the data itself - no whitespaces, no bad characters or problem causing characters, no datatype mismatch, nothing; it looks just like the last row. Here are the rows before and after as well as the one that failed:
737,10/15/2004,09:11:39,114,15536,1
737,10/15/2004,09:11:49,114,18408,1
737,10/15/2004,09:11:54,714,18024,1
I am not using column 5, but all the others. From last month to this month the number of offending rows increased from 1 to 7, so I don't want to start throwing away data that for all other intensive purposes looks good automatically in case it starts multiplying.
Since it works here but fails there, it has to be something environmental, maybe with character sets or??|||Generating files from SAS...Like from a mainframe?
I betcha you got some low values [CHAR('00') ] going on...
I know you don't want to alter your process, but I ALWAYS create a staging environment and load the data to it, then audit the data to look for problems...then I move the data in after I verify it...
And it's all done with a stored procedure|||Thanks for the tip. I am looking into how these "low values" occur and how these EBCDIC to ASCII conversions can get hung up. I'm sure the answer lies somewhere in there.
Well, the front end application will run a custom DTS package, but not a custom SP. At least the staging need is moot, because it rolls the whole thing back if one record fails...
I have a set of ActiveX transforms that execute on my customers flat transaction data files, destination a single database table. Since they switched to a new method of generating the flat file using SAS, the DTS package mysteriously will fail at a couple select records. The error is always the same, and turning on error logging in DTS yielded this:
Step 'DTSStep_DTSDataPumpTask_1' failed
Step Error Source: Microsoft Data Transformation Services Flat File Rowset Provider
Step Error Description:Too many columns found in the current row; non-whitespace characters were found after the last defined column's data.
Step Error code: 80043013
Step Error Help File: DTSFFile.hlp
Step Error Help Context ID:0
Step Execution Started: 11/16/2004 6:37:51 PM
Step Execution Completed: 11/16/2004 6:39:39 PM
Total Step Execution Time: 107.415 seconds
Progress count in Step: 515000
The exact same file parses all the way through on my laptop, with the same DTS package. Tests have revealed no strange characters or whitespaces in the data file, not at that record (running a Test... on any of the active x transforms will fail at row 515186 always, until that row is deleted and it fails on some subsequent row - this iteration went on at the customer site until about 5 rows were deleted this month and it finally worked), not at any other records. My database and the customer database are both using the same, default character set.
The only microsoft KB article referencing anything resembling my problem is
http://support.microsoft.com/default.aspx?scid=kb;en-us;292588
but this does not hold because I am not specifying fixed width, but rather comma delimited.
If anyone has any ideas about what other environmental variables are coming into play here, please let me know - I'm at the end of my rope. I believe we are both patched up to SQL 2000 SP3. They have an XP client connecting to a 2003 server; I have an XP client/server. Neither machine has the NLS_LANG environment variable set.This may not be helpful...but have you considered just using a stored procedure instead?|||What happens to that row when you try to import the file into access? If you create an extra column at the top of the flat file, it should insert whatevers in that column for the five offending rows right? Once you get it into a table query it with a NOT NULL. It might give you a clue as to what the offending characters are.
If your stuck with the file then you might just have to use the insertfail phase to make the pump task skip to the next record when it finds an offending row. Read up on multiphase to find out exactly how you'd do this.
Sorry can't help you more.|||Modify the DTS package to use an Execute Process Task and then use bcp.
-or-Use Execute SQL Task and the Bulk Insert Transact-SQL command.|||...the file imports fine here with the exact same DTS package, so I don't want to modify it to address a problem that isn't really the problem. IN other words, there is nothing to indicate there is anything actually wrong with the data itself - no whitespaces, no bad characters or problem causing characters, no datatype mismatch, nothing; it looks just like the last row. Here are the rows before and after as well as the one that failed:
737,10/15/2004,09:11:39,114,15536,1
737,10/15/2004,09:11:49,114,18408,1
737,10/15/2004,09:11:54,714,18024,1
I am not using column 5, but all the others. From last month to this month the number of offending rows increased from 1 to 7, so I don't want to start throwing away data that for all other intensive purposes looks good automatically in case it starts multiplying.
Since it works here but fails there, it has to be something environmental, maybe with character sets or??|||Generating files from SAS...Like from a mainframe?
I betcha you got some low values [CHAR('00') ] going on...
I know you don't want to alter your process, but I ALWAYS create a staging environment and load the data to it, then audit the data to look for problems...then I move the data in after I verify it...
And it's all done with a stored procedure|||Thanks for the tip. I am looking into how these "low values" occur and how these EBCDIC to ASCII conversions can get hung up. I'm sure the answer lies somewhere in there.
Well, the front end application will run a custom DTS package, but not a custom SP. At least the staging need is moot, because it rolls the whole thing back if one record fails...
DTS execution from T-SQL
I really need help!!!
I built a DTS package that makes data pump from Paradox 7.0 to SQL Server. If I execute this package from Package Designer everything goes fine no matter if Paradox files are located in local or mapped folders.
However, If I execute the package from T-SQL, it crashes when if Paradox files are located in a mapped drive saying "not valid path" (With local folder it works)
Im using xp_cmdshell 'DTS_Run /S "server name" /N "package name" /E'
Can anyone tell me whats wrong with this
ThanksFirst of all check whether the account used have necessary privileges to carry tasks on paradox and on the machine.
I built a DTS package that makes data pump from Paradox 7.0 to SQL Server. If I execute this package from Package Designer everything goes fine no matter if Paradox files are located in local or mapped folders.
However, If I execute the package from T-SQL, it crashes when if Paradox files are located in a mapped drive saying "not valid path" (With local folder it works)
Im using xp_cmdshell 'DTS_Run /S "server name" /N "package name" /E'
Can anyone tell me whats wrong with this
ThanksFirst of all check whether the account used have necessary privileges to carry tasks on paradox and on the machine.
DTS Execution Error in SQL server 2000..please help asap
Hi,
when i execute a dts to copy one database from one server to another i am getting the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB 'SQL OLEDB' reported an error.Authentication Failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE/DB provider returned message: Invalid authorization specification]
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDB Initialize: Initiliaze returned 0x80040e4d: Authentication failed.].
Please help me solve it.
DTS questions should be redirected to the DTS group: http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=sgDTS execution error
when i execute a DTS to copy a database from one server to another, I am getting the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB 'SQL OLEDB' reported an error.Authentication Failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE/DB provider returned message: Invalid authorization specification]
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDB Initialize: Initiliaze returned 0x80040e4d: Authentication failed.].
Can somebody please help?
I understand you run DTS Import/Export wizard in SQL 2000; if it is true i think you entered in Windows with an account that can not log on that SQL Server (that windows account have not a login in SQL Server ).Check this problem|||NO I am not using an import export wizard.it is a copy sql server objects task which is being used
DTS execute works, Schedule fails
Hey all,
I have created a DTS task that i can right click on and execute and it works fine on the server.
However when i try to schedule the task and run the job from SQL Server Agent, i get the following error.
Error Source= Microsoft VBScript runtime error Error Description: ActiveX component can't create object: 'CuteFTPPro.TEConnection' Error on Line 31
the error occurs on the following line
Set MySite = CreateObject("CuteFTPPro.TEConnection")
How is it possible that i can execute my package but cannot schedule it? i am executing the package from physically sitting at the server.
thanks in advance,
peteI'd say it's gotta be permissions...
When you run a dts package manually, it runs under the context of the client...
When scheduled it runs under the context of the sql server service account...
Also the same thing with the drive mappings
When you run it, it uses yours...
when scheduled it sees the servers drive
screwed up, huh...|||when i was running it manually i was sitting at the server so wouldnt it be running in the context of the server?
the scheduled task is run by the /MYSERVER/SYSTEM user... shouldnt that user have all permissions.
how do i change permissions for the user /MYSERVER/SYSTEM if it doesnt have all permissions.
thanks,
peter
Originally posted by Brett Kaiser
I'd say it's gotta be permissions...
When you run a dts package manually, it runs under the context of the client...
When scheduled it runs under the context of the sql server service account...
Also the same thing with the drive mappings
When you run it, it uses yours...
when scheduled it sees the servers drive
screwed up, huh...
I have created a DTS task that i can right click on and execute and it works fine on the server.
However when i try to schedule the task and run the job from SQL Server Agent, i get the following error.
Error Source= Microsoft VBScript runtime error Error Description: ActiveX component can't create object: 'CuteFTPPro.TEConnection' Error on Line 31
the error occurs on the following line
Set MySite = CreateObject("CuteFTPPro.TEConnection")
How is it possible that i can execute my package but cannot schedule it? i am executing the package from physically sitting at the server.
thanks in advance,
peteI'd say it's gotta be permissions...
When you run a dts package manually, it runs under the context of the client...
When scheduled it runs under the context of the sql server service account...
Also the same thing with the drive mappings
When you run it, it uses yours...
when scheduled it sees the servers drive
screwed up, huh...|||when i was running it manually i was sitting at the server so wouldnt it be running in the context of the server?
the scheduled task is run by the /MYSERVER/SYSTEM user... shouldnt that user have all permissions.
how do i change permissions for the user /MYSERVER/SYSTEM if it doesnt have all permissions.
thanks,
peter
Originally posted by Brett Kaiser
I'd say it's gotta be permissions...
When you run a dts package manually, it runs under the context of the client...
When scheduled it runs under the context of the sql server service account...
Also the same thing with the drive mappings
When you run it, it uses yours...
when scheduled it sees the servers drive
screwed up, huh...
DTS Execute SQL task on Access linked table
Can the DTS execute SQL task recognize a table in an Access database that is linked from another Access database?
I'm attempting to use a DTS Execute SQL task connected to Access1.mdb to make a new table in Access1.mdb that is using a Subquery from a linked table in Access2.mdb . (I know it sounds convoluded..) Its giving me an error in my FROM clause as if it can't recognize the linked table from Access2.mdb.
The reason why I'm doing this is that I'm not sure how to run an Access make table query from DTS that will overwrite the existing table in Access. I get an error that the table already exists.
Any Help?You can do testing in query analyzer by placing your select * from openquery statement. If it works in query analyzer then u could execute this sql statement in dts package.
By the way, since you are using DTS package, why not use MS Access connection which is more dynamic and stable than OpenQuery.
I'm attempting to use a DTS Execute SQL task connected to Access1.mdb to make a new table in Access1.mdb that is using a Subquery from a linked table in Access2.mdb . (I know it sounds convoluded..) Its giving me an error in my FROM clause as if it can't recognize the linked table from Access2.mdb.
The reason why I'm doing this is that I'm not sure how to run an Access make table query from DTS that will overwrite the existing table in Access. I get an error that the table already exists.
Any Help?You can do testing in query analyzer by placing your select * from openquery statement. If it works in query analyzer then u could execute this sql statement in dts package.
By the way, since you are using DTS package, why not use MS Access connection which is more dynamic and stable than OpenQuery.
Sunday, March 25, 2012
DTS Execute SQL Task failing in SQLServer 2005
Hello All,
We migrtated to SQLServer 2005 yesterday. On the same server, I uninstalled
SQLServer 2000 Enterprise Edition 32-bit Sp4 and installed SQLServer 2005
Enterprise Edition 32-bit SP2.
The new version is working fine and the performance is also comparable. For
the DTS packages, I download and installed the DTS Runtime components. All
the DTS packages work except for one. The package, calls a SP residing in the
user database in "Execute SQL" Task and then copies the data into a flat file
via DataPump Task on a remote share. The remote share is available all the
time.
Last night the Execute SQL task had failed and this morning I troubleshooted
it and found out that this is happening randomly and whenever it fails I see
this in log that it writes to. I tested it once after the migration and I ran
it once again now and in both the cases it worked fine.
This process never used to fail in SQLServer 2000. So, I was wondering if
this has anything to do with SQLServer 2005.
Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:[DBNETLIB][ConnectionRead (recv()).]General network
error. Check your network documentation.
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:0
Thanks,
rgnHi
Error number 80004005 is a generic error and has quite a wide range of
causes for instance:
http://tutorials.aspfaq.com/8000xxxxx-errors/80004005-errors.html
http://tinyurl.com/2s9qqm
In the long term you should be considering moving over to SSIS.
John
"rgn" wrote:
> Hello All,
> We migrtated to SQLServer 2005 yesterday. On the same server, I uninstalled
> SQLServer 2000 Enterprise Edition 32-bit Sp4 and installed SQLServer 2005
> Enterprise Edition 32-bit SP2.
> The new version is working fine and the performance is also comparable. For
> the DTS packages, I download and installed the DTS Runtime components. All
> the DTS packages work except for one. The package, calls a SP residing in the
> user database in "Execute SQL" Task and then copies the data into a flat file
> via DataPump Task on a remote share. The remote share is available all the
> time.
> Last night the Execute SQL task had failed and this morning I troubleshooted
> it and found out that this is happening randomly and whenever it fails I see
> this in log that it writes to. I tested it once after the migration and I ran
> it once again now and in both the cases it worked fine.
> This process never used to fail in SQLServer 2000. So, I was wondering if
> this has anything to do with SQLServer 2005.
>
> Step Error Source: Microsoft OLE DB Provider for SQL Server
> Step Error Description:[DBNETLIB][ConnectionRead (recv()).]General network
> error. Check your network documentation.
> Step Error code: 80004005
> Step Error Help File:
> Step Error Help Context ID:0
> Thanks,
> rgn
We migrtated to SQLServer 2005 yesterday. On the same server, I uninstalled
SQLServer 2000 Enterprise Edition 32-bit Sp4 and installed SQLServer 2005
Enterprise Edition 32-bit SP2.
The new version is working fine and the performance is also comparable. For
the DTS packages, I download and installed the DTS Runtime components. All
the DTS packages work except for one. The package, calls a SP residing in the
user database in "Execute SQL" Task and then copies the data into a flat file
via DataPump Task on a remote share. The remote share is available all the
time.
Last night the Execute SQL task had failed and this morning I troubleshooted
it and found out that this is happening randomly and whenever it fails I see
this in log that it writes to. I tested it once after the migration and I ran
it once again now and in both the cases it worked fine.
This process never used to fail in SQLServer 2000. So, I was wondering if
this has anything to do with SQLServer 2005.
Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:[DBNETLIB][ConnectionRead (recv()).]General network
error. Check your network documentation.
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:0
Thanks,
rgnHi
Error number 80004005 is a generic error and has quite a wide range of
causes for instance:
http://tutorials.aspfaq.com/8000xxxxx-errors/80004005-errors.html
http://tinyurl.com/2s9qqm
In the long term you should be considering moving over to SSIS.
John
"rgn" wrote:
> Hello All,
> We migrtated to SQLServer 2005 yesterday. On the same server, I uninstalled
> SQLServer 2000 Enterprise Edition 32-bit Sp4 and installed SQLServer 2005
> Enterprise Edition 32-bit SP2.
> The new version is working fine and the performance is also comparable. For
> the DTS packages, I download and installed the DTS Runtime components. All
> the DTS packages work except for one. The package, calls a SP residing in the
> user database in "Execute SQL" Task and then copies the data into a flat file
> via DataPump Task on a remote share. The remote share is available all the
> time.
> Last night the Execute SQL task had failed and this morning I troubleshooted
> it and found out that this is happening randomly and whenever it fails I see
> this in log that it writes to. I tested it once after the migration and I ran
> it once again now and in both the cases it worked fine.
> This process never used to fail in SQLServer 2000. So, I was wondering if
> this has anything to do with SQLServer 2005.
>
> Step Error Source: Microsoft OLE DB Provider for SQL Server
> Step Error Description:[DBNETLIB][ConnectionRead (recv()).]General network
> error. Check your network documentation.
> Step Error code: 80004005
> Step Error Help File:
> Step Error Help Context ID:0
> Thanks,
> rgn
DTS Execute Process Task question
In the properties sheet for a DTS Execute Process Task, there is a input box for 'Parameters' which allow you to add command-line options to the task exe specified. How can you use the DTS Global variables to be passed in this parameter field? Is there a special format to indicate that the parameters are in fact global variable tokens?I think the Global Variables are designed to be used for ActiveX Script Task only.
Dts Execute Problem
Hi all,
My dts fails in step 3 when i run it from asp.net. But it works fine in when i run in Designer.
In step 3, it reads some data from a text source (on a shared place). And it gives me error :
Step Error Source: Microsoft Data Transformation Services Flat File Rowset Provider
Step Error Description:Error opening datafile: Logon failure: unknown user name or bad password.
My application runs with my current AD username/password.
I use Interop.DTS class to run Dts. I can run other dts correctly with this method but i can't run this one.
My dts fails in step 3 when i run it from asp.net. But it works fine in when i run in Designer.
In step 3, it reads some data from a text source (on a shared place). And it gives me error :
Step Error Source: Microsoft Data Transformation Services Flat File Rowset Provider
Step Error Description:Error opening datafile: Logon failure: unknown user name or bad password.
Step Error code: 80004005
Step Error Help File
Step Error Help Context ID:0
I know that when i run it on Designer, it works by my current Windows Loginname. And when i execute dts in asp.net page, it works by SqlAgent's login name. And also i made SqlAgent's Username (as a Domain UserName) a Local Admin in the file shared server.
Any idea?
Regards..
It appears that your ASP.NET application does not have permission to access the flat file.
However, it is very unlikely that your ASP.NET application is running in the context of the SQL Agent account.
How are you launching the package - using the API in your code, or calling sp_start_job?
|||I think my ASP:NET application has permission because i can get list of that folder's files and can edit one of them.My application runs with my current AD username/password.
I use Interop.DTS class to run Dts. I can run other dts correctly with this method but i can't run this one.
Regards..
DTS Execute from Com Object (Workgroup Version)
Hey Guys,
I have written some code that executes a DTS package from a COM object. It works great on my staging server which is MSSQL 2000 Standard Edition. I just got a new live server which has MSSQL Server 2000 Workgroup Edition. Now I recieve an error message when trying to execute the DTS package from the COM object. Is this perhaps something that is not supported with the Workgroup edition? Is there anyway to varify for sure because as you all know it would cost me a good amount of money to upgrade.
Thanks in advance!
JayStang wrote:
Hey Guys,
I have written some code that executes a DTS package from a COM object. It works great on my staging server which is MSSQL 2000 Standard Edition. I just got a new live server which has MSSQL Server 2000 Workgroup Edition. Now I recieve an error message when trying to execute the DTS package from the COM object. Is this perhaps something that is not supported with the Workgroup edition? Is there anyway to varify for sure because as you all know it would cost me a good amount of money to upgrade.
Thanks in advance!
I recommend you try the DTS newsgroup microsoft.public.sqlserver.dts
DTS excution error from ASP Page
Hi,
I have to execute the DTS package from ASP file.
But the following line throws error
Set oPkg = Server.CreateObject("DTS.Package")
Error type:
Server object, ASP 0177 (0x800401F3)
Invalid ProgID.
Does anybody know the solution?
Thanks,
Mamatha
--
Message posted via http://www.sqlmonster.comHi
At a guess you need the DTS redistributables on the web server. This method
seems to be a better one:
http://www.sqldts.com/default.aspx?207,2
Alteratively you may want to run it through a stored procedure.
John
"Mamatha MD via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:7aefeb75208d4814b21443be6e91a5dd@.SQLMonster.com...
> Hi,
> I have to execute the DTS package from ASP file.
> But the following line throws error
> Set oPkg = Server.CreateObject("DTS.Package")
> Error type:
> Server object, ASP 0177 (0x800401F3)
> Invalid ProgID.
> Does anybody know the solution?
> Thanks,
> Mamatha
> --
> Message posted via http://www.sqlmonster.com
I have to execute the DTS package from ASP file.
But the following line throws error
Set oPkg = Server.CreateObject("DTS.Package")
Error type:
Server object, ASP 0177 (0x800401F3)
Invalid ProgID.
Does anybody know the solution?
Thanks,
Mamatha
--
Message posted via http://www.sqlmonster.comHi
At a guess you need the DTS redistributables on the web server. This method
seems to be a better one:
http://www.sqldts.com/default.aspx?207,2
Alteratively you may want to run it through a stored procedure.
John
"Mamatha MD via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:7aefeb75208d4814b21443be6e91a5dd@.SQLMonster.com...
> Hi,
> I have to execute the DTS package from ASP file.
> But the following line throws error
> Set oPkg = Server.CreateObject("DTS.Package")
> Error type:
> Server object, ASP 0177 (0x800401F3)
> Invalid ProgID.
> Does anybody know the solution?
> Thanks,
> Mamatha
> --
> Message posted via http://www.sqlmonster.com
Wednesday, March 21, 2012
DTS Compatibilities
Hi,
When I create a DTS in SQL 2000 and try to execute it under SQL 7.0, it does not get executed. Is there a way to get the DTS (created in SQL 2000/Win 2000) to run in SQL 7.0 (Win NT)What error do you get and how do you run it ('execute', call from a job...)?|||Open the DTS package in Sql server 2000 and Click on the save as button and Save it under SQL 7.O and execute it from there.
When I create a DTS in SQL 2000 and try to execute it under SQL 7.0, it does not get executed. Is there a way to get the DTS (created in SQL 2000/Win 2000) to run in SQL 7.0 (Win NT)What error do you get and how do you run it ('execute', call from a job...)?|||Open the DTS package in Sql server 2000 and Click on the save as button and Save it under SQL 7.O and execute it from there.
Monday, March 19, 2012
DTS and process task - doesnt wait my called exe application finish completely
Hi,
I need to execute a DTS that have a couple of steps and one of them is a process task that simply call an exe file i made that will send an email to warn the user.
What happens here is that the process task executes my exe file but it doesn't wait for it to compete and fires the next task after and finally closes.
There is anyway to make a "while statament" to wait until my exe application finishes?
Any idea?
Thanks in advance,
Tiago TeixeiraI Don't think there is a way to reply to SQL that an executable that it started has now stopped running.
Shame your not using the SQL email service but your prob doing other things as well.
Do you have any contriol over the .exe source code ?
Idea 1. it could possibly create a file in Dos then delete it as a last action before ending. TSQL would then loop (possibly with a while loop) to check if the file still exists notifying you that the .exe has done it's job.
GW|||Why not connect your steps with "On success" workflow/execution paths?
Also, why are you calling another application to send an e-mail when the DTS package designer can send e-mails for you? :D|||I Don't think there is a way to reply to SQL that an executable that it started has now stopped running.
Shame your not using the SQL email service but your prob doing other things as well.
Do you have any contriol over the .exe source code ?
Idea 1. it could possibly create a file in Dos then delete it as a last action before ending. TSQL would then loop (possibly with a while loop) to check if the file still exists notifying you that the .exe has done it's job.
GW
Hi,
Thanks for the help.
I don't use SQL email service because i don't have a MAPI server available to do it, and i don't want create it in IIS just for this purpose.
I found a free dll extension around that sends email without a MAPI account but, the problem is that the DTS aim is export data to excel file and after send this excel file by email, the problem is that when i send the email ( using that dll) an error occurs because the excel file still locked by the precedent task, so i created myself an exe file that frees the xls file and send the email using a .NET library instead of the dll i said, but the erro i reported appears.
About your second idea, can you please be more specific or paste a code snippet abou how to do that Loop. I would appreciate very much.
Thanks in adavance,
Tiago Teixeira|||Hi Teix
I was basically just thinking of something like this
DECLARE @.cnt int
WHILE(@.cnt <= 5)
BEGIN
DECLARE @.result int
EXEC @.result =xp_cmdshell'del Myfile.exe'
IF(@.result = 0)
PRINT'Success'
ELSE
PRINT'Failure'
WAITFORDELAY'00:15'
SET @.cnt = @.Cnt +1
END
Not sure how wise this approach is though
Good Luck
GW|||Hi GWilliy,
I found a way to manage it without code, i tried the code snippet you generously wrote but application behaved in the same way as before.
So, i created by hand a schedule job and inspite of make a single DTS package that would make all the stuff i needed i sliced the main job in 3 parts and i added each one sequentially to the Job and after that all the steps were accomplished correctly and i got the stuff working.
Thanks all for the precious help and ideas,
Best regards,
Tiago Teixeira|||Try windows handler dll, I think every exe program has unique windows handler id, use this id in a loop... Just an Idea...|||thanks i'll investigate that later, but from now i think i've the job done.
thanks
Tiago Teixeira|||Poison Ref Try windows handler dll, how would you access this from TSQL ?
may be handy for future ref
Teix - Glad U got sorted
GW
I need to execute a DTS that have a couple of steps and one of them is a process task that simply call an exe file i made that will send an email to warn the user.
What happens here is that the process task executes my exe file but it doesn't wait for it to compete and fires the next task after and finally closes.
There is anyway to make a "while statament" to wait until my exe application finishes?
Any idea?
Thanks in advance,
Tiago TeixeiraI Don't think there is a way to reply to SQL that an executable that it started has now stopped running.
Shame your not using the SQL email service but your prob doing other things as well.
Do you have any contriol over the .exe source code ?
Idea 1. it could possibly create a file in Dos then delete it as a last action before ending. TSQL would then loop (possibly with a while loop) to check if the file still exists notifying you that the .exe has done it's job.
GW|||Why not connect your steps with "On success" workflow/execution paths?
Also, why are you calling another application to send an e-mail when the DTS package designer can send e-mails for you? :D|||I Don't think there is a way to reply to SQL that an executable that it started has now stopped running.
Shame your not using the SQL email service but your prob doing other things as well.
Do you have any contriol over the .exe source code ?
Idea 1. it could possibly create a file in Dos then delete it as a last action before ending. TSQL would then loop (possibly with a while loop) to check if the file still exists notifying you that the .exe has done it's job.
GW
Hi,
Thanks for the help.
I don't use SQL email service because i don't have a MAPI server available to do it, and i don't want create it in IIS just for this purpose.
I found a free dll extension around that sends email without a MAPI account but, the problem is that the DTS aim is export data to excel file and after send this excel file by email, the problem is that when i send the email ( using that dll) an error occurs because the excel file still locked by the precedent task, so i created myself an exe file that frees the xls file and send the email using a .NET library instead of the dll i said, but the erro i reported appears.
About your second idea, can you please be more specific or paste a code snippet abou how to do that Loop. I would appreciate very much.
Thanks in adavance,
Tiago Teixeira|||Hi Teix
I was basically just thinking of something like this
DECLARE @.cnt int
WHILE(@.cnt <= 5)
BEGIN
DECLARE @.result int
EXEC @.result =xp_cmdshell'del Myfile.exe'
IF(@.result = 0)
PRINT'Success'
ELSE
PRINT'Failure'
WAITFORDELAY'00:15'
SET @.cnt = @.Cnt +1
END
Not sure how wise this approach is though
Good Luck
GW|||Hi GWilliy,
I found a way to manage it without code, i tried the code snippet you generously wrote but application behaved in the same way as before.
So, i created by hand a schedule job and inspite of make a single DTS package that would make all the stuff i needed i sliced the main job in 3 parts and i added each one sequentially to the Job and after that all the steps were accomplished correctly and i got the stuff working.
Thanks all for the precious help and ideas,
Best regards,
Tiago Teixeira|||Try windows handler dll, I think every exe program has unique windows handler id, use this id in a loop... Just an Idea...|||thanks i'll investigate that later, but from now i think i've the job done.
thanks
Tiago Teixeira|||Poison Ref Try windows handler dll, how would you access this from TSQL ?
may be handy for future ref
Teix - Glad U got sorted
GW
DTS and permissions
I'm developing a DTS package which will be scheduled and will write files out to a LAN share. When I execute the package manually from enterprise manager it works just fine. But when scheduled..fails with a access denied. Can someone point me towards the area I need to fix this? I'm running the SQL server under the System account.
Thanks,
EYeah...when you run the DTS package from your client, it runs under the context of your id (not to mention the mapped paths that you have).
When it's scheduled it runs under the context of the sql server service account (not to mention it's mapped drives)
Drove me NIUTS when I installed 7
What Id di was to map one of my drive to look exactly as the servers D:\ Drive
That way If I ran it or scheduled a package it would run fine...
If your problem is that you're trying t write from the server to another server, you'll need permissions for the sql server service account to that other server...
Why not write it out to the server itself if that's the case...
Good Luck|||Hi, lil_elvis2000
You should configure a domain user account to run SQL Server Service and SQL Server Agent Service.
You also have to grant write permission to that account on the lan share.
Regards,
Originally posted by lil_elvis2000
I'm developing a DTS package which will be scheduled and will write files out to a LAN share. When I execute the package manually from enterprise manager it works just fine. But when scheduled..fails with a access denied. Can someone point me towards the area I need to fix this? I'm running the SQL server under the System account.
Thanks,
E
Thanks,
EYeah...when you run the DTS package from your client, it runs under the context of your id (not to mention the mapped paths that you have).
When it's scheduled it runs under the context of the sql server service account (not to mention it's mapped drives)
Drove me NIUTS when I installed 7
What Id di was to map one of my drive to look exactly as the servers D:\ Drive
That way If I ran it or scheduled a package it would run fine...
If your problem is that you're trying t write from the server to another server, you'll need permissions for the sql server service account to that other server...
Why not write it out to the server itself if that's the case...
Good Luck|||Hi, lil_elvis2000
You should configure a domain user account to run SQL Server Service and SQL Server Agent Service.
You also have to grant write permission to that account on the lan share.
Regards,
Originally posted by lil_elvis2000
I'm developing a DTS package which will be scheduled and will write files out to a LAN share. When I execute the package manually from enterprise manager it works just fine. But when scheduled..fails with a access denied. Can someone point me towards the area I need to fix this? I'm running the SQL server under the System account.
Thanks,
E
DTS and Interbase/Firebird
I have written several DTS pakages to copy data from an Interbase/Firebird db to SQL Server. They execute under Enterrpise Manager perfectly but when scheduled return an error.
I am aware that permissions are a normal source of these problems but I have hopefully excluded them from teh equation by using a local admin/domain/sysadmin user throughout for SEM, SQLServerAgent and even the SQLServerAgentProxy.
The problem appears to be related to the interaction between the Microsoft OLE DB Provider for ODBC Drivers and the Interbase/Firebird Driver. I have checked several forums and the MSDN website but few people seem to have experienced this type of problem in this environment.
Error msgs:
Step Error Source: Microsoft OLE DB Provider for ODBC Drivers
Step Error Description:[Easysoft][InterBase]unavailable database
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:0
OR
Step Error Source: Microsoft OLE DB Provider for ODBC Drivers
Step Error Description:unavailable database
Step Error code: 80040E4D
Step Error Help File:
Step Error Help Context ID:0
My Setup:
MS SQL Server 2000 with SP3
MDAC 2.8 RTM
Interbase v6 /Firebird 1.03 db Server
Have tried the following driver/connectors:
Firebird ODBC v1.02.00.41
Easysoft ODBC v2.01.00.01
XTG Interbase6 ODBC v1.00.00.15
I have even tried OLE DB providers for Interbase/Firebird but they won't schedule either giving me an SQLCODE=-904
Any insight/help/sympathy appreciated. I have struggled with this single issue for nearly a week.I finally resolved the issue and wanted to post it so that someone else might benefit.
There is a difference between the way Enterprise Manager 'interfaces' with the ODBC connections setup on the machine and the way SQL Server Agent does when a scheduled job is executed. Not entirely sure what the difference is I think that the difference is that SQL Enterprise Manager assumes / defaults to a TCP/IP connection whereas the SQL Server Agent assumes / defaults to a named pipe connection.
So if you are using Interbase make sure you specify a TCP/IP connection in the ODBC setup, e.g. 127.0.0.1:C:\Interbase\db\test.gdb
I am aware that permissions are a normal source of these problems but I have hopefully excluded them from teh equation by using a local admin/domain/sysadmin user throughout for SEM, SQLServerAgent and even the SQLServerAgentProxy.
The problem appears to be related to the interaction between the Microsoft OLE DB Provider for ODBC Drivers and the Interbase/Firebird Driver. I have checked several forums and the MSDN website but few people seem to have experienced this type of problem in this environment.
Error msgs:
Step Error Source: Microsoft OLE DB Provider for ODBC Drivers
Step Error Description:[Easysoft][InterBase]unavailable database
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:0
OR
Step Error Source: Microsoft OLE DB Provider for ODBC Drivers
Step Error Description:unavailable database
Step Error code: 80040E4D
Step Error Help File:
Step Error Help Context ID:0
My Setup:
MS SQL Server 2000 with SP3
MDAC 2.8 RTM
Interbase v6 /Firebird 1.03 db Server
Have tried the following driver/connectors:
Firebird ODBC v1.02.00.41
Easysoft ODBC v2.01.00.01
XTG Interbase6 ODBC v1.00.00.15
I have even tried OLE DB providers for Interbase/Firebird but they won't schedule either giving me an SQLCODE=-904
Any insight/help/sympathy appreciated. I have struggled with this single issue for nearly a week.I finally resolved the issue and wanted to post it so that someone else might benefit.
There is a difference between the way Enterprise Manager 'interfaces' with the ODBC connections setup on the machine and the way SQL Server Agent does when a scheduled job is executed. Not entirely sure what the difference is I think that the difference is that SQL Enterprise Manager assumes / defaults to a TCP/IP connection whereas the SQL Server Agent assumes / defaults to a named pipe connection.
So if you are using Interbase make sure you specify a TCP/IP connection in the ODBC setup, e.g. 127.0.0.1:C:\Interbase\db\test.gdb
Friday, March 9, 2012
dts : call 2 procs within one execute sql task
Hi :
Can i call 2 procs within one task?
I have sp_proc1 ? (and have declared one global variable as input
parameter)
now i have another sp_proc2 which uses same input parameter
but if i write two statements like this within one task, i get an
error
exec sp_proc1 ?
exec sp_proc2 ?
I can solve the problem by writing them in 2 separate tasks, but would
like one task.
Please help..
thanks
Rashikarashika (rshivaraman@.ibs.com) writes:
> Can i call 2 procs within one task?
> I have sp_proc1 ? (and have declared one global variable as input
> parameter)
> now i have another sp_proc2 which uses same input parameter
> but if i write two statements like this within one task, i get an
> error
Standard question: what error is that? Even if the error appears to
be gibberish to you, it may not do to anyone else. (But since I don't
know DTS, it might be gibberish to me too. :-)
> exec sp_proc1 ?
> exec sp_proc2 ?
> I can solve the problem by writing them in 2 separate tasks, but would
> like one task.
You could always write a wrapping procedure:
CREATE MyWrapper @.s <datatype> AS
EXEC sp_proc1 @.s
EXEC sp_proc2 @.s
Note: the prefix sp_ is reserved for system stored procedure, and SQL Server
first looks for these in the master database. Do not use sp_ for your own
prodedure.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Wednesday, March 7, 2012
DTS "Execute Process Task" fails...
I have a DTS package which utilizes an "Execute Process
Task". This task calls an EXE local to my machine. When I
initially setup the DTS routine it worked. A few days
later I tried to run it again and the executable failed
on one of its steps.
I tested the executable thoroughly and decided that the
problem was with the DTS routine. I found that when I
perform the following steps the task runs properly:
1. Edit the DTS routine
2. Edit the properties of the Execute Process Task
3. Click on the "..." button to navigate to my EXE (even
though the path supplied is already correct)
4. Run it
However, after saving the package and then closing and re-
opening SEM, the same DTS routine fails when I execute it
manually without performing the steps above.
Any ideas?
Thanks.All I can think is that the pathing for the EXE is not the same for the server as it is on your local machine. Try running the job directly from the server in the same manner. If you get the same error it is to do with the pathing to the EXE file. I have something similar here and I have to run some DTS packages via terminal services to ensure correct pathing.|||I know what you are saying (I've run into that before). I don't think that's the case here though because I am running everything on my local machine. So, if I understand you right, I am running the job on the "server" (my machine).
Thanks for the input though. Any other ideas?|||Ok, cools, so the local machine is the server. What error message are you recieving?|||I'v been tinkering with DTS packages and found myself getting caught out with the server path vs client path. Some times it looks on the server i.e Browse folder will actually present a view of C:\Win... of the machine on which your instance resides and sometimes you're looking at your local machine. Confusing but makes sense sometimes.|||The error message:
Step Error Source: Microsoft Data Transformation Services (DTS)Package
Step Error Description:CreateProcessTask 'DTSTask_DTSCreateProcessTask_1': Process returned code -1, which does not match the specified SuccessReturnCode of 1.
Step Error code: 80040496
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:4900
I thought it was some kind of permissions issue, but I've tried several things relating to DTS package permissions to no avail.|||if you set the package as a job on the server then it is a problem with the path name. The automatied job will run on the server thinking that the server is "god" not the local machine. I would say to share the local drive, map it to the server and set the path that way. It will work if you are running a job.
Jim|||1. The server machine is my local machine so there is no issue with executable paths.
2. The DTS package is not running as a job. This issue occurs when I run it manually.
More information:
When I run the DTS package and it gets to the "Execute Process Task" step it does run the EXE every time. However, the EXE fails if I don't perform the steps listed in my initial post.
Thanks for the input.|||So it does not error out in the dts step it actualy dies when the exe is run? what is the EXE?
Jim|||The EXE is a C++ Console application which serves as a wrapper around a 3rd party DLL.
The DLL basically performs an import into SQL server while encapsulating necessary business rules.
It is a very simple application.
Step 1: Connect
Step 2: Import (it fails here)
Step 3: Disconnect|||The only thing that I can think of is that a paramiter is lost or not saved in the dts package. But why that would need a path for an exe im not sure. Does the exe have varialbles in it? Jim|||JDionne! You da man!! You got me thinking about the parameters and...
I just figured it out!!
I've been so busy looking at possible security resolutions that I missed an obvious one.
The problem was with the parameters to the EXE. One of the parameters is a filepath to an import file. Since the EXE is in the same directory with this file I had just been supplying the file name (with out specifying a path) to the parameter. This works when the current working directory is the path to my EXE. So that's why it would always work after performing the steps in my initial post. These steps changed the current working directory to be that of my EXE.
The solution was to add the path to my filepath parameter.
Thanks JDionne and everyone for the help!|||Im glad that I could help. A fresh mind is always a good thing.
If you ever have any other problems dont hesitate to ask. you can private msg me too if youd like.
Regards Jim
Task". This task calls an EXE local to my machine. When I
initially setup the DTS routine it worked. A few days
later I tried to run it again and the executable failed
on one of its steps.
I tested the executable thoroughly and decided that the
problem was with the DTS routine. I found that when I
perform the following steps the task runs properly:
1. Edit the DTS routine
2. Edit the properties of the Execute Process Task
3. Click on the "..." button to navigate to my EXE (even
though the path supplied is already correct)
4. Run it
However, after saving the package and then closing and re-
opening SEM, the same DTS routine fails when I execute it
manually without performing the steps above.
Any ideas?
Thanks.All I can think is that the pathing for the EXE is not the same for the server as it is on your local machine. Try running the job directly from the server in the same manner. If you get the same error it is to do with the pathing to the EXE file. I have something similar here and I have to run some DTS packages via terminal services to ensure correct pathing.|||I know what you are saying (I've run into that before). I don't think that's the case here though because I am running everything on my local machine. So, if I understand you right, I am running the job on the "server" (my machine).
Thanks for the input though. Any other ideas?|||Ok, cools, so the local machine is the server. What error message are you recieving?|||I'v been tinkering with DTS packages and found myself getting caught out with the server path vs client path. Some times it looks on the server i.e Browse folder will actually present a view of C:\Win... of the machine on which your instance resides and sometimes you're looking at your local machine. Confusing but makes sense sometimes.|||The error message:
Step Error Source: Microsoft Data Transformation Services (DTS)Package
Step Error Description:CreateProcessTask 'DTSTask_DTSCreateProcessTask_1': Process returned code -1, which does not match the specified SuccessReturnCode of 1.
Step Error code: 80040496
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:4900
I thought it was some kind of permissions issue, but I've tried several things relating to DTS package permissions to no avail.|||if you set the package as a job on the server then it is a problem with the path name. The automatied job will run on the server thinking that the server is "god" not the local machine. I would say to share the local drive, map it to the server and set the path that way. It will work if you are running a job.
Jim|||1. The server machine is my local machine so there is no issue with executable paths.
2. The DTS package is not running as a job. This issue occurs when I run it manually.
More information:
When I run the DTS package and it gets to the "Execute Process Task" step it does run the EXE every time. However, the EXE fails if I don't perform the steps listed in my initial post.
Thanks for the input.|||So it does not error out in the dts step it actualy dies when the exe is run? what is the EXE?
Jim|||The EXE is a C++ Console application which serves as a wrapper around a 3rd party DLL.
The DLL basically performs an import into SQL server while encapsulating necessary business rules.
It is a very simple application.
Step 1: Connect
Step 2: Import (it fails here)
Step 3: Disconnect|||The only thing that I can think of is that a paramiter is lost or not saved in the dts package. But why that would need a path for an exe im not sure. Does the exe have varialbles in it? Jim|||JDionne! You da man!! You got me thinking about the parameters and...
I just figured it out!!
I've been so busy looking at possible security resolutions that I missed an obvious one.
The problem was with the parameters to the EXE. One of the parameters is a filepath to an import file. Since the EXE is in the same directory with this file I had just been supplying the file name (with out specifying a path) to the parameter. This works when the current working directory is the path to my EXE. So that's why it would always work after performing the steps in my initial post. These steps changed the current working directory to be that of my EXE.
The solution was to add the path to my filepath parameter.
Thanks JDionne and everyone for the help!|||Im glad that I could help. A fresh mind is always a good thing.
If you ever have any other problems dont hesitate to ask. you can private msg me too if youd like.
Regards Jim
Subscribe to:
Posts (Atom)