Migrate Kinetic Attachments

ECM
1

We have a number of attachments that are stored either as filesystem links or SharePoint documents. I would like to mass-migrate these attachments to ECM. Is there a tool or process available to do this?

I’m sure Epicor Professional Services could do it for a fee, but hoping I could do it myself.

2 Likes
2

Well if you could wait a couple months, I’ll show you.
(Probably gotta do the same thing lol)

1 Like
3

It’s actually quite easy if you can write some SQL. Use SQL to generate two files - once that matches the DMT Attachments format and is used/set to Delete attachments.

The second file is a CSV that includes all the metadata needed for ECM to import and run a simple Workflow that will attach the document back to Kinetic. I’ve done this plenty of times. Easiest way is to pull the XfileRef and XfileAtach tables together (along with whatever source tables you need to get other metadata) and include the path to the filestore(d) file and GUID to the source transaction line it should be attached to.

Take a look at those tables and your files, but the SQL looks something like this. NOTE - this uses a custom function and is for my Sharepoint attached files, not filesystem, but the gist is clear enough.

select fa.RelatedToFile
	, [Content Type] = concat('ORDER','_', c.company)
	, Ordernum = fa.key1
	, CustID = c.custid
	, Customer = c.name
	, c.custnum
	, [_TableName] = 'Orderhed'
	, [_TableSysRowID] = c.SysRowID
	, [_EpicorDocTypeID] = 'Order'
	, [Schema] = 'ERP'
	, Company = c.company
	, Title = case when left(xfilename,4)='http' then dbo.ashgetfilenamefromurl(xfilename) else  dbo.ashgetfilenamefrompath(xfilename) end
	, ashworthutility.dbo.fn_FileExists(dbo.AshConvertAttachmentPath_SP(xfilename))
	, [FileName] = dbo.AshConvertAttachmentPath_SP(xfilename)
	, XFileName
	, [_Comments] = 'Imported from Epicor'
	, [ReplacedXfileRefNum] = fa.XFileRefNum
	--,*
	--, FileExists = ashworthutility.dbo.fn_FileExists(dbo.AshConvertAttachmentPath_SP(xfilename))
	--, fr.DocTypeID,  fa.key2, fa.key5, cr.key1, cr.Key2, xfilename, xfiledesc, fr.DocTypeID, dt.Description, dt.StorageType
from ice.XFileAttch fa join ice.XFileRef fr on fa.XFileRefNum=fr.XFileRefNum and fa.Company=fr.Company
	left join erp.OrderHed oh on fa.ForeignSysRowID=oh.SysRowID and oh.company=fa.Company
	left join erp.customer c on c.company=oh.company and c.custnum=oh.custnum
	left join ice.DocType dt on fr.DocTypeID=dt.DocTypeID and fr.Company=dt.Company
where xfilename like 'http://ashepicsps/200/Orderhed/%' 
	--and fa.relatedToFile='customer' 
		--and ashworthutility.dbo.fn_FileExists(dbo.AshConvertAttachmentPath_SP(xfilename))=1
order by fa.key1
2 Likes
4

The timing on this is mystical… Conversation happened in a meeting yesterday. Thanks Mike!
And Andrew for the inspiring thread!

1 Like
5

I did something similar, just using an update able BAQ and a post process BPM on GetList. But principle is the same I extract the key data into 2 CSV files, one to upload to ECM and one to delete the original using DMT. And I copy the files so then I can re-process back into ECM.

In theory it could all be done within the BPM using the business objects, I just never got there.

1 Like
6

Yeah, I started there, but I think that would be better for a “one at a time” approach, or at least “all docs attached to this transaction” kind of thing. Doing this for 25k documents is much better done in DMT batches.

7

Agreed, I restricted the results from the BAQ to run this in batches anyway.

The multi stage approach using CSV files and DMT etc. does somehow feel safer :smiley:

1 Like