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

5 comments:

Anonymous said...

impressive

Anonymous said...

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 Kumar said...

@"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

Anonymous said...

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?

Rahul Kumar said...

@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?

Post a Comment