question

abdullahzarour avatar image
abdullahzarour asked

Migration From COBOL data file to SQL Server using SSIS

Dear All , Good day and I hope all is well , I have case on my hand I need to migrate data from COBOL files using SSIS is this possible even that I have COBOL Record Layout . please any hints or guide in this task . thanks the type of file is COBOL File , with fix-width sample of record layout is : 01 lnrq. 02 lnrqst-key. 04 brnch-cd-lnq pic 99. 04 ln-type-lnq pic 9. 88 general-ln-lnq value 0. 88 prfsnl-ln-lnq value 1. 04 lnrq-cd-lnq pic 9(9). 02 lnrq-dt-lnq. 04 lnrq-yr-lnq pic 9(4). 04 lnrq-mn-lnq pic 9(2). 04 lnrq-dy-lnq pic 9(2). 02 ln-type-key-lnq. I have also the COBOL data file , my question is it possible to migrate using SSIS ? thanks in advance
ssis
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

BobF avatar image
BobF answered
If the data in the form of CSV files or some other type of the delimiter then could import the information using SSIS and then manipulate it from there?
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

In fact the data is not CSV files , it's a COBOL File ( data file with fixed - width ) , but I have the COBOL Layout Record . thanks
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
If the file is a fixed width then the flat file connector will allow you to define the column widths within an SSIS package. I would certainly give it a try. What is the file extension and can you supply a sample of the file format in your question? you may also be able to use something like LogParser or ReLog to format/import the data.
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Fatherjack <3 LogParser I really ought to take a look at that thing, you have raved about it in the past.
0 Likes 0 ·
@WilliamD - I do, its true :D Take a look at the blog series I did, takes you from "How To Spell LogParser" to "Solving Global Famine With LogParser and a Spoon" in five easy steps, or something like that.
0 Likes 0 ·
Exactly - I have seen you post about LogParser so many times before, either you wrote the tool, or just love it up to the sky!
0 Likes 0 ·
I love it because I can use it instinctively - its uses a SQL-like syntax - to move and transform data. I reads all sorts of file types and will convert and output to a lot too. It even generates chart images if you want it to analyse data statistically. I find it quicker than PoSh/SSIS etc so it gets used a lot. Est import of csv file to database with no preparation - inside 2 minutes with LogParser. I'd hardly have started the SSIS editor in that time.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
Not sure how you would handle REDEFINES (if you have any) in SSIS, as that is essentially determining the structure of the file based upon the content, presumably you could parse it conditionally. One thing to consider might be *signed numeric fields*, where the least significant character is coded to include the sign (+/-) - more details [here][1] - so a value -12 might be represented as `1K`. I'd be pretty surprised if you can't do this in SSIS, having said that though, I currently do something similar using [BULK INSERT][2], mainly because I'm more comfortable using T-SQL than SSIS! [1]: http://www.3480-3590-data-conversion.com/article-signed-fields.html [2]: http://msdn.microsoft.com/en-us/library/ms188365.aspx
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

thank you , but when i tried using bulk insert I face problem with the codepage , I have say that Record Types Fixed Length Records ,Most mainframe data is stored in a fixed-field, fixed-record format, where every field, and therefore every record, is fixed in size. There are no delimiters between either fields or records. what i want to say I have COBOL Record Layout and Data file and hinst any guide or any ODBC driver to import only data to SQL Server 2008 . thanks all
0 Likes 0 ·
If you want to use an ODBC driver to overlay the record layout - Transoft U/SQL is a product I've used in the past
0 Likes 0 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
I don't know the COBOL data files, but generally you can import any type of file using SSIS if you know how to process the input file. If you know the structure and know how to process the input file, you can use the Script Component in the data flow of SSIS as Source and write a code to open and process the input file and return the rows into the data flow. After that you have rows in SSIS and you can process them further and store them to destination.
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

thank you so much , I will try , but I might face problem with codepage as COBOL datafile different from SQL Server . let see thanks again
0 Likes 0 ·
Encoding is not a problem if you will use the Script component. When you read the source file, you can read them into a byte buffer and then easily convert the encoding to any other encoding. below code converts easily from a source encoding to destination UTF8 or whatever other you want. string str; byte[] buffer; buffer = Encoding.Convert(Encoding.GetEncoding(COBOL_CodePage), Encoding.UTF8, buffer); str = Encoding.UTF8.GetString(buffer); if you populate the buffer with the input data, you will receive data which can be used by SQL Server. I've used the code previously for converting the EBCDIC 870 code page which is also unsupported by SQL server and it works correctly. I was using it to convert the EBCDIC 870 to Win1250 so instad the Encoding.UTF8 I was using Encoding.GetEncoding(1250)
0 Likes 0 ·
thank you so much , I will come back to you one i finish the task ,thanks again :)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.