Float data

Hello.

We have a third party app that's database holds allsorts of info as floats. I have to update records in this database from my source database that stores it's data as decimal(8,2).

so when I update a row with 25.00 it goes into the 3rd party app database as 25 because it's a float. Is it possible to put the data in so it reads 25.00 without changing the datatype from a float?

Thanks.

You should not worry about the format of numbers as they come from a query. Use your application to apply any formatting that is needed.|||The "float" data type does not store non-significant digits. The trailing 0 is non-significant. So the answer is no.

You would have to reformat it to decimal(8,2) when you get it back out of the float.|||

Thanks for the replies, I thought as much after reading BOL but you see my source database is an Ingres database (remember them!) and it's float data type does store the non-significant digits so I was curious to see if SQL could do the same.

Thanks again

Flat File to SQL Destination - Unused Columns Warning

I have a flat file data source and SQL Server destination data flow. Only a subset of columns from the source are mapped to the destination. During execution SSIS returns DTS pipline warnings for every unmapped source column. Is some kind of transformation the only way to get rid of these warnings?

Also this data flow subsequently returns an error: [SQL Server Destination [1293]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security."

I'm researching this error, but if anyone is familiar with it your advice would be appreciated. Thanks.

You can either ignore the unused column warnings, or you can deselect them at the source connector so that they are not used. The preferred way is to not bring over additional columns from the source unless you need them for something.|||For the error message make sure that the file exists and that the user running the package has access to it.|||Amazing what you can miss the first time you work with a component. Deselecting the unwanted columns did the trick. Thanks Phil.|||

Rafael, it turns out I need to use an OLE DB destination instead of SQL Server destination because the database is on a remote server. See: http://msdn2.microsoft.com/en-us/library/ms141095.aspx

Specifically: "You cannot use the SQL Server destination in packages that access a SQL Server database on a remote server. Instead, the packages should use the OLE DB destination."

|||which it makes sense. I totally missread your post. Sorry about that.|||

M.Glenn wrote:

Rafael, it turns out I need to use an OLE DB destination instead of SQL Server destination because the database is on a remote server. See: http://msdn2.microsoft.com/en-us/library/ms141095.aspx

Specifically: "You cannot use the SQL Server destination in packages that access a SQL Server database on a remote server. Instead, the packages should use the OLE DB destination."

If you want to know more about why, read this:

Destination Adapter Comparison
(http://blogs.conchango.com/jamiethomson/archive/2006/08/14/SSIS_3A00_-Destination-Adapter-Comparison.aspx)

-Jamie

|||Thanks guys. The comparison article is also helpful.

Flat File Source, please Help !!

Hi all

I have some problems with the "Flat File Source" ...
I am trying to load a textfile, but IS allways cuts the rows ...
When I look at the preview while designing, the row is complete,
so I am wondering what IS is doing ...

Thanks for any comments

Best regards
Frank Uray

Here is what I am trying to load (one row from the file):
WPBX1 1.2 19330065002695435000 001200526000 000020002002-11-13-11.17.55.2220262006-03-03-05.50.44.322629002000010001AG2006-03-03-05.50.44.322629WIS030EPF033200602173410567000101 271275 2006030220060303200603032006030320060303 200603032006030320060303 200603032006030320060303200603031 0.000 200.000A UWCE 1 24617 10844890000000000 0.000000 0.000 0.000 0 149.500 149.500 149.500 00100010 0.000000000000000E+00 0.000000000000000E+00 0.000000000000000E+00 0.000000000000000E+00 1.000000000000000E+00 1.000000000000000E+00 0.000000000000000E+00 0.000000000000000E+0000 0000000001CV êê 00 1.150 200.000 0001-01-01-00.00.00.00000000120052600071200180K 712 71550 230.000 230.000 230.000 0.000 230.0000010 C 100.000000 2006-03-03-05.50.44.3226291567 230.000 230.000 230.000 0.000 230.0000010?C 100.000000 2006-03-03-05.50.44.3226291568 230.000 230.000 230.000 0.000 230.0000010?C 100.000000 2006-03-03-05.50.44.3226291585 230.000 230.000 230.000 0.000 230.0000010?C 100.000000 2006-03-03-05.50.44.3226291590 -80.500 -80.500 -80.500 0.000 -80.5000010?C 35.000000 2006-03-03-05.50.44.3226291640 -80.500 -80.500 -80.500 0.000 -80.5000010 C 35.000000 2006-03-03-05.50.44.3226291830 149.499 149.499 149.499 0.000 149.4990010?C 65.000000 2006-03-03-05.50.44.322629

On SQL Server I get only this:
WPBX1 1.2 19330065002695435000 001200526000 000020002002-11-13-11.17.55.2220262006-03-03-05.50.44.322629002000010001AG2006-03-03-05.50.44.322629WIS030EPF033200602173410567000101 271275 2006030220060303200603032006030320060303 200603032006030320060303 200603032006030320060303200603031 0.000 200.000A UWCE 1 24617 10844890000000000 0.000000 0.000 0.000 0 149.500 149.500 149.500 00100010 0.000000000000000E+00 0.000000000000000E+00 0.000000000000000E+00 0.000000000000000E+00 1.000000000000000E+00 1.000000000000000E+00 0.000000000000000E+00 0.000000000000000E+0000 0000000001CV

I can't be of much help here except to say check the data types in your flat file conenction manager. What are they set to?

-Jamie

|||

Hi Jamie

The DataType is set to: "string [DT_STR]"
I have also tried "text stream [DT_TEXT]"

IS seems not to be very consistently because why
is it correct on the Connection Managers Preview ?

Best regards
Frank Uray

|||Open the File connection manager and look at the advanced settings. Look at the output column width. By default the system sets it to 50. Increase this and it should work. Make sure this width matches the width on your field in the table.|||

Hi

Thanks for your comment!

I have checked this allready.

It has something to do with these characters ( êê) ...

Best regards
Frank Uray

|||

I think, if lenght is correct then ( )this might be set as a delimiter..

Chek in advanced properties

|||

Hi

The delimiter is set to "{CR}{LF}".
The problem is this character ( ), the question is how
can IS load such a row ?
As I told before, the preview in connection manager is correct.

Best regards
Frank Uray

|||

hi,

have a look at section fifty in the ProjectREAL docs, which may pertain to this situation.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/SQL05InSrREAL.asp

drew

sql

Flat File Source with Ragged Right Problems

For some reason, when I try to use the Flat File Source and set the record type to Ragged Right it does not seem to recognize 'short' records. It seems to be confused by the CRLF set delimiters and not recognize these in 'some' records. The input does not seem consistent. What am I missing?

SSIS parses column by column, not rows then columns. There are a fair number of posts on this. Basically the work around today is to bring in each row as a single string column, then parse it into indiviudal columns in the package.

Example here: http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx

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 Source Queston

I want to read the following file using the Flat File Source flow:

10000 Router
20000 Hub
10000 Switch
30000 Server
40000 Harddisk

Spaces are used between the numbers (e.g. 10000) and the following text (e.g. Router). Each line is temrinated by a {CR}{LF} pair. I would normally think of this as a "fixed width" file.

But in the Flat File connection manager, if I use format "Fixed width", the preview shows a row width of 1 character wide (the first row contains the number "1"; the next row contains "0", and so on for the remaining 3 rows.in the first line) Not what I had in mind. How do I setup SSIS to handle this file in intended maner?

TIA,

Barker

P.S. I never had any trouble with this type of file under Sql 2000 DTS. Also, have you noticed the crappy-looking "bitmap" displayed when one wants to click and define columns for a fixed-width file?

I might add that using "Fixed width" format I can successfully parse the columns: "Column 0" contains the digits; "Column 1" contains the spaces between the digits and the text; "Column 2" contains the text itself and "Column 3" contains the <CR><LF> line terminators. I'm sure I'm missing the point as this seems like a lot of work for something that should be very straightforward.

barker

|||

I'm beginning to think that "ragged right" is the preferred format. That format allows for fixed-width columns but assumes that last column has variable width (and is terminated). In other words, ragged right recognizes the line terminator as exactly that and doesn't think of it as another character(s) to be parsed.

Barker

|||

You've got it right. The RaggedRight seems as the most appropriate format for your file.

Thanks.

Flat File Source Problem...

I have a weird thing happening.
I have an .csv file. When I try to load it into a table, I can do it easily in DTS 2000. But when I am trying to do it in SSIS 2005 with exactly the same settings (like Text qualifier, row delimiter etc.), I am getting an error: "The last row in the sampled data is incomplete. The column or the row delimiter may be missing or the text is qualified incorrectly." I looked at the file and it looks complete to me.

What could be the problem ?

P.S. DTS 2000 is on 32-bit Windows, and SSIS 2005 is on 64-bit Windows 2003. Could that we a problem ?

Victor_V wrote:

I have a weird thing happening.
I have an .csv file. When I try to load it into a table, I can do it easily in DTS 2000. But when I am trying to do it in SSIS 2005 with exactly the same settings (like Text qualifier, row delimiter etc.), I am getting an error: "The last row in the sampled data is incomplete. The column or the row delimiter may be missing or the text is qualified incorrectly." I looked at the file and it looks complete to me.

What could be the problem ?

P.S. DTS 2000 is on 32-bit Windows, and SSIS 2005 is on 64-bit Windows 2003. Could that we a problem ?

I suppose it could be a problem, but DTS has historically been more forgiving when processing files. Are you sure you don't have a carriage/line-feed as the last line in the file? Sometimes this happens, and SSIS will think that is an incomplete row (It is, technically).|||It is true that the parsing behavior in SSIS is very different from DTS 2000. DTS was very forgiving as Phil mentioned, and there could be an extra carriage/line-feed which is causing this. Also DTS used to process files even if not all columns were populated in a row. However, SSIS would bail out on such files.|||

How do I check for this extra carriage return character ? It's not really visible. What editor would I use ?

Also, I usually get those "pipe" delimited files from Excel files, saving them as .CSV files.

Thanks,

Victor.

|||

Victor_V wrote:

How do I check for this extra carriage return character ? It's not really visible. What editor would I use ?

Also, I usually get those "pipe" delimited files from Excel files, saving them as .CSV files.

Thanks,

Victor.

Go to the last line of the file. Does the cursor sit at the end of a row of data, or on it's own line? That's one way.|||It sits on it's own line (at the beginning of a new line)..|||

Victor_V wrote:

It sits on it's own line (at the beginning of a new line)..

Hit backspace on that line and then save the file. Try running it again. Or try a ragged-right format.|||

Actually, I just opened this file in UltraEdit in HEX view, and the last 2 characters in the file are "OD OA", which is the end of the line, just like at the end of every row...

That's very strange...

|||

Victor_V wrote:

Actually, I just opened this file in UltraEdit in HEX view, and the last 2 characters in the file are "OD OA", which is the end of the line, just like at the end of every row...

That's very strange...

That's not strange, that's the point. SSIS sees that as a row, but there aren't any columns in it.|||No, what strange is that those 2 characters ARE the last characters in the row (and each row), and there is nothing else there. That's how it looks like in UltraEdit HEX editor. But when I open the same file in Notepad and hit Ctrl+End to go to the end of the file, that's when the cursor goes to the new row, instead of stopping at the end of the last row.|||I want to be sure on a few things...

The format is: Delimited
The text qualifier is: " (if needed)
The Header row delimiter is: {CR}{LF}

Under columns:

The Row delimiter is: {CR}LF}
The Column delimiter is: Comma {,}|||

The format is: Delimited
The text qualifier is: <none>

The Header row delimiter is: {CR}{LF}

Under columns:

The Row delimiter is: {CR}LF}
The Column delimiter is: Comma {|}

|||Phil ? Anyone ? Any thoughts ?|||

Victor_V wrote:

Phil ? Anyone ? Any thoughts ?

Can you post a sample line from the csv file?|||

Here are the last 5 rows in my .csv file:

12|Community Network Services, Inc|MI|C|T1020-Low|Personal care services, per diem |Blank||Per Diem||49|13461|13461|359677.92|26.72|26.72||0|13,461|13,461|0|615013.79|99.60%||0.00%|433.3|0.07%|2031.64|0.33%|617,479|0.00|45.69|0.00|0.03|0.15|45.87|24.96|20.63|33.51|22.81|29.7|same method from prior year
12|Community Network Services, Inc|MI|C|T1020-Moderate|Personal care services, per diem |TF||Per Diem||29|7391|7391|619809.26|83.86|83.86||0|7,391|7,391|0|490943.23|99.63%||0.00%|265.36|0.05%|1554.76|0.32%|492,763|0.00|66.42|0.00|0.04|0.21|66.67|68.16|53.58|68.53|54.88|67.43|rates reflect similar to prior years
12|Community Network Services, Inc|MI|C|T2003|Non Emergency Transportation|||Trip||2|148|148||0.00|0.00||0|148|148|0|544.16|100.00%||0.00%||0.00%||0.00%|544|0.00|3.68|0.00|0.00|0.00|3.68|0|7.99|0|7.92|0|
12|Community Network Services, Inc|MI||GF Pharmacy|GF Pharmacy||||||||||||||||||||||||753,848||||||||||||
12|Community Network Services, Inc|MI||H0038|Drop-in Center||||||||||||||||||||||||447744.18||||||||||||

Thanks, Phil.

Flat File Source option missing in the Sql Server Import & Export Wizard.

Hi All,

I want to import a txt file data to a sql server database table, to do this i used sql server import and export wizard. In this when we choose a Data Source, the option Flat file source is not coming up in the combo box in the wizard.

I am using sql server 2005, Management Studio to do this.

steps 1. right click on the database --> all tasks --> import data --> sql server import export wizard --> choose data source dialog box....

please help me.

thanks in advance.

On my installation, flat file source is 4th on the list below the .Net Framework providers for odbc, oracle & sql serversql

Flat file source not option for importing data

I was getting the product error associated with the full version of SSIS not installed so I ran the installation again and selected the Integration Services check box.

Now when attempting to import data into a database, the drop down list doesn't have a flat file option.

How do I import data from a txt, csv file?

Thanks

PatrickCrofoot@.hotmail.com

did you stop and restart Integration Services? is it running under a domain administrator account?|||I have. And no..

Simply using SQL Server Management Studio and right clicking my db then choosing import data under tasks. There is no Flat File datasource.

So the other route was to attempt the tutorial via integration services. That's when I get the more descriptive error that the FLATFILE option is not installed correctly.

I've completely un-installed and re-installed SQL Server 2005 and all associated tools. Same problem.
|||I'm having the exact same problem. I'm about to install CTP SP1 to see if it corrects the problem.|||I uninstalled and re-installed the complete application.... twice.. And now it seems to work. Nothing to do with SP1.

