I created a database view with the desired data and format. I used SQL Server Import and Export wizard to create a flat file export procedure and saved it to SSIS package.
The data exports correctly but no matter what I do the SQL agent job eventually gets stuck without an error at around 3000-3500 row mark (348kb size file). Nothing I have tried gets around this issue. Unfortunately, I have to ingest this data via CSV file and have no other option. I have about 100k in rows.
The datatype is mostly VARCHAR. I know it's stuck because when the job is left to run overnight no progress is made.
Any thoughts? One recommendation was to try to build SSIS package in Visual Studio to maybe get different results.