This is miracle. I just solved this error...
You don't need to create a template (dummy) excel, you need only 3 components.
1. Create Execute SQL Task according to this tutorial.
When set everything except SQLStatement I suggest creating empty excel and connect to OLE DB source (your server table). When you add excel destination and connect with excel destination manager, you will see question that VS ask if you want to create table like this query (but with different data types):
CREATE TABLE `Result` (
`ColName1` NVARCHAR(255),
`ColName2` NVARCHAR(255),
`ColName3` NVARCHAR(255),
`ColName4` NVARCHAR(255)
)
I don't know if I explained well, but you have previous step in tutorial.
2. Execute this task
3. Add Data Flow Task with OLE DB Source and Excel Destination
With OLE DB select all that you need from your Product table
Connect Excel Destination with SAME Excel Connection Manager like in Execute SQL Task. From drop-down select Result sheet name.
When you finish this delete this empty excel
4. Create a variable that will contain path and excel file name
5. According to tutorial from first step, click on Excel Connection Manager -> Properties -> Expressions -> ... (on right) -> ExcelFilePath (from drop-down). In 2nd column write @[User::YourVariableName]
Crucial step
6. In control flow, click on Data Flow Task and in properties for DelayValidation set True.
If you have a question, write me in comment below.
