Importing from Flat File via SSIS to ArcSDE

[JeffersonWest]

ArcSDE 9.3.1 - three tier for admin, two tier for user access
SQLServer 2008 64-bit

I'm trying to schedule a weekly batch job that pulls a flat file via FTP from a third-party and loads it into an SDE layer.  The layer is registered with USER maintained RowID column defined as INT NOT NULL IDENTITY.  Registration is handled with this command:

sdelayer -o register -l onecalls,shape -e np -C onecalls_id,USER -i devdb -u xxxx -p xxxx -x -180,-90,1000000 -G 4326 -t GEOMETRY

Data gets pulled via FTP fine, SSIS is used to read resulting flat file and load data into a table.  Two of the columns are Latitude and Longitude, and last step in the SSIS import is to generate point geometry from these two columns and write to Shape column (of type geometry) using STPointFromText().  Entity type of the layer is 'np'.

When viewed in ArcMap the layer looks fine, and zoom/pan works great.  If I open the attribute table I am able to view all of the data very nicely.  However, if I try to select any of the features, whether by clicking in the Attribute table, selecting with a rectangle, or by SQL query, ArcMap 'encounters a serious error' and goes away.  I suspect problems with RowID, but not sure what would be the issue.

Any ideas appreciated.

[VinceAngelo]

Are the user-set ids in fact unique? Even when the user-set IDs are not unique, it doesn't
usually crash ArcMap (stuff just goes missing).

Have you applied SQL-Server 2008 SP1 or SP2 or R2 SP1?

Are any of the shapes west of -180W? Best practice is to give a buffer off the lower-left
corner in the coordref X/Y origin (I use '-x -210,-120,1000000'), but even that shouldn't
fatal the application (queries just terminate early with a "load buffer" error).

Since you have ArcSDE installed, the 'asc2sde' utility is available for scheduled loading
of ASCII flat files into layers (in lieu of SSIS).  Also in se_toolkit is the 'sdequery'  utility,
which can be used to validate two-tier and three-tier spatial queries directly with the
ArcSDE API.

- V

[JeffersonWest]

Thanks for the ideas Vince.

User IDs are indeed unique.  My test data is only a few hundred rows so it's easy to see the Identity is working as it should.  I have seen things go missing when IDs aren't unique and that is what I expected would happen if the IDs weren't being managed properly, but given I could see the features OK, I couldn't figure out what else would cause the fatal just when doing a select.

I'm pretty sure I've got SS 2008 SP1, but I've asked my DBA to see about getting upgraded to R2 SP1.  My ArcSDE is at 9.3.1 SP1 as well, so I'll get that up to SP2 and see if the combination of upgrades makes anything smoother.

Coordinates are all well within the defined space, with a few whacky rows where the lat/long is null in the data so I default to 0,0.  Your admonition on origin offsets is a good one though, so I'll be sure to include that as well.

I do a few data type conversions and conditional statements using SSIS, so I'm not sure if asc2sde will do all I need it to or not, but I'll give it a try.  I've seen the SE_Toolkit mentioned before in the forums, but never tried it out, so now seems like as good a time as any for giving it some love.

Thanks again for the quick response.

[VinceAngelo]

The ASCII tools have fairly robust type conversion tools.  I'm working on a mathematical
expression processor for math evaluation and a true "Con" operator to allow inline
conditional operation (right now you'd need to reject to a named reject file, then use
a different control file).

- V

[GIS-Cambria]

This is just a simple thought: have you registered the table with the geodatabase. I'm not sure if registering with SDE actually registers with the GDB also.

http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/ArcSDE_and_Microsoft_spatial_types/002...

If you need the feature class to participate in geodatabase functionality, such as relationship classes, topology, geometric networks, cadastral fabrics, terrains, or schemas, or have subtypes, default values, domains, or validation rules, it must also be registered with the geodatabase.
You can register the datasets in ArcCatalog as follows:
Start ArcCatalog.
Connect to the geodatabase that contains the layer to be registered. Be sure to connect as the layer owner.
Right-click the layer you want to register with the geodatabase.
Click Register with Geodatabase.

