Column length in Excel source

Here is one very common warning with using Excel in SSIS

Whenever we use Excel file in SSIS, it takes default length of each column as 255. This results in having a warning if we try to map this column with column having length less than 255. We can avoid this warning my setting column length of the Excel source.

Right click to Open Excel source in Advance editor and set lenght of the column as depicted in the figure.

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