Handling variable number of columns on imports

Question

Hi,

I have a package which is looping through a filestore to import data feeds frmo 3rd parties. The data varies bewteen the files and is intended to be directed to different stage tables. This is managed by inputting a derived column based on the filename (these have been dictated to the providers). Each file has a different number of columns ranging from 3-13. I ignore the column headings in row 1.

The issue I have is that the data is not being transferred as I would expect. Where there are 13 source columns it works fine as I would expect but in the other instances it seems to want to include data all along one row which is leading to me missing key information. I can assume that this is caused by the variable number of columns but was wondering if anybody had a suggestion for how to overcome it? I do have some knowledge of vb if that is required.

Cheers

David

Answer 1

Hi David,

I suggest that instead of having a dataflow task, you have a child package, and you have one child package for each type of file. You can then fire the appropriate child package dynamically. This is because flat file connection managers can not be dynamically configured in the way that you want. The other (less elegant) option, is to have many branches, with a seperate data flow task on each branch.

Hope this helps.

---

Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/

Answer 2

Hi David,

Based on your statement above ("but in the other instances it seems to want to include data all along one row which is leading to me missing key information"), it seems to be a problem with the delimiter. Can you check if those files which have same number of columns have the same column delimiters in the file.

 

- Datta

---

Dattatrey Sindol
My Blog: Datta's Ramblings on Business Intelligence 'N' Life
The information provided here is "AS IS" with no warranties, and confers no rights.
Please mark the post as answered if it solves your problem.

Answer 3

Hi Datta,

If you have one Flat File connection manager, with multiple underlying schema, it will appear to be a delimiter problem, but in fact it is because you can not dynamically change the schema that a connection manager is expecting during package execution. That is why the best solution is to use child packages and dynamically choose which child package to call, as I have described above.

Pete

---

Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/

Answer 4

Hi David,

If you can use third-party solutions, check the commercial CozyRoc Data Flow Task Plus . It includes support for dynamic data flows at runtime. You can import all your 3rd party data feeds using only one data flow. The functionality is easy to use and doesn't require programming skills.

---

SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Answer 5

Hi David,

There are certain limitations on the data flow in handling varrying columns when it comes to explicitly mapping the source and target columns. You may have to try with SSIS package APIs.

 

Regards

Suresh M. Menon