Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jstikal
Starting Member
19 Posts |
 Posted - 2013-07-16 : 18:20:26
|
| 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
52326 Posts |
Posted - 2013-07-17 : 01:29:04
|
| 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
jstikal
Starting Member
19 Posts |
Posted - 2013-07-17 : 08:29:13
|
| 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? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-17 : 11:09:52
|
| Yep...your understanding is correct------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
jstikal
Starting Member
19 Posts |
Posted - 2013-07-17 : 11:22:40
|
| 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!!! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-17 : 12:25:56
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
Column out of sync error with excel data flow