August 25, 2020 at 12:36 pm
Hi folks!
I'm wondering if it's possible within an SSIS package to use the incoming package variable for the filename, to determine which connection manager is associated with a flat file source?
I'm trying to determine if I can just have one package for 6 different files, for which there are 3 that are in one file format, and the other 3 in the other file format. I'd much rather have just one package rather than two. The data ultimately ends up in the same table no matter which incoming file format it has. I'd also need a decision tree to branch based on the filename. I might be biting off more than I should be chewing, but I'm game for a challenge... Push comes to shove though, and it's back to two different packages.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 25, 2020 at 1:00 pm
You can use Script task to implement decision tree and to route the files accordingly.
August 25, 2020 at 1:05 pm
Okay, but that doesn't tell me anything about the exact how, nor does it solve the need to change the connection manager for the Flat File Source. Just telling me to use a Script Task leaves out one heck of a lot of detail...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 25, 2020 at 1:18 pm
You can use Configuration file to store your configurations (including flat file path or other connection string) and then use Script Task to apply decision tree. Right click on the Control flow gives an option to open Configuration Manager and allows to save your package configurations in XML file. You can also manipulate the configuration and with the help of Script Task read the configuration from Config file, store it in the Package variables and apply decision tree.
I hope this will help!
August 25, 2020 at 1:23 pm
Quick search on SSIS Dynamic Configuration, found these useful.
Using Package Configuration
Using Script Task
August 25, 2020 at 1:28 pm
Not really... again, I'm not talking about changing the connection manager's file path. That's already coming from a package variable. I'm talking about dynamically changing which connection manager a Flat File Source is using, on the fly, based on the file name. For a Script Task to do that, I'd need to understand what the Flat File Source manipulable properties are, and I haven't found anything online yet that tells me that there's a way to programmatically change such a thing. So I'm looking for either C# or VB code that could do such a thing.
The plan is to have two connection managers that both rely on the package variable for the file name. I'm not even sure this is practical or possible, and there just doesn't appear to be any useful guidance on this that I've been able to find. I'm hopeful that someone else searching online might use sufficiently different search terms that they can find something my brain just isn't going to think about.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 25, 2020 at 2:08 pm
Ohh got it.
I suspect it's not possible. A Flat File Source/Destination is tightly coupled with it's Connection Manager.
However, you can have single package with all 6 files and based on Package Configuration/Parameter apply decision tree.
Keep similar files of same type in Containers (such as Sequence Container). In your case there will be 2 containers. Now your decision tree will route the request to respective container based on Package Configuration/Parameter that will act as a kind of switch.
August 25, 2020 at 2:18 pm
Okay... finally, the package has to handle one and only one file. Unless I can see a way around the Flat File Source having it's connection manager changed, I have no way to do anything other than have two packages, or alternatively, scrap SSIS altogether and write a custom VBScript to do the job.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 25, 2020 at 2:29 pm
Take a look at the Scripting.FileSystemObject in VB. I used that in an SSIS package to check the date/time stamp for an xls file before importing, you should be able to inspect the file name too. Unfortunately I don't have access to that code any more, but it was not very complicated. HTH
August 25, 2020 at 2:41 pm
Take a look at the Scripting.FileSystemObject in VB. I used that in an SSIS package to check the date/time stamp for an xls file before importing, you should be able to inspect the file name too. Unfortunately I don't have access to that code any more, but it was not very complicated. HTH
I''ll already have the filename in the package variable, and Scripting.FileSystemObject is already very familiar to me... I've written thousands of VBScripts that used it. What I'm trying to avoid is having a second SSIS package just to be able to handle two different file formats. Unfortunately, it just doesn't appear to be possible without effectively replacing SSIS altogether.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 25, 2020 at 3:40 pm
This is actually really easy in SSIS. Select the connection manager in the connection managers pane and hit F4 to open the properties pane. Find Expressions, and open that up. Then you can change the values of the properties you need in there, and use the parameters/variables to define the values.
Quoting from another one of my posts, which explains how to do this, but at a project level (rather than package):
To then have a Connection Manager use this in it's Connection String you first need to create one (duh!). Set this up as normal, specifying an actual Instance you have. To actually make the connection manager use an expression for a property though, you (oddly) have to be editing a package. If you don't have a package in the project, create one. At the bottom you'll see the Connection Managers pane with your project SQL Connection manager. Select that and hit F4/Right Click->Properties for the Properties Pane to be opened for the Connection Manager. Locate Expressions, then click the blank box to the right and then the ellipsis (...). For Property, select ServerName, and then for the Expression into your Project Parameter you set up previously, in this example that's
@[$Project::SQLInstanceName], and then ok your changesNow your SQL Connection Manager will show a little fx symbol to the left, to show it is "dynamic". Now, when you change the configuration you are in, the project parameter's value will be changed, and along side it the value of ServerName in the Connection manager, meaning that depending on the configuration you're in, the SQL instance your Visual Studio Project connects to will change.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 25, 2020 at 8:03 pm
This is actually really easy in SSIS. Select the connection manager in the connection managers pane and hit F4 to open the properties pane. Find Expressions, and open that up. Then you can change the values of the properties you need in there, and use the parameters/variables to define the values.
Quoting from another one of my posts, which explains how to do this, but at a project level (rather than package):
To then have a Connection Manager use this in it's Connection String you first need to create one (duh!). Set this up as normal, specifying an actual Instance you have. To actually make the connection manager use an expression for a property though, you (oddly) have to be editing a package. If you don't have a package in the project, create one. At the bottom you'll see the Connection Managers pane with your project SQL Connection manager. Select that and hit F4/Right Click->Properties for the Properties Pane to be opened for the Connection Manager. Locate Expressions, then click the blank box to the right and then the ellipsis (...). For Property, select ServerName, and then for the Expression into your Project Parameter you set up previously, in this example that's
@[$Project::SQLInstanceName], and then ok your changesNow your SQL Connection Manager will show a little fx symbol to the left, to show it is "dynamic". Now, when you change the configuration you are in, the project parameter's value will be changed, and along side it the value of ServerName in the Connection manager, meaning that depending on the configuration you're in, the SQL instance your Visual Studio Project connects to will change.
I don't believe this is what he was looking for - it appears to me that he wants to setup a single connection manager for 2 different file formats. This isn't really possible in a simple way because the connection manager is bound to a specific format - and all downstream objects expect the column definitions to be defined based on that file format.
What you could do is create a 'generic' connection manager that reads in each row as a single column. Then - utilize a script component to read in that row and based on the variable/parameter/identifier - parse the row. The output from the script component would have all columns to be filed into the destination table. Whether each column has a value depends on the input file and if that input file has that field/column populated.
Personally - I would create a project and have 2 separate packages. But - managing a single package setup this way shouldn't be any harder to manage and maintain since any changes to a file format need to changed in either the script component or the connection manager (and the connection manager file format editor is quite clunky).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 26, 2020 at 8:07 am
I don't believe this is what he was looking for - it appears to me that he wants to setup a single connection manager for 2 different file formats. This isn't really possible in a simple way because the connection manager is bound to a specific format - and all downstream objects expect the column definitions to be defined based on that file format.
If that is the case, I agree and I have severely misunderstood. You would need 2 connection managers to handle different file definitions.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 26, 2020 at 3:28 pm
Steve, Sorry I thought that the file names were different between the two formats, and by inspecting the file name you would know which version you had.
August 26, 2020 at 3:34 pm
If that is the case, I agree and I have severely misunderstood. You would need 2 connection managers to handle different file definitions.
Or a script component to parse each 'row' of the file based on a parameter, variable, or data element. The connection manager would need to be setup to read each row as a single column - setup as ragged-right (most likely). If the files are delimited - you would have to split the row based on the delimiter and then assign the values to the defined output for the script component based on that parameter/variable. If the row is fixed width - then you would parse out the column values based on a substring.
It is doable...but might be harder to maintain if you are not familiar with script components.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply