packages are empty

Hello,
I have this problem on my SQL Server, when I go to local packages and open a
package, nothing shows inside it, when I execute it, it says that completed
successfully but no tasks are shown. I have to restart the whole computer
(Win 2K Server) inorder to see what is inside a package again, then after a
while or the next day, they redisapear.
Any help in solving this would be highly appreciated
thank you
Hi
Very strange. Have you opened the package on the server or on the client
workstation?
"M K W" <admin@.admin.com> wrote in message
news:ewfg%23XaiEHA.1644@.tk2msftngp13.phx.gbl...
> Hello,
> I have this problem on my SQL Server, when I go to local packages and open
a
> package, nothing shows inside it, when I execute it, it says that
completed
> successfully but no tasks are shown. I have to restart the whole computer
> (Win 2K Server) inorder to see what is inside a package again, then after
a
> while or the next day, they redisapear.
> Any help in solving this would be highly appreciated
> thank you
>
|||I've heard of this when the dts dlls are not registered
correctly. Try reregistering the dts dlls or reapply Service
Pack 3.
-Sue
On Tue, 24 Aug 2004 09:51:06 +0300, "M K W"
<admin@.admin.com> wrote:

>Hello,
>I have this problem on my SQL Server, when I go to local packages and open a
>package, nothing shows inside it, when I execute it, it says that completed
>successfully but no tasks are shown. I have to restart the whole computer
>(Win 2K Server) inorder to see what is inside a package again, then after a
>while or the next day, they redisapear.
>Any help in solving this would be highly appreciated
>thank you
>
|||We have the same issue and if, as the previous poster asked, the
difference is only evident when connecting from a w/station, the
problem is in the build.
Not sure exactly what fixes it, may be re-application of latest
SP/patch.
Regards
ALI
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:<e0e$9faiEHA.3608@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
> Hi
> Very strange. Have you opened the package on the server or on the client
> workstation?
>
> "M K W" <admin@.admin.com> wrote in message
> news:ewfg%23XaiEHA.1644@.tk2msftngp13.phx.gbl...
> a
> completed
> a
|||Hello Uri,
Thanks for your reply, I am opening the package from the server. I havn't
installed the latest SP yet, but I am afraid not to work too.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e0e$9faiEHA.3608@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Hi
> Very strange. Have you opened the package on the server or on the client
> workstation?
>
> "M K W" <admin@.admin.com> wrote in message
> news:ewfg%23XaiEHA.1644@.tk2msftngp13.phx.gbl...
open[vbcol=seagreen]
> a
> completed
computer[vbcol=seagreen]
after
> a
>

packages are empty

Hello,
I have this problem on my SQL Server, when I go to local packages and open a
package, nothing shows inside it, when I execute it, it says that completed
successfully but no tasks are shown. I have to restart the whole computer
(Win 2K Server) inorder to see what is inside a package again, then after a
while or the next day, they redisapear.
Any help in solving this would be highly appreciated
thank youHi
Very strange. Have you opened the package on the server or on the client
workstation?
"M K W" <admin@.admin.com> wrote in message
news:ewfg%23XaiEHA.1644@.tk2msftngp13.phx.gbl...
> Hello,
> I have this problem on my SQL Server, when I go to local packages and open
a
> package, nothing shows inside it, when I execute it, it says that
completed
> successfully but no tasks are shown. I have to restart the whole computer
> (Win 2K Server) inorder to see what is inside a package again, then after
a
> while or the next day, they redisapear.
> Any help in solving this would be highly appreciated
> thank you
>|||I've heard of this when the dts dlls are not registered
correctly. Try reregistering the dts dlls or reapply Service
Pack 3.
-Sue
On Tue, 24 Aug 2004 09:51:06 +0300, "M K W"
<admin@.admin.com> wrote:
>Hello,
>I have this problem on my SQL Server, when I go to local packages and open a
>package, nothing shows inside it, when I execute it, it says that completed
>successfully but no tasks are shown. I have to restart the whole computer
>(Win 2K Server) inorder to see what is inside a package again, then after a
>while or the next day, they redisapear.
>Any help in solving this would be highly appreciated
>thank you
>|||We have the same issue and if, as the previous poster asked, the
difference is only evident when connecting from a w/station, the
problem is in the build.
Not sure exactly what fixes it, may be re-application of latest
SP/patch.
Regards
ALI
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:<e0e$9faiEHA.3608@.TK2MSFTNGP09.phx.gbl>...
> Hi
> Very strange. Have you opened the package on the server or on the client
> workstation?
>
> "M K W" <admin@.admin.com> wrote in message
> news:ewfg%23XaiEHA.1644@.tk2msftngp13.phx.gbl...
> > Hello,
> > I have this problem on my SQL Server, when I go to local packages and open
> a
> > package, nothing shows inside it, when I execute it, it says that
> completed
> > successfully but no tasks are shown. I have to restart the whole computer
> > (Win 2K Server) inorder to see what is inside a package again, then after
> a
> > while or the next day, they redisapear.
> > Any help in solving this would be highly appreciated
> > thank you
> >
> >|||Hello Uri,
Thanks for your reply, I am opening the package from the server. I havn't
installed the latest SP yet, but I am afraid not to work too.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e0e$9faiEHA.3608@.TK2MSFTNGP09.phx.gbl...
> Hi
> Very strange. Have you opened the package on the server or on the client
> workstation?
>
> "M K W" <admin@.admin.com> wrote in message
> news:ewfg%23XaiEHA.1644@.tk2msftngp13.phx.gbl...
> > Hello,
> > I have this problem on my SQL Server, when I go to local packages and
open
> a
> > package, nothing shows inside it, when I execute it, it says that
> completed
> > successfully but no tasks are shown. I have to restart the whole
computer
> > (Win 2K Server) inorder to see what is inside a package again, then
after
> a
> > while or the next day, they redisapear.
> > Any help in solving this would be highly appreciated
> > thank you
> >
> >
>sql

packages are empty

Hello,
I have this problem on my SQL Server, when I go to local packages and open a
package, nothing shows inside it, when I execute it, it says that completed
successfully but no tasks are shown. I have to restart the whole computer
(Win 2K Server) inorder to see what is inside a package again, then after a
while or the next day, they redisapear.
Any help in solving this would be highly appreciated
thank youHi
Very strange. Have you opened the package on the server or on the client
workstation?
"M K W" <admin@.admin.com> wrote in message
news:ewfg%23XaiEHA.1644@.tk2msftngp13.phx.gbl...
> Hello,
> I have this problem on my SQL Server, when I go to local packages and open
a
> package, nothing shows inside it, when I execute it, it says that
completed
> successfully but no tasks are shown. I have to restart the whole computer
> (Win 2K Server) inorder to see what is inside a package again, then after
a
> while or the next day, they redisapear.
> Any help in solving this would be highly appreciated
> thank you
>|||I've heard of this when the dts dlls are not registered
correctly. Try reregistering the dts dlls or reapply Service
Pack 3.
-Sue
On Tue, 24 Aug 2004 09:51:06 +0300, "M K W"
<admin@.admin.com> wrote:

>Hello,
>I have this problem on my SQL Server, when I go to local packages and open
a
>package, nothing shows inside it, when I execute it, it says that completed
>successfully but no tasks are shown. I have to restart the whole computer
>(Win 2K Server) inorder to see what is inside a package again, then after a
>while or the next day, they redisapear.
>Any help in solving this would be highly appreciated
>thank you
>|||We have the same issue and if, as the previous poster asked, the
difference is only evident when connecting from a w/station, the
problem is in the build.
Not sure exactly what fixes it, may be re-application of latest
SP/patch.
Regards
ALI
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:<e0e$9faiEHA.3608@.TK2MSFTNGP09.phx.gbl
>...[vbcol=seagreen]
> Hi
> Very strange. Have you opened the package on the server or on the client
> workstation?
>
> "M K W" <admin@.admin.com> wrote in message
> news:ewfg%23XaiEHA.1644@.tk2msftngp13.phx.gbl...
> a
> completed
> a|||Hello Uri,
Thanks for your reply, I am opening the package from the server. I havn't
installed the latest SP yet, but I am afraid not to work too.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e0e$9faiEHA.3608@.TK2MSFTNGP09.phx.gbl...
> Hi
> Very strange. Have you opened the package on the server or on the client
> workstation?
>
> "M K W" <admin@.admin.com> wrote in message
> news:ewfg%23XaiEHA.1644@.tk2msftngp13.phx.gbl...
open[vbcol=seagreen]
> a
> completed
computer[vbcol=seagreen]
after[vbcol=seagreen]
> a
>

Package.Execute/IDTSEvents vs DTExec

I am executing a package via vb.net with package.execute using the IDTSEvents interface inheriting DefaultEvents. I am trying to emulate DTExec functionality. The package runs fine and I am catching events, but I don't get nearly as many info and progress events as are put out by DTExec. From my understanding DTExec also used the managedDTS application object, is that not the case? If it uses this does anyone have an ideas on how it is grabbing more events? For example I don't get these events that DTExec shows ..

Info: 2006-09-26 14:22:27.97
Code: 0x40016041
Source: DWB02130
Description: The package is attempting to configure from the XML file "D:\SSIS\ConfigurationsDatabase.dtsConfig".
End Info
Info: 2006-09-26 14:22:28.05
Code: 0x40016040
Source: DWB02130
Description: The package is attempting to configure from SQL Server using the configuration string ""Configurations Database";"[dbo].[SSISConfigurations]";"DWB02130";".
End Info

I also don't get as many progress updates during validation, I basically get 0 and 100% while DTExec shows several 1%, 3%, 10%, etc.

Any help is greatly appriciated.

Thanks!
Harry

Do you set fireAgain parameter to true for events like OnInformation, OnProgress? If not, the event might not be fired again to avoid overhead associated with sending an event (that presumably nobody listens too).

You can also implement IDTSLogging interface and pass it to Execute method in addition to IDTSEvents interface.sql

Package.Execute/IDTSEvents vs DTExec