Flat file source not option for importing data

I was getting the product error associated with the full version of SSIS not installed so I ran the installation again and selected the Integration Services check box.

Now when attempting to import data into a database, the drop down list doesn't have a flat file option.

How do I import data from a txt, csv file?

Thanks

PatrickCrofoot@.hotmail.com

did you stop and restart Integration Services? is it running under a domain administrator account?|||I have. And no..

Simply using SQL Server Management Studio and right clicking my db then choosing import data under tasks. There is no Flat File datasource.

So the other route was to attempt the tutorial via integration services. That's when I get the more descriptive error that the FLATFILE option is not installed correctly.

I've completely un-installed and re-installed SQL Server 2005 and all associated tools. Same problem.
|||I'm having the exact same problem. I'm about to install CTP SP1 to see if it corrects the problem.|||I uninstalled and re-installed the complete application.... twice.. And now it seems to work. Nothing to do with SP1.

Flat file source not communicating with script component properly

Hi there,

I have a flat file source in my data flow that is suppose to feed through a couple of columns to a script component. All of the columns have data but when I debug in my script component (with the aid of messageboxes) I can see that one of then is empty.

This is wrong because when I preview that data in the flat file source, I can see that the columns are all populated.

Has anyone ever encountered that a flat file source doesn't communicate with a script component properly? I mean it does pass through the data of all other columns except for one.

I have replaced the components in case it had become corrupt and I even rebuild the package but the problem still persists.

Any advice would be welcome

Regards
Mike

Put data viewer between the Flat File Source and the Script component and check what data actually enters your script.

HTH,

Bob

|||Thanks for the reply,

I put a viewer on and saw that the script gets all the populated columns except for the one I mentioned earlier. Thus the problem seem to be with the flat file source not passing the data in that column through to the script.
But why would it do that even though it shows all of the columns populated when I click on the preview button on the flat file source?

Regards

**EDITED**

I have also noted that when I check the "retail null values from row as null values in data flow" checked box, then it does populate all columns correctly but I cant have that checked box checked since it does not go to the correct path on my workflow with the nulls. When I uncheck it, it misses the one column again but not the other ones. This is puzzling me very much
|||

What version of the SSIS do you have installed (RTM, SP1, SP2)? I think there was a flat file parsing bug fixed in one of SP releases that would potentialy cause this behavior.

Thanks,

Bob

|||Hi Bob,

I have recently (last week) upgraded to service pack 2 and also updated my ssis from Version 9.00.20(something) to Version 9.00.3042.00.

