• Jeff Moden (2/16/2014)


    sqldriver (2/15/2014)


    I really hate SSIS and was hoping to avoid it. I guess I could try doing this all in another language that handles large file operations better. Of course, since these are all going to garbage staging tables, I could always generate dynamic import statements with extra variables. Hm.

    Who cares about filling up transaction logs, anyway?! :w00t:

    Heh... I'm right there with you on the subject of SSIS especially if all of these files have the same basic format. Even if they're different by report type, it's still not difficult to do with the likes of BULK INSERT written as a bit of dynamic SQL. You can use xp_Dirtree 'drive-or-unc-path',1,1 to get a list of files and store them in a table.

    There's just no need for PoSH for this type of stuff and BULK INSERT is nasty fast.

    How many different report codes do you have and how many files do you have for each report code? Is is safe to assume that each file that has the same report code will all be in the same format?

    There are six report types for ~75 locations. All of the report types have the same layout, which makes this easier. The only issue is that the headers have wonky characters in them that I'm going to have to deal with at some point before loading. I think doing that bit of pre-processing with PS should be fine; it's just one line, even if it is ~75 times.

    The thing that makes this situation a little weird is that the instance I'm loading them to lives on AWS :doze: which is why I started down the PS road in the first place: it has a cmdlet to upload files right to S3. It seemed like an awesome idea to append all the files together with some bells and whistles to skip lines and select only one line and etc. and so forth rather than load each file to a staging table and then run a larger insert to a combined staging table for each report type.

    I wrote a poem about my experience:

    Here I sit, broken hearted

    Ran my PowerShell script

    Hours later, had to abort it