CODEWITHSUNDEEP
ssis
Faiz
Faiz

Reputation: 283

Inserting Data from Flat file source to database between 2 dates

I have an SSIS package set up that imports downloaded data files to the database (one file at a time by date)

Current Setup (for a file):

  1. Downloaded file is at location (same file exists between the date range 1st Feb to Today)

    C:\DataFiles\GeneralSale_20170201.txt

  2. In SSIS the variables - For each file there are 4 variables. First is the location of the where the file is, called @Location

  3. The second simple gives the name of the file named @GeneralSales returning the value

    GeneralSale_

  4. The third is the date (@ExportDateFormatted) for which the code is, (DT_WSTR,8)(DATEPART("yyyy", @[User::ExportDate]) * 10000 + DATEPART("mm", @[User::ExportDate]) * 100 + DATEPART("dd", @[User::ExportDate])) and [ExportDate] is set as DATEADD("DD", 0, GETDATE()).

    [ExportDate] allows me to set the file date (which is already downloaded) that I want to import in my table dbo.GeneralSale i.e. If I want to import the file on 20170205 then I would adjust the export date and then run the package

  5. The final variable is the @ExportFileExtension returning the value

    txt

  6. Then in the DataFlow which looks like the below:

enter image description here

The flat file source connects to the connection string below. The Property > Expressions > ConnectionString of the connection string runs the variables to make a file name. This is where I use the variables from before 

@[User::Location] + @[User::GeneralSales] +  @[User::ExportDateFormatted] + "." +  @[User::ExportFileExtension]

Returning the value:

C:\DataFiles\GeneralSale_20170201.txt

This then populates the table with the data of that file. But to insert the date for another day I have to amend the date and run the package.

What I am trying to do is pass a start and end date to let the package insert all data from the files between those dates.

Hope the above information is clear of what goes on and what I am trying to achieve.

Upvotes: 0

Views: 147

Answers (1)

Ferdipux
Ferdipux

Reputation: 5246

You need to iterate between two dates. In SSIS its pretty straightforward; I would describe the main steps:

  1. Define two package parameters, StartDate and EndDate of type Date, and on the package start - validate that StartDate <= EndDate.
  2. Define a Date variable ExtrDate, and add For Loop with settings initial Expression @ExtrDate = @StartDate, Evaluation - @ExtrDate <= @EndDate and Assign - @ExtrDate = DateAdd("dd", 1, @ExtrDate). Purpose of this loop is quite clear.
  3. Put your extraction tasks inside For Loop container.

ExtrDate variable will be increased on each step of the loop.
Package parameters allow building more flexible package.

Upvotes: 1

Related Questions