May 16, 2017 at 10:55 am
<headdesk>
Feeling really stupid now.
I've done this before. Back in 2005 or 2006. This fact finally dawned on me after staying away from this project for a couple of days and working on other things. I don't use a FOREACH LOOP. I use a FOR LOOP. That allows me to set a variable to count up to the needed file count or use the FileCount variable to count down to 1. Either way, I then use the variable to add the number to the file name so the files don't overwrite themselves.
Grrr at myself for forgetting that. And thank you all who contributed to this thread. I'll let you know how it goes once I get the package reworked.
May 16, 2017 at 11:20 am
Brandie Tarvin - Tuesday, May 16, 2017 10:55 AM<headdesk>Feeling really stupid now.
I've done this before. Back in 2005 or 2006. This fact finally dawned on me after staying away from this project for a couple of days and working on other things. I don't use a FOREACH LOOP. I use a FOR LOOP. That allows me to set a variable to count up to the needed file count or use the FileCount variable to count down to 1. Either way, I then use the variable to add the number to the file name so the files don't overwrite themselves.
Grrr at myself for forgetting that. And thank you all who contributed to this thread. I'll let you know how it goes once I get the package reworked.
That was more than 10 years ago, don't be so hard on yourself! I can barely remember what I did last month.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 17, 2017 at 11:24 am
So here's how I got it to work.
I created two variables, @FileCount (set at 0) and @LoopCounter (set at 1). Using a T-SQL task with the below code, I populated the FileCount variable.
SELECT COUNT(*)/750000 AS FileCount
FROM dbo.MyTable
The Execute T-SQL task leads to a FOR LOOP container, which contains the following arguments:
InitExpression = @LoopCounter = @LoopCounter
EvalExpression = @LoopCounter <= @FileCount
AssignExpression = @LoopCounter = @LoopCounter + 1
Within the FOR LOOP, I have three data flow tasks. One to push out the header, one to push out the details, and one to push out the footer. Because the MultiCast / OLE DB Command were causing too many problems (took too long, kept getting disconnected from the database, etc.), I managed to come up with a proc that would update my ExportedToFile bit column as well as grab the first 750,000 rows. I created a one column table called MyTableExported with the MyTableID as the only column. Here's the code for that:
SET NOCOUNT ON;
INSERT INTO dbo.MyTableExported (MyTableID)
SELECT TOP 750000 mt.MyTableID
FROM dbo.MyTable mt
LEFT OUTER JOIN dbo.MyTableExported mte
ON mt.MyTableID = mte.MyTableID
WHERE mte.MyTableID IS NULL AND mt.ExportedToFile = 0;
SELECT mt.MyTableID, mt.Columns....
--I'm not going to list out all the columns here, but this is where I do list them in the actual proc
FROM dbo.MyTable mt
INNER JOIN dbo.MyTableExported mte
ON mt.MyTableID = mte.MyTableID
WHERE mt.ExportedToFile = 0;
UPDATE mt
SET ExportedToFile = 1
FROM dbo.MyTable mt
INNER JOIN dbo.MyTableExported mte
ON mt.MyTableID = mte.MyTableID
WHERE mt.ExportedToFile = 0;
After the FOR LOOP, I have a new FOR LOOP to archive off the files using the same logic as the previous container. I do this separately because there are other steps between the two that can't be run in a loop. Added note: The file connection manager uses a variable for its connection string which adds "File" + [@LoopCounter] + ".txt" to the end of the file name so that each file gets a new number and doesn't overwrite the previous file.
And that's how I got the silly thing to work. Now if I can just figure out how to use powershell to loop through these files for SFTP purposes, I'll be set.
May 17, 2017 at 11:34 am
Bravo. Just one thing; doesn't SELECT COUNT(*)/750000 AS FileCount
FROM dbo.MyTable
Give you one less than FileCount?
Eg, if Count(*) = 500000, FileCount would be calculated as zero.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 17, 2017 at 12:40 pm
Phil Parkin - Wednesday, May 17, 2017 11:34 AMBravo. Just one thing; doesn'tSELECT COUNT(*)/750000 AS FileCount
FROM dbo.MyTable
Give you one less than FileCount?
Eg, if Count(*) = 500000, FileCount would be calculated as zero.
Hrm.... I didn't catch that. Thank you.
Changing code to this:
SELECT CASE WHEN (COUNT(*)/750000.00) > (COUNT(*)/750000) THEN (COUNT(*)/750000) + 1 ELSE (COUNT(*)/750000) END AS FileCount
FROM dbo.Mytable
May 17, 2017 at 12:47 pm
Brandie Tarvin - Wednesday, May 17, 2017 12:40 PMPhil Parkin - Wednesday, May 17, 2017 11:34 AMBravo. Just one thing; doesn'tSELECT COUNT(*)/750000 AS FileCount
FROM dbo.MyTable
Give you one less than FileCount?
Eg, if Count(*) = 500000, FileCount would be calculated as zero.Hrm.... I didn't catch that. Thank you.
Changing code to this:
SELECT CASE WHEN (COUNT(*)/750000.00) > (COUNT(*)/750000) THEN (COUNT(*)/750000) + 1 ELSE (COUNT(*)/750000) END AS FileCount
FROM dbo.Mytable
I'd do it like this
SELECT CEILING(COUNT(*)/750000.00) AS FileCount
FROM dbo.Mytable
May 17, 2017 at 12:50 pm
FridayNightGiant - Wednesday, May 17, 2017 12:47 PMI'd do it like this
SELECT CEILING(COUNT(*)/750000.00) AS FileCount
FROM dbo.Mytable
Ah. Forgot about that function. Thanks.
May 17, 2017 at 1:20 pm
Brandie Tarvin - Wednesday, May 17, 2017 12:49 PMFridayNightGiant - Wednesday, May 17, 2017 12:47 PMI'd do it like this
SELECT CEILING(COUNT(*)/750000.00) AS FileCount
FROM dbo.MytableAh. Forgot about that function. Thanks.
Whereas I'd rely on integer arithmetic and simply add 1 to the result:
SELECT COUNT(*) / 750000 + 1
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 17, 2017 at 2:12 pm
Phil Parkin - Wednesday, May 17, 2017 1:20 PMWhereas I'd rely on integer arithmetic and simply add 1 to the result:
SELECT COUNT(*) / 750000 + 1
If you have exactly 750000 records or a multiple of 750000 then you will end up with an extra file.
May 17, 2017 at 2:20 pm
FridayNightGiant - Wednesday, May 17, 2017 2:12 PMPhil Parkin - Wednesday, May 17, 2017 1:20 PMWhereas I'd rely on integer arithmetic and simply add 1 to the result:
SELECT COUNT(*) / 750000 + 1If you have exactly 750000 records or a multiple of 750000 then you will end up with an extra file.
So you'd use SELECT (COUNT(*) - 1) / 750000 + 1
But I personally think that the CEILING version is much cleaner and clearer.
May 17, 2017 at 2:35 pm
FridayNightGiant - Wednesday, May 17, 2017 2:12 PMPhil Parkin - Wednesday, May 17, 2017 1:20 PMWhereas I'd rely on integer arithmetic and simply add 1 to the result:
SELECT COUNT(*) / 750000 + 1If you have exactly 750000 records or a multiple of 750000 then you will end up with an extra file.
Well spotted.
Note to self: don't post when you have a hangover.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 18, 2017 at 5:03 am
It was that extra file reason why I went with the CASE statement instead of just adding the plus 1. But then, I had forgotten about CEILING since I never actually have needed to use it for anything until now.
May 18, 2017 at 5:08 am
Good to see you got a solution in the end Brandie. I did keep trying with the object variable over the weekend and gave up. I now think I understand the frustration you felt! :hehe:
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 18, 2017 at 5:23 am
Thom A - Thursday, May 18, 2017 5:08 AMGood to see you got a solution in the end Brandie. I did keep trying with the object variable over the weekend and gave up. I now think I understand the frustration you felt! :hehe:
Thanks, Thom. @=)
Now all I have to do is fix my Powershell script for WinSCP to SFTP all the files, test it (which I can't do until the receiving department tells me they're ready for testing), and I should (cross my fingers) be set.
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply