October 18, 2018 at 11:56 am
importing 1 column from an XL spreadsheet into a SQL Server table.
is it possible to import via a task in SSIS package just one column from XL file?
This column is called SSid and it is is not in the same position in each arriving spreadsheet among other columns. it has the same name though (in the header) .
XL files arrive via sftp every week, and they have different number of columns with SSid column among them. it can be the first or last column, but more often in the middle among other columns.
Can a data flow be used to do that? but there would be no mapping available however. That is why I doubt it is possible to do via Data Flow/XL source.
Should a SCRIPT task be used? would C# or VB.net code be complex?
Likes to play Chess
October 18, 2018 at 12:16 pm
VoldemarG - Thursday, October 18, 2018 11:56 AMimporting 1 column from an XL spreadsheet into a SQL Server table.
is it possible to import via a task in SSIS package just one column from XL file?
This column is called SSid and it is is not in the same position in each arriving spreadsheet among other columns. it has the same name though (in the header) .
XL files arrive via sftp every week, and they have different number of columns with SSid column among them. it can be the first or last column, but more often in the middle among other columns.Can a data flow be used to do that? but there would be no mapping available however. That is why I doubt it is possible to do via Data Flow/XL source.
Should a SCRIPT task be used? would C# or VB.net code be complex?
It can be done, but it's a little complicated to implement. Here are the basic steps:
1) Read the header row using a script task, to determine which column (by letter) contains SSid
2) Set a variable to define the Excel range you are interested in (eg, if the worksheet is called 'sheet1' and the column you want is 'G', you'd have something like this: sheet1$G2-G (which is, I think, all of column G, from row 2 onwards)).
3) In your data flow, set the Data Access mode to 'Table name or view name variable' and set the variable name to whatever you created in (2)
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 18, 2018 at 12:51 pm
THANK YOU! i like your idea a lot.
Likes to play Chess
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply