October 29, 2009 at 6:56 pm
Hi,
I need to import unformatted text file to new table. Every row in text file continued in 2 nd row in the file. 2nd row started in 3rd row in the file. FIXED LENGTH file.
Example
0001234567 John minneaplois 000-487-8374 minnesota
america xyz@abc.com 55464
0000998887 peter st paul 000-333-3333 jersey
america abc@xyz.com 08854
0000282822 kevin nashville 900-999-9999 tenneessee
america cde@feg.com 40404
fist two lines are first row
second two lines are second row
third two lines are third row
I need to create a new table and import the text file to new table. The text file is FIXED LENGTH
Thanks,
bmr
October 29, 2009 at 7:18 pm
You'll most likely need to use a Script Component for this. I did a presentation on this a while back, feel free to download my sample code here: http://www.timmitchell.net/page/Resources.aspx, under the "SSIS Scripting" event for 8/1/2009.
Let me know if you need a push in the right direction.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
October 30, 2009 at 5:24 pm
Thank you, BUT I am getting this following error and I am not able to open that package
TITLE: Microsoft Visual Studio
------------------------------
The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails.
------------------------------
ADDITIONAL INFORMATION:
The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails.
------------------------------
BUTTONS:
OK
------------------------------
Thanks,
bmr
October 30, 2009 at 8:18 pm
Could be a version incompatibility. What version of BIDS (Business Intelligence Developer Studio) are you using? The sample was developed using BIDS 2008.
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
October 31, 2009 at 7:17 pm
Mine is 2005 version. Can you send 2005 version file please? If possible. or can you send screen shots or step by step procedure please?
Thanks,
bmr
November 2, 2009 at 10:46 am
Hi Tim/Others,
Similar problem here. I am on VS2005 and unable to open the sample packages.
My situation here is recordset with wrong delimeter: example
"AAA","BBB","CCC","","DDD",,"EEE
...
...
That is the 6th field comes as blank - whereas it is expected to be "".
I wish to write a script task that would replace blank to "" and reformat the source flatfile before use it as a source.
Checked the demo scripts from Tim's page - looks like they would have been of great help. But I am unable to open the VS2008 scripts 🙁
Can you please get me a script code snippet to do the needful?
[font="Comic Sans MS"]--
Sabya[/font]
November 9, 2009 at 10:10 pm
For the original issue, unfortunately I don't have BIDS 2008 on this dev machine. I'll attach the C# code snippet, which will hopefully give you some insight into what I suggest (note that you'd need to translate it to VB.NET for use in 2005). I'll describe what is required:
Ordinarily, you'll have one output row for each input row. However, since you're dealing with an unusually-formatted data file, that's not the case here. So you'll need to create one row in your output buffer (see the AddRow() method in the sample file), but you'll be building data from more than one line in the file (note the multiple calls to ReadLine()). This methodology does assume that you've got a consistent structure to the file, specifically that every sequential even/odd line number combination should be merged into a single row of data.
Hopefully this makes some sense - the attached code file should help. Shout out again if you get stuck.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
November 9, 2009 at 10:17 pm
Sorry, duplicate file upload... I did this once and it appeared to hang before the reply was done.
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
November 9, 2009 at 10:26 pm
For the second issue (missing delimiters), I think you may be confusing delimiters with qualifiers. Delimiters separate values within your text, but qualifiers sort of do the opposite, keeping blocks of text together even if they contain the delimiting character - this is especially common in CSV files since you often find values such as "Smith,John" in a CSV file.
That being said, if you have missing qualifiers for blank values, I don't think you'll have a problem with it (note: I haven't explicitly tested this - YMMV). If you have missing quotes around values such as Smith,John then you have a different issue which could get a little tricky.
Shout back if this doesn't answer your question.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
November 19, 2009 at 12:58 pm
Tim,
Sorry I missed your reply somehow. I will check this out and let you know.
[font="Comic Sans MS"]--
Sabya[/font]
November 20, 2009 at 8:00 am
Thanks Tim for the heads up. Yes - it was my wrong understanding of delimeter and text qualifier. As you suggested to doesn't matter if "" qualifier is not present for null column.
Thank you.
[font="Comic Sans MS"]--
Sabya[/font]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply