Skip to content
+1-888-319-3663

COMMUNITY FORUM

Get source filename

Dan Tomlin asked 5 years ago
I’m importing data from a csv file into a sql table.  One of the fields in the sql table is the “file source” which is the csv filename as multiple files are imported into this table and we want to be able to reference where the data came from.   How to get the file name mapped to the “file source” field in the sql table?
I think I would create a calculated column but can’t figure out the script..
 
Thanks, Dan
Answers
Patrick Roth Staff answered 5 years ago
Dan,
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
 
Dan replied 5 years ago

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?

Daniel replied 5 years ago

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….

Filename in Sql field replied 3 years ago

I have the same question. Has this been fixed in a later version?
Thanks,
Tanya


If you would like to submit an answer or comment, please sign in to the eOne portal.