SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 Column out of sync error with excel data flow
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jstikal
Starting Member

15 Posts

Posted - 07/16/2013 :  18:20:26  Show Profile  Reply with Quote
I am attempting to set a connection string within an excel manager dynamically based on the latest file in a particular folder. Both the file name and sheet name is dynamic from file to file. I have two script tasks for two variables, one for the full file path for the connection string and the second for the sheet name. I have set "delay validation" to true on both the excel connection manager as well as the data flow task.

When I execute I'm receiving the error: "external columns for Excel Source are out of synchronization with the data source columns".

I cannot view the available columns in the excel data flow component because the sheet and file is determine during execution.

I'm not sure where to go from here. Any assistance would be greatly appreciated.

Thank you!

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/17/2013 :  01:29:04  Show Profile  Reply with Quote
Do you mean to say that metadata (structure) of file is unknown? You need to fix the metadata inside SSIS before you start the file operations. The filename, sheetname etc can be determined at runtime but the file structure has to be consistent throughout for all files and need to be mapped and created at design time.
The usual method used is to create a test excel file with a structure same as your actual file structure. Then using expression builder map the connection string, sheetname etc properties to required variables so that while execution it will pick the files based on dynamic names and still will find it in same structure as your test file which you'd used for creating mappings.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jstikal
Starting Member

15 Posts

Posted - 07/17/2013 :  08:29:13  Show Profile  Reply with Quote
The structure is known, the files are the exact same with just a file name and sheet name change. This sounds like I did not configure the tasks correctly.

To ensure I understand...

First configure the excel source with a static file then configure the column mappings in the excel source and SQL destination based on the single file. Then update the excel source with the expressions for the dynamic connection string and sheet name. Is that correct?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/17/2013 :  11:09:52  Show Profile  Reply with Quote
Yep...your understanding is correct

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jstikal
Starting Member

15 Posts

Posted - 07/17/2013 :  11:22:40  Show Profile  Reply with Quote
That was the issue. Once I built the metadata with a template file the issue was resolved. I also found that I needed to set the "FirstRowHasColumnName" via expression once I updated the connectionstring as an expression but not a big deal. Thank you so much for the assistance!!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/17/2013 :  12:25:56  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000