SSIS - Migrating Mainframe Data To SQL 2005 Only Mainframe Group Level Exposed
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?
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.
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?
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.
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?
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.
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
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?
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?
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
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:
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
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
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
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?
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?
I've seen examples of creating a Web Service that executes an SSIS package through code. But that involves developing the web service, configuring IIS on the same server as SSIS to expose the web service. I was wondering if it is easy to use the HTTP endpoints within SQL Server 2005 to create a web service interface that calls an SSIS package, supplying any parameters the package needs to run. Also, this would be called from within Excel VBA that uses a web-reference via the Web Reference Toolkit.
I have successfully used migrate wizard to migrate DTS pacakge to TrainingDTS.dtsx. What should I do next? when I run
C:>dtexec /file "C:TrainingDTS.dtsx"
Error: 2008-05-13 09:14:31.36 Code: 0xC0029172 Source: File Transfer Protocol Task undefined FTP Task Description: The connection is empty. Verify that a valid FTP connection is p rovided. End Error Error: 2008-05-13 09:14:31.36 Code: 0xC0024107 Source: File Transfer Protocol Task undefined Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1).
I have to create a migration package ..means package should migrate the sql server 2000 tables to 2005 tables (Not dealing with data at this point of time and ignoring SPs,DTS packages).But there are lot of normalisation ans schema changes in 2005 compared to 2000.Like,
- One 2000 table devided into 3-4 tables in 2005 - Lot of changes in the filed names - Handling integrity relationship between the newversion tables
Being new to SSIS ,iam in confusion like how to start and where to start.can you pls tell me the steps(Structured way) i have to fallow
-- I have around 8-9 tables in 2000 ,I have to migrate them into 18-19 tables (with some new fileds )
-- For each table i have to create one package(bcoz lot of transformations are there) or I can create one package for all of those ? but the finally i have to handover one package to the client
pls ask me if u need any further info to come up with the explanation..bcoz iam not sure whether i provided enough info or not
Below is a migration plan that I've compiled to migrate SQL 2000 DTS packages to SSIS 2005. Once these DTS packages have been migrated i will need to create a job and schedule them in SQL 2005.
I would appreciate and feedback or questions on this migration plan.
Migration DTS 2000 packages to SSIS 2005:
1. Will need to save the current production DTS package as structure storage file. We do not have a UDL file. We set the data connections within each DTS package. 2. Go to Sql 2005 - ManagementLegacyData Transformation Services - right-click and open previous saved structure storage file. 3. Modify the DTS data creditentials to reflect the SQL 2005 connection data. Modify any SQL 2000 MAPI settings to utilize SQL 2005 new database mail. Save the package on SQL 2005. 4. After the modified DTS package has been updated and saved on SQL 2005, save this file as a structure storage file. 5. go to BIDS. Create a new SSIS project. Right-click on SSIS packages and select Migrate DTS 2000 package. This will migrate over the DTS 2000 package with the updated SQL 2005 data creditentails. 6. click on the package properties - protectionlevel and change it to dontsavesensitive. 7. right-click and select package configurations..., select to store data creditentials in xml format. 8. right-click on execute DTS 2000 package task, select Edit... and click on Load DTS2000 package internally. This will embed this task into the new SSIS package. Test the package. Continue if successful. 9. Use SSIS deployment functionality to move the package over to SQL 2005 Integration Services. Right-click on package and select Run Package, if successful, create a job and schedule it to run on SQL 2005 Agent. 10. When creating the Job under SQL Agent, change the Owner: of the job to reflect the owner of the new SSIS package. Schedule the job.
I've just coming up to speed on SSIS 2005. Therefore, this is what i've been able to piece together up to this point and I'm looking for some industry advice/feedback on whether or not this is a good migration plan. I need to provide a migration plan to management by 2/18. Thanks
I'm trying to get a database from SQL2005 to SQL2000. I already recreated the database in SQL2005 and I'm just trying to get some data from just a few tables.
It doesn't appear the DTS or any export functionality is available in my version of SQL Express 2005...so how do I do this. I've tried installing everything I can find.
I've also tried BCP but I keep getting these errors:
SQLState = 08001, NativeError = 53 Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [53]. SQLState = HYT00, NativeError = 0 Error = [Microsoft][SQL Native Client]Login timeout expired SQLState = 08001, NativeError = 53 Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
This is a LOCAL database and I have tried -T for trusted, I have also tried a username and password combination. The database is set to Allow Remote connections. Nothing is working.
i am migrating one database from SQL Server 2008 where I have around 20 databases to SQLServer 2012. To migrate the login I was thinking about to use SP_Help <g class="gr_ gr_114 gr-alert gr_spell ContextualSpelling ins-del multiReplace" data-gr-id="114" id="114">revlogin</g>Â store procedure but now what I believe is this SP scripts out whole server level login and I don't need all the logins in the server except related to the database that I am migrating to.
need to migrate a cluster with an AG dtabases to new data center cluster with AG.
I was wondering if is possible to do mirroring on top of the AG configuration? or what other options could be to migrate a cluster that has 3 nodes and setup the ag databases to a new datacenter.
Hi, I use lookups to map surrogate of level 1 dimensions to my fact tables in SSIS. But how to handle a level 2 dimension with a ValidFrom and a ValidUntil date field? I do not use an IsCurrent column, because this could problem with late arriving facts.
- In dts I used an SQL statement like this:
update SA SET SA.DimProdRef = Dim.RecordID FROM SAWarenEingang SA, DimProd Dim where SA.ProduktNumber = Dim.ProduktNumber and SA.ArtikelkontoBewegungsdatum between Dim.ValidFrom and Dim.ValidUntil
Now in SSIS I want to handle the whole thing in the data flow without using a staging table: - Using Lookups: I would have to pass the date column for each inside the fact table into the lookup. That does not work. - Using Execute SQL in the data flow: would be very slow, because the statement will be executed for any line in the dataflow
I have been using AlwaysON AG for a long time now and currently have about 10TB of data across 120 databases and 3 AG groups for any application that is on SQL 2012 with great success. Each AG group is running on patch level 11.0.5058.0 with 2 synchronous replica(on different SANS) in Primary Data center and 1 ASYNC replica in DR. Migration has been a non-issue because none of the databases weren't substantial enough that I could not fit into my maintenance window which is 12-4AM on SAT morning.
My issue is that my last application to migrate to 2012 includes a 4TB TDE encrypted databases database which is about 10x larger than any of the previous ones I have migrated. The database takes 4 hours to backup after tuning extensively(I hate TDE!!)
The restore to the primary replica is instant because of seeding incremental but the issue comes from having to backup the database before adding to the availability group. 4 hours is my exact outage window and I can't get any more. My plan to migrate application is to -
First Outage Window
1) Restore Database from 2008 to 2012 Primary Replica 2) Change application ARECORD(or cname not sure which) to Primary replica 3) Run database on single node until next outage window
Week Later 1) Add database to availability group 2) Change ARECORD/CNAME to listener
What I don't like about this is I am going an entire week with 1 node instead of 3 which is worrisome. How to accomplish this I would love to hear from you or any type of comment from people who have worked with VLDB in availability groups and what you like/hate/loved about doing it. I am trying to go all in on this software and have loved it so far but getting worried when it comes to the VLDB migration.
Does anyone else have this error message pop up in SSMS when you try to parse sql statements:
.Net SqlClient Data Provider: Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
There was a thread back in March 2006 that mentioned this error, but the posted resolution was to install SP1. I have SP1 installed but I still get the error.
I only receive the error when I'm parsing statements, if I run the statement it's fine.
I have a report which is grouped by a field called R_ID, which gives me a list of records for each R_ID. So here is the thing, I want to get only top 3 records for each R_ID. Is there any way to do this thing in the report level. I tried it from the query level, but the result is not like what I wanted.
I need to group by transactions at line item level. As the each item in the transaction may belong to different category and I'musing case statement to identify particular category for one column , I'm using sub-query to retrieve the results at item level.