I am executing a package via vb.net with package.execute using the IDTSEvents interface inheriting DefaultEvents. I am trying to emulate DTExec functionality. The package runs fine and I am catching events, but I don't get nearly as many info and progress events as are put out by DTExec. From my understanding DTExec also used the managedDTS application object, is that not the case? If it uses this does anyone have an ideas on how it is grabbing more events? For example I don't get these events that DTExec shows ..

Info: 2006-09-26 14:22:27.97
Code: 0x40016041
Source: DWB02130
Description: The package is attempting to configure from the XML file "D:\SSIS\ConfigurationsDatabase.dtsConfig".
End Info
Info: 2006-09-26 14:22:28.05
Code: 0x40016040
Source: DWB02130
Description: The package is attempting to configure from SQL Server using the configuration string ""Configurations Database";"[dbo].[SSISConfigurations]";"DWB02130";".
End Info

I also don't get as many progress updates during validation, I basically get 0 and 100% while DTExec shows several 1%, 3%, 10%, etc.

Any help is greatly appriciated.

Thanks!
Harry

Do you set fireAgain parameter to true for events like OnInformation, OnProgress? If not, the event might not be fired again to avoid overhead associated with sending an event (that presumably nobody listens too).

You can also implement IDTSLogging interface and pass it to Execute method in addition to IDTSEvents interface.

package variables passed to Execute SQL Task

Hi,

I am having trouble getting the Execute SQL Task to recognize the package-level variables in an SSIS package. The tasks fail execution. The package contains three Execute SQL tasks and none of them receive the variables. However, when I replace the '?' in the code with the variable values they execute fine. These are the steps I have taken thus far:

Made sure the three variables are package level.

Verified I'm using an OLE DB valid connection.

Verified the variables are properly mapped in the parameters mapping section of the task properties.

Tried all 4 types of variable to parameter mapping (even though '?' in the query and '0,1,2..' in the name should work).

Changed the Bypassprepare property to 'True'.

Breakpoints and watch windows to verify the variable values.

I actually tried the simple tutorials on a one-liner SQL statement that uses variables, but had the same problem. I know there must be something simple that I'm missing here, but any info would be greatly appreciated!

Thanks!

Also, the code for the first package that holds the parameter is:

DECLARE @.DatabaseName varchar(100)
SET @.DatabaseName = ?
DECLARE @.SPID smallint

And, of course, when I replace the ? with the DB name in the Value field of the variable it executes.

The Parameter Mapping has the following values:

User:atabasename Input Varchar 0

And the Variables set up has the following values:

databasename package string DB_NAME

Is there a way to see the query after it reads the variables to see if I have something syntactically incorrect in the values? Please let me know if any further info would help.. I'm really stumped on this since I get the same results with examples found everywhere else too.

Thanks!|||

You could use an alternative approach to built the SQL statement. Create a new variable, let's say SQLStatement; then use an expression (set EvaluateAsExpresion=True) in that variable to form the SQL statement. something like:

"Select * from " + @.[User::Variable1]

Then in the execute SQL task set SQLSourceType=Variable and select the variable name in SourceVariable drop-down list.

I like this approach because you an see easily how the sql statement is being built.

|||Thanks for the quick reply Rafael. I am unclear as to how this would resolve the issue. The package contains three lengthy Execute SQL tasks, and the package needs to be replicated for several different databases that are independently accessed in a development environment. My original idea was to use the package variable to simply change the DB name. Are you saying I should try changing each task to a SQLSourceType=Variable and use a SQLStatement-like variable that uses an expression for the code presently used in the Execute SQL task? If so, it's definitely worth a try, but I'm still unclear as to how I'm misusing the package variables and I'm sure I'll find future situations where they will be useful. Any ideas?

Thanks again!
|||

jheywood wrote:

Are you saying I should try changing each task to a SQLSourceType=Variable and use a SQLStatement-like variable that uses an expression for the code presently used in the Execute SQL task?
Thanks again!

Yes, that is my idea. That give you the benefit of testing the expression and check how exactly the sql statement is gong to be sent.

jheywood wrote:

but I'm still unclear as to how I'm misusing the package variables and I'm sure I'll find future situations where they will be useful. Any ideas?

Not sure..did you try changing the value of ByPassPrepare in the execute sql task?

|||Yes, I tried that too. I was hoping it was maybe just some stupid syntax problem that I might have overlooked with the parameter or the variable, but I guess not.

I'll try the other method since that definitely seems like a viable option. Thanks so much for the help!

Please let me know if you think of anything else about the var use, or need more information. I'm really stumped on it.
sql

Package Validation Error in Custom PipelineComponent

Hi

I'm developing an PipelineComponent (ComponentType.Transform). When I try to execute this component, the following error is thrown:

Code Snippet

===================================

Package Validation Error (Package Validation Error)

===================================

Error at Data Flow Task [DTS.Pipeline]: Buffer Type 1 had a size of 0 bytes.

Error at Data Flow Task [DTS.Pipeline]: The buffer manager failed to create a new buffer type.

Error at Data Flow Task [DTS.Pipeline]: The Data Flow task cannot register a buffer type. The type had 50 columns and was for execution tree 0.

