SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Multiple parameters for a query


Multiple parameters for a query

Author
Message
apache626
apache626
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 67
I receive a flat file which I am importing with SSIS. The problem is the file does not contain a needed field(ssn). I have to query another table to find the ssn information. As an example, I receive a file with about 250 records. These records have firstname, lastname, and dob. I need to use this information to query the other table to find the SSN and add that to a new table. So for each record I must find the corresponding SSN located in the other table. Does anyone have a good idea about how to approach this?

Thanks in advance.
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15171 Visits: 14396
I think you'll want to use the SSIS Lookup Transformation. There are many ways yo configure it to suit your scenario. Most considerations surround the number of records in the incoming file and the number of rows in the lookup table. Post here, or post a new topic if you have more questions related to setting it up.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8735 Visits: 7660
apache626 (9/7/2012)
I receive a flat file which I am importing with SSIS. The problem is the file does not contain a needed field(ssn). I have to query another table to find the ssn information. As an example, I receive a file with about 250 records. These records have firstname, lastname, and dob. I need to use this information to query the other table to find the SSN and add that to a new table. So for each record I must find the corresponding SSN located in the other table. Does anyone have a good idea about how to approach this?

Thanks in advance.


I agree with OPC on this one, the lookup is the component you'll want for SSIS... however, this is inverted to expected row counts (something he mentioned). Lookup works best at many inbound rows to few checked, not few inbound rows to billions checked... because it has no indexing.

My recommendation with this few records being imported would be to complete your import and then immediately run a standard update T-SQL script against the data to review the index on your main table to add the SSN to the information. Without an index this will be a horribly painful operation either way. Make sure you've got a nonclustered index on the lookup table on DoB, First, Last and INCLUDE the SSN column so you have the tightest lookup available for your update.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15171 Visits: 14396
Evil Kraig F (9/7/2012)
apache626 (9/7/2012)
I receive a flat file which I am importing with SSIS. The problem is the file does not contain a needed field(ssn). I have to query another table to find the ssn information. As an example, I receive a file with about 250 records. These records have firstname, lastname, and dob. I need to use this information to query the other table to find the SSN and add that to a new table. So for each record I must find the corresponding SSN located in the other table. Does anyone have a good idea about how to approach this?

Thanks in advance.


I agree with OPC on this one, the lookup is the component you'll want for SSIS... however, this is inverted to expected row counts (something he mentioned). Lookup works best at many inbound rows to few checked, not few inbound rows to billions checked... because it has no indexing.

My recommendation with this few records being imported would be to complete your import and then immediately run a standard update T-SQL script against the data to review the index on your main table to add the SSN to the information.

I agree, if the destination of your incoming data and the location of the SSN are on the same instance. My pointer towards using the Lookup Transformation assumed the destination of your incoming data and the location of the SSN were on different instances, i.e. not reachable via T-SQL and excluding Linked Servers.

Without an index this will be a horribly painful operation either way. Make sure you've got a nonclustered index on the lookup table on DoB, First, Last and INCLUDE the SSN column so you have the tightest lookup available for your update.

If the data is on separate instances (i.e. proceeding with the Lookup Transformation) and you'll be searching a ton of data when doing the SSN lookup then I recommend disabling cache altogether and letting SSIS do the 250 sequential lookups. With that few lookups to do there is no sense in caching any of the table that carries the SSN if that table is large. Having an index on the SSN table as Craig mentioned to keep lookup time to a minimum will be critical to the overall performance of the Transformation.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88830 Visits: 41134
apache626 (9/7/2012)
I receive a flat file which I am importing with SSIS. The problem is the file does not contain a needed field(ssn). I have to query another table to find the ssn information. As an example, I receive a file with about 250 records. These records have firstname, lastname, and dob. I need to use this information to query the other table to find the SSN and add that to a new table. So for each record I must find the corresponding SSN located in the other table. Does anyone have a good idea about how to approach this?

Thanks in advance.


Just curious... what company are you working for?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
apache626
apache626
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 67
I think the issue that I'm having is how to use multiple sets of parameters. For example in C# i would have an array and say foreach "set of firstname, lastname and dob" select the corresponding ssn and then i would put the results in an array. I'm not understanding how to say this in tsql. To answer your question Jeff a hospital system based out of California.

Thanks
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15171 Visits: 14396
In SQL an array is modeled as a Table.

Parameters to a stored procedure are more like arguments to a method in C#, however there is no concept of overloading in T-SQL.

Starting with SQL 2008 you can pass tables to a stored procedure as a parameter, lookup Table-Valued Parameters for more info there.

You never told us whether the destination of your flat file and the SSN data are on the same instance, or different instances.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15171 Visits: 14396
I just realized you posted in a SQL 2005 forum for which Table Valued Parameters (TVPs) are not an option.

Some options for passing a table (i.e. an array) to a stored procedure as a parameter in SQL 2005 are:

- pass an xml document parse it as a table in your stored procedure
- pass a delimited string parse it as a table in your stored procedure

I have used both of these methods myself and have passed it along to a lot of other people on these forums. Here is the most comprehensive research I have found to date on the topic tailored specific for SQL 2005:

Arrays and Lists in SQL Server 2005 and Beyond by Erland Sommarskog

Here also is the updated version for 2008 which details TVPs:

Arrays and Lists in SQL Server 2008 by Erland Sommarskog

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88830 Visits: 41134
apache626 (9/10/2012)
To answer your question Jeff a hospital system based out of California.


Then, depending onn how the SSN's are stored, you may have a much larger problem than you think. From the sounds of it, the SSN's are stored as clear text. In other words, not encrypted and, if it's not against the law, it should be. I know for sure that it violates PCI and would never pass such an audit. My recommendation would be to alert management to the problem of having SSNs stored as clear text.

As for matchinng SSNs by name and DOB, it's nearly impossible to do such a thing with 100% accuracy even if a human gets involved because there are so many permutations as to how a name may appear not to mention the fact that people with the same name can also have the same DOB. That, notwithstanding, the first place to start would simply be to join the new table with the existing table based on the first name, last name, and DOB and use that join to update the new table.

Going back to the SSN problem, it would be far better to assign a "customer number" or some such instead of perpetuating the problem of having SSNs in clear text.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15171 Visits: 14396
Jeff Moden (9/10/2012)
the first place to start would simply be to join the new table with the existing table based on the first name, last name, and DOB and use that join to update the new table.

I agree, assuming the SSN data is on the same instance where the flat-file is headed.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search