SSIS Connection String by Project Variable

Question

Post reply

SSIS Connection String by Project Variable

DinoRS

SSCrazy

Points: 2683
More actions
October 17, 2018 at 5:14 am

#379661

Hi there,
I have a For Each Loop Container which goes through all files in a folder to do the full load. Now, how would I approach changing the connection string to only check for files with the current date in the same folder and if it's there, load the file, otherwise ignore this step.
I was thinking of having some sort of File Exists Check before running the package (I have such check in this project already) however I fail to see how I could pass as new connection string CurrentConnectionFolder\FilenameFileDate.txt if I set a project Variable (bool) which should decide wether we want to do a full load or a daily load.
So currently we have(FLC):
Folder: Destinationfolder
Files: File_*.txt
Fully Qualified
Traverse Subfolders
Connection String:
Expressions: @FileName
Hope it makes sense, any suggestions?
If not exists (file name containing today's date), load all files in folder

Is that correct? If so, please fill in the 'what happens next' bit.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

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

... check for files with the current date ...

What do you mean by this?

DateCreated = today?
DateModified = today?
Filename contains today's date, in some form?
Data within the file contains today's date?
something else?

Note that best practice is to move a file to an archive folder after processing it. Doing that should mean that you can always process all files in the folder without having to perform date gymnastics.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

DinoRS SSCrazy Points: 2683 More actions · Answer 2

Hi Phil,

the Filename contains the date.
Actually we have two different Folders, one for the current file (which does not have the date in the filename yet, tho it's getting fixed ... somewhen) with a total shorter string (folder path) than the folder where all files end up in - which is where I pick up the files right now.

Ideally I would change the folder for the daily load but that would mean I have to adopt a few more things like the Derived Column Transformation that extracts the date from the filename. However this might be much more work than just being able to switch processing type within the same folder. I could do two connection Strings, one for full and one for daily load. If I do so I would need to alter within 2 Derived Column Transformations the following expression

(DT_WSTR,8)(LEFT(RIGHT(@[User::SOURCE_FULLFILEPATH],8),4) + LEFT(RIGHT(@[User::SOURCE_FULLFILEPATH],10),2) + LEFT(RIGHT(@[User::SOURCE_FULLFILEPATH],12),2))

to essentially still extract the right date.

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

Can you clarify the required logic a little, please?

What I understand so far:

If exists (file name containing today's date), load the file.

What happens next?

DinoRS SSCrazy Points: 2683 More actions · Answer 4

well what I want to achieve is this:
If specified by flag: do a full load,
otherwise: attempt a daily load. If there is no file available, skip processing to the next step
of course, if I want to do a full load nothing afterwards is going to be skipped.

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

DinoRS - Wednesday, October 17, 2018 5:51 AM
well what I want to achieve is this:
If specified by flag: do a full load,
otherwise: attempt a daily load. If there is no file available, skip processing to the next step
of course, if I want to do a full load nothing afterwards is going to be skipped.

One way of doing this is by manipulating the 'Files' property of the FOREACH loop at runtime, depending on the load type. If load type = 'daily', set 'Files' to the exact name of the expected daily file.
This, of course, assumes that the format of the file name is very well defined.
The manipulation can be done by an expression or by a script task.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

DinoRS SSCrazy Points: 2683 More actions · Answer 6

I was thinking of solving this by expression, how I understand things in this case as my project variable is read only I first have to read the project variable into a package variable. Then I can evaluate precedence constraints and for the daily load I get another package variable and read the project variable with the filepath from another user variable and add the file extension there, is that the right approach?
Will the Filename Variable (File_YYYYMMDD.txt) be preserved in any case wether I have to run the Filename Variable for a full load (unchanged) or daily load (dynamically created) on a certain day? It might be not that cool if Filename get's changed once and afterwards full loads won't work.

Phil Parkin SSC Guru Points: 246985 More actions · Answer 7

DinoRS - Wednesday, October 17, 2018 6:21 AM
I was thinking of solving this by expression, how I understand things in this case as my project variable is read only I first have to read the project variable into a package variable. Then I can evaluate precedence constraints and for the daily load I get another package variable and read the project variable with the filepath from another user variable and add the file extension there, is that the right approach?
Will the Filename Variable (File_YYYYMMDD.txt) be preserved in any case wether I have to run the Filename Variable for a full load (unchanged) or daily load (dynamically created) on a certain day? It might be not that cool if Filename get's changed once and afterwards full loads won't work.

The 'project variable' you are referring to is actually a parameter. And, as you suggest, parameters are read only at runtime.
So you need to create a variable which depends on the value of the parameter.
If the parameter is 'full', set the variable to *.txt'.
If the parameter is 'daily', set the variable to 'File_YYYYMMDD.txt' (where YYYYMMDD decodes to today's date)

Finally, you need to set an expression in your FOREACH container which assigns the variable to the FileSpec property:

This stuff all gets evaluated at run time. You do not need to worry about the property being wrongly persisted between runs, as long as you have set the parameter's value correctly.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

DinoRS SSCrazy Points: 2683 More actions · Answer 8

How do I make a variable dependent on a project parameter?

Phil Parkin SSC Guru Points: 246985 More actions · Answer 9

DinoRS - Wednesday, October 17, 2018 7:47 AM
How do I make a variable dependent on a project parameter?

Create a variable whose expression references the project parameter in a conditional manner.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

DinoRS SSCrazy Points: 2683 More actions · Answer 10

ah ok, so I wasn't completely off the right path, now to figure out how to evaluate a BOOL Expression or simply change the parameter. 😉 Thanks in advance

DinoRS SSCrazy Points: 2683 More actions · Answer 11

as this is still somehow related to Variables and the likes, I'll make this a follow up question:

Imagine there is the daily load which passes the daily File_YYYYMMDD.txt to be loaded. All your files are YTD which means as we're in 2019 and all cool, we want to change the logic behind the full load scenario.
A full load would have the following conditions:
It is known in which year (yr0) this whole thing started, respectively since when data is available so we want to:
1) first check the difference current year and yr0
2) for each year between current year and yr0, we need a FILE_YYYYMMDD.txt string which we can use in a for (each ?) loop container as the filenames which should be imported
3) once all of the YTD files are processed, the last file (today) should be processed.

My current idea is something like:

1) Expression Task which loops through all the years between current and yr0 and creates an array of strings in a variable. Another approach might be some sort of Script Task?
2) For Loop Container which passes connection strings with the file part based on the array to a data flow Task which then imports the files in the correct order starting with yr0

is there a better approach I should be looking at?