Error at Data Flow Task [DTS.Pipeline]: The layout failed validation.

Error at Data Flow Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

Program Location:

at Microsoft.DataTransformationServices.Project.DataTransformationsPackageDebugger.ValidateAndRunDebugger(Int32 flags, DataWarehouseProjectManager manager, IOutputWindow outputWindow, DataTransformationsProjectConfigurationOptions options)
at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchDtsPackage(Int32 launchOptions, ProjectItem startupProjItem, DataTransformationsProjectConfigurationOptions options)
at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchActivePackage(Int32 launchOptions)
at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchDtsPackage(Int32 launchOptions, DataTransformationsProjectConfigurationOptions options)
at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.Launch(Int32 launchOptions, DataTransformationsProjectConfigurationOptions options)

Can anyone help me, please? The "Integration Services Error and Message Reference" didn't help me much.

Thank you

Manuel Bauer

The error does not give me any clues other than it happens during a validation stage, so what is in your Validate method?

Have your tried debugging this, i.e. Visual Studio attached to the execution host?

|||

Hi

Thank you for your answer. My pipeline component's validate method runs without any problems, I debugged this in design time and in run time (before the exception occured). In my opinion, the problem is the early validation (http://sqljunkies.com/WebLog/knight_reign/archive/2005/04/20/12365.aspx) of the data flow task. As I expect, the data flow task does any validation itself.

I can't imagine what "The Data Flow task cannot register a buffer type. The type had 50 columns and was for execution tree 0." means, so I really don't know where the problem could be.

The exception is thrown immediately after running the package (also after running the validate method of my pipeline component).

|||

Does the package work if you take your component out of it?

The early validation vs late validation is the same Validate method calls, and since it generally works, I'd say it is clear that your transform has a problem, regardless of if it is the early or late call. Early and Late means normally Validate gets called twice, there is no difference between them. The Task will have a validate method, and a Data Flow will then call Validate for each child component.

How can the exception be thrown "after" running a package, Execute will never get called if a task fails validation. What is the return of your Validate method when you debug it?

Did you debug against dtsdebughost or just Visual Studio (designer)? For simplicity when trying to resolve run-time issues I set the code project debug option to use dtexec directly, just using /F and a package file. It is faster than attaching to a designer instance of VS.

|||

I found a solution for my problem. I was thinking wrong about this validation thing, I thought that the data flow task does some independant, general validation itself.

In my component, I "forgot" to call base.Validate(), which would no be a problem if the own validate method is implemented as id should. In my case, it was not.

Thank you for your competent analysis of my problem.

Manuel Bauer

Package Validation Error + Code 0xC004801C

Hi,

I am learning to use SSIS for creating packages. I am getting the following error when I try to execute my package...

Package Validation Error

Error at Data Flow Task[DTS. Pipeline]:The "runtime connection "FlatFileConnection "(122)" in the connection manager collection, Connections, of component "Flat File Destination ("120") does not have a value for the ID property. Verify that the ConnectionManagerID property of the runtime connection object has been set for the component.

Error at Data Flow Task [DTS.Pipeline]: component "Flat File Destination" (120) failed validation and returned error code 0xC004801C.

