Import excel 2007 and previous version within same data flow
-
I need to import data that has the same output destination, a SQL table, same work and analysis to do with them through different excel version sheet.
However from articles, 2007 is not "natively" supported by SSIS like prior version of excel are.
Therefore, I am trying to avoid redundancy in the package flow, I iterate through all the excel file one by one in the control flow and I stored the current excel file version into a variable before launching the data flow. Then I'm trying to do a conditional split at the start the data flow with that variable to differentiate the excel version and open the right data source (Excel Source for older version or an OLD DB source like this article explain so well: http://www.sqlservercentral.com/articles/SSIS/64585/) before doing any work.
However I'm plague with error messages like "The destination component does not have any inputs for use in creating a path." or "Lineage not used in a previous flow" and I'm beginning to wonder if I go off track for that package.
I could differentiate the excel version at the control flow layer but then (I believe) that I would need to have almost exactly two data flow except for the source which one is for old excel version and one for excel 2007. (redundant analysis, work etc)
Is someone has an idea to workaround that issue or have a different structure to handle this issue while minimizing redundancy?
-
Megistal (2/12/2009)
I need to import data that has the same output destination, a SQL table, same work and analysis to do with them through different excel version sheet.However from articles, 2007 is not "natively" supported by SSIS like prior version of excel are.
Therefore, I am trying to avoid redundancy in the package flow, I iterate through all the excel file one by one in the control flow and I stored the current excel file version into a variable before launching the data flow. Then I'm trying to do a conditional split at the start the data flow with that variable to differentiate the excel version and open the right data source (Excel Source for older version or an OLD DB source like this article explain so well: http://www.sqlservercentral.com/articles/SSIS/64585/) before doing any work.
However I'm plague with error messages like "The destination component does not have any inputs for use in creating a path." or "Lineage not used in a previous flow" and I'm beginning to wonder if I go off track for that package.
I could differentiate the excel version at the control flow layer but then (I believe) that I would need to have almost exactly two data flow except for the source which one is for old excel version and one for excel 2007. (redundant analysis, work etc)
Is someone has an idea to workaround that issue or have a different structure to handle this issue while minimizing redundancy?
If you are handy, you can implement source script component and handle different excel versions from there.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
Indeed, but I would like to do it by using the minimum SSIS scripts components. I do have some spare time right now, so I'll learn a bit more that way I believe.
Also I've found a strange behavior in the for each loop files expression for separating old excel (2003 and previous versions) and excel 2007 files (.xls and .xlsx). Under the hood, SSIS add an asterix next to (*.xls) for the files expression in the for each loop. Therefore using *.xls get me file like *.xlsx and *.xls123 which shouldn't. Argh
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply