September 8, 2008 at 7:54 pm
Hi Everyone,
In the past I've always stuck with what I knew because there is usually NO time to try and learn something new to solve a problem even if in the long run it's a better solution. So almost every data import I've done in the past has required me to build a VB6 application to do the job.
Most of my experience has been in the SQL 2000 environment and now I want to get my hands dirty learning the tools that come with 2005 (and eventually 2008) and wondered if anyone could help me determine if there was a better way using the tools that MS gives us than the way I did it.
Here's an actual project I've completed using VB6 to import and then export the requested user data with an UI to excel. Was there a better more efficient way to work this project?
Customer has an excel sheet that has data from 1/1/1990 to today. The data is in this format:
SHEET1 (2 YR), SHEET 2 (3 YR), etc. (5 sheets total - each sheet holding the bonds that mature in different lengths)
THEN,
DATE, BOND 1, BOND 2, BOND 3, etc. in columns A, B, C, D, etc.
ROW 1: GROUP INFO. Using background color and labels to show columns belong to SECTOR groups. Like columns G-J might be TREASURIES
ROW 2: Issuer information
ROW 3: Cusip
ROW 4, 8 more header information
ROWS 9-2500 hold actual bond market data for each BOND starting from COL B-CX (so far)
Daily this information is added to for each bond and it would be crazy to rebuild the table each time.
Using a tool I built in VB6 I create a table (initial load of data) adding a column to the table for each col in excel calling them COLUMN_0, COLUMN_1, COLUMN_2, etc. (With column_0 being the date)
I also created a table called COLUMN_INFO which holds the HEADER info for each column.
Table 1 called SHEET_1, Table 2 called SHEET_2, etc. in the db holds the date and then the data for each column.
When reporting, I have to pull the HEADER information from COLUMN_INFO and attach to query.. so SELECT COLUMN_0 [Date], COLUMN_1 [Par Trsy 2Y Aaa/AAA], COLUMN_2, [Par AGY 2Y Aaa/AAA], etc.
A. Can SSIS import this data following the above layouts (esp redirect header lines into separate table)
B. Can SSRS report on this data allowing the user to select which columns they want to see and also joining header information to the column name the way I did above?
Thanks so very much! 🙂
-David
September 9, 2008 at 5:35 am
The answer is sort-of.
You could achieve the same importing and reporting end-products, but you would probably have to re-think your approach significantly.
First, SSIS is not a product designed for dynamic schema changes. Having it create and populate a table that will have a different number of columns on a regular basis is not something it is going to react to well. In SSIS each data flow component has input and output definitions that include the full definition of every column. So, from the standpoint of importing the spreadsheet, the number of columns constantly changing would really be a pain. It could be dealt with using a script component (VB.Net), a custom data source (built with any .Net language), or through some tricks with excel data sources that gets the provider to import every possible column rather than just the ones that have data. Regardless of the solution, you would probably need to change your table structure to something completely normalized to make each "column" you are dynamically creating into it's own record so schema does not have to change on you during the import process.
On the reporting services end, setting column headers based on data is pretty easy actually. Most of the time when a report is created, you will do what the wizard does, simply type the column names into the cells and call it a day. A textbox on a report can get data from any data source, so you can have a second query that returns your column header information and just use that for the column header source. Dynamic columns can also be a bit tricky in reporting services, but they are not impossible. If you have a set number of possible columns, it is pretty easy to simply make them visible or invisible based on user input.
Your process is pretty complex, so it may be more work to use SSIS and reporting services that to write something custom. I would recommend you change over to C#. Get a book, it's not that hard - I would recommend this over VB.Net because you have been working in VB6. Much of the syntax you already know will work, but it is going to promote really bad coding practices for the .Net language - switching to C# will help enforce the fact that it is a completely different programming language and should be treated as such.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply