Handling Flowfields in SSIS/DTS

AK

Hi,

how is your approach to handle flowfields when filling tables using Integration Services when copying records from one database to another? The package I created complains that the flowfields can't be assigned with NULL, so I'm in the process of using the derived column tool. But the table I have to handle has a lot of flowfields, so this is quite elaborate. Is there a better way?

Thanks!

kennie

It is not supported or recommended to use direct SQL (or SSIS with table destinations) for copying data. You could use script tasks in SSIS to do the data copying using web services instead

Slawek_Guzek

Flowfields are fields calculated by NAV on the fly. They do not exist at the SQL level, so your SSIS data adapter cannot see them.

There are a few options oh handling them:

1) you need to know how they are calculated and you need to duplicate the calculation in your SSIS code. Each of the flowfields individually

2) you can get the data from NAV through the web services interface and rely on NAV calculating all of the data for you. This usually requires building in NAV pages and exposing them as a web service, usually one for each table you wand the date from

3) you can build a set of XMLports in NAV, one XMLPort for one table, or combined XML port for few tables, and export the data into files, and then load the data in SSIS from generated files. The files can be flat CSV files or XML files

EDIT

Sorry didn't see the purpose of your question in the first place

The above is not applicable when you copy data from one database to another. You do not copy flowfields in that case, but you have to copy tables which are used to calculate the flowfield values.

Slawek

AK

It is common practice to copy data into a dummy table that has no business logic and use NAV code to validate the real table with the data from the dummy table. No problems there. My question remains.

Slawek_Guzek

Yes, but it does not apply to flowfields. Flowfields are results of some lookups/sums/counts, not the source data requiring validation

AK

Thanks Slavek, I know that flowfields aren't reals fields in SQL server, but let me elaborate:
For Example I may want to copy the item table into a dummy table and process it later via NAS. The dummy table is a copy of the item table, but all flowfields are converted to normal fields. If they are not mapped in DTS, they would get assigned with NULL, which is not allowed.
I could alter the table to allow NULL, but I want to avoid that, so I have to assign a empty value to each of those fields manually by creating derived columns. The fields don't need to have any value, they just don't have to be NULL. If there are a lot of flowfields, this is labor intensive.

Web Service and files don't work in my case, because they aren't fast enough. SSIS is way faster when it comes to handle a couple of hundred thousand records.

Slawek_Guzek

Before you go that way I'd like to point out that @kennie is right. It is not supported or recommended using SQL to manipulate the data.

You will probably hit the problem with NST data caching. It manifest itself in strange way - when looking at the table updated by the SQL query you may not see the updates. Unless you restart the NST, or clear the buffers


The simplest soultion would be to remove in your destination temp tables fields
corresponding to flowfields. Leave only the 'real' fields. The data in the "flowfields" will be useless anyway, it will only take some storage space.

If you cannot do that then you need to fill up the values with NAV 'nulls', Almost each NAV 'null' is actually some value. For NAV text/code fields it is simple an empty string, for NAV decimals/integers/options it is zero value.

The most tricky and frequently used are NAV date, time, datetime

They are all represented as a datetime in the SQL table, and the null value for them is a specific date value of 1753-01-01 00:00

AK

For bypassing the cache, SELECTLATESTVERION does the trick, doesn't it?

Assigning the NAV 'nulls' is what I do with derived columns right now, but again this is a PITA.
You're right with getting rid of the former flowfields in the dummy table, but that would create some additional work when creating the dummy tables I would like to avoid, so I'm looking for an easier solution in DTS.

Slawek_Guzek

Yes, SELECTLATESTVERION clears the session buffers/cache.

It is your call but I would be rather opting for a bit more work when creating temp tables in NAV.

In NAV it is a simple operation - open a table in designer, remove all the flowfields, remove all the code (easy two keystroke operation when using old editor in DEV Env), save as new table. Job done.

In SSIS / DTC you would need to define a default for each field non existing in the source table. And if you have a few hundred thousand records and a few flowfields you are saving 2 bytes of the storage on every text/code flowfield, 4 bytes on every option/integer, 8 bytes on every decimal / date/ time / datetime. If you have many flowfields in a long table it is really worth the effort, especially you are after fast copy operation

David_Singleton

kennie wrote: »

It is not supported or recommended to use direct SQL (or SSIS with table destinations) for copying data. You could use script tasks in SSIS to do the data copying using web services instead

Kennie, I understand that this is not supported, but why is in not recommended? Maybe I am not understanding exactly what you are saying, but I have been directly modifying NAV data directly from SQL for at least 15 years now (I think the first time I did it was in a NAV 3.60 database where we reduced the go live process of importing historical data from many days of processing to a couple of hours). Of course you need to know what you are doing, but so long as you do it right it's not a problem.

AK

Slawek_Guzek wrote: »

Yes, SELECTLATESTVERION clears the session buffers/cache.
In NAV it is a simple operation - open a table in designer, remove all the flowfields, remove all the code (easy two keystroke operation when using old editor in DEV Env), save as new table. Job done.

In a static environment this is absolutely right, but in our scenario the tables to copy can change, so I'm planning to create the temp tables on the fly. At this point it seems changing the field type might be easier.
However, I'm in the conceptual phase and I might indeed switch to delete the flowfields.

Thanks for the input about size, I hadn't thought of that.

But back to topic, is there a easier way to define empty defaults for fields that aren't copied?

Slawek_Guzek

AK wrote: »

... I'm planning to create the temp tables on the fly.

You mean in the code? C/AL code?

How are you going to create a temp table in NAV on the fly?

If they are not supposed to be created in NAV how are you going to make them visible in NAV

Slawek_Guzek

AK wrote: »

is there a easier way to define empty defaults for fields that aren't copied?

No. You have to know the NAV field type to fill empty values correctly. There is no way to extract this information from the database at the SQL table level.