FTP Task. / Validation Makes Mainframe Downloads Impossible.
Jul 24, 2007
When attempting to use the FTP task to download a file from a Mainframe system the task fails stating the filename as invalid because it doesn't begin with a "/".
Adding the slash to the front of the file name causes the mainframe to be unable to locate the file.
Commandline version of FTP.exe mimicks this behavior by working perfectly when the filename has no slash, and being unable to find the file when the slash is present.
Why does microsoft force a filename to start with a "/" and is there a way to make SSIS skip the validation phase for the FTP task?
Hi There, I have a database which I attached to Sql Server 2005 Express (With advanced features) and enabled full text search. I created a FT catalog and FT index on a column and can successfully run the following query:SELECT expertise FROM faculty_user WHERE FREETEXT (expertise, 'Berry' )My issue is that full text search requires user instances to be turned off. Simply setting "User Instance=False" over in my web.config's connection string (in VWD) does not solve the problem.I still get the following error and I think it may be caused by my ASPNETDB. I can't find my connection string to ASPNETDB to turn off user instances and I can't diagnose my other connection strings because of it.Generating user instances in SQL Server is disabled
Here is a sample of my other connection strings: <add name="myConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|myDatabase.mdf;Integrated Security=True;User Instance=False" providerName="System.Data.SqlClient"/> From what I understand, I should also not be attaching the database from my connection string like this because it relys on User Instances? I am officially confused! Thanks for reading, -Derek
I have a Package that downloads apprx 15 data archives from an FTP site using FTP Tasks.
My problem is that the data contained within these archives (.Z) are becoming corrupt after download/extracting. I have isolated the problem to the FTP File Task by manually downloading the archives myself from IE's built-in FTP client and disabling the downloads in the Control Flow. The Control Flow will then execute the extracting script and continue without any problems.
If I run the Package with the FTP Tasks enabled it will get to a random spot in the validation (I haven't been able to see any consistency with where it errors) of the flat files and error with either a truncation error or a (don't remember exactly what it was) error that talks about an unexpected end in the file. I believe this is because the data has become corrupt and has started skipping/excluding columns. I have opened the flat file and examined it, and sure enough, the data at the end seems to just end.
I have tried setting the FTP Connection to Use Passive Mode in the config file - doesn't work.
So, my question is: Has anyone encountered similar problems? If so, what did you do to fix the problem? I'd really like to use the FTP Task rather than reinventing it with a custom script.
I am trying to use the FTP Task within a DTS Package to copy a file from the Mainframe (OS390). We have SQL2000 with SP2 running on NT. This process works when I do it manually with ftp. At first, when I would execute the package it would just hang like it is copying but it would never end.
Then, I added a Dynamic Properties Task to the package. I used the Dynamic Properties Task to set up a Constant with the actual name of the Source File located on the Mainframe. When I ran this I received "Invalid string format for source file name".
Next, I removed the Dynamic Properties Task from the DTS Package, saved the package, and executed it. The package executed successfully, but when I looked in the folder on my hardrive the file that was supposed to be copied was not there. In order to get the package to execute successfully it appears that I have to go through the following process: add the Dynamic Properties Task, set up a constant to change the Source File name, save the package, delete the Dynamic Properties Task, save the package and then execute it.
Also, on one execution of this package, it brought across the file from the Mainframe to my hard drive but the file was in Binary format.
What do I have to do to get the FTP Task within a DTS Package to work? Would you specify in detail exactly how the Dynamic Properties Task needs to be set up? How can I specify how to change the file format from Binary to ASCII in the DTS Package?
Has anyone had any success sending or receiving file(s) from either Script or FTP task? I've Google and found examples and no luck for me. The main idea is to send a file from local PC/server to mainframe.
I've used this workaround, SSIS Script Task but no good.
SQL Server Feedback Workarounds 281893. SSIS FTP Task - Mainframe When you try to connect to a mainframe (os / 390) to ftp receive a file you get an error message stating that the path does not begin with a "/". Active feedback entered 6/7/2007 by EWisdahl
Entered by EWisdahl on 6/7/2007
Add a script task (as follows) to download the desired files... ' Microsoft SQL Server Integration Services Script Task ' Write scripts using Microsoft Visual Basic ' The ScriptMain class is the entry point of the Script Task. Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain ' The execution engine calls this method when the task executes. ' To access the object model, use the Dts object. Connections, variables, events, ' and logging features are available as static members of the Dts class. ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure. ' ' To open Code and Text Editor Help, press F1. ' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main() Try 'Create the connection to the ftp server Dim cm As ConnectionManager = Dts.Connections.Add("FTP") 'Set the properties like username & password cm.Properties("ServerName").SetValue(cm, "myServer") cm.Properties("ServerUserName").SetValue(cm, "myUserName") cm.Properties("ServerPassword").SetValue(cm, "myPassword") cm.Properties("ServerPort").SetValue(cm, "21") cm.Properties("Timeout").SetValue(cm, "0") 'The 0 setting will make it not timeout cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb cm.Properties("Retries").SetValue(cm, "1") 'create the FTP object that sends the files and pass it the connection created above.
Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing)) 'Connects to the ftp server ftp.Connect() ftp.SetWorkingDirectory("MyFolder.MySubFolder.MySubSubFolder")
Dim files(0) As String files(0) = "MyfileName" ftp.ReceiveFiles(files, "C: emp", True, True)
' Close the ftp connection ftp.Close()
'Set the filename you retreive for use in data flow Dts.Variables.Item("FILENAME").Value = maxname Catch ex As Exception Dts.TaskResult = Dts.Results.Failure
In using SSIS to migrate data from mainframe to SQL 2005, I had a situation where only group level data was exposed through the ODBC to SSIS, so I pulled this information as varchar on the SQL destination side. Now I would like to break that group into the individual numeric columns I need on SQL Server. However, the positive and negative sign did not convert because it came of character. I can write something to convert the positive signs to positive numbers; however I cannot do the negative because I would need get rid of the leading zeros in order to place the negative sign before the number. Is there anything I could have done to get SSIS to do the conversion like it did for every one-to-one mapping?
Hi, this is my first post and I'm relatively new to SSIS so please go easy on me.
Without going into too much detail about it, I've set up a simple SSIS package which does this in a nutshell:
Foreach loop picks up all *.xls files in a given folder 1 - Puts the name of the current spreadsheet into a variable 2 - File System Task copies the current spreadsheet ("abc.xls") to a file called "work.xls" 3 - Data Flow task performs data extraction on "work.xls" and puts it into a SQL server database 4 - File System Task moves "abc.xls" into a "success" folder Continues with loop - move onto next spreadsheet
This works fine, so long as the spreadsheets all have the same number of columns.
As soon as one of them has a column missing (believe me, this will happen - we're dealing with users here) the package falls over at step 3.
When the package comes across an erroneous spreadsheet, what I'd like to do is move the offending file to a failure folder (making step 4 either a success or failure file move) and carry on with the next one.
I know that you can have an error path (the red line) from any step within the dataflow task, but this doesn't help me because the error lies in the structure of the spreadsheet and not the contents.
I've already come up with a work around whereby each file is moved into the failures folder just after step 2, then moved from the failures folder into the success folder at step 4.
This almost gives me what I want, although of course the package still falls over whenever it encounters a dodgy looking spreadsheet.
Is there any way that I can get the package to do what I'm after?
Now is this a limitation in SSIS or am I missing something. I have an XML file that needs validation. I am using the 'XML Task' control for that. The validation runs fine so long as the file is below a certain size. I am not sure what the cutoff size is but the validation runs fine if the XML file size is 60Mb. I include a few more records in my XML file to make the file size 65Mb and run ETL again and validation fails!
There is not much detail in the error message other than 'Task XML Validation failed'. I dont think there should be any kind of timeout issue. The whole process takes less than a minute before failing.
The issue is definately not with the new records that get added. I have verified the new file with xmlspy and it validates just fine. I have also validated the newly added records (5 Mb) through the same ETL process and that runs fine too.
Please suggest if I should spend time finding what the issue is or simply move on assuming its a known bug? Has any one experienced this?
I am using the XML Task to validate an XML file against the XSD schema. Everything works fine, but about detailed errors in the case of the failed validation?
1) I specified €śOperationsResult€? Destination property to save the task execution outcome into a file. 2) Also, I turned on all possible events for logging.
Unfortunately, neither option produces a detailed validation error message. Option 1 saves one single word €śtrue€? or €śfalse€? to a file. Logging produces the following message: Task failed to validate "A validation error occurred when validating the instance document.".
As end result, it is impossible to pinpoint the problem with the xml file and, consequently, fix the issue.
The expected behavior should be a detailed error message like the following (produced by StylusStudio): file:///c:/temp/fafa/feed_bad.xml:12824,52: Datatype error: Type:InvalidDatatypeFacetException, Message:Value '244212' must be less than or equal to MaxInclusive '4'.
It shows a precise location of the error in the XML file plus the specific XSD rule which failed.
Any idea what could be done to get the detailed error message for the XML Task Validation?
I use a "Bulk Insert Task" inside of a "ForEach Container" and set the ConnectionString-Property of the "FlatFile-ConnectionManager" with an expression, pulling from a variable. The packages works fine, but I get an Error-Message in the Output-Window:
Fehler: 0xC001401E bei Package, Verbindungs-Manager 'FlatFile': Der in der Verbindung angegebene Dateiname 'c:old.txt' war ungĂĽltig.
Translation:
Error: 0xC001401E at Package, Connection-Manager 'FlatFile': Cannot open the file 'c:old.txt'. (Sorry, in this office is the no english version available).
'C:old.txt' is the initial value of the variable. The new value is 'C:ew.txt' and I can see the new value in the Debugger or in a Scripttask.
Before the execution of the "Bulk Insert Task" starts, SSIS makes a validation. I have tried to stop the validation on setting the property "DelayValidation" = True (Package,ForEach-Container,BulkInsert-Task,FlatFile-ConnectionManager), but it is not possible to stop the validation and the error-message. After printing the error-message, the variable is evaluated and the package works fine. After the first loop, there is no more error-message.
The error-message is a problem, because the operators look at the error-output.
As a workaround I can set the property in a Scriptatsk. Is there another solution????
I have a package with an XML Task with OperationType = Validate. The source is a string variable and the second operand is a file connection to a schema file. The schema file <include>'s other schemas. I run the package in debug mode in Visual Studio with no errors. I exit Visual Studio. Then I open Visual Studio again and run the package in debug mode like before and it fails in the xml task with error: "Task failed to validate "No schema to use in validation was found.". I don't understand why it fails since no changes were made. The strange thing is that if I click the File Connection Manager for the schema file and click Browse and then reselect the same schema file again then it runs OK. Note that I have to exit Visual Studio and not just close the package in order to repro the error. It seems like exiting Visual Studio frees some resource that is essential to the validation. Reselecting the schema file in the Connection Manager seems to restore the resource. (SP1)
I am trying to executed a packege so that it loads data from from the excel file to the SQL Server Server database. When I execute it, it prompts the following error message and 1 warning The excel file has three colums, Week, Item and Value
Error 4 Validation error. Data Flow Task: OLE DB Source [94]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E37 Description: "ORA-00942: table or view does not exist ". Test - GET NW PERF 1.dtsx 0 0
Warning
Warning 1 Validation warning. Data Flow Task: OLE DB Destination [36]: The external metadata column collection is out of synchronization with the data source columns. The column "DAY" needs to be added to the external metadata column collection. The column "TCH_AVAIL" needs to be added to the external metadata column collection. The column "PDROP" needs to be added to the external metadata column collection. The column "P_HR" needs to be added to the external metadata column collection. The column "SFAIL" needs to be added to the external metadata column collection. The "external metadata column "VALUE" (90)" needs to be removed from the external metadata column collection. The "external metadata column "ITEM" (89)" needs to be removed from the external metadata column collection. Not in use - GET NW STATS.dtsx 0 0
I have an SSIS package which calls two other SSIS packages as part of it's control flow (using the Execute Package task). Both packages are stored in the same parent folder on the same server. The first child package runs successfully, the second does not. It ran fine for days until yesterday. Not sure what I might have done. I have tried to delay validation and recreate the connection. No go. Has anyone else run into this. I am running SQL 2005 RTM and VS 2005 RTM on XP Pro SP2. Any suggestions would be welcome.
Out of Memory when working with big XML Files:when validating XSD it process small files but when size close to 1gb it throws .I have 16 xml files out of which 8-10 Â files size will be around 1gb processing one by one in FOR EACH LOOP container in SSIS[XML Task] Error: An error occurred with the following error message: "Exception of type 'System.OutOfMemoryException' was thrown.".Task XSD Validation failed.
system configuration : processor:Intel(R) Xeon (R) CPU E5-2670 v2 @2.50GhZ 2.50 Hz Installed memory (RAM): 61 GB System type: 64 -Bit operating System Visual studio:2012,32 bit virtual memory : 12499 MB
whether the size of the XML Document a limitation for the validation task ? or any system configuration i need to improve?
I went to install SQL 2005 and did not have sufficient memory, I registered prior to the download, will i still be able to down load it after nov 7 and after i buy more memory ( there is no iso file to save ) Thanks Ray
Does anybody know where I can download the samples ? What I mean is not the Northwind or pubs database but samples that are usually installed in "C:Program FilesMicrosoft SQL Server90Samples" directory; I am missing them.
Hi, Just a quick note for all that the SSIS team has started to post fresh content for download.
We will be adding some links to various pages so you can easily see things when looking at the the SSIS portal on MSDN http://msdn.microsoft.com/SQL/bi/integration/default.aspx
For now if you search for "ssis" on the microsoft downloads site you will find the current content. Sample Logging reports, jump start training, Meta Data info, and sample components. More coming over the next few weeks... http://www.microsoft.com/downloads/search.aspx?displaylang=en
Thank you and I hope everyone enjoys winding down 2005, or blowing it out, depending on your likes :)
While Creating a script task in Control Flow, I am getting "Package Validation Error". Here is the complete message:
Error at Validate File and Load Data: The task is configured to pre-compile the script, but binary code is not found. Please visit the IDE in Script Task Editor by clicking Design Script button to cause binary code to be generated. (Microsoft.DataTransformationServices.VsIntegration)
As mentioned in the message, I opened the script IDE and added the code I need. When I close the VSA IDE, package designer displays the same error message.
The worst part of whole story is that if I close the package designer and reopen it, I find that all the code I wrote in the script task has been deleted by the package designer. This is not at all acceptable as I saved the package the and still lost all my work. I did all the coding from scratch for that task.
Please respond if anyone faced similar problem.
Thanks in advance!
Anand
PS: If any one from Microsoft is reading this, please see what you guys are coding there. Due to the buggy software you deliver, I am loosing my credibility.<P< P>
Teacher Starter Kit VB http://msdn.microsoft.com/vstudio/eula.aspx?id=4d31bb50-22da-411f-b747-a7b2288fc720 C# http://msdn.microsoft.com/vstudio/eula.aspx?id=06b20449-cbba-4f2f-ab8c-b99c1266cc7e Collection Manager Starter Kit http://msdn.microsoft.com/vstudio/eula.aspx?id=98c8074d-d28e-49e1-be44-94f0114f372a Club Web Site EventCalendar control source code http://www.asp.net/starterkits/downloads/eventcalendar.zip
2. None of the download links shown below for the Sample Applications found at this URL work:
Has anyone been able to use an ftp task to pull a flat file from a z/os mainframe? The ftp task appears to want the remote file to begin with a /, which pulls you into unix system services on z/os.
Being that this issue first surfaced September 2005 and it's now six months later (March 29, 2006), has the problem with recognizing VMS datasets (not requiring a '/') been addressed?
We would like to use DTS to pull data from DB2 on the mainframe into SQL Server 7.0 databases. Does anyone know what 3rd party products are required for us to do this (and which ones are the best to use)? What has to be installed on the mainframe and on the SQL Server?
What would I need in order to setup replication between SQL2000 SP3 and MVS Subsystem running Db2 database . I mean, does it need any 3rd party tool or regular replication setup between SQL and Db2 is enough
I am trying to send files over to an ftp server on Mainframe using scripts that is provided in this link, but I can't get it to work. The problem is the sendfile method concatinates the source file name to the MVS dataset name which causes the issue.
Here's my code (FTPConn is a connecation mgr for FTP):
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dim mgr As ConnectionManager
mgr = Dts.Connections("FTPConn")
Dim conn As FtpClientConnection
conn = New FtpClientConnection(mgr.AcquireConnection(DBNull.Value))
Dim toFiles As String
toFiles = "ftpp.a.b.xxxxx.yyyyyy.int"
Dim fromFiles(0) As String
fromFiles(0) = "c: est.txt"
Try
conn.Connect()
conn.SendFiles(fromFiles, toFiles, True, True)
Catch ex As Exception
Finally
conn.Close()
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
Output:
SSIS package "Package.dtsx" starting.
Error: 0xC001602A at Package, Connection manager "FTPConn": An error occurred in the requested FTP operation. Detailed error description: 200 Representation type is Ascii NonPrint
200 Port request OK.
501 Invalid data set name "ftpp.a.b.xxxxx.yyyyyy.int/test.txt". Use MVS Dsname conventions.
Some categories have sub-categories. Therefore, some entries will have null for parentID while others will point to another category.
I need to select all of the categories, but have them returned in the correct parent/child order (parents BEFORE children). I can't seem to get this to work correctly. Is this not possible or am I just being stupid?? :)
I need to store the output of two select statement in a Temporal Table inside a storeProcedure. and used them later in a Select statement in the same Procedure
CREATE PROCEDURE [update_Alarm_1]
declare @datetime1 ?????
declare @datetime2 ?????
[SELECT Coming FROM Student_Log where datepart(hour, AL_Coming) = 9 and AL_State = 0 ] = @datetime1
[SELECT Coming FROM Student_Log where datepart(hour, AL_Coming) = 9 and AL_State = 1 ] = @datetime2
I need to store the output of two select statement in a Temporal Table inside a storeProcedure. and used them later in a Select statement in the same Procedure
CREATE PROCEDURE [update_Alarm_1]
declare @datetime1 ?????
declare @datetime2 ?????
[SELECT Coming FROM Student_Log where datepart(hour, AL_Coming) = 9 and AL_State = 0 ] = @datetime1
[SELECT Coming FROM Student_Log where datepart(hour, AL_Coming) = 9 and AL_State = 1 ] = @datetime2
First of all, here's a description of the three tables involved in the query:
clients
id name
services
id name
clients_services
id client_id service_id
As you can imagine, I have a list of clients and each client can have several services assigned to him. So, I have trying to come up with a query that would allow me to do display the following information inside a DataGridView without the use of any additional code:
I would like to be able to display all the services included in the services table and then, depending on the client that is currently selected, to have each of the services display a checkbox in the DataGridView showing the services currently assigned to that particular client. Something like this:
The services table has the following entries: Research, Consulting, Development, Data Protection and Design The selected client id is "2" That client has the following services assigned to him: Development and Design The DataGridView should then display:
hi I am loading from Mainframe to Sqlserver. Right now i am truncating the tables in sqlserver connecting to mainframe and downloading the tables to Sql server. The problem is sometimes i fail to connect to Mainframe. So is there any way of checking whether i can connect to mainframe and if succesful truncate the tables. Thanks
is there a solution outside for getting of DB2 for OS/390 into SQL server? I mean not replication or copying of data with flat files or an ETL tool, but a kind of integration of DB2 tables as they would be "normal" mssql tables.
Oracle has Transparent Gateways for os/z DB2 and many other non-Oracle databases, is there some similar for SQL Server?
Could it be realisable with an ODBC client for DB2?
