Dynamically populating an IN() clause within an SSIS package.

Hi,

I currently have a list of User IDs (in a flat file) and I need to connect to a database I have read-only access to, so that I can retrieve additional data about these users.

I imagined a package that ran a query something like:

SELECT * FROM table WHERE UserID IN (<dynamically populated from flat file>).

Can somebody give me some advice as to how I can achieve this (either the way I suggested or another way).

Kind Regards,

Adam.

First thought would be to do it on the server if you have access to the file. Create a temp table bulk insert the file into it then join to the temp table for the query.|||I think the simplest solution is to use a script task to read your file and create the list of users for an IN clause as you mention above. You'd put the list into a variable, and then build your query in an expression-based variable, and set your ole db source's data access mode to "sql command from variable".

Maybe the script would look something like this:

Code Snippet

Public Sub Main()
Dim UserNames() As String = System.IO.File.ReadAllLines(Dts.Variables("FileName").Value.ToString())
Dim s As New System.Text.StringBuilder
Dim IsFirst As Boolean = True
For Each UserName As String In UserNames
If Not IsFirst Then
s.Append(",")
End If
s.Append("'")
s.Append(UserName)
s.Append("'")
IsFirst = False
Next
Dts.Variables("InList").Value = s.ToString()
'Windows.Forms.MessageBox.Show("InList = " + Dts.Variables("InList").Value.ToString())
Dts.TaskResult = Dts.Results.Success
End Sub

|||

2 more:

Have a for each loop to iterate through the file to get each value; then inside of the conatiner have the query logic using and equi-join (=). The thing is that you would run the query as many times as values in the file Have an script task to read the file and build the query; put it in a variable; then you can use that variable as the source uof your query in a Excute SQL task or OLE DB source component.|||

AdamSQLMan wrote:

Hi,

I currently have a list of User IDs (in a flat file) and I need to connect to a database I have read-only access to, so that I can retrieve additional data about these users.

I imagined a package that ran a query something like:

SELECT * FROM table WHERE UserID IN (<dynamically populated from flat file>).

Can somebody give me some advice as to how I can achieve this (either the way I suggested or another way).

Kind Regards,

Adam.

There are many ways to accomplish this. My first thought is this:

Use a data flow to load the flat file, and output it to a data reader destination. Then use a script task to process the data reader into a comma delimited string, and use that in an expression to build your query for use in a second data flow.

|||

NigelRivett wrote:

First thought would be to do it on the server if you have access to the file. Create a temp table bulk insert the file into it then join to the temp table for the query.

If the number of user name is large, then this is probably better than using an IN statement.
|||

JayH wrote:

NigelRivett wrote:

First thought would be to do it on the server if you have access to the file. Create a temp table bulk insert the file into it then join to the temp table for the query.

If the number of user name is large, then this is probably better than using an IN statement.

I Agree.

|||

This would make a good interview question. How would you filter a resultset from a list of IDs in a text file.

Whatever the first answer is then ask what you would do if the tool suggested wasn't available. If the file was a lot bigger than you thought, if it might have invalid data etc.