The package was built on an older version and pre service pack 2. I wonder if that is not the case here.

Thanks for the reply

Regards
Mike
|||

Would it be possible for you to isolate a few rows that fail, create another simple file with only those two rows, obfuscate your data and post your file here?

Just make sure the newly created file still fails the same way as the original one.

Thanks,

Bob

|||Hi Bob,

Just for some extra clarity, none of the rows are actually failing. They all pass through successfully except for one of the columns (SiteCode) in the file does not get read properly by the flat file source. It does not fail or warn me about anything, it just doesn't detect the data in this specific column. It passes an empty string through for all rows in that column.

Like I mentioned before, the funny thing is that when I preview the file in the flat file source in the data flow task, it does actually show that the column is populated. All of the SiteCodes are populated in the preview window but that preview data just doesn't get passed through to the other data flow component.

Would it still be usefull for me to post the file?

Regards
Mike
|||

Yes, by "failing" I meant not being parsed properly.

If you can provide the sample of data I can try to debug it here and eventually find out what is causing the behavior you are seeing.

Thanks,

Bob

|||Hi,

I hope this will be sufficient
The column with the 'BAL' data doesn't get read properly
Regards

FMC|FMCG|22110|FMCG Warh|BAL|Balance Sheet|BAL|Balance Sheet|22110|FMCG Warehouse Balance sheet|10030||Cost - Comp Equip 3yrs|2006|10|144.00|AA|General Ledger||
FMC|FMCG|22110|FMCG Warh|BAL|Balance Sheet|BAL|Balance Sheet|22110|FMCG Warehouse Balance sheet|10050||Cost - Comp Equip 10yrs|2006|10|233.00|AA|General Ledger||

|||

I tried to parse these two rows using Flat File Source and could not see any problem with it.

Could you tell me again what is not read properly? I have seen "BAL" values read in both; 5th and 7th columns.

|||Hi Bob,

I managed to find a workaround on this problem. When I click the "retain null values from source as null values in data flow" check box, the data does get passed on correctly but to the wrong exit point (error exit point).

I did further investigation as to why it would go to the error exit point and realized that it actually picks up on a null value and does not know how to handle it. I got the "object reference not set as an instance of a ...etc" error.

Adding code to check for these nulls and setting it to dbnull.value helped with my problem and things are working fine now. I also believe that my problem might also be because we did not use text qualifiers in this project but this is only speculation.

From now on I will know what to do with custom scripts reading and processing text files so that I don't leave the system open for these possibly unhandled situations.

Regards

Flat file source not communicating with script component properly

Hi there,

I have a flat file source in my data flow that is suppose to feed through a couple of columns to a script component. All of the columns have data but when I debug in my script component (with the aid of messageboxes) I can see that one of then is empty.

This is wrong because when I preview that data in the flat file source, I can see that the columns are all populated.

Has anyone ever encountered that a flat file source doesn't communicate with a script component properly? I mean it does pass through the data of all other columns except for one.

I have replaced the components in case it had become corrupt and I even rebuild the package but the problem still persists.

Any advice would be welcome

Regards
Mike

Put data viewer between the Flat File Source and the Script component and check what data actually enters your script.

HTH,

Bob

|||Thanks for the reply,

I put a viewer on and saw that the script gets all the populated columns except for the one I mentioned earlier. Thus the problem seem to be with the flat file source not passing the data in that column through to the script.
But why would it do that even though it shows all of the columns populated when I click on the preview button on the flat file source?

Regards

**EDITED**

I have also noted that when I check the "retail null values from row as null values in data flow" checked box, then it does populate all columns correctly but I cant have that checked box checked since it does not go to the correct path on my workflow with the nulls. When I uncheck it, it misses the one column again but not the other ones. This is puzzling me very much
|||

What version of the SSIS do you have installed (RTM, SP1, SP2)? I think there was a flat file parsing bug fixed in one of SP releases that would potentialy cause this behavior.

Thanks,

Bob

|||Hi Bob,

I have recently (last week) upgraded to service pack 2 and also updated my ssis from Version 9.00.20(something) to Version 9.00.3042.00.

The package was built on an older version and pre service pack 2. I wonder if that is not the case here.

Thanks for the reply

Regards
Mike
|||

Would it be possible for you to isolate a few rows that fail, create another simple file with only those two rows, obfuscate your data and post your file here?

Just make sure the newly created file still fails the same way as the original one.

Thanks,

Bob

