August 11, 2014 at 4:03 pm
I've collected a dataset and assigned it to an object variable using the RecordSet destination.
Based off of the number of records in the dataset, I need to loop through the dataset and spit out records to a file. For example, if there were 5000 records in the original dataset, I need to loop through it 5 times to create a file containing 1000 records, called MyFile_1.txt, MyFile_2.txt, etc.
In my script component task in the dataflow, I'm populating the output buffer with the columns from the RecordSet. In doing this, I create a new OleDbAdapter and DataTable, then fill them with the object variable.
This works great the first time. However, the second time, the DataAdapter.Fill(DataTable, ObjectVariable) method executes without error, but the DataTable has no rows in it. While debugging, I can drill down to see that the Object variable still has records in it.
Anyone have any ideas?
August 12, 2014 at 8:31 am
Let's first clarify the objective. Your post says you have 5,000 records, but you have to loop through it 5 times to get 1,000 records in each of 5 files. I'm not sure you need an object variable at all. Have you tried using a conditional split? You can have 5 different destinations from a single source that way, and as long as you can specify the conditions necessary to put a record in one destination vs. another, you're good to go. That gets you out of involving any kind of object variable or the need to do anything that isn't basic stock functionality. Does that help?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 12, 2014 at 8:53 am
That's all well and good, but I need to be able to adjust to a parameter that specifies a maximum number of records per file. In other words, the number of files can/will be different each time. I've found that the problem is my da.Fill(dt, RecordSet) call. The RecordSet object thinks it's completely accessed on the first time it goes through the loop and won't return any more results. I found a work around for this using raw files. Let me know if you're interested and I'll post the link I found.
August 12, 2014 at 9:46 am
Okay, now I'm wondering what, exactly, you're doing. Apparently, it's not anywhere near as simple as your original post suggested. If you can post a more specific description of ALL the rules the solution needs to conform to, then we have a shot at helping. Lacking that, it's difficult to offer useful feedback. Sending a link to your solution doesn't really help, because the nature of the problem is still an unknown.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply