Establishing VPN Connection thru DTS package

Hi,
I have defined a DTS package which establishes VPN connection with my
client's server. When I execute this package thru SQL Enterprise
Manager (SEM) it works fine, but when I schedule it as a job or try to
execute thru SQL Query Analyzer using 'xp_cmdShell' stored proc, it
doesn't establish connection. NE ideas on what am I doing wrong?
Yr prompt response will be highly appreciated.
Thanks,
Um JunkWhen you execute it using xp_cmdshell the package is run under the context
of the SQL Server startup account (if the user connected via Query Analyzer
is a member of the sysadmin role) or the SQL Proxy account (if the user
connected via Query Analyzer is not a member of the sysadmin role).
Login to the machine using these accounts and see if you can run the
package from Enterprise Manager. This could give you some additional
information.
Rand
This posting is provided "as is" with no warranties and confers no rights.sql

Establishing VPN Connection thru DTS package

Hi,
I have defined a DTS package which establishes VPN connection with my
client's server. When I execute this package thru SQL Enterprise
Manager (SEM) it works fine, but when I schedule it as a job or try to
execute thru SQL Query Analyzer using 'xp_cmdShell' stored proc, it
doesn't establish connection. NE ideas on what am I doing wrong?
Yr prompt response will be highly appreciated.
Thanks,
Um Junk
When you execute it using xp_cmdshell the package is run under the context
of the SQL Server startup account (if the user connected via Query Analyzer
is a member of the sysadmin role) or the SQL Proxy account (if the user
connected via Query Analyzer is not a member of the sysadmin role).
Login to the machine using these accounts and see if you can run the
package from Enterprise Manager. This could give you some additional
information.
Rand
This posting is provided "as is" with no warranties and confers no rights.

errors while running ssis packages from the command prompt.

I have created ssis package. It has been successfully running at UI level.

But when i am trying to execute it from the command prompt by using dtexec utility it is showing the following error messages.

Error: 2005-12-23 17:01:57.67
Code: 0xC00470FE
Source: Data Flow Task DTS.Pipeline
Description: The product level is insufficient for component "Flat File Source" (1).
End Error
Error: 2005-12-23 17:01:57.67
Code: 0xC00470FE
Source: Data Flow Task DTS.Pipeline
Description: The product level is insufficient for component "Script Component" (9).
End Error

i have entered the command as follows.

dtexec /f "c:\somefolder\package1.dtsx"

Any points regarding this issue would be helpful.

Take a look here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=112469&SiteID=1

Same problem is being discussed.

HTH,
Ovidiu

|||

I am receiving a similar error. We are running SS2005 Enterprise Edition. We can connect to SSIS with SS Management Studio so that verifies that it's installed. Here is our error log. Did you solve your probelm, and if so, how?

*************************************Run GPS_EXTRACT_QA.dtsx*********************************************
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 5:14:34 PM
Progress: 2006-01-26 17:14:36.01
Source: Data Flow Task
Validating: 0% complete
End Progress
Progress: 2006-01-26 17:14:36.03
Source: Data Flow Task
Validating: 50% complete
End Progress
Progress: 2006-01-26 17:14:36.21
Source: Data Flow Task
Validating: 100% complete
End Progress
Progress: 2006-01-26 17:14:36.53
Source: Execute SQL Task
Executing query "truncate table sap_extract_staging
delete from sap".: 100% complete
End Progress
Progress: 2006-01-26 17:14:36.54
Source: Data Flow Task
Validating: 0% complete
End Progress
Progress: 2006-01-26 17:14:36.56
Source: Data Flow Task
Validating: 50% complete
End Progress
Progress: 2006-01-26 17:14:36.60
Source: Data Flow Task
Validating: 100% complete
End Progress
Error: 2006-01-26 17:14:36.60
Code: 0xC00470FE
Source: Data Flow Task DTS.Pipeline
Description: The product level is insufficient for component "Flat File Source" (749).
End Error
Warning: 2006-01-26 17:14:36.60
Code: 0x80019002
Source: GPS_EXTRACT_QA
Description: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
End Warning
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 5:14:34 PM
Finished: 5:14:36 PM
Elapsed: 2.562 seconds

|||

I am also getting same error.

any sugessions regarding this issue

sql

errors while running ssis packages from the command prompt.

I have created ssis package. It has been successfully running at UI level.

But when i am trying to execute it from the command prompt by using dtexec utility it is showing the following error messages.