|||Hi Bob,

Just for some extra clarity, none of the rows are actually failing. They all pass through successfully except for one of the columns (SiteCode) in the file does not get read properly by the flat file source. It does not fail or warn me about anything, it just doesn't detect the data in this specific column. It passes an empty string through for all rows in that column.

Like I mentioned before, the funny thing is that when I preview the file in the flat file source in the data flow task, it does actually show that the column is populated. All of the SiteCodes are populated in the preview window but that preview data just doesn't get passed through to the other data flow component.

Would it still be usefull for me to post the file?

Regards
Mike
|||

Yes, by "failing" I meant not being parsed properly.

If you can provide the sample of data I can try to debug it here and eventually find out what is causing the behavior you are seeing.

Thanks,

Bob

|||Hi,

I hope this will be sufficient
The column with the 'BAL' data doesn't get read properly
Regards

FMC|FMCG|22110|FMCG Warh|BAL|Balance Sheet|BAL|Balance Sheet|22110|FMCG Warehouse Balance sheet|10030||Cost - Comp Equip 3yrs|2006|10|144.00|AA|General Ledger||
FMC|FMCG|22110|FMCG Warh|BAL|Balance Sheet|BAL|Balance Sheet|22110|FMCG Warehouse Balance sheet|10050||Cost - Comp Equip 10yrs|2006|10|233.00|AA|General Ledger||

|||

I tried to parse these two rows using Flat File Source and could not see any problem with it.

Could you tell me again what is not read properly? I have seen "BAL" values read in both; 5th and 7th columns.

|||Hi Bob,

I managed to find a workaround on this problem. When I click the "retain null values from source as null values in data flow" check box, the data does get passed on correctly but to the wrong exit point (error exit point).

I did further investigation as to why it would go to the error exit point and realized that it actually picks up on a null value and does not know how to handle it. I got the "object reference not set as an instance of a ...etc" error.

Adding code to check for these nulls and setting it to dbnull.value helped with my problem and things are working fine now. I also believe that my problem might also be because we did not use text qualifiers in this project but this is only speculation.

From now on I will know what to do with custom scripts reading and processing text files so that I don't leave the system open for these possibly unhandled situations.

Regards

Flat file source component hangs after installing SP2

Hi there,

after we've upgraded to SP2 my flat-file-source component hangs in the validation phase.

normally at 50% or 66%.

i see no activity what-so-ever cpu-wise/memory-wise.

have no idea how to fix it.

