Cannot Import File From Mainframe
Mar 8, 2006
Hello Everybody!
Im trying to migrate my SQL 2000 packages that´s currently working in the company production enviroment to SSIS packages. So, in the 2000 version i got the flat file from Mainframe and i had to do a trick to transform all the columns to match the same size as the example above:
1432 1138734217 1144256628<CRLF>
1432 1138734217<CRLF>
1433 1136657788 1122441177<CRLF>
1433 1125554545 1122441177<CRLF>
1433 1192925544 1122441177<CRLF>
So, when i import the file, first of all i have to transform the Text file to another texfile file fixing the size to 32..... but you can see that in the second row im receiving a CRLF and if i try to import without the trick, the preview of the ragged file show me the columns desorganized...
Someone knows how to import it without transform to another text file with fixed length?
Thanks
Cleber
View 2 Replies
ADVERTISEMENT
Apr 25, 2007
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?
View 9 Replies
View Related
Jul 20, 2007
Howdy all,
I've seen several posts about reading and writing files that have different record types with varying column metadata. My particular file has 11 record types plus several header types and looks something like:
<Header1>
<Header2>
<Detail01-#1>
<Subdetail02>
<Subdetail03>
...
<Detail01-#2>
<Subdetail02>
<Subdetail03>
...
...
Since i need to get different detail and subdetail records, i can't really use the technique of 3 dest file connection managers found in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=87269&SiteID=1
I've tried using an exec sql to get the main detail records and then a forech ADO en umerator that would get the subdetails, but it all seems so kludgy. I'm starting to think that I should just write the bulk of the file creation code in a c# app instead of trying to smush this into SSIS. Opinions? Am I missing some trick in SSIS?
TIA,
-Peter
View 7 Replies
View Related
Jun 3, 2015
I need to be able to export a data file as flat file (.txt) with fixed columns for use by Mainframe.
I will be uploaded this file using the Windows File Share Option
Render Format does not have .txt, but does have a data feed option. So I will try that.
But, I do not see an option for fixed column width.
View 7 Replies
View Related
Jan 12, 2006
Hi all,
when trying to Ămport files to our database server from a client, I keep getting an error:
- Validating (Error)
Messages
Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Source_txt" (1).
(SQL Server Import and Export Wizard)
Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Data Conversion 1" (175).
(SQL Server Import and Export Wizard)
... doing the same import when logged on the server, hasn't been giving me any errors, how come. I can from my client without trouble import tables from other DB servers but when ever it is files it won't do it.
I tried as mentioned in other threads rerun setup to re-install SSIS, but as it was already installed it wouldn't re-install. My next move would be to make a clean install, but not sure it would help, as I think this is a buck.
best regards
Musa Rusid
View 1 Replies
View Related
Sep 30, 2005
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.
View 7 Replies
View Related
Mar 29, 2006
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?
View 2 Replies
View Related
Jun 15, 2000
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?
View 2 Replies
View Related
Apr 22, 2004
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
View 1 Replies
View Related
Jan 24, 2008
Hi,
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.
View 13 Replies
View Related
Sep 18, 2000
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
View 1 Replies
View Related
Aug 30, 2002
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?
Thanks, in advance.
View 1 Replies
View Related
Jan 18, 2008
Hi,
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?
Thanks
View 1 Replies
View Related
Feb 26, 2004
Can I get drivers to setup Replication to Mainframe . I really don't want to install HIS
Thanks
View 4 Replies
View Related
Jul 20, 2005
I have a million record mainframe flat file that I BULK INSERT into aSQL table with CHAR(fieldlength) deined for every column to preventimport errors.Once imported I "INSERT INTO ... SELECTdbo.MyScrubFunction(columnN),..."My scrub functions will take for example a char(8) YYYYMMDD date fieldand return either a valid datetime variable or a NULL for 8-spaces or8-zeros....or return a MONEY datatype by dividing by 100.PROBLEM: This is extremely SLOW!QUESTION: Should I do multiple "UPDATE ...SET" statements back intoCHAR() columns, then let SQL Server do the CHAR() to DATETIME andMONEY conversions itself? What is the most efficient or recommendedmethod to transform/scrub imported data?P.S. I also have to convert low-values and reformat dates which Ialso use my own UDFs for.THANKS
View 1 Replies
View Related
Jan 24, 2007
Hi,
I want to extract all fields from a table and ftp the output file to the mainframe. My problem is the columns in the extracted file do not line up, which is required by the mainframe program. Example column would be this column of last names:
Smith
Jones
Sullivan
How could I format the extracted data to look like this:
Smith
Jones
Sullivan
Thanks,
Jeff
View 8 Replies
View Related
Sep 21, 2007
Hello,
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.
Username: imtheuser
Password: pwd
Source file: c:myfile.txt
Destination: 'PROD.ABC.DAILY.BATCH'
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
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
Thanks again.
View 5 Replies
View Related
Feb 13, 2007
I have data on the mainframe that contains special characters such as a copyright symbol (letter c with a circle) when running the SSIS package migration from mainframe database to SQL Server 2005 database the data in the column is truncated at the point it hits this symbol with no errors. I had error reports set up for truncation and did not see an error report for this.
I have tried to change the data type to different data types and re-run nothing changes things.
The symbol is a EBCDIC 'b4' on the mainframe side - I have no idea what that would translate to.
Anyone have any ideas? Am I stuck with substituting something else for the symbol on the mainframe side and re-running the package? Is there anyway to handle this situation in SSIS? I am using the Import/Export Wizard for this one because before I ran into this problem it seemed to be working fine.
Thanks, MaryOS
View 3 Replies
View Related
May 22, 2007
I am migrating mainframe data to SQL Server 2005 and have found that from a mainframe character field with leading zeros for example the value of 00023 to a SQL Server column defined as varchar (5) the resulting column value is 23 not 00023. I need the leading zeros because these are account ids, etc. So the value is 00023 not 23. Is this some setting in SQL Server 2005 that needs to be changed or what? This is not a numeric field on the mainframe or a numeric column for SQL Server.
View 11 Replies
View Related
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?
View 7 Replies
View Related
Jan 29, 2003
Guys.
This has been an issue for me. It happens once in a while.
I am importing a File from a shared folder thru DTS package. Once in a while the DTS package fails due to the following error.
"Error Opening Data File: Process cannot access the file because it is being used by another process"
Is there anyway I can specify that the file is going to be opened in read only, so that the DTS will not fail?
Any other solution/suggestion?
-MAK
View 1 Replies
View Related
Dec 28, 2005
I want to import XML file as the '@doc' value when I execute 'sp_xml_preparedocument', many thanks!
View 1 Replies
View Related
Sep 28, 2001
Hello every one
i unstalled my sql server and installed it again, and i want the old database. so how i can i import the old database file .mdf, ldf here. i tried to copy this 2files in to data folder, but its not showing database files in the server.
please help me out,i havent taken any backup before uninstallation
with regards
kittu
View 1 Replies
View Related
Jan 2, 2002
Im looking to write a DTS script to import a CSV file into an existing table. I have made sure that all columns correspond. I ideally want to create a stored procedure and a variable to be entered as the file name.
How can I do this?
View 1 Replies
View Related
Apr 15, 2004
How do you import a dbf file to sql and dump it into a table please help . iam totaly new at this.
View 2 Replies
View Related
Apr 8, 2008
Is there a way through SSIS to import a file from the Internet instead of a local file?
http://www.domain.com/files/filename.csv
I would like to be able to do this. If it is not possible, what is a good practice for doing this?
- - - -
- Will -
- - - -
http://www.strohlsitedesign.com
http://blog.strohlsitedesign.com/
http://skins.strohlsitedesign.com/
View 5 Replies
View Related
Apr 9, 2008
I have a problem in importing .dbs file into sqlserver 2005.
Please suggest me with a way.
View 4 Replies
View Related
Oct 31, 2007
I want to schadule a file import, but file is in a zip format. is there any way to import it? in zip there is a foxpro(dbf) file.
waiting for help in this regard.
View 6 Replies
View Related
Nov 1, 2007
I'm new to the SQL environment and am looking for some help. I have a client that has a CSV file that we need to import to a table but we need to make this part of an existing job. I'm wondering if anyone has any scripts for importing a CSV file into a DB. I've looked at the Import Wizard but don't think that is going to work the way I want it to.
Thanks
Damon
View 4 Replies
View Related
Jul 23, 2005
Env: SQL Server 2000 on in WIN NT 5.xJob: import mutiple flat files into several tables daily.Catch: one or two of the several flat files might be empty.First thought/test:Use [first row as fields] option for the import process.Problem, DTS can't complete (as a package).As an alternative, I could probably detect if a file is empty thendecide what to do with it, with VB activeX, it might be feasible,question, VB has a command for "FileExist", how about "FileLen" or thelike for determining the length of a file?TIA.
View 6 Replies
View Related
May 15, 2008
Is there a simple VB.Net code example to import a csv file from a directory into a table in sqlserver using SSIS?
I have a file located on a network drive. I want the program to import the file (with options like use row one for column names) into a table.
I know how to do it using SSIS but I need the program to be able to pass the filename.
Any simple example to get me started?
View 5 Replies
View Related
Jun 2, 2006
I have a SQL Server 2005 database *.mdf file that I would like to import into another SQL Server 2005 engine on another computer. I don't see how to do this.
How can I import a *.mdf file to create a copy of an exiisting database from another computer?
Thanks.
View 7 Replies
View Related
Jun 30, 2007
Hi guys
i am trying to import a csv file into the sql, but my problem is that I have a message in one of the columns. suggestions
- Executing (Error)
Messages
Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "INTVAL" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
(SQL Server Import and Export Wizard)
Error 0xc0209029: Data Flow Task: The "output column "INTVAL" (22)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "INTVAL" (22)" specifies failure on error. An error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)
Error 0xc0202092: Data Flow Task: An error occurred while processing file "E:minessightlastinfoattrib.csv" on data row 2.
(SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - blastinfoattrib_csv" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
(SQL Server Import and Export Wizard)
Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.
(SQL Server Import and Export Wizard)
View 4 Replies
View Related
