How to create a dynamic project file connection

Question

How to create a dynamic project file connection

Jez-448386

SSCarpal Tunnel

Points: 4594
More actions
November 28, 2017 at 5:48 am

#341035
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:
- 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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Jez-448386 - Tuesday, November 28, 2017 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:
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

Jez-448386 SSCarpal Tunnel Points: 4594 More actions · Answer 2

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

Jez-448386 - Tuesday, November 28, 2017 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

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

Jez-448386 SSCarpal Tunnel Points: 4594 More actions · Answer 4

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.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

Jez-448386 - Tuesday, November 28, 2017 7:22 AM
Also 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