Hi All,

I have some excel report files to be imported into SQL Server.
- Files are in a local directory.
- Need to pick year and month info from the file name and add as additional
columns on the SQL Server table (Ex: ABC2005_T06.xls -> "2005" goes to year
column and "06" goes to Term column).
- Have the year & term data in files' header on a cell, but header info is
complicated; legend, different titles, sums etc.
- Planning to create one task for each import in a DTS package.

Q1: How can I read and parse the file names prior to importing?
Q2: Should I use VBS or a Query using OPENROWSET etc?

I appreciate your suggestions.

Ada
SQL Server DBA

SQL Server DBA

Our example here

Looping, Importing and Archiving
(http://www.sqldts.com/default.aspx?246)


Exposes the filename to you. Once you have that you can break it using the
VBScript MID function and assign the relevant bits to the GVs. You then use
GVs as the sources to your transformations.

Use the Excel Provider in DTS

Allan


"Ada" <Ada (AT) discussions (DOT) microsoft.com> wrote