| Author |
Topic |
|
BLarche
Starting Member
21 Posts |
 Posted - 2013-01-04 : 12:18:12
|
I am trying to create a dynamic dealer feed. Each dealer has it's own text file that they submit. The fields can be in any order and they can have anywhere from 1-30 fields in the file. Once the file is received, it is uploaded into a temporary DB table for processing.I have some setup/link tables to tell my program how to convert the values in the dealer text file. For instance, one field may be a vehicle make (vm_id/vm_name). There is a vehicle make table that has defined makes (Ford, Chrysler, Chevrolet) etc. I need to convert the text value from the dealer text file to the unique ID associated with that field. I created several lookup tables to tie back the columns, fields, etc.Below is my vehicle table that all of this data needs to be transferred into from the temporary table (via text file): The next table is my config table for each dealer. It tells me which field in the text fiel (or temp DB) ties back to which field in the vehicles table above. I'll provide another table below that is the setup table to link the config table with the vehicles table: I also created another table to as a field lookup. This tells the feed what the field name is in the vehicles table (df_v_field_id), what the primary key of the field is (df_f_field_id), what the name of the field storing the value is (df_f_field_name), what the datatype of the field is (df_f_datatype), and what the lookup table of the field is in (df_f_table). The reason I do this, is so that I get consisent data. For instance, I have a vehicle make table (tbl_Vehicles_make). The dealer feed will submit the vehicle make as 'FORD'. I need to convert this to the numeric value in the vehicle make table to store into the vehicles table. The vehicle make ID for 'FORD" is 2. "2" will get stored into the vehicles table in the "vm_id" field. However, in order to do this, I need to convert from text to numeric by looking up "FORD" in the vehicle make table. Below is my temporary table that I am dumbing the data from the text file into (the data is being dumped as it arrives in the text file): I am trying to create a stored procedure to loop through the temp table, do the conversions from text to int where applicable, and insert the data into the vehicles table into the appropriate columns.Does anyone have any suggestions? I started a stored procedure but have loop upon loop trying to grab everything I need for my links/fields/conversions. |
|
|
BLarche
Starting Member
21 Posts |
Posted - 2013-01-08 : 09:02:38
|
| Does anyone have any suggestions? What other information should I provide to get this solution rolling? |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-08 : 23:57:03
|
| why do you need a loop here? sounds like simple set of lookups for me which you can implement by means of joins. didnt understand the deal with field lookup table though. do you mean same field can contain values from any reference tables?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
BLarche
Starting Member
21 Posts |
Posted - 2013-01-09 : 08:31:52
|
| We have hundreds of dealers. Each dealer submits a dealer feed text file to us. My vehicles table contains all of the details for each vehicle in the DB. Within the vehicle table, there are several fields that tie back to other tables primary ID field. For instance, vehicle make in the dealer feed will come in as "Chevrolet". In my vehicles table, I don't store the vehicle make's name, but rather the ID of say "1". So I need to convert the name "Chevrolet" to "1" to insert into my vehicles table.The trouble is, I don't know what order the data is being submitted by the dealer feed text file. For instance, one dealer may submit in the following order:Vehicle IDVehicle MakeVehicle YearVehicle MileageVehicle DescriptionAnother dealer will submit in this order:Vehicle MileageVehicle DescriptionVehicle MakeVehicle YearVehicle IDMy tbl_DealerFeed_link table defines which order each dealer is submitting the information to me in their text file. I need this so that I can properly insert the fields into my tbl_Vehicles table. I also need the tbl_DealerFeed_fields table to tell me whether or not I need to convert the value being submitted in the text file to an integer (primary key) value (Chevrolet to 1).Does this make more sense? |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2013-01-09 : 20:05:09
|
Do the dealers have anything in the files that identifies what the column names would be?--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." When writing schedules, keep the following in mind:"If you want it real bad, that's the way you'll likely get it." |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2013-01-09 : 20:37:05
|
p.s.All those "TEXT" datatypes are going to crush your performance not to mention making your life totally miserable further down the line. If you can't actually right-size the columns, at least use the MAX datatypes especially since the "TEXT" datatype has been deprecated by MS.--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." When writing schedules, keep the following in mind:"If you want it real bad, that's the way you'll likely get it." |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-09 : 22:31:32
|
quote:
Originally posted by BLarche
We have hundreds of dealers. Each dealer submits a dealer feed text file to us. My vehicles table contains all of the details for each vehicle in the DB. Within the vehicle table, there are several fields that tie back to other tables primary ID field. For instance, vehicle make in the dealer feed will come in as "Chevrolet". In my vehicles table, I don't store the vehicle make's name, but rather the ID of say "1". So I need to convert the name "Chevrolet" to "1" to insert into my vehicles table.The trouble is, I don't know what order the data is being submitted by the dealer feed text file. For instance, one dealer may submit in the following order:Vehicle IDVehicle MakeVehicle YearVehicle MileageVehicle DescriptionAnother dealer will submit in this order:Vehicle MileageVehicle DescriptionVehicle MakeVehicle YearVehicle IDMy tbl_DealerFeed_link table defines which order each dealer is submitting the information to me in their text file. I need this so that I can properly insert the fields into my tbl_Vehicles table. I also need the tbl_DealerFeed_fields table to tell me whether or not I need to convert the value being submitted in the text file to an integer (primary key) value (Chevrolet to 1).Does this make more sense?
why cant you standardize the order (metadata) of files? This approach will have all sorts of issues to deal to as fields vary in datatypes. So validation checks etc can be a real pain.A much better approach is to decide on common standardized file structure with dealers and dispense with lookup table altogether.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
BLarche
Starting Member
21 Posts |
Posted - 2013-01-10 : 09:04:13
|
The dealers provide a template file that tells me which fields are which in their DB. Using that file, I then tie back their fields to my fields using the tbl_DealerFeed_links table. visakh16: I cannot standardize the way dealers submit their feeds. They are all independent and they submit their feeds to more than one website. Most will not work with you in standardizing to your format. That is why we need to work around this and create lookups/links to tie the fields back together. |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2013-01-10 : 19:13:57
|
It's pretty simple then. Just make a BCP format file for each dealer.--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." When writing schedules, keep the following in mind:"If you want it real bad, that's the way you'll likely get it." |
|
|
|
BLarche
Starting Member
21 Posts |
Posted - 2013-01-11 : 08:37:42
|
| Thanks Jeff. I looked up how to use format files for each dealer. My only question is how to I convert the values to my unique identifiers for the sub-tables? For instance, I receive "Chevrolet" as the data value and I need to tell my format file to convert this to "1" to insert into the main DB table. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-11 : 08:45:33
|
| nope...insert into temp table and then do a join to get id from master table------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
BLarche
Starting Member
21 Posts |
Posted - 2013-01-11 : 11:51:29
|
| Gotcha. The format file is to insert the values into the temp table. I'll give that a whirl. Thanks! |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2013-01-11 : 15:33:43
|
quote:
Originally posted by BLarche
Gotcha. The format file is to insert the values into the temp table. I'll give that a whirl. Thanks!
Correct. These types of things are called "staging tables". It's actually a good habit to get into because you never know what you're going to get in a data feed. It's almost always best to load the data into a staging table first, validate the data, figure out what the "merge" will be, and then "copy" from staging to the final tables.--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." When writing schedules, keep the following in mind:"If you want it real bad, that's the way you'll likely get it." |
|
|
|
BLarche
Starting Member
21 Posts |
Posted - 2013-01-11 : 16:54:27
|
| I ran into a problem. I do not have permission to run a bulk load statement as I am using a shared DB."To do what you would requesting would mean that we would have to elevate your permissions above DBO to a bulkadmin role. We do not do this on a shared SQL server account. We will not do this for anyone else either. In a shared database world you have to accept what is fair for everyone. "Now what? |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2013-01-12 : 10:46:46
|
Heh... that's a load of hooie. The BulkAdmin role certainly isn't above those of DBO.Ask them if you can have a job run the proc for you. And ask if it's ok to use SSIS. If neither is ok, tell them the job can't be done.They should take on a better attitude. Like my old systems DBA used to say to me... "You find a way to write it... I'll find a way to run it."--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." When writing schedules, keep the following in mind:"If you want it real bad, that's the way you'll likely get it." |
|
|
|
BLarche
Starting Member
21 Posts |
Posted - 2013-01-12 : 11:37:44
|
| This was the full response. I am not familiar with SSIS."To do what you would requesting would mean that we would have to elevate your permissions above DBO to a bulkadmin role. We do not do this on a shared SQL server account. We will not do this for anyone else either. In a shared database world you have to accept what is fair for everyone. If you want to do this locally tell us what database and server database we are talking about and we will get you a backup of the database you do it locally then back it up and send it back to us and we will restore it. Another option is to connect and use SSIS to import the data from your local server. In any case it requires SA to do create database rather than create temporary tables which you can do." |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2013-01-12 : 12:45:50
|
Write the query as a stored procedure and have them execute it as a scheduled job. It'll run without giving anyone any extra privs.I also think that it's stupid that they'll allow you to use SSIS for this... you can mess up a whole lot more with SSIS than you ever could with simple BulkAdmin privs.Shifting gears, my recommendation would be that everyone in your company needs to recognize that this job must be done. There are a half dozen different ways to do it and the easiest (perhaps safest) way to do it is through a scheduled job. The "team" needs to come together and make it happen. If it costs the DBA team a little work, they probably won't die from the effort.--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." When writing schedules, keep the following in mind:"If you want it real bad, that's the way you'll likely get it." |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-13 : 22:59:06
|
| Even in case of SSIS, you dont require any elevated access to your account. You can ask them to setup a proxy account with required permissions and configure a job to use the proxy account which calls the package to do the required functions------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
BLarche
Starting Member
21 Posts |
Posted - 2013-01-14 : 10:14:54
|
| When I load it as a stored procedure, I still do not have the necessary permissions. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-15 : 00:08:37
|
| what do you mean by load it as a stored procedure? DO mean executing SSIS from stored procedures?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
Dynamic Dealer Feed From Text File