SSIS Script component input

Question

I have an SSIS package with a C# script component. The script processes some documents and extracts information to several SQL tables. Combining the tables is not an option. The script runs perfectly aside from taking a long time to process all the files. Each file contains the date they were last modified, my solution is to store that date in a SQL table and then the next time the package is run to retrieve the previous dates and compare. If a file has not been modified then the date will be the same and the file skipped.

Unfortunately I am unable to figure out how to bring in the SQL table. The connections and script component settings appear to be correct but my C# code does not work. Ideally I would like to bring the table in as either a dictionary or list. The table itself consists of only two columns: file name, last modified date.

Answer 1

Follow this steps:

1. Add a Execute SQL Task in which you select all data from the Date Table

   Select * From [Table]
   

2. Store the result in a Object Variable by selecting it as ResultSet

3. Then load this variable inside the script

For more information, read the following detailed articles:

• SSIS Basics: Using the Execute SQL Task to Generate Result Sets
• Map Result Sets to Variables in an Execute SQL Task
• SSIS - How To Read Object Type Variable In Script Task [Script Task SSIS] This article contains a very similar example
• Using Variables in the Script Task