Monday, March 02, 2009

Getting Daily Feed in SSIS

Many time there comes a situation when you need to use a daily feed kind of file which is copied in a fixed directory and we need to use the file for data import.

Below I will show an approach to automate the process so that SSIS package would check if the today's file is present or not and if present then do the data import.

For Example
File name format : FileNameDate (FileName03022009.xls)
Fixed Directory : D:\Abc

1.Create a Variable "FileName" This will hold the path and name of the daily feed file.

2. Use script task. specify readwrite variable as "FileName"

3. Coding
a) Configure FileName to have path and name of the daily feed file.
Dts.Variables("FileName").Value = "D:\Abc\FileName" _ & Date.Today.Month.ToString.PadLeft(2, "0"c) & Date.Today.Day.ToString.PadLeft(2, "0"c) _ & Date.Today.Year & ".xls"

b) check if file is present or not. (Fail the package if file is not present)
'check if file is present
Dim Fi As New FileInfo(CStr(Dts.Variables("FileName").Value)) If (CBool(Fi.Exists().ToString())) Then Dts.TaskResult = Dts.Results.Success Else MsgBox(CStr(Dts.Variables("FileName").Value) & " is not present") Dts.TaskResult = Dts.Results.Failure End If

4) Create a Excel Connection Manager and in Expression property specify


5) Use the Excel Connection Manager in Excel source of DATA FLOW
6) Execute
at
Labels:

5 comments:

  1. AnonymousApril 21, 2009 at 3:54 PM

    impressive

    ReplyDelete
  2. AnonymousJune 14, 2011 at 1:01 AM

    When i got Property Expression Window, there is a DROP-DOWN for values that I can have under PROPERTY. I don't see anything like SERVER NAME (as per your example) and I an unable to add it manually too. Please suggest.

    ReplyDelete
  3. Rahul KumarJune 14, 2011 at 10:29 AM

    @"When i got Property Expression Window, there is a DROP-DOWN for values that I can have under PROPERTY. I don't see anything like SERVER NAME (as per your example) and I an unable to add it manually too. Please suggest."
    Rahul:
    Can you make sure you are looking at property of connection manager

    ReplyDelete
  4. AnonymousDecember 19, 2011 at 1:13 PM

    I tried your code and i got this error message:
    "[DTS.Pipeline] Warning: The output column "START DATE" (28) on output "Excel Source Output" (9) and component "Excel Source" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance".

    What is this? and What if I wanted a email notification saying that there is an existing file, What should i do?

    ReplyDelete
  5. Rahul KumarDecember 20, 2011 at 12:23 PM

    @Lastmsg...
    What u have got is a warning msg coz of [Start Date] not utilized downstream.
    U can get performance improvement if u remove it.

    Did u get any other error msg?

    ReplyDelete

Subscribe to: Post Comments (Atom)

Google Dataset Search

Google's Vision statement  is “ to provide access to the world's information in one click. ” Google’s mission Statement is “ ...