Migrated DTS package problem
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.DataTransformat
--------------------------
Program Location:
at Microsoft.DataTransformati
at Microsoft.DataTransformati
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
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.DataTransformat
--------------------------
Program Location:
at Microsoft.DataTransformati
at Microsoft.DataTransformati
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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?
ASKER
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
Thanks for pointing me to the newer task
You're welcome :)
Glad it works now...
Glad it works now...
ASKER
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.
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.
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
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
ASKER
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
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
ASKER
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_64B
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
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_64B
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
ASKER
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
I also ticked 32 bit under execution options and all is well now
ASKER
Happy to hear of a better/alternative way to achieve this