Error at Data Flow Task[DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

I'd appreciate if someone can help me with this. Do I need to change some settings? Also, what does the number in brackets indicate, say in FlatFileConnection(122)? Is it valuable information while debugging?

Just to give an idea, my package is like this -

source FlatFile -> Derived Column -> Lookup1 ->Lookup2 ->OLE DB destination(SQL server DB table)

Lookup1 and Lookup2 have error redirected to one flat file destination each.

Thanks in advance!

Geez....the error was just because I hadn't assigned a Connection Manager to that Flat File destination!! Oops!!!

Package Runs but as soon as it is scheduled in SQL Server it hangs

Hi

I'm trying to get a cute FTP script running from a package that connects to a FTPS web site.

Regardless of what method I use to execute the script it runs sucessfully in Debug mode, if I import the package into Integration Services from SQL Server Management Studio it runs, however as soon as create a SQL Server job using the stored package it hangs.

I have tried an Active X, VB.Net and an Execute Process item and get the same result everytime.

I'm also getting the same problem now with a java script I'm running from an 'Execute Process' item. Runs fine until I create a job..

Has anyone experienced the same problem? I haven't a clue what is going on and the logs aren't giving me much information.

Thanks for your help

one thing to take into account is that the script will most likely run under a different security context when it is running as a job. When you debug the script it's running with your security context, when running as a job it will run under either a proxy account context or the context of the sql agent service.

This change in security context may be causing a dialog box to pop, or you may be experiencing some kind of hidden failure.

I'd suggest having a look at the script and evaluating what security context is needed for each call. After that insert some logging into the script and evaluate what statement is not returning.

Package Hangs With Transations On

When I set the transaction option to required my package just hangs when I try to execute it. The status bar says "Validating" and then the name of the first destination data flow component (whatever that happens to be). I've let it sit for long periods and nothing happens. Any suggestions?

It's quite possible that the destination is trying to join the transaction (if enabled by you) and things start going wrong in there...

Can you tell us a little more about the components inside your package? Which ones are joining the transaction and what are their transaction properties set to?

sql

package goes into loop while reading paradox file

I have an SSIS package which is suppose to read this db file and insert it into the SQL 2005 table. But when I execute this package it goes into a loop, reading the file over and over again. The package fails after inserting some 10 million rows ( actual rows are not more then 270,000) giving the error msg'Not enough space on temporary disk'.

On examing the data transfered into SQL there are duplicate rows.

I also used the import export wizard (thinking there might be some error in Package code) provided by SQL to transfer the data from .db to SQL but it has the same result(goes in a loop).

I would appreciate any help in this problem. Let me know if you have any other questions.

Thanks

Do you have anything except the source and the destination in your package?

Thanks.

|||

If incase you are successful extracting 5-10 rows from a paradox file, The same must do for 270000 as well. Please also check if ending rows were corrupt.

|||

Hello Bob,

The package which I have created has source, conversion and destination components. The conversion component converts nvarchar field to varchar. This package works on another .db file say file 'YYY.db'. but then fails to read file 'ZZZ.db'.

The file does not seem to be corrupted coz when I try to view it through a paradox viewer it gives me the correct record count, field names etc and also displays the data correctly. We also have another viewer through which the file attributes and data are correctly displayed. Therefore it seems that ZZZ.db is not corrupted. The package also reads the file but then it fails to stop reading, seems as if it thinks there is no end of file. But the other independent viewers are able to read the file.

My computer does have the latest updates. Any ideas/suggestions/thoughts would be greatly appreciated.

Thanks,

Smoky

Package fails when it is scheduled

Hi
I'm using SQL Server 2000. I've created a local package which runs
fine when i execute it manually from Enterprise Manager, but
constantly fails when I schedule it. Can anybody suggest why this
might be?
Thanks
Colin
Most probably, its the permissions given to the userid that is used to
call the job.
either case, What does the history log say? (right click on the job
and select "view history")
On Mar 6, 5:52Xpm, Bobby <bob...@.blueyonder.co.uk> wrote:
> Hi
> I'm using SQL Server 2000. I've created a local package which runs
> fine when i execute it manually from Enterprise Manager, but
> constantly fails when I schedule it. Can anybody suggest why this
> might be?
> Thanks
> Colin
|||On 6 Mar, 23:56, schal <shivaramcha...@.gmail.com> wrote:
> Most probably, its the permissions given to the userid that is used to
> call the job.
> either case, What does the history log say? (right click on the job
> and select "view history")
>
The history log says the following:
Executed as user: SQLSRV01\SYSTEM. ...Start:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_2 DTSRun OnError:
DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error
string: The driver returned invalid (or failed to return)
SQL_DRIVER_ODBC_VER: 2.00 Error source: Microsoft OLE DB
Provider for ODBC Drivers Help file: Help context: 0
Error Detail Records: Error: -2147467259 (80004005); Provider
Error: 444 (1BC) Error string: The driver returned invalid (or
failed to return) SQL_DRIVER_ODBC_VER: 2.00 Error source:
Microsoft OLE DB Provider for ODBC Drivers Help file: Help
context: 0 Error: -2147467259 (80004005); Provider Error: 0
(0) Error string: Cannot find all files in data path Error
source: Microsoft OLE DB Provider for ODBC Drivers Help
file: ... Process Exit Code 2. The step failed.
The package has just three steps. Step 1 is an execute SQL task which
deletes from two tables. Steps 2 & 3 connect to our accounts package
(Sage) using ODBC and uses two Transform data tasks to copy data from
Sage tables to the two SQL server tables. I am connecting to SQL sever
using the Microsoft OLE DB provider. I know that Step 1 is working
because after the package fails the two SQL tables are empty.
The package works fine if I
|||On 8 Mar, 09:44, Bobby <bob...@.blueyonder.co.uk> wrote:
> On 6 Mar, 23:56, schal <shivaramcha...@.gmail.com> wrote:
>
> The history log says the following:
> Executed as user: SQLSRV01\SYSTEM. ...Start:
> DTSStep_DTSExecuteSQLTask_1 X DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_1 X DTSRun OnStart:
> DTSStep_DTSDataPumpTask_1 X DTSRun OnStart:
> DTSStep_DTSDataPumpTask_2 X DTSRun OnError:
> DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) X X XError
> string: XThe driver returned invalid (or failed to return)
> SQL_DRIVER_ODBC_VER: 2.00 X X XError source: XMicrosoft OLE DB
> Provider for ODBC Drivers X X XHelp file: X X X XHelp context: X0
> Error Detail Records: X X XError: X-2147467259 (80004005); Provider
> Error: X444 (1BC) X X XError string: XThe driver returned invalid (or
> failed to return) SQL_DRIVER_ODBC_VER: 2.00 X X XError source:
> Microsoft OLE DB Provider for ODBC Drivers X X XHelp file: X X X XHelp
> context: X0 X X X X Error: X-2147467259 (80004005); Provider Error: X0
> (0) X X XError string: XCannot find all files in data path X XXError
> source: XMicrosoft OLE DB Provider for ODBC Drivers X X XHelp
> file: X X ... XProcess Exit Code 2. XThe step failed.
> The package has just three steps. Step 1 is an execute SQL task which
> deletes from two tables. Steps 2 & 3 connect to our accounts package
> (Sage) using ODBC and Xuses two Transform data tasks to copy data from
> Sage tables to the two SQL server tables. I am connecting to SQL sever
> using the Microsoft OLE DB provider. I know that Step 1 is working
> because after the package fails the two SQL tables are empty.
> The package works fine if I
.....right click on it and select Execute package. It only fails when
it is scheduled. Sorry, I got carried away when typing and hit the
wrong key!
Thanks for any help,
Colin
sql

