Get source filename
I think I would create a calculated column but can’t figure out the script..
There isn’t a way to reference this inside of SmartConnect – we don’t expose the source file selected as any kind of variable. Would be nice sometimes though.
However you should be able to get it out of SQL.
When a map is run, it is saved (in case you changed it).
It writes the definition of the map back which includes the data source file. SO in your case a “text file data source”. And so in the map definition somewhere it has to store that path.
So all you need to do is look that path up.
Finding stuff in the SC tables is a bit challenging to be honest – I spend time digging myself.
So for a text file, I could write the query for the SmartConnect database.
select a.Path from TextFileDataSource a
inner join BulkDataSourceBase b on
a.TextFileDataSourceId = b.BulkDataSourceBaseId
where b.MapId = ‘FIXED’
where FIXED is my map id in question
now you could run this as a Map Pre task and run it in a SQL Task and return the results back to a global variable.
Then use a calculation to read that global variable.
Or just make a calculation with vb.net and script a connection to SQL and run it that way.
Either way should work
That might work on when it is the same file name but I have to use the “folder data source” because the file name change everyday based on the date… when does it write the data source, each time the map is run?
I did some testing and your suggestion works when I have the same file every time but when I use the “folder data source”, it doesn’t work because that information is not written to those tables to get the data. Is there a table that stores the information? The program changes the file name so it must be reading it….
I have the same question. Has this been fixed in a later version?
If you would like to submit an answer or comment, please sign in to the eOne portal.