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.

  • "The job" this is running from a SQL Agent job or is that just a generic term for "when the package is running" How long does it remain "stuck"? How do you know it's stuck? What are the data types in your source? – billinkc Apr 25 '17 at 20:00
  • updated made to original question. – Sarge Apr 25 '17 at 22:10
  • What will happen if you do not save it and run from the wizard? Other option is to add an output file for you job steps to see what is going on just before it is stuck? – SqlWorldWide Apr 25 '17 at 22:16
  • How do you know no progress is made (not saying you're wrong, just want to know what the evidence is)? Does anything show up in your flat file? If you need to be able to follow exactly what is (or isn't) happening, you should be able to create an Integration Services project in Visual Studio, import the saved package, and run it in debug mode - that will let you see how many rows are being consumed, etc. – RDFozz Apr 25 '17 at 22:56
  • Test it using a smaller number of rows? Any Anti-virus running? Is the file being written to local or on a network share? – Jonathan Fite Apr 26 '17 at 1:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.