SQLTeam.com | Weblogs | Forums

Importing Excel data with blank first row

SSIS and Import/Export
#1

Hi

I'm trying to import a file into DB that a user would drop into a folder from BOXI. At this point the Excel file has the top row missing, due to the set up in BOXI, and when I map in SSIS the column names are F1, F2 etc.

Obviously I want the first row to be the column names.

I've set up a variable for the filename, but first need to set the expression of the variable to set the column definitions, but not sure of the actual text of expression script. In this exppression I want to ignore the first row and define the next row as the header.

Do I just use the actual names of the columns in the sheet and cast them as varchar(X) i.e.

select CAST(Name AS VARCHAR(100)) AS Name, CAST(Address AS VARCHAR(200)) AS Address, ....,......,....,..., from 'Name (Alpha)$A2:F'

Note: Sheet name is default 'Name (Alpha)$' never heard of that but that's what it exports as.

Thanks

Andrew

#2

Maybe try IMPORTAS() function?

#3

Any idea why my excel connection manager keeps dropping the link to the mapping file?

So i've got a folder with the mapping file in it, this is to the set up the column names and structure of the file connection, then a for loop to loop through any files found in the folder.

When I open the connection manager, browse to the mapping file, click it and then ok, there is a red cross on the excel source and then you open the connection manager the path to the file is blank. This seemed to happen after I set up my loop with the variable for the file name in the for each loop editor.

#4

Do you have DelayValidation set to True on the source? If you are using a variable as the connection, then you should

#5

Yes, delay validation set to true, in both for loop, and data flow, Just figured out the issue, change name in for each loop Collections to "fully qualified" file name. I'm now trying to move the file using a file task in the for loop, so once the first part is done it moves file to an archive folder.

Should I add the file system task to the current foreach loop, and set the path to the archive folder in the foreach loop collections and set the variable for it as index =1.

As the expression in the for loop is set to full path to pick up the xls file, will a file system task be able to use this same expression to pick up the file and move it to an archive folder?

Thanks

G

1 Like