FlatFile Parsing Problem - Urgently Need Assistance!

We have a BIG problem that has been occurring for quite sometime. We have a RAGGED-RIGHT FFS (FlatFileSource) component which receives a FF and then transforms to XML. We define 10 FIELDS with the last field as {CRLF} per ragged right style. Simple right? The source file is sent to us from multiple separate groups, some of which don’t use the last two fields and have early line termination, so they basically are not there. The problem is that when we define X amount of fields in SSIS, it expects X amount of fields to be there. So when we receive the source file that doesn’t contain last two fields, SSIS tries to read in the next line, and in most cases successfully places the fields from the next line into the missing fields. Now what we end up with is a source file with 10 rows, but only half successfully transformed, with every other row fitted into the last two XML fields. SSIS completely ignores the {CRLF} until those specific numbers of spaces/fields have been met – even if it has to go onto the next line to get them. This seems horribly wrong to me. I would think that at the very least there should be some type of validation that prevents it from grabbing the next line. I thought that is what fixed-length SSIS style is for, and ragged right for allowing to parse multiple rows. We could enforce all clients to produce X char spaces (and we do), but there can be cases in any aspect where a file could get accidentally sent over with a shortened row. I have heard from certain individuals that SSIS adapter wasn’t designed with this in mind. Is this true, I am to believe that this would still get parsed without throwing any errors and potentially damage the ERP system it is going into? If this is the case then I feel extremely disheartened about SSIS – especially with all of the great advancements it has made.

We desperately need a workaround or at the very least some way to validate that a new row {CRLF} isn’t getting picked up in the first row of the flat file connection. Please can someone help ASAP!?

You could read the rows and then process in a script component based on your logic for handling these errors.

This is a common issue with processing flat files, and producing generic solutions. Biztalk was a real pain. If I remember from my Biztalk days a lot of the flat file parsing capabilities was driven by EDI. What you may think is logical may not suit some applications.

|||

Unfortunatelly Simon that seems to be the answer all around the board - too bad since the product was already designed (150+ DF transforms have to be modified). So do you recommend to do away with the FF Conn Mgr, and use a script source component to dynamically read in and calc the file rows? Can you perhaps provide an example of how to do what you mention?

Thanks,

|||

Haven't heard a response back, just keeping this on top - as this is a hot issue for us!

Simon (or anyone for that matter), I am interested in hearing how you would approach this, would you use a connection mgr, or use a custom src component and dynamically get in code, etc...

Thanks for helping!

|||

Have a look at the package I/ve created.

http://sqlblogcasts.com/files/3/transforms/entry409.aspx

I haven't done any type casting, or validation of field lengths just used the default 50 character column size.

|||Interesting, thanks Simon. One issue, we still need the ability to filter error rows, as the ff source allows. Can we do this using an intermediate custom transform, or must we manually create a new output for the bad rows? If the latter is the only way then do you just create a new output with columns x,m,z... and then similarly have an if/catch that would contain:

Row.Column(X,M,Z...) = extra_split_columns(col)

or, is there another function/method used to do this new output redirection?

|||

