I have an Excel connection manager which should write to a file which does not exist prior to runtime but rather is created during execution. I have an expression set for the connection string property of the excel connection manager which points to the file that will be created. The problem I am having is that since the file does not yet exist (prior to execution), when I go to run the package it is giving me an error since it can not find the file. If i create a dummy file and place it where the excel connec manager is pointing the package will run ok.
What I have tried to do is point the excel connection manager to an existing file prior to run time since it will not give an error and then hopping it will then pick up the new Path (excel connection string) from the Expressions but what is happening is that since I have an expressions set for the connection string property it will not let me overwrite the connection string property, it keeps defaulting to the value of the expressions which contains other variable that are populated during run time so that is where I am getting my error. The expressions string prior to runtime is not pointing to an actual file because it does not yet exist but mainly because the expressions is not fully populated until runtime since it also contains variables.
As of right now it seems like I might have to just point to a dummy template file (without using any expressions) prior to runtime and then maybe use a Script to change the connection string property of the excel connec manager to = a variable containing an expression containing the path to the file....
Does this sound like the way to go or am I over complicating things?