(sorry if i am posting this twice, couldn't see my previous post)

Hi Mafti,

could you give us more information about your flat file source configuration? What type of flat file, how many columns, etc?

Also, could you make sure it actually hangs in the flat file source: copy your package and simplify it to have only a single data flow with the single flat file source connected to the row count?

Thanks.

Flat File Source Column Parsing Error

Hello All,

I have come across this issue with the Flat File Source when the delimiter is set to a comma.

"""KAILUA KONA,HI""","CA",

In the data snippet above and with the setting of using a comma as a column delimiter

and a " as the text qualifer.

the data will be parsed in this fashion:

"""KAILUA as a column:

HI""" as a column

CA as column

when it should be

"KAILUA,HI" as a column

CA as column.

Is there a way to let the Flat File Source to let it know not to parse the data in multiple quotes ?

Thank you

Eric Flores

The flat file parser does not know how to handle embedded qualifiers.

In this case, you can workaround this by loading the qualified data and then stripping the qualifiers in Derived Column or Script transform.

HTH.

|||

I will try that out and see if it will work. Though in my data snippet, the comma in question is not even a qualifier but is part of the data.

What I do not get is that in DTS for SQL Server 2000 when specifying a flat file as having comma as the column delimited and a double quote as the text qualifier that when given the data snippet in my previous post that it would parse the data into the correct two columns.

|||

Eric,

feel free to go to the product feedback site below and report your issue. That will create a bug in our system.

http://lab.msdn.microsoft.com/productfeedback/default.aspx

|||This has been "resolved" in product feedback. Was there a resolution or is this just the way it is going to be?

Flat File Source Column Parsing Error

Hello All,

I have come across this issue with the Flat File Source when the delimiter is set to a comma.

"""KAILUA KONA,HI""","CA",

In the data snippet above and with the setting of using a comma as a column delimiter

and a " as the text qualifer.

the data will be parsed in this fashion:

"""KAILUA as a column:

HI""" as a column

CA as column

when it should be

"KAILUA,HI" as a column

CA as column.

Is there a way to let the Flat File Source to let it know not to parse the data in multiple quotes ?

Thank you

Eric Flores

The flat file parser does not know how to handle embedded qualifiers.

In this case, you can workaround this by loading the qualified data and then stripping the qualifiers in Derived Column or Script transform.

HTH.

|||

I will try that out and see if it will work. Though in my data snippet, the comma in question is not even a qualifier but is part of the data.

What I do not get is that in DTS for SQL Server 2000 when specifying a flat file as having comma as the column delimited and a double quote as the text qualifier that when given the data snippet in my previous post that it would parse the data into the correct two columns.

|||

Eric,

feel free to go to the product feedback site below and report your issue. That will create a bug in our system.

http://lab.msdn.microsoft.com/productfeedback/default.aspx

|||This has been "resolved" in product feedback. Was there a resolution or is this just the way it is going to be?sql

flat file source and destination - need fixed width output

I have a text file that is comma delimited and im pulling it in with a flatfile connection manager. I want to read some of the data, then output another flat file but in a fixed column width. What settings do I made to the connection manager of the output flatfile ?Choose a fixed-width format when setting up the destination. And then define your columns.|||

Hi

A solution is:

- Go to advanced properties of output flat file.

- Define the fields as text and the property OutputColumnWith with the size you want

- Export your data to that file and convert it to text.

Raul

|||I had some other steps in the middle, so I took them out just to simplify things. In the connection mgr for my destination, it wants to know the input column widths. Should I really need to bother with this, since in the end, I just want whatever output i get during any previous steps, to simply be output to the flat file as fixed width ?|||Ok, ive managed to get my output in fixed width in the output file, but it appears the lines arent terminating where they should be. What controls where the lines terminate ? I dont have a header row (no column names in the first row), so what, if anything should the "header row" settings be set to ?|||Ive got a data file with values seperated with commas. I want to read in this text file, do a lookup and add the lookup column on the front of the other columns and save the output in a fixed width format, similar to what you would get if you saved the query reqults to a file in sql mgt studio.

What I have so far is just a flat file source , a lookup and a flat file destination.

I can get the output to generate, but there doesnt seem to be any row terminating, its all one big string.

help ?|||Use "Fixed width with Row Delimiters" option for the flat file connection manager.|||

Eric Wisdahl wrote:

Use "Fixed width with Row Delimiters" option for the flat file connection manager.

Where is that option available ? Under the general tab on my flat file conn mgr, I have only the options:
"fixed width"
"delimited"
"ragged right"

If I have fixed width selected, and go to the advanced tab, start entering my own columns, the column delimiter option is greyed out.|||When you are first creating the flat file connection manager it gives you the option of delimited, fixed width, fixed width with row delimiters, ragged right. All that fixed width with row delimiters does is add another column to each record which contains the row delimiter. You can accomplish the same thing by adding it in with the derived column and adding it to the end of your output record.

Flat file source and ConnectionString property

Hi,

i have inherited a SSIS project that was left unfinished by a previous developer. One thing i notice with it is that all the flat file sources in the connection manager have hardcoded paths for the ConnectionString property. I would like to change this so that at least the path, and if possible the file name, are dynamic - i.e. they are determined either by parameters passed into the package when it is run or they are contained within a config file.

Is this possible? Can anyone supply a link to an article or tutorial specifically covering this?

Many thanks

Hi Sluggy

It's simple.

1) Create a Variable called SourceFolder of Package Scope

2) Choose the Value Property of SourceFolder Variable while Setting Package Configuration.

Now it's ready for use in expression.

3) In the Expression builder for Flat File Connection Manager, choose ConnectionString property for the flat file and use the following expression: [User:ourceFolder] + "\FileName.txt"

Thanks

Subhash Subramanyam

Flat File Source - Add Output Field

I am moving data from a flat file source to a SQL Server table. But I want to add a columm that IS in the destination table, but NOT in the source file. Say the table column name is XXX in destination table, and there will be a global variable called @.[User::XXX] that remains constant throughout the package. I would like to put the variable value into the destination column, even though the source file does not contain the field. Is there an easy way to do this?Add a derived column transformation between your source and destination. Then simply drop the User:XXX variable into it. That will add a new column to the data flow.|||Thanks! This was the solution. I actually came across that while trying to solve a problem or recalculating and input field while passing through.

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.