November 28, 2017 at 5:48 am
Hi,
I am creating a SSIS project that will consist of a parent package with a number of child packages. The basic flow is this:
However, the problem with this is that if the definition of the file changes then I would need to make changes to the connection in each of the child packages.
I was thinking of using a using a project connection where I would only need to change the definition once. However, I cannot see anyway of setting the file connection string at run time (using an expression) from the parent package.
Is this possible? Is there a better way?
Thanks
Jez
November 28, 2017 at 6:09 am
Jez-448386 - Tuesday, November 28, 2017 5:48 AMHi,I am creating a SSIS project that will consist of a parent package with a number of child packages. The basic flow is this:
- The parent package will use a for each loop to identify the name of the file to be loaded (the file name changes every day with a YYYYMMDD date stamp)
- The parent package will execute each child package
- Each child package will load data from the file identified in the parent package
Currently, I am passing the file name into each child package as a package parameter and each child package has its own file connection manager to the file. This seems to work.However, the problem with this is that if the definition of the file changes then I would need to make changes to the connection in each of the child packages.
I was thinking of using a using a project connection where I would only need to change the definition once. However, I cannot see anyway of setting the file connection string at run time (using an expression) from the parent package.
Is this possible? Is there a better way?
Thanks
Jez
Are the file formats (column names, column order, number of columns, data types) all the same?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 28, 2017 at 6:17 am
Hi Phil,
There is only one import file which is delimited with column names in the first row. The structure is outside my control and I cannot change it.
Each row contains data that might populate up to 15 tables. Currently we have a single data flow task that splits the incoming file and multi casts each row into 15 destinations.
The problem with the current process is that it takes far too long. Typically we have 5k - 10k rows but it takes about 1 hour. The import file has over 6,000 columns and the SSIS package is very difficult to maintain as it takes ages to validate. By splitting the import into 15 child packages I hope to speed up the process and make maintaining it more manageable.
Jez
November 28, 2017 at 7:02 am
Jez-448386 - Tuesday, November 28, 2017 6:17 AMHi Phil,There is only one import file which is delimited with column names in the first row. The structure is outside my control and I cannot change it.
Each row contains data that might populate up to 15 tables. Currently we have a single data flow task that splits the incoming file and multi casts each row into 15 destinations.
The problem with the current process is that it takes far too long. Typically we have 5k - 10k rows but it takes about 1 hour. The import file has over 6,000 columns and the SSIS package is very difficult to maintain as it takes ages to validate. By splitting the import into 15 child packages I hope to speed up the process and make maintaining it more manageable.
Jez
Sounds to me like SSIS may not be the best tool for the whole of this job. I suspect that you'd get contention at the file level if you try to spawn multiple threads all reading from the same file concurrently (which is what I think you are proposing).
Have you considered blasting the entire contents of the file into a single staging table and then running a stored proc to do all the necessary updates, once the file import is complete?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 28, 2017 at 7:22 am
We rejected the notion of a complete redesign over complexity and risk. We would have to remap 6,000 source to destination columns. Also trying to extract 6,000 columns from a variable length delimited string might perform even worse then the current package.
I believe that SSIS is a good tool for the job. All I am trying to do is create a single reusable file connection to the import file.
November 28, 2017 at 7:41 am
Jez-448386 - Tuesday, November 28, 2017 7:22 AMAlso trying to extract 6,000 columns from a variable length delimited string might perform even worse then the current package.
I don't understand why this is an issue, but that's probably because I don't understand the file format.
If the file format changes, you'd have to change the data flows in each of the child packages individually.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply