looping through dates in ssis

Question

looping through dates in ssis

frdrckmitchell7

Ten Centuries

Points: 1351
More actions
October 29, 2013 at 4:20 am

#300589

Good day all the clever ones
I have a table (stock) that I need to update every day from a certain date (the first day of the month) to the the present day - 1. In other words, the stock needs to be updated until yesterday. Is there anyone that can help me with this? I am working through a Calendar table where all the dates are already set up. Is there anyone out there with an idea of how I can go about doing this?
I need to loop through a container in ssis to do the following:
UpdateCubeOn |UpdateDateFrom |UpdateDateTo
2013/10/01 | 20131001 |20131001
2013/10/02 | 20131001 |20131002
2013/10/03 | 20131001 |20131003
2013/10/04 | 20131001 |20131004
2013/10/05 | 20131001 |20131005
.
.
.
So my stock will now be:
2013/10/01 = 100
2013/10/02 = 110
2013/10/03 = 90
2013/10/04 = 101
2013/10/05 = 92
Kind regards
Fred

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Nevyn SSChampion Points: 14210 More actions · Answer 1

You could start with an execute SQL task to query your calendar table for the current date (or current date - 1, not sure of your logic), and put the start and end dates into string variables.

Then you can set up another variable with your data source query, and append the date variables, and then set up an expression in your main data flow for the sql command in your data source, putting the string in.

Phil Parkin SSC Guru Points: 246961 More actions · Answer 2

frdrckmitchell7 (10/29/2013)
Good day all the clever ones
I have a table (stock) that I need to update every day from a certain date (the first day of the month) to the the present day - 1. In other words, the stock needs to be updated until yesterday. Is there anyone that can help me with this? I am working through a Calendar table where all the dates are already set up. Is there anyone out there with an idea of how I can go about doing this?
I need to loop through a container in ssis to do the following:
UpdateCubeOn |UpdateDateFrom |UpdateDateTo
2013/10/01 | 20131001 |20131001
2013/10/02 | 20131001 |20131002
2013/10/03 | 20131001 |20131003
2013/10/04 | 20131001 |20131004
2013/10/05 | 20131001 |20131005
.
.
.
So my stock will now be:
2013/10/01 = 100
2013/10/02 = 110
2013/10/03 = 90
2013/10/04 = 101
2013/10/05 = 92
Kind regards
Fred

I'm a bit confused by your question. Can you take a look at the link in my signature & attempt to provide sample data in a format which is more easily consumed? Along with expected results? From the above, I cannot see where the 100,110 etc results come from, for example.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

frdrckmitchell7 Ten Centuries Points: 1351 More actions · Answer 3

Hi Phil

I am not sure with the 'link in your signature' so I will respond this way:

The 100, 110, etc is the fluctuation of the stock, in other words the stock being sold on different day. The 100, 110, etc. is the stock that is left on that day. Every day the stock at a certain branch will be different. What essentially has to happen is I need to create in my ssis package a for loop that will populate a table from the dates stipulated what the stock at a certain branch are. This was I will see if the stock needs to be ordered, or if one branch has stock that the other don't have, then move the stock to the branch that do not have the stock.

Kind regards

Fred

frdrckmitchell7 Ten Centuries Points: 1351 More actions · Answer 4

Hi Phil

Here is additional sample data. This is what I get when I run a query.

facilityItemCode ItemDescription TxnQtyFacOpbBalanceOnDateDate

41179 Gouda CL20Kg Cuts 2x10kg395803958 20131011

41179 Gouda CL20Kg Cuts 2x10kg000 20131002

41187 F/Value Gouda Round 2x3.5kg1.501.5 20131017

41187 F/Value Gouda Round 2x3.5kg000 20131011

41187 F/Value Gouda Round 2x3.5kg-328.70-328.7 20131014

41187 F/Value Gouda Round 2x3.5kg868.10868.1 20131023

41187 F/Value Gouda Round 2x3.5kg487.30487.3 20131007

41187 F/Value Gouda Round 2x3.5kg000 20131010

41187 F/Value Gouda Round 2x3.5kg-160.10-160.1 20131019

This is the table I have created that will be populated with the above data:

CREATE TABLE [dbo].[DW_Fact_InventoryBalances](

[KeyDate] [int] NOT NULL,

[KeyDepot] [int] NOT NULL,

[KeyItem] [int] NOT NULL,

[TransactionQuantity] decimal(11,3) NULL,

[FacilityOpeningBalance] decimal(11,3) NULL,

[BalanceOnDate] decimal(11,3) NULL

In this table I want to store my data for a certain period (month). It will always have to populate the table from the first of the month to yesterday.

This query finds me the first day of the month.

Select TrxDateNumeric /*for example: 20131011*/

from dbo.View_Dim_CalendarDefinition as C WITH (NOLOCK)

inner join dbo.View_Dim_SystemParameters WITH (NOLOCK)

on Run_Type = 'SALES'

where TrxDate = RUN_DateHistoryFrom

I need to do a for loop that will take the above query and start populating my table as stipulated above.

This table will then have the available stock (BalanceOnDate) for a branch (facility) for a particular day. Now, looking at the data of my query I know on the 19th (as per the data above) I will have to get more stock.

Kind regards

Fred