AND:

http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/Registering_a_third_party_table_contai...

AND:
http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/A_quick_tour_of_registering_tables_wit...

This may not be the case with your issue, but SDE is a fickle beast. I've run into problems where using an unregistered, non-spatial table responded in a similar manner. It would work until I tried to select. One way I got around this was to create a query layer from the table. This is a feature of AGIS10.

[JeffersonWest]

Finally got around to playing with this again.  According to Tech Support my original problem is due to a known issue in ArcSDE.  ArcSDE does not play well when the business table has a pre-defined USER maintained RowID and you add a row manually.  I'm guessing it causes the iTable to get out of sync, among other things.  So I've been messing around with se_toolkit in hopes of avoiding writing my own C routine to do this import.  I think I can do what I need but I was hoping somebody, maybe Vince, would have some insights.  I've a few questions:

1)  Given I am looking to append to an existing SDE layer, is asc2sde or sdeupdate more appropriate?
2)  How do I handle columns that are not present in my incoming flat file, but that have defaults set up in my table definition, i.e. CreatedTimeStamp defaults to CURRENT_TIMESTAMP when a record gets added, but the flat file itself has no relationship to this column?
3)  Within the COLUMNS section of a control file, there seems to be a five section structure, the first section of which is the column name, the second is data type or calculated value, what is the third section?  It is a dash (-) in the samples, but in the old forums I saw an example with values in this section.
4)  What am I missing if I get an error saying "Target table does not have 'XX' column", when in fact the target table does have 'XX' column?  I've listed all columns in the table in the control file.

Here's some sample data, with a bit of redaction:

MI,XXXXX,MIB1295XXXX,WAYNE,DETROIT /C,TORONTO ST,25,ARTURO GOMEZ,20111023,REMV/REPLC CURB & SDWLK,CLR,PDEGT,42.2770400,-83.1521100,42.2770400,-83.1521100
NJ,XXXX,NJ11296XXXX,UNION,LINDEN,4001 S WOOD AVE,5,NATALIE SOUSA,20111023,EMERGENCY - REPAIR/REPLACE WATER FACILITY,CLR,PDRMW,,,,

Here's the text of my control file:

SETENV  TZ="UTC"
SKIP  1
DELIMITERS ","

COORDSYS GCS_WGS_1984
COORDREF_XY -210,-120,1000000
EFLAGS  "np"

COLUMNS
OneCalls_ID Sequence()   - 10 N
StateCode String    - 2  N
MemberCode String    - 10  N
TicketNum String    - 15  N
County  String    - 30 Y
Place  String    - 30 Y
StreetAddress String    - 50 Y
TicketType String    - 5 Y
Requestor String    - 50 Y
CompletionDate Date("%Y%M%D")   - 8 Y
WorkType String    - 250 Y
Disposition String    - 10 Y
Locator  String    - 50 Y
BeginLatitude Latitude(DD)   - 12.7 Y
BeginLongitude Longitude(DD)   - 12.7 Y
EndLatitude Latitude(DD)   - 12.7 Y
EndLongitude Longitude(DD)   - 12.7 Y
Create_TS Date("CURRENT_TIMESTAMP") - 20 N
shape  GeoPoint(BeginLatitude,BeginLongitude) - 1 Y
END

Many thanks.

Jeff

[VinceAngelo]

1) Append should be 'asc2sde -o append' ('sdeupdate' will alter existing rows).

2) You might have an issue with NOT NULL columns that automatically populate with a default
value at the database (there are checks to make sure that all NOT NULL columns in a row are
in the INSERT stream). Of course, with timestamp compute columns, the loader can populate
those values, too. Constants can be assigned via AsString("foo") or AsInt32(12345) compute
columns.

3) The dash is a place-holder for delimited text (it contains the start position with fixed format).

4) I'd need to see the 'sdetable -o describe' output for the target table for the "does not have"
messages (make sure you're using setk93b41 for maximum functionality). Be careful of escape
sequences in the control file. I loathe comma-delimited input because of the frequency of commas
in user data.

The 'Date("CURRENT_TIMESTAMP")' is probably going to generate null values if the import string
isn't "CURRENT_TIMESTAMP" -- use GMTIME() to generate "now" values, StartTime(GMTIME) for
the time at the start of load, or set a date into an environment variable before load, and extract it
with GETENV(), then parse that with each row for a single date value for all rows in the load.

- V

[JeffersonWest]

Thanks Vince.

I figured out the missing column was because my column was defined as 'date' not 'datetime'.  Now when I execute asc2sde, I get no errors, but I get no data.  I assume that means something is still not quite right with my CTL file. 

Here is output from sdetable -o describe:

Column name             Attribute type   Null?      Length,DPs    RowID Column?
-------------------------------------------------------------------------------
OneCalls_ID             SE_INT32         NOT NULL       10        SDE Set
StateCode               SE_STRING        NOT NULL        2
MemberCode              SE_STRING        NOT NULL       10
TicketNum               SE_STRING        NOT NULL       15
County                  SE_STRING        NULL           30
Place                   SE_STRING        NULL           30
StreetAddress           SE_STRING        NULL           50
TicketType              SE_STRING        NULL            5
Requestor               SE_STRING        NULL           50
CompletionDate          SE_DATE          NULL            0
WorkType                SE_STRING        NULL          250
Disposition             SE_STRING        NULL           10
Locator                 SE_STRING        NULL           50
BeginLatitude           SE_FLOAT64       NULL           12,7
BeginLongitude          SE_FLOAT64       NULL           12,7
EndLatitude             SE_FLOAT64       NULL           12,7
EndLongitude            SE_FLOAT64       NULL           12,7
Create_TS               SE_DATE          NOT NULL        0
shape                   SE_SHAPE         NULL            0

Here's the new CTL file:

SETENV  TZ="UTC"
SKIP  1
DELIMITERS ","

COORDSYS GCS_WGS_1984
COORDREF_XY -210,-120,1000000
EFLAGS  "np"

COLUMNS
OneCalls_ID Sequence()   - 10 N
StateCode String    - 2  N
MemberCode String    - 10  N
TicketNum String    - 15  N
County  String    - 30 Y
Place  String    - 30 Y
StreetAddress String    - 50 Y
TicketType String    - 5 Y
Requestor String    - 50 Y
CompletionDate Date("%Y%M%D")   - 20 Y
WorkType String    - 250 Y
Disposition String    - 10 Y
Locator  String    - 50 Y
BeginLatitude Latitude(DD)   - 12.7 Y
BeginLongitude Longitude(DD)   - 12.7 Y
EndLatitude Latitude(DD)   - 12.7 Y
EndLongitude Longitude(DD)   - 12.7 Y
Create_TS GMTIME()    - 20 N
shape  GeoPoint(BeginLatitude,BeginLongitude) - 1 Y
END

Here's my asc2sde input string:

asc2sde -o append -f f:\folders\data.csv -C f:\folders\onecalls.ctl -i servicename -u username-p password -v -l onecalls_test,shape

Jeff

[VinceAngelo]

What output do you get from the loader?

Have you tried 'ascinfo -o scan -v -f ...' on the file?

- V

PS: The 'SETENV TZ="UTC"' works on Unix, but isn't reliable on Windows; using a PC, it's
best to put a "set TZ=UTC" before asc2sde in a .BAT.

[JeffersonWest]

When I execute asc2sde it pauses a moment, and then just brings up a new prompt with no messages of any kind.  ascinfo -o scan -v doesn't provide any output either.  I tried -r, but nothing gets dumped into the reject file.