SSIS Package auto reading files

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

SSIS Package auto reading files

Author

Topic

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2011-01-25 : 08:29:54

Hi,

I have a table which I would the SSIS package to read a file based on the location intput in that table:

So for example

Table1.
fileid, location, status
1, c:\file1.txt, n
2, c:\file2.txt, y

So how would I go about getting SSIS to pick up the file based on statement

select location
from table1
where status = 'y'

or something like that. Instead of hardcoding a location in the txt file connection manager in SSIS.

Thanks in advance.

ZZartin
Starting Member

30 Posts

Posted - 2011-01-25 : 16:50:21

Have you tried pulling the file name into a variable using an execute sql task then using an expression on the file's connection manager to set the connection string on the file?

SSISJoost
Starting Member

9 Posts

Posted - 2011-01-27 : 01:56:54

1) Use a Execute SQL task to get the list of files. Store te result in a variable
2) Use a Foreach loop to loop through the records in your variable. Store the file path in a variable
3) Add a Data Flow task within the loop and use the variable of step 2 the overwrite the value of connection string(with an expression)

(of cource this only works if the file have the same structure, but there are plenty of Foreach loop examples on the net)

Joost
http://microsoft-ssis.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources