13 Replies - 1058 Views - Last Post: 30 July 2018 - 09:30 AM

#1 seniorCSGuy   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 02-March 18

SQL server and SSIS Package

Posted 25 July 2018 - 11:00 AM

I have a SSIS package and it generates a report but I have to change the content of a variable each time manually to get the appropriate report. My goal is to iterate through a specific folder and extract part of the file name(s) as it contains the content I replace to run the package. It should be able to extract part of each filename one at a time, replace the content run the package and repeat the process until is done with all appropriate files in the folder. I am trying to use a for each container to do this. What are the steps that I should take to accomplish this?

Is This A Good Question/Topic? 0
  • +

Replies To: SQL server and SSIS Package

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15079
  • View blog
  • Posts: 60,221
  • Joined: 12-June 08

Re: SQL server and SSIS Package

Posted 25 July 2018 - 12:03 PM

Are you looking to do this in some sort of way to do interact? A winform, webform, etc?
Was This Post Helpful? 0
  • +
  • -

#3 seniorCSGuy   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 02-March 18

Re: SQL server and SSIS Package

Posted 25 July 2018 - 12:05 PM

No, Just locally for now
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15079
  • View blog
  • Posts: 60,221
  • Joined: 12-June 08

Re: SQL server and SSIS Package

Posted 25 July 2018 - 12:11 PM

Is this folder on the actual DB server, or elsewhere?

EX:
https://www.mssqltip...ation-services/
http://www.sqlserver...s+(SSIS)/67871/
http://www.sqlserver...s+(SSIS)/91665/
Was This Post Helpful? 0
  • +
  • -

#5 seniorCSGuy   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 02-March 18

Re: SQL server and SSIS Package

Posted 25 July 2018 - 12:16 PM

It is inside the package folder
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6796
  • View blog
  • Posts: 28,083
  • Joined: 12-December 12

Re: SQL server and SSIS Package

Posted 25 July 2018 - 12:28 PM

Are you talking about a batch file or PowerShell script, for example?
Was This Post Helpful? 0
  • +
  • -

#7 seniorCSGuy   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 02-March 18

Re: SQL server and SSIS Package

Posted 25 July 2018 - 12:47 PM

Probably a C# or VB script because it is compatible with SSIS
Was This Post Helpful? 0
  • +
  • -

#8 DarenR   User is online

  • D.I.C Lover

Reputation: 615
  • View blog
  • Posts: 4,062
  • Joined: 12-January 10

Re: SQL server and SSIS Package

Posted 26 July 2018 - 07:55 AM

i am confused-- you said you arent making a page to run this but then say you want to use c# please elaborate how you are going to use c# but not create a form
Was This Post Helpful? 0
  • +
  • -

#9 seniorCSGuy   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 02-March 18

Re: SQL server and SSIS Package

Posted 26 July 2018 - 11:39 AM

I have a SSIS package my goal is to go through each file in a folder extract part of the filename and save the extracted part to a variable go through the package to finish the steps and move the processed file to a different folder. I need to do this for each file in the folder. Can anyone guide me?

Thank you
Was This Post Helpful? 0
  • +
  • -

#10 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6796
  • View blog
  • Posts: 28,083
  • Joined: 12-December 12

Re: SQL server and SSIS Package

Posted 27 July 2018 - 12:52 AM

View PostseniorCSGuy, on 25 July 2018 - 07:47 PM, said:

Probably a C# or VB script because it is compatible with SSIS

If you will use VBScript (or a batch file or PowerShell script) then all of these have the capability to iterate through files in a folder. A C# console application could do the same.

Decide which kind of script you will write and it isn't hard to find code that demonstrates iterating through files in a folder.

Once you have made a little progress you could come back if you have a more specific question.
Was This Post Helpful? 0
  • +
  • -

#11 seniorCSGuy   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 02-March 18

Re: SQL server and SSIS Package

Posted 30 July 2018 - 09:09 AM

I am not able to extract filename and save it to a variable in SSIS. I am trying to save a sub string of a filename if and only if it matches the pattern in regex in VIncident.

 public void Main()
        {
            String source = @"F:\";
            String destination = @"F:\";
            Regex regex = new Regex(@"EIRSRequest_(?<val>[a-zA-Z0-9]{1,})");
            DirectoryInfo di = new DirectoryInfo(source);
            //FileInfo fi = new FileInfo(di.GetFiles);
            foreach (FileInfo fi in di.GetFiles())
            {
                var match = regex.Match(Path.GetFileNameWithoutExtension(fi.Name));
                if (match.Success)
                {
                    //Console.WriteLine(match.Groups["val"].Value);
                       Dts.Variables["User::vIncident"].Value= match.Groups["val"].Value;
                    //Console.WriteLine(ticket);
                    File.Move(source + "\\" + fi.Name, destination + "\\" + fi.Name);
                    //Console.WriteLine("This is a new line");
                    break;


                }
            }
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }




This post has been edited by seniorCSGuy: 30 July 2018 - 09:11 AM

Was This Post Helpful? 0
  • +
  • -

#12 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15079
  • View blog
  • Posts: 60,221
  • Joined: 12-June 08

Re: SQL server and SSIS Package

Posted 30 July 2018 - 09:28 AM

Please do not create duplicate topics. Merging.

:/ Hmm. Seems like the same post.
Was This Post Helpful? 0
  • +
  • -

#13 seniorCSGuy   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 02-March 18

Re: SQL server and SSIS Package

Posted 30 July 2018 - 09:30 AM

View Postmodi123_1, on 30 July 2018 - 09:28 AM, said:

Please do not create duplicate topics. Merging.

://>


Sorry about that the
Was This Post Helpful? 0
  • +
  • -

#14 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15079
  • View blog
  • Posts: 60,221
  • Joined: 12-June 08

Re: SQL server and SSIS Package

Posted 30 July 2018 - 09:30 AM

I'll just remove one of the two.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1