Package fails when it is scheduled

Hi
I'm using SQL Server 2000. I've created a local package which runs
fine when i execute it manually from Enterprise Manager, but
constantly fails when I schedule it. Can anybody suggest why this
might be?
Thanks
ColinMost probably, its the permissions given to the userid that is used to
call the job.
either case, What does the history log say? (right click on the job
and select "view history")
On Mar 6, 5:52=A0pm, Bobby <bob...@.blueyonder.co.uk> wrote:
> Hi
> I'm using SQL Server 2000. I've created a local package which runs
> fine when i execute it manually from Enterprise Manager, but
> constantly fails when I schedule it. Can anybody suggest why this
> might be?
> Thanks
> Colin|||On 6 Mar, 23:56, schal <shivaramcha...@.gmail.com> wrote:
> Most probably, its the permissions given to the userid that is used to
> call the job.
> either case, What does the history log say? (right click on the job
> and select "view history")
>
The history log says the following:
Executed as user: SQLSRV01\SYSTEM. ...Start:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_2 DTSRun OnError:
DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error
string: The driver returned invalid (or failed to return)
SQL_DRIVER_ODBC_VER: 2.00 Error source: Microsoft OLE DB
Provider for ODBC Drivers Help file: Help context: 0
Error Detail Records: Error: -2147467259 (80004005); Provider
Error: 444 (1BC) Error string: The driver returned invalid (or
failed to return) SQL_DRIVER_ODBC_VER: 2.00 Error source:
Microsoft OLE DB Provider for ODBC Drivers Help file: Help
context: 0 Error: -2147467259 (80004005); Provider Error: 0
(0) Error string: Cannot find all files in data path Error
source: Microsoft OLE DB Provider for ODBC Drivers Help
file: ... Process Exit Code 2. The step failed.
The package has just three steps. Step 1 is an execute SQL task which
deletes from two tables. Steps 2 & 3 connect to our accounts package
(Sage) using ODBC and uses two Transform data tasks to copy data from
Sage tables to the two SQL server tables. I am connecting to SQL sever
using the Microsoft OLE DB provider. I know that Step 1 is working
because after the package fails the two SQL tables are empty.
The package works fine if I|||On 8 Mar, 09:44, Bobby <bob...@.blueyonder.co.uk> wrote:
> On 6 Mar, 23:56, schal <shivaramcha...@.gmail.com> wrote:
> > Most probably, its the permissions given to the userid that is used to
> > call the job.
> > either case, What does the history log say? (right click on the job
> > and select "view history")
> The history log says the following:
> Executed as user: SQLSRV01\SYSTEM. ...Start:
> DTSStep_DTSExecuteSQLTask_1 =A0 DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_1 =A0 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_1 =A0 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_2 =A0 DTSRun OnError:
> DTSStep_DTSDataPumpTask_1, Error =3D -2147467259 (80004005) =A0 =A0 =A0Err=or
> string: =A0The driver returned invalid (or failed to return)
> SQL_DRIVER_ODBC_VER: 2.00 =A0 =A0 =A0Error source: =A0Microsoft OLE DB
> Provider for ODBC Drivers =A0 =A0 =A0Help file: =A0 =A0 =A0 =A0Help contex=t: =A00
> Error Detail Records: =A0 =A0 =A0Error: =A0-2147467259 (80004005); Provide=r
> Error: =A0444 (1BC) =A0 =A0 =A0Error string: =A0The driver returned invali=d (or
> failed to return) SQL_DRIVER_ODBC_VER: 2.00 =A0 =A0 =A0Error source:
> Microsoft OLE DB Provider for ODBC Drivers =A0 =A0 =A0Help file: =A0 =A0 ==A0 =A0Help
> context: =A00 =A0 =A0 =A0 =A0 Error: =A0-2147467259 (80004005); Provider E=rror: =A00
> (0) =A0 =A0 =A0Error string: =A0Cannot find all files in data path =A0 =A0= =A0Error
> source: =A0Microsoft OLE DB Provider for ODBC Drivers =A0 =A0 =A0Help
> file: =A0 =A0 ... =A0Process Exit Code 2. =A0The step failed.
> The package has just three steps. Step 1 is an execute SQL task which
> deletes from two tables. Steps 2 & 3 connect to our accounts package
> (Sage) using ODBC and =A0uses two Transform data tasks to copy data from
> Sage tables to the two SQL server tables. I am connecting to SQL sever
> using the Microsoft OLE DB provider. I know that Step 1 is working
> because after the package fails the two SQL tables are empty.
> The package works fine if I
=2E....right click on it and select Execute package. It only fails when
it is scheduled. Sorry, I got carried away when typing and hit the
wrong key!
Thanks for any help,
Colin