Error: 2005-12-23 17:01:57.67
Code: 0xC00470FE
Source: Data Flow Task DTS.Pipeline
Description: The product level is insufficient for component "Flat File Source" (1).
End Error
Error: 2005-12-23 17:01:57.67
Code: 0xC00470FE
Source: Data Flow Task DTS.Pipeline
Description: The product level is insufficient for component "Script Component" (9).
End Error

i have entered the command as follows.

dtexec /f "c:\somefolder\package1.dtsx"

Any points regarding this issue would be helpful.

Take a look here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=112469&SiteID=1

Same problem is being discussed.

HTH,
Ovidiu

|||

I am receiving a similar error. We are running SS2005 Enterprise Edition. We can connect to SSIS with SS Management Studio so that verifies that it's installed. Here is our error log. Did you solve your probelm, and if so, how?

*************************************Run GPS_EXTRACT_QA.dtsx*********************************************
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 5:14:34 PM
Progress: 2006-01-26 17:14:36.01
Source: Data Flow Task
Validating: 0% complete
End Progress
Progress: 2006-01-26 17:14:36.03
Source: Data Flow Task
Validating: 50% complete
End Progress
Progress: 2006-01-26 17:14:36.21
Source: Data Flow Task
Validating: 100% complete
End Progress
Progress: 2006-01-26 17:14:36.53
Source: Execute SQL Task
Executing query "truncate table sap_extract_staging
delete from sap".: 100% complete
End Progress
Progress: 2006-01-26 17:14:36.54
Source: Data Flow Task
Validating: 0% complete
End Progress
Progress: 2006-01-26 17:14:36.56
Source: Data Flow Task
Validating: 50% complete
End Progress
Progress: 2006-01-26 17:14:36.60
Source: Data Flow Task
Validating: 100% complete
End Progress
Error: 2006-01-26 17:14:36.60
Code: 0xC00470FE
Source: Data Flow Task DTS.Pipeline
Description: The product level is insufficient for component "Flat File Source" (749).
End Error
Warning: 2006-01-26 17:14:36.60
Code: 0x80019002
Source: GPS_EXTRACT_QA
Description: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
End Warning
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 5:14:34 PM
Finished: 5:14:36 PM
Elapsed: 2.562 seconds

|||

I am also getting same error.

any sugessions regarding this issue

errors while running ssis packages from the command prompt.

I have created ssis package. It has been successfully running at UI level.

But when i am trying to execute it from the command prompt by using dtexec utility it is showing the following error messages.

Error: 2005-12-23 17:01:57.67
Code: 0xC00470FE
Source: Data Flow Task DTS.Pipeline
Description: The product level is insufficient for component "Flat File Source" (1).
End Error
Error: 2005-12-23 17:01:57.67
Code: 0xC00470FE
Source: Data Flow Task DTS.Pipeline
Description: The product level is insufficient for component "Script Component" (9).
End Error

i have entered the command as follows.

dtexec /f "c:\somefolder\package1.dtsx"

Any points regarding this issue would be helpful.

Take a look here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=112469&SiteID=1

Same problem is being discussed.

HTH,
Ovidiu

|||

I am receiving a similar error. We are running SS2005 Enterprise Edition. We can connect to SSIS with SS Management Studio so that verifies that it's installed. Here is our error log. Did you solve your probelm, and if so, how?

*************************************Run GPS_EXTRACT_QA.dtsx*********************************************
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 5:14:34 PM
Progress: 2006-01-26 17:14:36.01
Source: Data Flow Task
Validating: 0% complete
End Progress
Progress: 2006-01-26 17:14:36.03
Source: Data Flow Task
Validating: 50% complete
End Progress
Progress: 2006-01-26 17:14:36.21
Source: Data Flow Task
Validating: 100% complete
End Progress
Progress: 2006-01-26 17:14:36.53
Source: Execute SQL Task
Executing query "truncate table sap_extract_staging
delete from sap".: 100% complete
End Progress
Progress: 2006-01-26 17:14:36.54
Source: Data Flow Task
Validating: 0% complete
End Progress
Progress: 2006-01-26 17:14:36.56
Source: Data Flow Task
Validating: 50% complete
End Progress
Progress: 2006-01-26 17:14:36.60
Source: Data Flow Task
Validating: 100% complete
End Progress
Error: 2006-01-26 17:14:36.60
Code: 0xC00470FE
Source: Data Flow Task DTS.Pipeline
Description: The product level is insufficient for component "Flat File Source" (749).
End Error
Warning: 2006-01-26 17:14:36.60
Code: 0x80019002
Source: GPS_EXTRACT_QA
Description: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
End Warning
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 5:14:34 PM
Finished: 5:14:36 PM
Elapsed: 2.562 seconds

|||

I am also getting same error.

