September 29, 2016 at 8:49 pm
So after a long fought battle what I ended up doing was........
Setting up a directory folder with an Excel template, with this I set the columns correctly, matching the names in the query (DMX)
File System Task to Delete the present template
File System Task to Copy the template to the new location that was recently deleted
Actual DMX query to the Excel file
3 seperate data flows that utilized a flattened query to the excel file (ARIMA, ARTXP, and Mixed)
Loaded my relation table from the Excel file
With a case statement I updated the table to turn nulls into 0's, for the value, stdev and variance.
The reason I used this approach was due to the limitation of the ARTXP and Mixed algorithms, if they start to go wild the forecast craps out and it stops, leaving you with a string NaN, Infinity or something else. This strategy allows you to retain all records and update the table at the end.
Probably not the best way to get things done, but I tried the derived table and converting task, I just found them more of a headache to be honest.
Querying Microsoft SQL Server 2012/2014 - Certified
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply