Ok guys. So.. here is what has happened so far... I decided to try something.. so at test(destination) I used the "generate script wizard"... generated a "drop and create" statement so I could drop all constraints.. and then I could import from prod using the "impoprt/export wizard" and after that created the constraints and that has worked so far for 3DBs
but for one particular db, and its the last, I kept getting errors, and so I did the import in batches to reduce the workload, all succeded but this one table.. and when I try to import just that one table I get
Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Source - attachments_blob returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure
Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Memory allocation failure".
(SQL Server Import and Export Wizard)
maybe my google-fu is weak, but I haven't been able to narrow down an answer for this one.