any sugessions regarding this issue

Error:602,Severity:21,State:4

Hi,

In my application i used to do the take the backup of current

database and restore it with a backup which was taken earlier and

execute a select command on a particular table.

My problem is,backup & restore operations get completed

successfully but error is raised when tried to open a recordset

for a select statement.Following error was recorded in the sql

error log.

Error: 602, Severity: 21, State: 4

Could not find row in sysindexes for database ID 15, object ID

1109578991, index ID 1. Run DBCC CHECKTABLE on sysindexes..

After this i terminated my application and repeated the above

operation this time i got the same error but with index ID 0 but

the database id and object id remained the same.

When scanned the sysobjects for the particular objectid and i

found out that the id belong to a SP - dt_setpropertybyid_u

I have encountered the same problem three times,but after that i

did not get the problem

Regards

I would suggest running a CHECKDB on the database. If the database is too large find out which tables the query touches and run a CHECKTABLE on the those tables.

The following article could be helpful:

http://msdn2.microsoft.com/en-us/library/aa258727(SQL.80).aspx

http://msdn2.microsoft.com/en-us/library/aa258762(sql.80).aspx

HTH

Error: The data types text and text are incompatible in the equal to operator.

I get this error:

The data types text and text are incompatible in the equal to operator.

when trying to execute this query

SELECT id FROM users WHERE username=@.userName

Any Ideas?We'll need to know te data type of the username column, as well as the SqlDbType you are using for the @.userName parameter.|||I'm using text and text.|||Can't do that. There are a number of things that you can't do with blob data types, and that's one of them. Considering the name of the field is username, I'm guessing that you should be using varchar instead.

Error: System.Runtime.InteropServices.COMException ...

Error: System.Runtime.InteropServices.COMException (0x80040428): Exception from HRESULT: 0x80040428 at DTS.PackageClass.Execute() at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()

Can anyone tell what caused this error when I tried to run the DTS package from my SSIS package ?

Thanks.

Steve

I had the same problem....drove me batty. Packages run fine using Execute Package utility in SSMS - but fail when running as a scheduled job, or called from within a Stored Procedure - failed every time.

I found my particular issue was not permission, per say, but rather the Exception file, I was referencing in the Data Driven Query task - which kept failing. Once I removed the Exception File, everything runs fine.

|||

I have had a similar problem running a SQL2000 DTS job from within an SSIS package. The strange thing I found that on the production server, it would readily run the job, yet a similarly created DTS on the development servers failed.

What was the "Exception file" that you had to remove?

|||

If you trust the internal logic of your package, then an error condition - especially from an Agent job - usually points to the package's interaction with external resources; in particular, to permissions issues. That's probably why no longer trying to write to an exception file solved the problem for the other gentleman. Check the package's use of database connections and file system resources, and determine whether the account under which it's executing does indeed have the required rights to connect, to read and write, etc.

Here's the Agent Job kb article:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;918760

-Doug

|||

I have rewrote the DTS package into a stored procedure that

1) Truncates the target tables

2) Copy the data from the source on another database to the target tables

3) Backs up the target database.

Now the account that would not run the DTS package runs a stored procedure successfully!

Error: System.Runtime.InteropServices.COMException ...

Error: System.Runtime.InteropServices.COMException (0x80040428): Exception from HRESULT: 0x80040428 at DTS.PackageClass.Execute() at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()

Can anyone tell what caused this error when I tried to run the DTS package from my SSIS package ?

Thanks.

Steve

I had the same problem....drove me batty. Packages run fine using Execute Package utility in SSMS - but fail when running as a scheduled job, or called from within a Stored Procedure - failed every time.

I found my particular issue was not permission, per say, but rather the Exception file, I was referencing in the Data Driven Query task - which kept failing. Once I removed the Exception File, everything runs fine.

|||

I have had a similar problem running a SQL2000 DTS job from within an SSIS package. The strange thing I found that on the production server, it would readily run the job, yet a similarly created DTS on the development servers failed.

What was the "Exception file" that you had to remove?

|||

If you trust the internal logic of your package, then an error condition - especially from an Agent job - usually points to the package's interaction with external resources; in particular, to permissions issues. That's probably why no longer trying to write to an exception file solved the problem for the other gentleman. Check the package's use of database connections and file system resources, and determine whether the account under which it's executing does indeed have the required rights to connect, to read and write, etc.

Here's the Agent Job kb article:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;918760

-Doug

|||

I have rewrote the DTS package into a stored procedure that

1) Truncates the target tables

2) Copy the data from the source on another database to the target tables

3) Backs up the target database.

