SSIS - 13 or more different files from the same directory

Question

SSIS - 13 or more different files from the same directory

Naomi Nosonovsky 8,371

Good morning,

I'm trying to figure out a best SSIS solution for the following problem. I have 13 or more input files of different structure. I already designed 13 data flow tasks for these text files and I used string variables file1, file2, etc. for connection string property for each of these files. However, right now the names are hardcoded. I want to automatically assign the names based on each file pattern, say, adr_.txt, adrlang_.txt, etc.

What would be the best way to achieve this functionality?

I have another question for the same files. All of them have a footer in the format of 99|00000630

where 630 will be the number of rows. How do I design the file connection for the footer and do I need 13 separate flat file connections?

Thanks in advance.

Accepted answer

0 additional answers

Your answer

Answer 1

Azar 28,155 MVP Moderator

Hi there Naomi Nosonovsky

Thanks for using QandA platform

You can use a Foreach Loop Container in SSIS to loop through files matching patterns like adr_*.txt, storing the path in a variable and using expressions to set each connection's ConnectionString. Since the file structures differ, you’ll still need 13 separate Flat File Connections. For footers like 99|00000630, handle them in a Script Component by skipping rows that start with 99|, or preprocess the file in a Script Task to remove the footer before loading.

If this helps kindly accept the answer thanks much

Naomi Nosonovsky 8,371 Reputation points

2025-04-29T20:27:36.1666667+00:00

Hi Azar,Do I need 13 loop containers or how can I set my 13 different file names accordingly? Each file starts with different pattern name and I need to assign variables accordingly.

Naomi Nosonovsky 8,371

I added a script task but it is failing with the following error:

at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)

at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)

at System.Reflection.RuntimeMethodInfo.Invoke(Object ob j, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)

at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)

at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

This is the script task code:

{
            //User::fileAdr,User::fileAdrLang,User::filePatc,User::filePDea,User::filePNC,User::fileProv,
            //User::fileProvLic,User::fileProvNPI,User::fileProvSpcl,User::filePta,User::fileSpclTyp,User::fileTaxIdNbr,User::fileTelCmnct

            string fileName = Dts.Variables["User::fFileName"].Value.ToString();

            fileName = fileName.ToUpper();

            if (fileName.Contains("ADR_"))
                Dts.Variables["User::fileAdr"].Value = fileName;

            if (fileName.Contains("ADRLANG_"))
                Dts.Variables["User::fileAdrLang"].Value = fileName;

            if (fileName.Contains("PATC_"))
                Dts.Variables["User::filePatc"].Value = fileName;

            if (fileName.Contains("PDEA_"))
                Dts.Variables["User::filePDea"].Value = fileName;

            if (fileName.Contains("PNC_"))
                Dts.Variables["User::filePNC"].Value = fileName;

            if (fileName.Contains("PROV_"))
                Dts.Variables["User::fileProv"].Value = fileName;

            if (fileName.Contains("PROVLIC_"))
                Dts.Variables["User::fileProvLic"].Value = fileName;

            if (fileName.Contains("PROVNPI_"))
                Dts.Variables["User::fileProvNPI"].Value = fileName;

            if (fileName.Contains("PROVSPCL_"))
                Dts.Variables["User::fileProvSpcl"].Value = fileName;

            if (fileName.Contains("PTA_"))
                Dts.Variables["User::filePta"].Value = fileName;

            if (fileName.Contains("SPCLTYP_"))
                Dts.Variables["User::fileSpclTyp"].Value = fileName;

            if (fileName.Contains("TAXIDNBR_"))
                Dts.Variables["User::fileTaxIdNbr"].Value = fileName;

            if (fileName.Contains("TELCMNCT_"))
                Dts.Variables["User::fileTelCmnct"].Value = fileName;



            Dts.TaskResult = (int)ScriptResults.Success;

Do you see the error or how can I get frendlier error message ?

Naomi Nosonovsky 8,371 Reputation points

2025-04-29T21:52:28.6866667+00:00

Never mind, found typo at the top of the script.

Share via

SSIS - 13 or more different files from the same directory

0 additional answers

Your answer