If you create an output and assoicate with the input (set the synchronous input ID to the ID of the input. That way you can redirect the row. Or you can have a seperate output where you have to copy the values from the input row to the error row.

I've updated the package http://sqlblogcasts.com/files/4/integration_services/entry412.aspx

|||Very good Simon, I think this hits the nail on the head... Thanks.

Flatfile output truncating after 255 characters

Hi,

iam bringing an output in flatfile which is truncating after 255 characters,some of the data is vanishing because of this.can anyone pls help.

pls advice.

Regards,

sg

Check the field definitions in the flat file connection.|||

Hi phil,

Iam running the combined query in Management studio and finding that the result is truncated.

some rows are exceeding 255 characters and the data after these 255 characters are getting truncated.

What should be done for this?

Regards,

sg.

|||

swan_sgp wrote:

Hi phil,

Iam running the combined query in Management studio and finding that the result is truncated.

some rows are exceeding 255 characters and the data after these 255 characters are getting truncated.

What should be done for this?

Regards,

sg.

I don't think the results are getting truncated. If you are expecting more characters, try exporting the results to a file and look there. It may be Management Studio's behavior to only show 255 characters in the result list.|||Also, look in Management Studio... Tools, Options, Query Results, SQL Server, Results to Grid and Results to Text. There are settings in each of those that specify how many characters are returned.|||

Hi phil,

I tried all the ways you have mentioned,you were correct that management studio is showing 255 characters and truncating all characters after that,

i ran the package,and some of the table-column's that were not shown in management studio are shown when a flatfile is generated but one table is not generating though,unable to solve even after recoding repeatedly.

only one table is not coming in flatfile fully.

pls advice.

Regards,

sg.

|||Redirect errors and inspect them. Or ensure that your flat file has been defined correctly. Embedded NULLs inside strings will terminate them, so for example:

If you have the string, "THIS STRING CONTAINS A [NULL] VALUE", the only thing that SSIS will see will be "THIS STRING CONTAINS A ".

Just a thought.

Flatfile into MSSQL

I currently have a flatfile with a seperate COBOL copybook. I need to be able to import all of it correctly into a db in mssql. Are there any *free programs that will turn the flatfile into some form for insertion into the db? What about csv format into the db?No need...just use bcp with a format file

What's the lrecl?

You could bcp the wole thing in as 1 column and the parse it to it's final destination...but creating a format file is the way to go...|||00002001 FLATFILE-REC.
000030 03 F-MASTER-PAR.
000040 05 F-MASTER-PARCEL-NUMBER PIC X(23).
000050 05 F-CARD-NUM PIC XXX.
000060 03 F-NUM-CARDS PIC XXX.
000070 03 F-APPR-PIN-NUMBER PIC X(22).
000080 03 F-PARCEL-LOCATOR.
000090 05 F-USER-CODES.
000100 07 F-USER-FILLER-1 PIC X(8).
000110 07 F-E-CODE PIC X.
000120 07 F-USER-FILLER-2 PIC X.
000130 03 F-APPR-DATE PIC 9(8).
000140 03 F-A-N-CODES.
000150 05 F-APPR-BY PIC XX.
000160 05 F-NEW-NOTICE PIC XX.
000170 03 F-APPR-UNUSED PIC 9(05).
000180 03 F-IMPROVEMENT-CODES.
000190 05 F-IMP-1.
000200 10 F-IMP-1-N PIC 99.
000210 05 F-IMP-2.
000220 10 F-IMP-2-N PIC 99.
000230 03 F-ACCOUNT-NUM PIC X(12).
000240 03 F-SOURCE-INFO-CODE PIC X.
000250 03 F-SUMMARY-VALUES.
000260 05 F-BLDG-DEPR-VALUE PIC 9(11).
000270 05 F-BLDG-RCN-VALUE PIC 9(11).
000280 05 F-XFOB-DEPR-VALUE PIC 9(11).
000290 05 F-LAND-VALUE PIC 9(11).
000300 05 F-SPECIAL-LAND-VALUE PIC 9(11).
000310 03 F-LAND-LINE-SUPERSET.
000320 04 F-LAND-LINE OCCURS 6 TIMES.
000330 05 F-USE-CODE PIC X(4).
000340 05 F-ZONING PIC X(6).
000350 05 F-UNIT-PRICE PIC 9(7)V99.
000360 05 F-NUMBER-UNITS PIC 9(7)V9(3).
000370 05 F-LAND-LINE-TYPE PIC X(2).
000380 05 F-LAND-LINE-VALUE PIC 9(9).
000390 05 F-FRONT PIC 9(5).
000400 05 F-DEPTH-XX PIC 9(5).
000410 05 F-DEPTH-FACT PIC 9V999.
000420 05 F-D-TAB PIC X.
000430 05 F-COND-FACT PIC 9V99.
000440 05 F-OTHER-ADJ PIC X(17).
000450 05 F-OTHER-ADJ-O11O REDEFINES F-OTHER-ADJ.
000460 10 F-SIGN1 PIC X.
000470 10 F-ROAD-FRONTAGE PIC 99.
000480 10 F-SIGN2 PIC X.
000490 10 F-TYP-ACCESS-OTHER PIC 99.
000500 10 F-SIGN3 PIC X.
000510 10 F-LOCATION-FACTOR PIC 99.
000520 10 F-SIGN4 PIC X.
000530 10 F-TOPO-FACTOR PIC 99.
000540 10 F-SIGN5 PIC X.
000550 10 F-SHAPE-FACTOR PIC 99.
000560 10 F-TYPE-RD PIC XX.
000570 05 F-LAND-NOTES PIC X(10).
000580 05 F-ADJ-UNIT-PRICE PIC 9(7)V999.


There is part of the copybook file starting with the FLATFILE-REC at the top and a Filler at the bottom. How do I go about changing this into a format file?|||Do you know what table(s) you need to put the data in?

Because I'm thinking this is 3 tables.

You would have a parent (the main master part) and 2 children.

1 For the Occurs, and another for the redefines..

And the more I think about it...I would set up a staging environment and use sql to so the work...

You can then also audit the data...

Got any low values?

Is this a 1 time thing *, or does it need to be repeated on a scheduled basis?

Do you know what DTS is?

Hell, I'd just bcp it in to a a table with 1 varchar(8000) column

How many rows are we talking about?

* Biggest lie in the industry|||Well, I was just going to place it into one table. This is definately a one time thing. I don't know what DTS is, and I am even surprised to be understanding any of this at all. I am pretty new to SQL/COBOL, but I am trying to learn. For the amount of rows, there are 38629 in total. The flatfile is around 100MB in size, and I have no chance on actually looking at the data through Excel or Access, but currently use KEDIT to view as a text file and to handle the size.|||We are talking about SQL Server correct?

Do you have SQL Server Client side tools installed?

Do you know what Enterprise manager or Query Analyzer is?

You're going to need them...

Do you have access to the sql server drives? Like can you map to a share?

You should put the file you want to import there.

And OK, you say 1 table...you can get away with the occurs part and have 6 repetitive columns...

But what about the redefines....can't do that unless you make everything char data|||REDEFINES allows you to reformat the same storage area.

OCCURS means that fields from line 330 through 580 will be repeated in the same order 6 times.|||Yes, I have all the tools via client side, and have an actual test server running on the same machine (So I don't fubar anything on the live server). 1 table was what my supervisor was looking for, but it seems like 3 would be a better solution. So what do I do from here?|||REDEFINES allows you to reformat the same storage area.

OCCURS means that fields from line 330 through 580 will be repeated in the same order 6 times.

holy sh-t...someone else knows COBOL?|||So... What do I do now?|||holy sh-t...someone else knows COBOL?Yes, and Algol, and Fortran, and Ratfor, and BAL, and...

-PatP|||We are talking about SQL Server correct?

Do you have SQL Server Client side tools installed?

Do you know what Enterprise manager or Query Analyzer is?

You're going to need them...

You need to answer the question....

BUT

I would create a table in Query Analyzer

CREATE TABLE myTable99(Col1 varchar(8000))
GO

I would ftp the file to the SQL Server server...please don't use IND$FILE 100 MG would take a awhile

I would then bcp the data in

EXEC master..xp_cmdshell 'bcp dbname.dbo.myTable99 IN c:\filelocation\filename -Sservername -Uuserid -Ppassword -c'

Then I would create a SELECT Statement using SUBSTRINGs

SELECT SUBSTRING(col1,1,32) AS F-MASTER-PARCEL-NUMBER
,ect for every column

I might even do conversion to the correct data types...

The I'd have a look

Then I would do a

SELECT...all your column defenitions
INTO myNewTable99
FROM myTable99

Which would create your final table

That's really how I would do it.

BUT!

I think most people would use DTS....in Enterprise manager...

It's a lot like importing data in to Access..but much more robust...|||Yes, and Algol, and Fortran, and Ratfor, and BAL, and...

-PatP

You left off Assembler and PL1...

What was that language we used to code on a commodore 64?|||I did leave off PL/1, even though I'm pretty fair at it (they still use it at the University!!!). You must have missed BAL.

I wrote Commodore Basic and 6502 Assembler on the Commodore, although I've always preferred the Apple ][.

-PatP|||There was that, and this

http://www.atarimagazines.com/compute/issue55/logo.php

And what'dya think...

Budster should install the clientside tools and dts the sucker in?|||I don't see 3 tables there, I see only 2 (where's blindman, he usually sees better than me ;))

