Bulk Data Import
-
Ok, this isn't so much a "I need help" question as it is a "what do you think of this" question. I'm building a .net desktop app that accesses it's database through a web service. I need to implement a way for my users to upload large data files into the database on demand. The data files are generated by third parties and their format changes quite frequently. When these files are uploaded, only some columns from the files are uploaded into the DB tables. Now, I've looked at several options and here's what I've decided on. I'm going to create a DTS package and job for each import file and I'm going to fire it off using sp_start_job. I'm then going to poll the job's status using sp_help_job. Does this sound reasonable or can anybody think of a simpler way? It's gotta be flexible and easy to update without having to recompile/redistribute code, which is why I'm going the DTS package route.
-
Ok, this isn't so much a "I need help" question as it is a "what do you think of this" question. I'm building a .net desktop app that accesses it's database through a web service. I need to implement a way for my users to upload large data files into the database on demand. The data files are generated by third parties and their format changes quite frequently. When these files are uploaded, only some columns from the files are uploaded into the DB tables. Now, I've looked at several options and here's what I've decided on. I'm going to create a DTS package and job for each import file and I'm going to fire it off using sp_start_job. I'm then going to poll the job's status using sp_help_job. Does this sound reasonable or can anybody think of a simpler way? It's gotta be flexible and easy to update without having to recompile/redistribute code, which is why I'm going the DTS package route.
Nasty, ugly painful SOB of a thing. I have grown to dislike most of MSs ETL products, Biztalk and SSIS. We have a similar problem which requires the ETL to handle additional columns in a data file that is uploaded daily. SSIS will choke on this and changing a package, redeploying etc AFTER it has choked is not an option. Our solution. The package reads the first line of the file where the column headers are and checks against the target table in SQL. All cols are varchar 500. If there is a new column (it always grows) SSIS drops the target table and passes the column names into a stored proc to recreate the table (some column names are duplicated and this is handled by the proc). The package then bulk copies the data into the target table, it is guaranteed to work because of the previous process. I then have a proc to do the transforms into the production database. I find changing a proc to be easier than a package.
Never underestimate the power of human stupidity RAH
-
Nasty, ugly painful SOB of a thing. I have grown to dislike most of MSs ETL products, Biztalk and SSIS. We have a similar problem which requires the ETL to handle additional columns in a data file that is uploaded daily. SSIS will choke on this and changing a package, redeploying etc AFTER it has choked is not an option. Our solution. The package reads the first line of the file where the column headers are and checks against the target table in SQL. All cols are varchar 500. If there is a new column (it always grows) SSIS drops the target table and passes the column names into a stored proc to recreate the table (some column names are duplicated and this is handled by the proc). The package then bulk copies the data into the target table, it is guaranteed to work because of the previous process. I then have a proc to do the transforms into the production database. I find changing a proc to be easier than a package.
Never underestimate the power of human stupidity RAH
I agree. For some reason (I believe that the reason is between the chair and the keyboard :)) I often find it difficult to implement tasks with "advanced" functionality with SSIS. Usually I use stored procedures and if T-SQL cannot provide enough functionality I typically create an assembly using C# and add that to the database. This way I can get more reusable functionality to the place where it's actually needed (=DB). I think that the problem with SSIS for me is that logic is easily scattered to different modules and it's more difficult to understand later or maintain. Also SSIS is not so powerful that it could be used as "programming platform". However, what comes to Analysis Services I find SSIS usable. It's quite easy to load data from OLTP to cubes so I haven't totally discarded it. Also simple data pumping tasks seem to work fine. What comes to original question, I think your idea is good and if you don't have any difficulties implementing this using SSIS, you're safe. Mika
-
I agree. For some reason (I believe that the reason is between the chair and the keyboard :)) I often find it difficult to implement tasks with "advanced" functionality with SSIS. Usually I use stored procedures and if T-SQL cannot provide enough functionality I typically create an assembly using C# and add that to the database. This way I can get more reusable functionality to the place where it's actually needed (=DB). I think that the problem with SSIS for me is that logic is easily scattered to different modules and it's more difficult to understand later or maintain. Also SSIS is not so powerful that it could be used as "programming platform". However, what comes to Analysis Services I find SSIS usable. It's quite easy to load data from OLTP to cubes so I haven't totally discarded it. Also simple data pumping tasks seem to work fine. What comes to original question, I think your idea is good and if you don't have any difficulties implementing this using SSIS, you're safe. Mika
Mika Wendelius wrote:
if you don't have any difficulties implementing this using SSIS
You've got to be joking, took me 3 days and more googling than I like to think about to identify the shortcomings and work around them. I still prefer DTS! We were in the position of needing to move an SSAS database from the default (also want to stripe it across drives) and went to out outsource DBA support (IBM)for help, "sorry we have no experience in SSAS" WTF. SSAS presents it's challenges as well.
Never underestimate the power of human stupidity RAH
-
Mika Wendelius wrote:
if you don't have any difficulties implementing this using SSIS
You've got to be joking, took me 3 days and more googling than I like to think about to identify the shortcomings and work around them. I still prefer DTS! We were in the position of needing to move an SSAS database from the default (also want to stripe it across drives) and went to out outsource DBA support (IBM)for help, "sorry we have no experience in SSAS" WTF. SSAS presents it's challenges as well.
Never underestimate the power of human stupidity RAH