How can I use parameters in a connection file manager and source in ssis 2015 to load 2 different file definitions

How can I use parameters in a connection file manager and source in ssis 2015 to load 2 different file definitions with variable names from the same directory into sql server.

I want to define connection managers, parameter(s), file sources, and for each loop to load 2 different .txt file definitions into sql server.

the 2 different defintions are a seperate files named with a date time stamp at the trailing end of the file name:
def1 = posdef<dtstamp>.txt
def2 = posdef2<dtstatmp>.txt

both files are loaded to the same directory periodically.
I don't understand how to implement a combination(s) of params to account for varaible file names and different definitions to load using for each loop(s) to load into different tables in an ssis package
conardbAsked:
Who is Participating?
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Okies, if you want to go head with foreach loop and dynamic connections below can help.

http://www.sqlerudition.com/example-of-ssis-foreach-loop-item-enumerator-and-dynamic-oledb-connection/
0
 
Pawan KumarDatabase ExpertCommented:
Well in this case you have to use string Variable say FileName & Flat File Connection Manager. Below Blog URL will help you in step by step -

http://beyondrelational.com/modules/2/blogs/106/posts/11113/ssis-dynamically-set-flat-file-connection-manager.aspx

You just have to follow these simple steps.
0
 
conardbAuthor Commented:
Thanks Pawan, I don't think that will work.  The two different definitions will be in two different files.  Each will have a different name.  The difference will be a time stamp when the file was generated so the name can't be stored in a table and they will have the same unique leading characters for each file def.  file1<timestamp>.txt. filename2<timestamp>.txt.

I'm thinking I need to be able to do a for reach loop through directory that has a seperate connection to each of the two file def by unique leading characters.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
conardbAuthor Commented:
I was thinking use the fully qualified path+file name and process the files based on a substring of the path that uses the leading file name characters
0
 
conardbAuthor Commented:
Thanks, this looks to take table data in a database and write to text files.  I'm need to take text files from a directory and load into sql server table and I need to determine the connection or source in the ssis package by the leading characters of the text file name.
0
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
I'm not a SSIS expert but already created some packages before.
Are you using a Flat File Connection Manager? If so you can set a variable that will produce dynamically the file name by using T-SQL only in the expression. Example:
'File1'+YEAR(GETDATE())+MONTH(GETDATE())+DAY(GETDATE())+'.txt'
0
 
conardbAuthor Commented:
Yes, I'm using the flat file connection manager.  The flat files are the source, so I have the connection manager, for each loop container, dataflow, source in the data flow.  There may be 10 files for example with two file defintion / file types.  Each file def/type is indicated the the prefix or starting char of the file name (file1<timestamp>.txt, file2<timestame>.txt)   The record length and columns are different for each file def
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Good. Do you have any variable set?
For your case you'll need to have a variable for each file.
In the example I gave above you'll keep the file name as File1YYYYMMDD.txt so you can work from there.
0
 
conardbAuthor Commented:
what are the settings for the connection manager and source.  That's where I keep having issues is with the connection mgr and source specifying the record length
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
what are the settings for the connection manager and source.
What do you mean? Can you show the settings that you're having now?
0
 
conardbAuthor Commented:
I have a connection manager and flat file source for each file definition.
A-connection-manager-for-each.docx
0
 
conardbAuthor Commented:
here's the loop settings and var settings
ForEachLoop-Properties.docx
0
 
conardbAuthor Commented:
cannot find specified file
cannot-find-file.docx
0
 
conardbAuthor Commented:
I think I got it now...
0
 
Pawan KumarDatabase ExpertCommented:
Great..
0
 
conardbAuthor Commented:
used a single var for folder path, 2 vars for each file def: one for filename and one for folder path and file combination for a total of 5 vars and 2 for each loops... initially, I had tried to use the multi file connection in an attempt to avoid multiple connections and variables per file def, I'd be interested in knowing if there is someway to use one connection manager for multiple sources or would a connection manager and source always be required for each file def.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.