Package fails to execute as a scheduled job

I've managed to get the basics coded into my package. Nothing fancy, it is all quite literally embedded inside the package. No config files, parameters, variables, etc. I've imported the package into SSIS. I can right click on the package and select Run Package. The package fires off, validates, runs through the entire process, and completes successfully. However, when I set up the package to run as a scheduled task, it fails almost immediately with the following output.

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 64-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 3:28:07 AM
Error: 2007-04-25 03:28:07.41
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTSroperty" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
End Error
Error: 2007-04-25 03:28:11.36
Code: 0xC001602A
Source: Package Connection manager "FTP Connection Manager"
Description: An error occurred in the requested FTP operation. Detailed error description: The password was not allowed
.
End Error
Error: 2007-04-25 03:28:11.36
Code: 0xC002918F
Source: Card10 FTP Task
Description: Unable to connect to FTP server using "FTP Connection Manager".
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 3:28:07 AM
Finished: 3:28:11 AM
Elapsed: 4.297 seconds

Help.......

Hi Michael,

There was a discussion of a similar error message here recently. What is the ProtectionLevel property setting for the package?

Andy

|||

There is also a KB article where they describe 4 diffrent methods for schedulling the package:

An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step:

http://support.microsoft.com/kb/918760

Package fails but single Task ends with success

Hi

I've created a simple package that contains only one task that is an execute sql task. When I run only this single task from Business Intelligence development studio it runs successfully. But when I run the whole package (also from Business intlligence studio), the package fails.

The data source I access is ODBC. I'm sure the real reason for the error is the bad ODBC driver of the data source but this can't be changed. So I need to know what is different from running only a task in a package to running the whole package. If I knew that I might be able to adjust some setting and make it work.

Any help welcome.

What is the error you are receiving?

Rafael Salas

|||I had a similar probem the other day.

all the individual steps would run ok in visual studio, but the package as a whole would fail.

the problem was a spurious executable that was not visible on the control flow design screen, but was listed as an executable in package explorer.

its was this spurious executable that was failing, and so causing my package to fail.

have a look to see what is shown as an executable in the package explorer tab?|||

Hi Rafael

Thanks for your answer.

I get the messagebox-error with "Unable to load DLL, Fatal Error!" in the messagebox title and the dll's path in the message body. The dll that is indicated there belongs to the system that should be accessed.

Christian

|||

I've checked the package in package explorer but there's only the one executable that I've just created.

Christian

sql

Package Execution with SQL Server Agent or DTEXEC

Hello,

I want execute package with SQL Server Agent. I create a job and I try all the solutions : my job doesn't want to work.

I success to launch the package but the execution failed.

I try the same job launching from the server : it doesn't work either.

So, I copy the command line (/FILE, /DTS or /SQL) and with DTEXEC utility it works when this command is executed on the server (on a client computer, the problem "the product level is insufficient" appears).

Why is it working with this utility and not with a job ?

I try to change every secured option (credential --> proxy --> Run as ; change the owner of the job ; etc.)

What options must be checked and modified ?

Thanks for your help !

Somewhere there will be an error, and without that I have no idea. Try using DTEXEC through a job.

Package Execution with SQL Server Agent or DTEXEC

Hello,

I want execute package with SQL Server Agent. I create a job and I try all the solutions : my job doesn't want to work.

I success to launch the package but the execution failed.

I try the same job launching from the server : it doesn't work either.

So, I copy the command line (/FILE, /DTS or /SQL) and with DTEXEC utility it works when this command is executed on the server (on a client computer, the problem "the product level is insufficient" appears).

Why is it working with this utility and not with a job ?

I try to change every secured option (credential --> proxy --> Run as ; change the owner of the job ; etc.)

What options must be checked and modified ?

Thanks for your help !

I need HELP please :)|||

We created a package which read a flat file from another server and imported it into a table. Our package would run, but the job would not.

We found that we had to change two options - one was the location of the flat file. Our package was looking at a specific directory like "J:\Import Files\file.txt". We replaced the specific drive letter with the shared directory alias \\ServerName\Alias\Import Files\file.txt.

The second change was to alter the package to "Rely on Server ..." instead of "Encrypt Sensitive With User Key".

These two changes allowed Sql Agent to run the job.

|||

Hello,

thank you for answering, I tried your solution but no effect appears.

Actually your solution enable to execute the package in a step with a job. It seems to not fail.

The package executes through DTEXEC works fine and my destination table is filled.

When I use the same command line with a job, it works but my table is still empty. No new lines from the package !

How is it possible considering I use the same command line ?

sql

Package executing error

I wrote package using Integration Services and tried execute it on Visual Studio 2003 (Visual Basic). Package was loaded, but execute method has failed without any error messages. When I tried to run it on Visual Studio 2005, execution was successful.

Codes:

Dim App As DTSLib.IDTSApplication90 = New DTSLib.Application

