Flat File source to Oracle OLE DB Target (ETL)

What could be simpler: map a flat file record structure, extract the data, and populate essentially the same flat file record struc in an Oracle table. Let the fun begin.

Specifically: the flat file record struc is fixed length 196 bytes. A particular field consists of 4 bytes of Integer data; IS deals very nicely with the definition, does not appear to be any issue with that. The issue is trying to get the 4 bytes of integer to map and load into the Oracle table. The data type in the flat file def is DT_UI4. The data type in the Oracle target is DT_NUMERIC. One would think that perhaps a simple transform and Viola?! I've defined the transform but does not seem to matter - whatever I try yeilds the same results.

I 've tried many different src/trg data type defs., but all yeild the same results.

Execution Results from debug:

Everything validates and then...

[kcd [8671]] Error: Data conversion failed. The data conversion for column "load_time_min" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

[kcd [8671]] Error: The "output column "load_time_min" (11050)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "load_time_min" (11050)" specifies failure on error. An error occurred on the specified object of the specified component.

Any ideas appreciated!

Thanks.

What precision and scale are you specifying for the DT_Numeric? Since the Integer column is defined as an unsigned integer, you need to allow enough room in the numeric to support it. Or you could use a derived column transform to explicity cast the integer to the numeric, if you know there will not be an overflow.

(DT_NUMERIC, 10, 0) [Your_Column]

Flat file produced but need line returns

My Integration Services creates a flat file using OLE DB Source and then a Flat File Destination. The flat file is created from data from my data source with is just a table with many rows.

Each row in my flat file .txt is appended onto each other, there are no line returns after each record. How can I put in a return after each row in my flat file that is outputted from the Flat File Destination component in conjunction with the properties in my Flat File Connection Manager. What am I missing here in order to ensure each row from my table creates a carriage return in my .txt flat file?

The column separator for your last column need to be {CR}{LF}

HTH,
Ovidiu

|||

In my File Connection Manager, I have {CR}{LF} for the header row delimeter but it's not putting a return at the end of each row in my flat file still.

I do not have a header row since this is coming straight from the table so I don't understand why the requirement to specify header information in the File Connection Manager at this point but it is required.

So I'm not sure what else I'm missing, my flat file still doesn't return each row, it appends each row right after another still.

|||I see what you're saying, put

{CR}{LF}

in the column delimeter for the last column's properties, is this correct? If so, I can't that property is greyed out (because it doesn't need it when specifying a flat file) when I click on the last column in the Advanced Tab of the File Connection Manager.

I just don't see where to specify the row delimeters after looking at all the tabs.

|||

Here's my flat file, still doesn't have carriage returns:

http://photopizzaz.biz/junk/flat_file.txt

|||What format are you using for your flat file? You should use RaggedRight if you want carriage returns at the end of your row.|||

The file needs to be a fixed format of 100 bytes. It needs to be formatted like this:

http://www.photopizzaz.biz/junk/final_flat_file.txt

It has to have line carriages just like this one.

|||Ranjeeta , you just made my day, thank you! it was so obvious but I had no clue!|||I had trouble figuring that out too and found the ragged-right solution on my own. But I have a different problem now. I specified a multi-line header in the flat-file destination component, but the text file produced only uses the line-endings of the header when viewed in WordPad or some other advanced text editor.

Notepad only shows little boxes and this make the header useless.

How do I get a real line-ending into the file at the end of each of my header lines so that notepad will display it properly?

Thanks!|||Don't use notepad. Try VIM, or wordpad, or TextPad or something like that. Notepad is a "dumb" editor and can't handle too many special characters.|||That particular solution is already in place. Some of my users are stubborn. You know how that goes. Notepad is their friend... In any case, there are a large number of less savvy software packages out there that will have the same trouble as notepad, and it would be nice for everyone using SSIS to be able to produce flat files that they can consume.

TextPad is what I generally use. Wordpad works too.

Thanks though.|||Right, but Notepad only processes CR-LF line terminations. If a line terminates with LF, it won't display correctly in Notepad. You can't do anything about that, unless you issue a CR-LF.

Flat file produced but need line returns

My Integration Services creates a flat file using OLE DB Source and then a Flat File Destination. The flat file is created from data from my data source with is just a table with many rows.

Each row in my flat file .txt is appended onto each other, there are no line returns after each record. How can I put in a return after each row in my flat file that is outputted from the Flat File Destination component in conjunction with the properties in my Flat File Connection Manager. What am I missing here in order to ensure each row from my table creates a carriage return in my .txt flat file?

The column separator for your last column need to be {CR}{LF}

HTH,
Ovidiu

|||

In my File Connection Manager, I have {CR}{LF} for the header row delimeter but it's not putting a return at the end of each row in my flat file still.

I do not have a header row since this is coming straight from the table so I don't understand why the requirement to specify header information in the File Connection Manager at this point but it is required.

So I'm not sure what else I'm missing, my flat file still doesn't return each row, it appends each row right after another still.

|||I see what you're saying, put

{CR}{LF}

in the column delimeter for the last column's properties, is this correct? If so, I can't that property is greyed out (because it doesn't need it when specifying a flat file) when I click on the last column in the Advanced Tab of the File Connection Manager.

I just don't see where to specify the row delimeters after looking at all the tabs.

|||

Here's my flat file, still doesn't have carriage returns:

http://photopizzaz.biz/junk/flat_file.txt

|||What format are you using for your flat file? You should use RaggedRight if you want carriage returns at the end of your row.|||

The file needs to be a fixed format of 100 bytes. It needs to be formatted like this:

http://www.photopizzaz.biz/junk/final_flat_file.txt

It has to have line carriages just like this one.

|||Ranjeeta , you just made my day, thank you! it was so obvious but I had no clue!|||I had trouble figuring that out too and found the ragged-right solution on my own. But I have a different problem now. I specified a multi-line header in the flat-file destination component, but the text file produced only uses the line-endings of the header when viewed in WordPad or some other advanced text editor.

Notepad only shows little boxes and this make the header useless.

How do I get a real line-ending into the file at the end of each of my header lines so that notepad will display it properly?

Thanks!|||Don't use notepad. Try VIM, or wordpad, or TextPad or something like that. Notepad is a "dumb" editor and can't handle too many special characters.|||That particular solution is already in place. Some of my users are stubborn. You know how that goes. Notepad is their friend... In any case, there are a large number of less savvy software packages out there that will have the same trouble as notepad, and it would be nice for everyone using SSIS to be able to produce flat files that they can consume.

TextPad is what I generally use. Wordpad works too.

Thanks though.|||Right, but Notepad only processes CR-LF line terminations. If a line terminates with LF, it won't display correctly in Notepad. You can't do anything about that, unless you issue a CR-LF.

Flat file export

I am new to the Integration Services and have a question. I need to export some data to a flat file. I set up a project and have a OLE DB Source object that I wrote a query to grab the data. I then pass that data to a Flat File Destination object. My question is that in the database one of the fields is stored at True or False, but in the flat file I need for it to be -1 or 0. Any help would be appreciated.

Thanks

The easiest way to do this is in your data set. Use a sql CASE statement to read the value and return the appropriate values to the column for TRUE and FALSE.

Hope that helps.

|||That will work perfect, Thank you.
|||

You can also do the same thing in a derived column transform using:

Code Snippet

[YourColumn] ? -1 : 0

assuming 0 is false and -1 is true.

flat file destination -data all on one line

I have a flat file destination that Im sending data to, from an OLE DB data source. There are two records, but for some reason they are both going on the same line in the output. This is after setting the output to fixed width, from comma delimited.

Help ?

If you are viewing the flat file from a text editor, recheck the option on your editor to verify word wrap is not occurring. The file may be perfectly fine, it's the representation in the editor.|||

If you are using the pure FixedWidth format the row delimiter is not going to be inserted. It assumes all your columns are fixed width and it does not put any delimiters.

If you want to break your rows in new lines, choose the Fixed Width with Row Delimiters option at the time you create the flat file connection from the Flat File Destination. This will configure the connection to use the RaggedRight format and add a dummy column with the new line delimiter at the end. You should be able to do this manually as well, if you are changing an existing connection.

HTH.

Flat File Destination

I have a red link from my OLE DB Destination to my Flat File Destination. I'm not sure why it's read but the OLE DB shoves some data into a table. I want to then take that data and move it into a flat file using the flat File Destination step but when I run my project, it shows the Flat File Destination Step green but my flat file shows nothing at the end.

I'm not sure what I'm doing wrong but the line linking the two is still red. Check out my picture here:

http://www.photopizzaz.biz/junk/1.jpg

and then the properties of my flat file destination module:

http://www.photopizzaz.biz/junk/2.jpg

http://www.photopizzaz.biz/junk/3.jpg

and my flat file connection manager editor properties:

http://www.photopizzaz.biz/junk/4.jpg

http://www.photopizzaz.biz/junk/5.jpg

Actually you redirected only the ErroroOutput of the OLE DB Destination.

Once the rows got into OLEDB Destination they don't move further. You need to add a Multicast transform in front of OLE DB destination to multiply the rows. And you can send one output to OLE DB Dest and one output to Falt File Destination

HTH,
Ovidiu

|||thanks a lot. But then what hooks up to the Multicast transform prior to the multicast transform? After my OLEDB Destination, then what, I just hook that up to the Multicast Transformation or hook something else to it first? How do I continue the flow after the OLE DB Destination is done I guess is what I'm asking...going from the OLEDB to the Multicast ? What's in between those 2?|||what did you mean by "Actually you redirected only the ErroroOutput of the OLE DB Destination."|||

Ok, got it to work, I didn't need what you said. I just used an OLE DB Source like I tried oringally but just deleted and reset it up shown here:

With that, how do I specify order of the workflow between my left side and right side. In other words, how do I ensure that after the OLE DB Destination is run, that it runs my OLE DB Source next? Since I can't hook an arrow between the 2, what is the proper way to ensure workflow and order here between the left set of tasks and right set of tasks show here:

http://www.photopizzaz.biz/junk/6.jpg

|||

You cannot force parallel graphs in the same Data Flow to execute sequentially. By setting them in the same Data Flow task you are actually setting a preference for them to be executed concurrently.

To assure your data flows are executed sequentially use multiple Data Flow tasks and connect them to define a wished order.

Thanks.

|||thanks, I actually figured out the data flow as you suggested.

flat file as source

Hello Ereryone,

I have Flat File as my source. Before i tried to load the data in to ORACLE Destination thru SCD component the error was with ole db.

any ways i try to load the data in Access DB but I’m getting different error in same component (OLE DB) After SCD Component. can any one help me out in this.

thank you

What exactly is your problem? We would need more information to help you out...

What errors are you getting, what transforms are you using, have you looked at the data directly before your destination, if so does it seem to be in order, do you have any column type mismatches, etc.