Everything before OCCURS is your master table, and starting from OCCURS and including REDEFINES (you can redefine every field, its storage will not change) with the pointer to the master table would go to the child table.|||Yes, I have all the tools via client side, and have an actual test server running on the same machine (So I don't fubar anything on the live server). 1 table was what my supervisor was looking for, but it seems like 3 would be a better solution. So what do I do from here?

^^

I am in DTS import wizard, and now I just need help understanding how the cobol seperates the columns. For the flatfile, here is the first line (weird formatting with spacing and whatnot) Btw, I did not include the whole copybook.

0002 00059001 001001 1 06A5 1990010101 2020B5000000000054433 00000000000000000000000000000000000000410240000000 000000120A1 0001300000000606860AC000410237216000312507004074+0 4+00-20-10+00RP 0000676000 0000000000000000000 00000000000000000000000 000 0000000000 0000000000000000000 00000000000000000000000 000 0000000000 0000000000000000000 00000000000000000000000 000 0000000000 0000000000000000000 00000000000000000000000 000 0000000000 0000000000000000000 00000000000000000000000 000 0000000000 000000000 10001000 0000000000000 00000000000000000000000000000000000000000000000000 00000000000000000000000000000000000 00 000 00000000000000000000000000000000000000000 000000000000000000000000000000000000 190019000000 000000000000000000000000000000000000 190019000000 000000000000000000000000000000000000 190019000000 000000000000000000000000000000000000 190019000000 000000000000000000000000000000000000 190019000000 000000000000000000000000000000000000 190019000000 000000000000000000000000000000000000 190019000000 000000000000000000000000000000000000 190019000000 000000000000000000000000000000000000 190019000000 000000000000000000000000000000000000 190019000000014160258062001WDXV0003215000138706090 42001WDXV000918000013840508042001WDXV0000000000138 20238032001QCXV000000000013750273032001WDXV0051500 00WAS 760 AC MADE SPLITS IN 2001, REDUCED AC TO 606 SOLD 3 TIMES IN 2001 00000000000000000000000000 00000000000000000000000000 00000000000000000000000000 00000000000000000000000000 00000000000000000000000000 00000000000000000000000000 00000000000000000000000000 00000000000000000000000000 00000000000000000000000000 00000000000000000000000000 00000000000000000000000000 00000000000000000000000000 00000000000000000000000000 00000000000000000000000000 00000000000000000000000000 STRICKLAND THOMAS P D/B/A WAYSIDE RD 0004940000000000000000049400000000606860ACLL 32 59 62 227 LD16 606.86AC01600 00000494000000494000000000000000000000020020715RAI NTREE PROPERTIES 00 00|||Well...the data is fixed width format...

Just import the whole thing as 1 column and do what I mentioned...

Go to Data Transformation Services in Enterprise Manger, right click on the icon, choose all tasks, the choos import data.

Select the source data type as text...and follow the yellow brick road...

PS It's a wizard...

And rdjabarov, you don't think the redefines should be it's own table?|||OK, I have the whole file in a db now, but all as 1 column. What should I do now?|||You need to create a SELECT Statement that matches your layout...

First you need to determine the offset of each field so you can determine the starting position...

Actually make 3 SELECTS...make the first one up to the OCCURS columns...

What's the name of the table and what did you name the column?

Then I would create a SELECT Statement using SUBSTRINGs

SELECT SUBSTRING(col1,1,32) AS F-MASTER-PARCEL-NUMBER
,ect for every column

I might even do conversion to the correct data types...

The I'd have a look

Then I would do a

SELECT...all your column defenitions
INTO myNewTable99
FROM myTable99

Which would create your final table|||I was able to get the flatfile into a database using your suggestion, but now there is like wordwrapping going on. Also, Ive tried the SELECT statement, and it seems to work well on the first column, but how do I continue to do the rest of the columns?|||Word Wraping?

Where are you doing this? If it's in QA look at your settings...

And you need to count your offsets and use the starting position for each filed with the length as defined in your layout...|||OK, ya gotta work with me here...you reading all of this?

It's going to look like...

SELECT F-MASTER-PARCEL-NUMBER = SUBSTRING(Col1,1,23)
, F-CARD-NUM = SUBSTRING(Col1,24,3)
, F-NUM-CARDS = SUBSTRING(Col1,27,3)
, F-APPR-PIN-NUMBER = SUBSTRING(Col1,30,22)
, F-USER-FILLER-1 = SUBSTRING(Col1,52,8)
, F-E-CODE = SUBSTRING(Col1,60,1)
, F-USER-FILLER-2 = SUBSTRING(Col1,61,1)
, ect
FROM myTable99

Just open the txt file in Excel

The offsets are calculated like =SUM(C3+F3)-1|||OK. The query above works great! Thanks for all that. As for the wordwrapping, these are the steps I go through from DTS.

Choose Data Source -> Source = Text File

Select File Format -> Format = Fixed Field
File Type = ANSI
Skip Rows = 0
Row delimiter = {CR}{LF}

Now, right after that where it asks for the column positions, I get a preview of 3 lines. The first two are exactly what I want to see, yet the 3rd line looks like it has been wordwrapped. I have also tried setting "Row delimiter" to none and setting the Row Width either at 8000, or inbetween the black bars (which is what I am guessing represents a break, or enter in the data).|||Oh yah, can you explain the OCCURS and REPLACES again, being in different tables?|||Does each record have a unique key?

Id it does then for the occurs you would do

SELECT KeyName = SUBSTRING(Col1,n,m)
, OCCURS_FIELD_1 = SUBSTRING(Col1,n,m)
, OCCURS_FIELD_2 = SUBSTRING(Col1,n,m)
, Ect
UNION ALL
SELECT KeyName = SUBSTRING(Col1,n,m)
, OCCURS_FIELD_1 = SUBSTRING(Col1,n,m)
, OCCURS_FIELD_2 = SUBSTRING(Col1,n,m)
, Ect

Do that 6 time, 1 for each offset|||And I think I would forget about the redefines and just leave it as CHAR(17)

(mostly because it's be a pain...you'd have to check that the datatype of a column is numeric or not and the isolate the sets...still...)

for the occurs you need to have 6 union alls. I set it up so you can just add the offset

SELECT KEY-FIELDS = SUBSTRING(Col1,n,m)
--F-LAND-LINE OCCURS 6 TIMES.
, F-USE-CODE = SUBSTRING(Col1,151,4)
, F-ZONING = SUBSTRING(Col1,155,6)
, F-UNIT-PRICE = SUBSTRING(Col1,161,9)
, F-NUMBER-UNITS = SUBSTRING(Col1,170,10)
, F-LAND-LINE-TYPE = SUBSTRING(Col1,180,2)
, F-LAND-LINE-VALUE = SUBSTRING(Col1,182,9)
, F-FRONT = SUBSTRING(Col1,195,5)
, F-DEPTH-XX = SUBSTRING(Col1,196,5)
, F-DEPTH-FACT = SUBSTRING(Col1,201,4)
, F-D-TAB = SUBSTRING(Col1,205,1)
, F-COND-FACT = SUBSTRING(Col1,206,3)
, F-OTHER-ADJ = SUBSTRING(Col1,209,17)
, F-LAND-NOTES = SUBSTRING(Col1,225,10
, F-ADJ-UNIT-PRICE = SUBSTRING(Col1,235,10)
FROM myTable99
UNION ALL
SELECT KEY-FIELDS = SUBSTRING(Col1,n,m)
--F-LAND-LINE OCCURS 6 TIMES.
, F-USE-CODE = SUBSTRING(Col1,151+95,4)
, F-ZONING = SUBSTRING(Col1,155+95,6)
, F-UNIT-PRICE = SUBSTRING(Col1,161+95,9)
, F-NUMBER-UNITS = SUBSTRING(Col1,170+95,10)
, F-LAND-LINE-TYPE = SUBSTRING(Col1,180+95,2)
, F-LAND-LINE-VALUE = SUBSTRING(Col1,182+95,9)
, F-FRONT = SUBSTRING(Col1,195+95,5)
, F-DEPTH-XX = SUBSTRING(Col1,196+95,5)
, F-DEPTH-FACT = SUBSTRING(Col1,201+95,4)
, F-D-TAB = SUBSTRING(Col1,205+95,1)
, F-COND-FACT = SUBSTRING(Col1,206+95,3)
, F-OTHER-ADJ = SUBSTRING(Col1,209+95,17)
, F-LAND-NOTES = SUBSTRING(Col1,225+95,10
, F-ADJ-UNIT-PRICE = SUBSTRING(Col1,235+95,10)
FROM myTable99
UNION ALL
SELECT KEY-FIELDS = SUBSTRING(Col1,n,m)
--F-LAND-LINE OCCURS 6 TIMES.
, F-USE-CODE = SUBSTRING(Col1,151+190,4)
, F-ZONING = SUBSTRING(Col1,155+190,6)
, F-UNIT-PRICE = SUBSTRING(Col1,161+190,9)
, F-NUMBER-UNITS = SUBSTRING(Col1,170+190,10)
, F-LAND-LINE-TYPE = SUBSTRING(Col1,180+190,2)
, F-LAND-LINE-VALUE = SUBSTRING(Col1,182+190,9)
, F-FRONT = SUBSTRING(Col1,195+190,5)
, F-DEPTH-XX = SUBSTRING(Col1,196+190,5)
, F-DEPTH-FACT = SUBSTRING(Col1,201+190,4)
, F-D-TAB = SUBSTRING(Col1,205+190,1)
, F-COND-FACT = SUBSTRING(Col1,206+190,3)
, F-OTHER-ADJ = SUBSTRING(Col1,209+190,17)
, F-LAND-NOTES = SUBSTRING(Col1,225+190,10
, F-ADJ-UNIT-PRICE = SUBSTRING(Col1,235+190,10)
FROM myTable99
--ect

I gotta start charging soon...sql

Flatfile Destination Variable Filename

Why does the raw file have an option for a variable path and the flat file destination does not? Not having this feature makes it impossible to work with variable environments. Please add this option to the Flatfile Destination.You can use expressions on the flat file destination to change the location of the file.|||Thank you so much for your quick reply. I have the flat file destination highlighted but there are no properties for Expressions. I also looked in the connection properties also and could not locate a place to do this. I ended up writing a vb script but I would like to have the ability to do it graphically. The SSIS team definately needs to set up a way to set Global Variables (which could contain file path settings) via a sql statement. They also need variable file pathes exactly like the raw file.|||When you right-click on the flat file connection manager, you can select properties. In there is an Expressions property. You'll want to set the ConnectionString property to the full path containing the file.|||Oh awesome! Thanks Phil.

FlatFile connection and security

My DTS package, deployed and run from the file system, works just fine for me, but fails when someone else runs it. The only explicit error from the dtexec command is:

Error: 2005-06-24 12:35:03.33
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
End Error

This error just started when we tried to point to a file on a network server instead of a file local to the machine running the package. The other person definitely has access rights to that network server, but the package fails anyway.

One possible solution might be to specify a username/password for the file, but I don't see how to include that in the connection string. Alternatively, this may be a more general security issue with the whole package. I first turned to BOL for some explanations and answers, but wasn't able to find anything specific to my problem.

Any help or suggestions will be greatly appreciated. Thanks!
Phil
This is because the default protection level is to encrypt with a user key. That means only a user on his/her machine can open the package.

You probably want to use Package Password here instead.|||

I am getting a similar error on a DTS package created by me and migrated by me and executed by me via Dtexec. Shouldn't ssql 2000 ownership carry over to 2005? The package is not a flat file. And, by the way, where does the "DTS Property" come in?

thanks

Ted

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
NULL
Started: 9:24:56 AM
Error: 2006-02-08 09:24:56.50
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that
the correct key is available.
End Error
Error: 2006-02-08 09:24:56.51
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that
the correct key is available.
End Error

|||

SSIS is a completely new product so I don't think it follows that DTS behaviour should make it into SSIS. Furthermore, there's been a very high profile tightening up of security within Microsoft products and the behaviour you're seeing here is probably as a result of that.

The reason you see references to "DTS" in various places is for legacy reasons. Alot of the product had already been built before the name change came about.

-Jamie

sql

FlatFile connection and security

My DTS package, deployed and run from the file system, works just fine for me, but fails when someone else runs it. The only explicit error from the dtexec command is:

Error: 2005-06-24 12:35:03.33
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
End Error

This error just started when we tried to point to a file on a network server instead of a file local to the machine running the package. The other person definitely has access rights to that network server, but the package fails anyway.

One possible solution might be to specify a username/password for the file, but I don't see how to include that in the connection string. Alternatively, this may be a more general security issue with the whole package. I first turned to BOL for some explanations and answers, but wasn't able to find anything specific to my problem.

Any help or suggestions will be greatly appreciated. Thanks!
PhilThis is because the default protection level is to encrypt with a user key. That means only a user on his/her machine can open the package.

You probably want to use Package Password here instead.|||

I am getting a similar error on a DTS package created by me and migrated by me and executed by me via Dtexec. Shouldn't ssql 2000 ownership carry over to 2005? The package is not a flat file. And, by the way, where does the "DTS Property" come in?

thanks

Ted

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
NULL
Started: 9:24:56 AM
Error: 2006-02-08 09:24:56.50
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that
the correct key is available.
End Error
Error: 2006-02-08 09:24:56.51
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that
the correct key is available.
End Error

|||

SSIS is a completely new product so I don't think it follows that DTS behaviour should make it into SSIS. Furthermore, there's been a very high profile tightening up of security within Microsoft products and the behaviour you're seeing here is probably as a result of that.

The reason you see references to "DTS" in various places is for legacy reasons. Alot of the product had already been built before the name change came about.

-Jamie

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 - row delimiter problem

Hi,

I'm trying to design this package where i take data from a source and need to transform it into a flatfile with some extra static information.

I use a SQL script like this (ex.):

SELECT '

BS0220131264202400000130001'+cast(wa.perf_applicant_number as nvarchar)+'000000000' + wa.perf_firstname + ' ' + wa.perf_lastname + CHAR(13)+

'BS0220131264202400000330001'+REPLICATE('0',(15-LEN(wa.perf_applicant_number)))+cast(wa.perf_applicant_number as nvarchar)+'000000000' + WAPD2.strvalue+ '

BS0520131264202410001130001'+REPLICATE('0',(15-LEN(wa.perf_applicant_number)))+cast(wa.perf_applicant_number as nvarchar)+'000000000 tekst der skal st? p? kortet' as nvarchar

FROM dbo.WAIT_Applicant WA (nolock)

This makes the text (from one record) split up over several lines in the output.

I succeded with this in a SQL2000 DTS package and the flat txt-file looked liked I wan't it to. But now i tried doing it in 2005. And now it is not workin' anymore

In my Flat File Connection Manager Editor i chose {LF} as the row delimiter and the preview looks really nice. Like this:

BS0220131264202400000130001000000015826727000000000S?ren Hesth

BS0220131264202400000330001000000015826727000000000adfasdf

BS0520131264202410001130001000000015827207000000000 tekst der skal st? p? kortet

But in the file that is created it doesn't split up over several lines. Instead of a carriage return it puts a [black box] - a sign which counts as the carriage return.

I don't know if I have explained this well enough, but I hope that someone can help me. I've been trying for 3 days now.

I'm guessing you want the Flat File to output records on individual lines (if you opened the file in notepad). If that is the case use {cr}{lf} as your row delimiter, since in a Windows environment that is the standard newline character combination. As single {lf} is often employed in Unix/Mainframe environments as a newline character, which is why it is an option.
Larry Pope
|||

That was also what i started with, but then i read in another discussion inhere, where they suggested to use {LF}, so i changed it.

What i want is, that one record is printed over several lines in the text-file. After that record, then the next record is printed, also over several lines in the text-file.

I have tried to change it back and tried almost every possible combination of

- rowdelimiter ( CRLF, CR, LF...)

- format (ragged right, delimied...)

and so on.

And nothing works.

Is there any other way of doing this. Maybe there is something I can do in the script.

|||If what you want is something like the following (Assume #is a comment line and doesn't exist in the file).
#Record1
Column1Column2
Column3Column4
Column5Column6
#Record2
Column1Column2
Column3Column4
Column5Column6
...
If that is what you want, then you will need to build both a custom component either through a script transform or a full-fledged component.
The code would be something similar to the following
Dim sw As New System.IO.StreamWriter("c:\temp\test.txt", True)
sw.WriteLine(Row.Column1.ToString & Row.Column2.ToString)
sw.WriteLine(Row.Column3.ToString & Row.Column4.ToString)
sw.WriteLine(Row.Column5.ToString & Row.Column6.ToString)
sw.Close()
This will append to an existing file, so if you may need to create a task that deletes the existing file prior to the data flow task. You'll also should check for errors (null values, stream writer was created, etc).
Larry Pope
|||

But i succeded with doing this with my the package i wrote in SQL server 2000.

There must be a way that I can make a carriage return, so that the notepad will read it correctly.