?
Solved

Migrated DTS package problem

Posted on 2011-10-18
10
Medium Priority
?
1,545 Views
Last Modified: 2012-05-12
I have mograted a simple DTS package to SSIS 2008. All seemed fine during migration.
Step 1: delete contents of an MS Access table - works fine
Step 2: transfer rows from SQL tables to MS Access table - Works fine
Step 3: FTP MS Access DB using a control file - will not parse/test.

I get a red exclamation mark against step 3 and the error:
===================================

Package Validation Error (Package Validation Error)

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

Error at FTP using control file [Execute Process Task]: File/Process "" is not in path.

Error at FTP using control file: 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.DtsPackagesFolderProjectFeature.ExecuteTaskOrPackage(ProjectItem prjItem, String taskPath)


Not sure what this means. I look at the properties of the task and it has (what I think should be) the correct info.

arguments is correct path to the ftp txt file containing paths and authentication details
executable is ftp.exe -i -s:
working directory is C:\WINDOWS\system32

Other than that I am not sure what else should be set or what is wrong with the current settings
0
Comment
Question by:QPR
  • 6
  • 4
10 Comments
 
LVL 21

Accepted Solution

by:
Jason Yousef, MS earned 2000 total points
ID: 36993964
Hi, In ssis there's a new task called FTP TASK or you can even use FTP inside a script task, you don't have to call ftp.exe  using a process task.

it all depends on, What you're trying to use the FTP for? push files? pull files? look for files?
0
 
LVL 29

Author Comment

by:QPR
ID: 36995506
go to a folder and using the txt file that contains the remote path and username/password, put a file onto our webserver (file which is an access db and the text file are in the same folder.

Happy to hear of a better/alternative way to achieve this
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 36995610
OK, your requirement is so simple and could be done using the gui "FTP TASK"
This article will help you learn about the FTP TASK.
http://www.techbubbles.com/sql-server/using-file-system-task-and-ftp-task-in-sql-server-ssis/

Just a concern, does the ACCES DB file that you need to download from the server has a static name or dynamic name? like a name and date?
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 29

Author Comment

by:QPR
ID: 36995656
Thanks I've worked it out, as you say was very simple using the ftp task so I dithced the ftp step from the original package and it runs fine now. Hopefully it will do so when scheduled too... soon find out :)
Thanks for pointing me to the newer task
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 36995662
You're welcome :)
Glad it works now...
0
 
LVL 29

Author Comment

by:QPR
ID: 36995688
But not when sceduled :(
If the package runs under the security context of the sql agent service but the ftp task has hard coded username and password, would I be right in saying that the ftp task runs under the credentials set within the task?

Actually looking at the "job" history I see that's a different issue and one that a colleague here is also facing on a different package.

Microsoft (R) SQL Server Execute Package Utility  Version 10.50.2500.0 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  8:52:35 a.m.  Error: 2011-10-20 08:52:47.53     Code: 0xC00291B1     Source: Setup ActiveX Script Task     Description: User script threw an exception: "An error occurred while adding the managed SSIS type library to the script host. Verify that the DTS 2000 runtime is installed.".  End Error  Error: 2011-10-20 08:52:47.53     Code: 0xC0024107     Source: Setup      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  8:52:35 a.m.  Finished: 8:52:47 a.m.  Elapsed:  12.734 seconds.  The package execution failed.  The step failed.
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 36995751
The ftp task , hard coded username and password is for accessing the ftp server, has nothing to do with the sql agent account.

Is your college is calling a DTS package from inside an SSIS package?

that might help you to install the run times, also learn about the limitation on 64BIT if you're using 64BIT.
http://msdn.microsoft.com/en-us/library/bb500440.aspx
0
 
LVL 29

Author Comment

by:QPR
ID: 36995797
No DTS in the equasion. The error above was mine and yes 64 bit.
Now I open my (early morning) eyes properly I can see that a whole step was left behind during the conversion!
The actual repopulating of the access db from a SQL Server select.
The failing step was due to a setup task that was setting some ftp parameters, I guess I can lose this step now that the ftp file has the details it needs via the ftp connection manager.

I'll try to recreate the "copy column step" or whatever the SSIS equivalent is now
0
 
LVL 29

Author Comment

by:QPR
ID: 36996023
ok I've removed the active X task and added a data flow task, mapped the columns and done a preview and all looks good. Works perfectly when executed from within SSIS but when run as a SQL job it fails with the message
Microsoft (R) SQL Server Execute Package Utility  Version 10.50.2500.0 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  9:24:20 a.m.  Error: 2011-10-20 09:24:21.09     Code: 0xC0016016     Source:       Description: Failed to decrypt protected XML node "DTS:Property" 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: 2011-10-20 09:24:21.34     Code: 0xC0209303     Source: FoodGradings Connection manager "Microsoft Access"     Description: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR.  The requested OLE DB provider MICROSOFT.JET.OLEDB.4.0 is not registered -- perhaps no 64-bit provider is available.  Error code: 0x00000000.  An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".  End Error  Error: 2011-10-20 09:24:21.34     Code: 0xC020801C     Source: re-populate access db OLE DB Destination [34]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Microsoft Access" failed with error code 0xC0209303.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: 2011-10-20 09:24:21.34     Code: 0xC0047017     Source: re-populate access db SSIS.Pipeline     Description: component "OLE DB Destination" (34) failed validation and returned error code 0xC020801C.  End Error  Error: 2011-10-20 09:24:21.34     Code: 0xC004700C     Source: re-populate access db SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2011-10-20 09:24:21.34     Code: 0xC0024107     Source: re-populate access db      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  9:24:20 a.m.  Finished: 9:24:21 a.m.  Elapsed:  0.422 seconds.  The package execution failed.  The step failed.


When I check the package properties in BIDs I see that it is set to encrypt key etc which is no good if I am storing the package in MSDB. When I try to change it to ServerStorage it tells me I can't select that as I am saving to the file system.
I should probably open up a new question on this so I can work out how I can change this.
My method for getting the package into SSIS has been to open SSIS, right click on msdb and select import package and point to the dtsx file on the file system
0
 
LVL 29

Author Comment

by:QPR
ID: 36996516
All fixed, I had to do a save copy as, rather than import into SSIS.
I also ticked 32 bit under execution options and all is well now
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question