Now the account that would not run the DTS package runs a stored procedure successfully!

Error: System.Runtime.InteropServices.COMException ...

Error: System.Runtime.InteropServices.COMException (0x80040428): Exception from HRESULT: 0x80040428 at DTS.PackageClass.Execute() at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()

Can anyone tell what caused this error when I tried to run the DTS package from my SSIS package ?

Thanks.

Steve

I had the same problem....drove me batty. Packages run fine using Execute Package utility in SSMS - but fail when running as a scheduled job, or called from within a Stored Procedure - failed every time.

I found my particular issue was not permission, per say, but rather the Exception file, I was referencing in the Data Driven Query task - which kept failing. Once I removed the Exception File, everything runs fine.

|||

I have had a similar problem running a SQL2000 DTS job from within an SSIS package. The strange thing I found that on the production server, it would readily run the job, yet a similarly created DTS on the development servers failed.

What was the "Exception file" that you had to remove?

|||

If you trust the internal logic of your package, then an error condition - especially from an Agent job - usually points to the package's interaction with external resources; in particular, to permissions issues. That's probably why no longer trying to write to an exception file solved the problem for the other gentleman. Check the package's use of database connections and file system resources, and determine whether the account under which it's executing does indeed have the required rights to connect, to read and write, etc.

Here's the Agent Job kb article:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;918760

-Doug

|||

I have rewrote the DTS package into a stored procedure that

1) Truncates the target tables

2) Copy the data from the source on another database to the target tables

3) Backs up the target database.

Now the account that would not run the DTS package runs a stored procedure successfully!

Error: System.Runtime.InteropServices.COMException ...

Error: System.Runtime.InteropServices.COMException (0x80040428): Exception from HRESULT: 0x80040428 at DTS.PackageClass.Execute() at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()

Can anyone tell what caused this error when I tried to run the DTS package from my SSIS package ?

Thanks.

Steve

I had the same problem....drove me batty. Packages run fine using Execute Package utility in SSMS - but fail when running as a scheduled job, or called from within a Stored Procedure - failed every time.

I found my particular issue was not permission, per say, but rather the Exception file, I was referencing in the Data Driven Query task - which kept failing. Once I removed the Exception File, everything runs fine.

|||

I have had a similar problem running a SQL2000 DTS job from within an SSIS package. The strange thing I found that on the production server, it would readily run the job, yet a similarly created DTS on the development servers failed.

What was the "Exception file" that you had to remove?

|||

If you trust the internal logic of your package, then an error condition - especially from an Agent job - usually points to the package's interaction with external resources; in particular, to permissions issues. That's probably why no longer trying to write to an exception file solved the problem for the other gentleman. Check the package's use of database connections and file system resources, and determine whether the account under which it's executing does indeed have the required rights to connect, to read and write, etc.

Here's the Agent Job kb article:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;918760

-Doug

|||

I have rewrote the DTS package into a stored procedure that

1) Truncates the target tables

2) Copy the data from the source on another database to the target tables

3) Backs up the target database.

Now the account that would not run the DTS package runs a stored procedure successfully!

ERROR: Subscription

After setting up and running the report subscription, the following Job error
message is returned?
'EXECUTE AS LOGIN' failed for the requested login 'NT AUTHORITY\NETWORK
SERVER'. The step failedI am sending the logfile for review. Thank you.
ReportingServicesService!dbpolling!9!11/28/2005-01:34:45:: EventPolling
polling service stopped
ReportingServicesService!dbpolling!c!11/28/2005-01:34:45:: EventPolling
heartbeat thread exiting for stop.
ReportingServicesService!dbpolling!9!11/28/2005-01:34:45::
NotificationPolling polling service stopped
ReportingServicesService!dbpolling!b!11/28/2005-01:34:45::
NotificationPolling heartbeat thread exiting for stop.
ReportingServicesService!dbpolling!9!11/28/2005-01:34:45:: SchedulePolling
polling service stopped
ReportingServicesService!dbpolling!9!11/28/2005-01:34:45:: UpgradePolling
polling service stopped
ReportingServicesService!servicecontroller!9!11/28/2005-01:34:47:: Service
controller exiting.
"Terry" wrote:
> After setting up and running the report subscription, the following Job error
> message is returned?
> 'EXECUTE AS LOGIN' failed for the requested login 'NT AUTHORITY\NETWORK
> SERVER'. The step failed
>|||Hi Terry,
Do you allow 'NT AUTHORITY\NETWORK SERVER' to access your database?
if i not wrong, the 'NT AUTHORITY\NETWORK SERVER' don't have permission to
access the database
thanks
Nicky