Dim p As DTSLib.IDTSPackage90 = App.LoadPackage("d:\temp\Package.dtsx", True, Nothing)

p.Execute()

What can be the reason of the trouble?

Thanks,

Alexander

Only one .NET runtime can live in one process. Visual Studio 2003 uses .NET 1.1, and SSIS requires .NET 2.0 - so SSIS fails if the process has already loaded .NET 1.1.

You can use config file to change the .NET version used by applications developed with .NET 1.1 and force them to use .NET 2.0, but it will break Visual Studio debugging.

So it is highly recommended to use Visual Studio 2005. In Visual Studio 2005 you can also use managed API for SSIS - in Microsoft.SqlServer.ManagedDTS assembly, which is more convinient than using interop from DTSLib.

Package cancelled

Hi,
I'm trying to transfer data from a DB2 database to a MS SQL Server database (approx. 150 000 rows).

When I execute the package, the process is always cancelled (In output window: SSIS package "DB2_test_TranAddl.dtsx" finished: Canceled.)

I use a view on DB2 to provide the data and I can modify it to return only a few rows. To achieve this, I use a WHERE clause: WHERE OUTLET_NO Between X And Y.

When I try OUTLET_NO Between 1 And 5 (255 rows) the package executes successfully.
When I try OUTLET_NO Between 5 And 10 (388 rows) it works.

When I try OUTLET_NO Between 1 And 10 (643 rows) it fails!!
Any idea?

How fast does each of the queries run (1-5 and 5-10)? Suppose the connector is timing out?|||

Phil Brammer wrote:

How fast does each of the queries run (1-5 and 5-10)? Suppose the connector is timing out?

A few seconds (less than 5)

The timeouts are set to 0.

I tried changing them to 60 seconds but it doesn't work...

|||

OK here's an update:

I was using the IBM driver, it is not functionnal.

I installed the Microsoft OLE DB provider for DB2 and (after spending like half an hour trying to configure the connection) it works. 150 000 rows transferred successfully in a little more than 8 minutes. Takes about 1min30sec. with the DTS version so I guess I have to do some tweaking...

EDIT: I use the FastLoad on the destination. Takes approx 1min30sec.

|||

Fleo,

I have the same performance issue you mentioned. 300,000 rows takes only 3 minutes with the ODBC connection in DTS, but more than 5 minutes with the microsoft OLD DB provider for DB2 in SSIS. Have you figured out any tweaking that may help speed up the transfer?

Thanks.

|||

Any reason you can't use an ODBC connection in SSIS?

-Jamie

|||I need to load data from SQL2005 to AS400 and there is no ADO.NET/ODBC destination. Also, the unicode issue.|||

Jane2006 wrote:

I need to load data from SQL2005 to AS400 and there is no ADO.NET/ODBC destination. Also, the unicode issue.

Right, well, let's keep your issue to the thread you started: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1723508&SiteID=1

You can use the OLE DB Destination to write to AS400.

Package cancelled

Hi,
I'm trying to transfer data from a DB2 database to a MS SQL Server database (approx. 150 000 rows).

When I execute the package, the process is always cancelled (In output window: SSIS package "DB2_test_TranAddl.dtsx" finished: Canceled.)

I use a view on DB2 to provide the data and I can modify it to return only a few rows. To achieve this, I use a WHERE clause: WHERE OUTLET_NO Between X And Y.

When I try OUTLET_NO Between 1 And 5 (255 rows) the package executes successfully.
When I try OUTLET_NO Between 5 And 10 (388 rows) it works.

When I try OUTLET_NO Between 1 And 10 (643 rows) it fails!!
Any idea?

How fast does each of the queries run (1-5 and 5-10)? Suppose the connector is timing out?|||

Phil Brammer wrote:

How fast does each of the queries run (1-5 and 5-10)? Suppose the connector is timing out?

A few seconds (less than 5)

The timeouts are set to 0.

I tried changing them to 60 seconds but it doesn't work...

|||

OK here's an update:

I was using the IBM driver, it is not functionnal.

I installed the Microsoft OLE DB provider for DB2 and (after spending like half an hour trying to configure the connection) it works. 150 000 rows transferred successfully in a little more than 8 minutes. Takes about 1min30sec. with the DTS version so I guess I have to do some tweaking...

EDIT: I use the FastLoad on the destination. Takes approx 1min30sec.

|||

Fleo,

I have the same performance issue you mentioned. 300,000 rows takes only 3 minutes with the ODBC connection in DTS, but more than 5 minutes with the microsoft OLD DB provider for DB2 in SSIS. Have you figured out any tweaking that may help speed up the transfer?

Thanks.

|||

Any reason you can't use an ODBC connection in SSIS?

-Jamie

|||I need to load data from SQL2005 to AS400 and there is no ADO.NET/ODBC destination. Also, the unicode issue.|||

Jane2006 wrote:

I need to load data from SQL2005 to AS400 and there is no ADO.NET/ODBC destination. Also, the unicode issue.

Right, well, let's keep your issue to the thread you started: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1723508&SiteID=1

You can use the OLE DB Destination to write to AS400.