ease20022002
Technical User
Hi,
I have an ETL package that performs the following steps:
1) Truncates the Import table where I import the file
2) Calls a stored procedure function to get the name of the txt file I am going to import (the name of the file is a date). The function populates a string variable, which takes the place of the Flat File Connection hard coded string path.
3) The Data Flow object begins and loads the files from the flat txt file to the import table that was truncated.
4) Upon completion, I call another stored procedure that runs a process that cleanses and formats the imported file and then runs a bunch of inserts and updates to fit into my star schema db.
The problem I have is that when I am finished running, the flat file connection to the import file does not have a path listed anymore in the, I believe, connection property, it only has the variable that is being used to store the txt file name. So if I want to rerun the process, the ETL package fails b.c it can't find the file b.c the Flat File Connection doesn't have a path and file name associated with it, only a file name b.c of the variable. Do I need to create another Constant (if possible) or variable that keeps the path and any file name in the import folder (the original flat file connection path) so that when the ETL package is finished loading, I can put in one more process that puts the path back to the Flat File Connection?
I am not looking to loop through every file in the path as I have a function that calculates the name of the file name each week.
Any help would be appreciated.
Thank You
I have an ETL package that performs the following steps:
1) Truncates the Import table where I import the file
2) Calls a stored procedure function to get the name of the txt file I am going to import (the name of the file is a date). The function populates a string variable, which takes the place of the Flat File Connection hard coded string path.
3) The Data Flow object begins and loads the files from the flat txt file to the import table that was truncated.
4) Upon completion, I call another stored procedure that runs a process that cleanses and formats the imported file and then runs a bunch of inserts and updates to fit into my star schema db.
The problem I have is that when I am finished running, the flat file connection to the import file does not have a path listed anymore in the, I believe, connection property, it only has the variable that is being used to store the txt file name. So if I want to rerun the process, the ETL package fails b.c it can't find the file b.c the Flat File Connection doesn't have a path and file name associated with it, only a file name b.c of the variable. Do I need to create another Constant (if possible) or variable that keeps the path and any file name in the import folder (the original flat file connection path) so that when the ETL package is finished loading, I can put in one more process that puts the path back to the Flat File Connection?
I am not looking to loop through every file in the path as I have a function that calculates the name of the file name each week.
Any help would be appreciated.
Thank You
