May 23, 2012 at 11:05 am
I have an SSIS package that currently loads X number of records into a text file and then emails the file off to a recipient. Now, I need to break that file up into equal-sized increments. No more than 500 records are allowed per file. I have two issues, one easier to resolve than the other.
The first issue is that the record counts aren't always the same, so the number of additional files to be added won't always be the same. I have an idea of how to deal with that (Got my idea from this link: http://consultingblogs.emc.com/jamiethomson/archive/2005/12/04/SSIS-Nugget_3A00_-Splitting-a-file-into-multiple-files.aspx).
The second issue is attaching the files to an email. This is not a situation where I can zip the files up and attach them to the email. I have to add each file individually to the email. Which wouldn't be a problem if there were always the same number of files, but there won't be. The file count could be between 1 and N (though I've never had more than 7 files realistically).
Does anyone have any suggestions (or comments on the above link) to offer for my situation? As in Transformation Task ideas, etc.?
May 23, 2012 at 11:43 am
Start with an SQL task to generate a dataset with a list of the file names, and the range of records for each. Easy to do with a basic Numbers table.
Loop through that, creating a text file and exporting to it, in a For Each Loop on the ADO recordset. The start and end record values, from the initial query, are fed into the export query as input parameters used in the Where clause.
Concatenate a variable with the list of filenames separated by pipes, as usual, and assign that to the attachments either by expression or variable.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 23, 2012 at 11:46 am
Ah. Pipes. I didn't even think about that.
Most of what you said is what I've been thinking, but you said two things that hadn't yet occurred to me. Thanks.
May 25, 2012 at 6:17 am
Glad I could help.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 25, 2012 at 9:10 am
Okay, so the Recordset Destination is giving me fits. I've never used it before and am having trouble figuring out how to use it as a source when I go to loop through the records. I am interested in hearing thoughts in how that works.
I do, however, have a workaround. I'm using Raw File Destinations as recommended in the link I found. Instead of using a Script Task Transformation to get Top X rows, I'm using the Row Sampling Transformation which works a treat! And I can still use the code from the existing Script Component Destination that puts in 3 different rows per record (of varying formats) without rewriting that part of my package. (Less rewriting = Good Thing).
I did find out that using the Raw File Destination / Source was a bit of a stumper. It didn't like reading from and writing to the same file names. Go figure. @=) So I had to create a Source and Destination variable for each of them and then let them take on the name of each file # I was going to add, so I ended up with multiple raw files left over in my directory. I think I shall build some cleanup so they don't remain behind after the package is done executing.
Things look good so far. I'll give updates as they occur.
May 25, 2012 at 11:59 am
Wow. I love it when I finally figure out something that's been driving me nuts all day.
I kept getting an extra empty file out of the Raw File Destination when I was running my FOR Loop because the file would get created BEFORE the loop would recognize there were zero records left. The addition of a Conditional Split right after the Row Count, but right before the Raw File Destination enabled me to check the record count and if it was greater than zero, send it to the Raw File Destination.
The other Conditional Split output has no destination. (Can't use a Trash Destination because I can only use official MS Software or stuff we code ourselves).
So, no more "extra" Raw File in my folder.
Now I just have to figure out the cleanup and I think I've got the whole thing solved.
I may have to write another article, if I have the time.
EDIT: Damn. Spoke too soon.
May 29, 2012 at 8:40 am
For future reference:
On my initial stored procedure (in the first Data Flow Task), I added a column using Row_Number() to get record "identities" then used a Row Count Transformation when storing the records into a Raw File Destination.
Then I created a FOR Loop to create my individual files using the Row Count variable as the Eval Expression. Within the Loop, I had my Data Flow to create the files, then an Execute SQL Task to append the file names to an Attachments variable, and finally a File System Task to delete the raw files after they'd been used as a source.
The inside of my last Data Flow Task is attached.
I ditched the Sampling (because it didn't work... and correctly so, once I thought about it) and I didn't do the Script Component because the code I found never did want to work correctly.
Lastly, I never did find a way to prevent one last (empty) file from creating in this Data Flow Task, so I had to add an additional File System Task outside of the Loop to delete the remaining Raw file so it wouldn't cause hiccups the next time the package ran.
May 30, 2012 at 6:54 am
Can't you add an SQL Script task to check for rows to export before you run the file-create step? Worth doing?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 30, 2012 at 8:17 am
GSquared (5/30/2012)
Can't you add an SQL Script task to check for rows to export before you run the file-create step? Worth doing?
Are you talking about a Execute SQL Task or a Script Task?
I don't know how to connect to a Raw File with a T-SQL Statement, and I'm not terribly good with scripting. Google was taking to long to find what I needed (after 2 days of searching, I had to give up). I had a deadline to meet, so I stuck with what I knew.
I still have time to fix it, so if you can point me in the correct direction, I'd appreciate it.
June 4, 2012 at 6:33 am
Brandie Tarvin (5/30/2012)
GSquared (5/30/2012)
Can't you add an SQL Script task to check for rows to export before you run the file-create step? Worth doing?Are you talking about a Execute SQL Task or a Script Task?
I don't know how to connect to a Raw File with a T-SQL Statement, and I'm not terribly good with scripting. Google was taking to long to find what I needed (after 2 days of searching, I had to give up). I had a deadline to meet, so I stuck with what I knew.
I still have time to fix it, so if you can point me in the correct direction, I'd appreciate it.
Sorry for the delay in responding. I've been sick in bed for a few days, and not much on SSC during that.
I thought your data source was SQL Server tables. I was refering to an SQL Script task to tell if you have rows left to export, based on that concept. I must have misread something is all.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 4, 2012 at 7:49 am
My data source is a stored procedure which does a SELECT at the end, then sends records in two different directions. One to the file and one to a staging table that has inception to date records in it.
I did actually find some useful info on RecordSet Destinations last week and was fiddling with it for another project. Until, of course, I realized RecordSet wouldn't solve my problem.
@sigh. When I try to learn the stuff, nothing at work comes up that can use the stuff I'm trying to learn. Why won't work projects cooperate with my training, darnit?
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply