I have a SSIS job, one of the last steps it performs is to execute a SQL 2000 DTS package. This has to be done as a SQL 2000 DTS package as it is performing rebuilds of SQL 2000 Analysis Services dimensions and cubes. We've found that when the DTS fails the SSIS job is happily completing showing as a success, we would prefer to know it went wrong.
As far as I'm aware SSIS merely starts the DTS off and doesn't care about it's result. I've taken a look in to turning on the logging for the execute DTS package and thought that the ExecuteDTS80PackageTaskTaskResult would give me the answer I need...but is merely written to the log not available as an event-handler. It also looks like it is not safe to put a SQL task in as the next item to go look at the SQL 2000 system tables to look at the log for the DTS package as the SSIS documentation warns that the DTS package can continue to run after the execute DTS package task has ended.
Ideally I want any error raised within the DTS package to cascade up to be an error in the SSIS job, I can then handle it appropriately. I cannot find a way to do this. Is there a way?
If not, can anyone suggest how in the remainder of the SSIS tasks I can be sure that the DTS has completed before I start any other tasks that will check for the SQL 2000 log of its execution?
I have developed an SSIS package for ETL purpose. I am invoking the SSIS package through .Net console application by referencing the ManagedDTS Assembly. I am able to execute the package in Sql Server 2005 Developer Edition and it runs fine till completion.
But when i try to execute the packahe in Sql Server 2005 Standard edition, by invoking the package through .Net console application the status of the package is failure.
Can any one help me how to over come this problem.
I have a SSIS Package with a "Execute Package Task" to call a child package. I am trying to have the master/parent package complete its execution regardless the outcome (failure or success) of the child package. The overall structure of the master package is:
1. Perform Pre-load tasks (stored procedure).
2. Execute Package Task (call child package)
3. Perform Post-load Tasks (stored procedure)
I have try everything and cannot get the results that I want... I have tried the combination of "failparentonfailure", "forceexecutionvalue", etc. The master package stops at childs failure. I would like to resume to completion
When saving a DTS package, we get 4 options (listed below). Can someone please guide me which options should I used and what's the difference/advantage/disadvantage amongst these options:
[list=1] SQL Server SQL Server Meta Data Services (this appears dimmed/disabled - i don't know why :confused: Structured Storage File Visual Basic File[/list=1]
My team is getting this message when altering a DTS Package, using SQL 2000 DTS Designer. We have downloaded all the SQL updates (Backward Compatibility, and the one specifically for DTS Save problems. Have tried this in SQL2005 as well. Have uninstalled both of these programs, and reinstalled on local machines. No avail. Here is what happens:
1) Editing an existing DTS Package, such as a Text Change, or simple connection change 2) Saving the file creates the exact message below:
Package Error Error Source: Microsoft Data Transformation Services (DTS) Package Error Description: Code execution exception: EXCEPTION_ACCESS_VIOLATION
3) Then you have to choose OK in the windows Popup Box. 4) The DTS is brought back to the original screen, or completely exited out of Designer. 5) From that point, the Package can no longer be opened for editing, unless you open a previous version (which means changes aren't saved.
We have also tried to SAVE AS another filename. This doesn't work either.
Our team has close to 75 DTS Packages, some simple, some more complex. We are corrupting these left and right. This problem happens no matter what is created in the DTS Package.
This is running into our business operations, and we are unable to find out a resolution...HELP
Hi MS NewsgroupI am using SQL 2000 SP3 + latest hot fixes and I am editing a DTSpackage. When I save the package I get a SQL Server Access Violationand cannot save the package. This does not crash SQL nor EnterpriseManager but no matter what I do I cannot save the package. This occursfor only 1 package on the server (others are ok) and for all users ofany privilaged access level. I have tried to save to file, VB, etc butall fail similarly.I have read MS KB and have tried all options they suggest but to noavail! I'd like not to have to rewrite the DTS again from scratch.Ideas anyone?Thanks
Hi all, I know that you can save a DTS package to SQL Server (localpackage under Data Transformation Services in the EM).I wonder can I limit which login has the right to save DTS package? Imean, I would like logins with sa right to have this right but not forother ordinary logins.Can this be done in SQL Server?
I am trying to load a ssis package into a folder (lets say SSIS packages) that I created in MSDB but its geeting saved in the root (MSDB) not into the SSIS packages folder...I am doing this thru C#...
Primary platform is Framework 2.0 and Sql25k 64-bit When I try to save my DTSX package from my client to the production server I get this error: "No Description found"
I have several DTS packages saved 'locally' to the SQL server. I want to duplicate a package, so that I can make some changes then replace the original. I certainly don't want to rebuild the entire package from scratch. So, I open up the original package, go to the 'Package' menu and choose 'Save As', then give it a new name and press OK. No errors, all appears well, the title bar even shows the new name of the package. But, when I close the package and go the the 'local' package list, it (the new package name) doesn't appear in the list. Refresh, exit SEM, reboot - doesn't show up. I even looked in the MSDB table where packages are supposed to be stored (at least the name / package id / etc), and it doesn't show there as well. Tried from several client machines.
OS: Windows 2000 Server (advanced) SP2 SQL: SQL 2000 Server (no SP's)
I am using the IBM DB2 UDB for iSeries IBMDASQL OLE DB Provider to import data via query (in a tranformation task) to a SQL2000 server within a DTS package.
Works great! However, the provider does not save my (iSeries) password although I choose this option. I really need the password to be saved so that I can schedule the DTS package... I don't want to have to login and run the package each time (even IT guys take vacations). Or at least I don't want to have to login everytime the server is restarted...
I am getting this when I try to save my package. Any Ideas?
Failed to apply package protection with error 0xC0014061 "The protection level, ServerStorage, cannot be used when saving to this destination. The system could not verify that the destination supports secure storage capability.". This error occurs when saving to Xml.
I am using the IBM DB2 UDB for iSeries IBMDASQL OLE DB Provider to import data via query (in a tranformation task) to a SQL 2000 server within a DTS package.
Works great! However, the DTS package(?) does not save my (iSeries) password although I choose this option. I really need the password to be saved so that I can schedule the DTS package... I don't want to have to login and run the package each time (even IT guys take vacations). Or at least I don't want to have to login everytime the server is restarted...
Perhaps I am ignorant, but, I can not find any configuration setting within DTS that will allow me to save the password for the IBMDASQL OLE DB provider. Nor have I been able to find a suitable solution to this by using the keywords search string of "package configuration" within the MSDN forums.
As a workaround, I can use the an ODBC connection along with a DSN file to save the username and password. However, I do not want this information sitting out there in plain text. I would like to use the OLE DB provider and store the username and password on the SQL server as the properties dialog says that I should be able to. Or I can define a UDL (still text file).[Or I can use an ODBC connection set up as a system DSN - I want to use the OLE DB provider]
I've read about adding a connection string to .Net's machine.config file - but, I do not want to use the .NET OLE DB provider and, again, I do not wish to store username and password information in plain text.
Is this a bug in as much as how SQL Server 2000 uses the IBMDASQL OLE DB provider?
I've called and spoke to an IBM tech in regard to this and was informed that the dialog box and error regarding the password are not IBM generated, but rather MS generated.
I'm new to SQL 2005. I am setting up some SSIS packages which will connect to an Oracle database and copy some tables from it. These packages will then be scheduled to run on a daily basis. Because they will run automatically, it is required that passwords be saved along with the connection string. However, even though the password is saved (and encrypted, I checked the .dtsx in notepad), when I run the package, the connection to Oracle fails. Only if I respecify the password does it run correctly. How can I correctly save this password so that I can schedule automatic execution? Thanks for any info.
I am running SQL Server 2000 SP3 and I am trying to save a DTS package into Meta Data Services and I am receiving the following "Package Error"
Error Source: Microsoft Data Transformation Services (DTS) Package Error Description: General Error -2147217355 (80041035).
I have searched for this error and I cannot find anything related to it. Also, I saw some of the comments about right clicking "Data Transformation Services" and checking the box for allow save to Meta Data Services, however, I do not see that checkbox to allow for this. Has anyone else had this problem and resolved it? I'm beginning to get very frustrated with it.
When I try to run any dts package that tries to copy stored procedures from one db to another, it fails and i get this message:
Need to run the object to perform this operation [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION
The problem appeared a couple of weeks ago, the same package that now fails ran with no problems for some 6 months or so.
When i searched the Microsoft site i found a bugfix that fit the problem exactly but the workaround and SP3 installation (which supposedly fixes the problem) didn't work. Please help!!
I backed up my database on the live server copied it to the backup server and restored it successfully. I then set up a DTS export on the live server to copy objects and data to the backup db. The job keeps failing. Each time it fails it reports an "ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint ...... However, there is nothing wrong with any foreign key, I have checked and the data in the tables are definitely fine and key is definitely fine (I removed it and added it again checking the data). I am able to DTS these tables which are supposed to have problems as a separte DTS job. I believe the job is inserting data in the table which has the foreign key before it inserts data in the table with the primary key and this is causing the problem. As an aside this job used to work perfectly well.
Can someone offer a suggestion on what I should do.
I have a DTS package which keeps failing giving me the error message “A connection with the transaction manager was lost". The DTS package is using two different servers as connection and the DTS package has Use Transaction, Rollback transaction on failure and Join transaction if present ticked. I have checked the MSDTC service is running on both servers, the following options on both servers have been checked; Network DTC Access, Allow Inbound and Outbound. Does anybody know what is causing this error message?
Because of recent changes in the company, I have inherited the SQL Server. I have a little bit of experience, but I will be knee deep in study for the foreseeable future. In the meantime, I do have to manage the SQL Server and deal with all of the problems. It has been fairly self-sufficient until now. I have a regularly scheduled SSIS package that has recently been erroring out. I just don't even know where to start figuring this out. When I look at the History of this two step job, I see that it's failing on the second step, which is a T-SQL script. I am way more of a GUI kinda guy (at least for now) and I am lost when looking at the script. I know that I am not giving enough info in order for someone to help me, but my problem is I just don't know where to even look. Can someone at the very least point me to a place where I can get a better clue as to what is happening and why???
Thanks for listening and God bless anyone who understands at all what I'm even trying to ask!!!!!
Package Load Failure Package 'Microsoft.SqlServer.Tools.PublishWizard.VSCommands.MenuCommandsPackage, MenuAndCommands, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bg3856ad364e35' has failed to load properly ( GUID = {A38CF415-6AD2-45F6-8132-9CC33EBE0629}).
I get this error after I have opened VS and then open the Server Explorer Window.
I have been using VS2005 Professional since December of 2005 and have never encountered this before. I have never had a beta version of anything on this PC. I have been searching the Net but haven't found a solution. At this point, I haven't tried reloading VS because even if it does fix it, what is the guarantee that I will not encounter it again. I would rather find and fix the cause.
Does anyone have any ideas?
Additional: I have now uninstalled and reinstalled VS2005 and SQL Server 2005 including service packs and am still getting the error.
Additional: When I ran devenv.exe, the section of the resulting log referencing the GUID indicated an invalid Package Load Key:
i'm using a Foreach Container to run certain tasks, these ones are inside a Sequence Container. I'd like to know if there is a way to have a task failure without a foreach failure, i don't need the hole process to fail, just the current iteration so i can log it.
Anyone seen this error or know what this could be? I got this when executing a package. It works on my dev machine, however failed with this error on another test machine/environment.
The session was canceled. (Exception from HRESULT: 0x800700F0) (Microsoft Visual Studio)
------------------------------ Program Location:
at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo) at Microsoft.DataWarehouse.VsIntegration.Interop.NativeMethods.ThrowOnFailure(Int32 hr, Int32[] expectedHRFailure) at Microsoft.DataWarehouse.VsIn
We have an application comprising of a number of SSIS packages that run every few hours. When a package fails, we would like an email to be sent to a pre-configured address with the required information. I would like to use Send Mail task with SMTP server like smtp.sbcglobal.yahoo.com, but this server requires authentication and I need to provide my user_id and password for that. I have not been able to figure out where I can configure user_id and password for getting authenticated by the SMTP server. If SMTP connection cannot be used, is there any other way to notify admin of the failure?
I am running a DTS Package from a stored procedure using xpcmdshell. The DTS Package begins with a SQL Task to delete records from 2 tables (this works fine), but the data transfer task for importing records from a SQL Anywhere 5.0 database gives me the error 'Unable to connect to database server: Unable to start database engine'. the weird thing is that from Enterprise Manager I can execute the DTS Package and it works fine. What am I missing here?????
Trying to build a deployment package. I have a number of dtsx in a project that share a connection config file. When I build, the error states: 'Could not copy file "whatever.dtsconfig" to the deployment utility output directory. ... The file already exists'
I have a package that is failing because of a truncation error. Now, by default (and I leave this for ALL my packages) if one row fails processing the entire package should fail and nothing gets loaded into db. But instead I am actually getting a partial db load.
I have confirmed the "Rows per btach" value (blank) and the "Maximum insert commit size" value (0) for the OLE DB Destination Editor so I have no idea what is going on. Are there any other properties I should be checking?
I am developing an SSIS package and need the execution of the package to continue even if one of the tasks within the package fails. I have an OnError event handler for this task which fires when it fails but want the rest of the package to continue.
I have a job that moves a file to import directory where the SSIS package picks it up and processes it. 95% of the time is works flawlessly and fast.
The other 5% of the time the process fails. It appears that the file is inaccessible to SQL Server. I run it again and it works perfectly. It appears to be completely hit and miss.
The last step in the package is on-completion delete the file. When the package fails, the file (as directed) is not deleted.
I've included a snippet of the error log. I can place the log out here if it would help more.
Any ideas what could possibility going wrong? I hate inconsistent failures.
Warning: 2008-04-06 15:05:30.80 Code: 0x80070002 Source: Data Flow Task Flat File Source [317] Description: The system cannot find the file specified. End Warning Error: 2008-04-06 15:05:30.80 Code: 0xC020200E Source: Data Flow Task Flat File Source [317] Description: Cannot open the datafile "D:Processes 40720080105260140306.txt". End Error Error: 2008-04-06 15:05:30.80 Code: 0xC004701A Source: Data Flow Task DTS.Pipeline Description: component "Flat File Source" (317) failed the pre-execute phase and returned error code 0xC020200E. End Error
