Importing Data From Various Data Sources With Non Standard Formats
Mar 19, 2007
Hi all :)
I'm wondering if SSIS will be the solution to the problem I'm working on.
Some of our customers give us an Excel sheet with data they want to insert or update in the database.
I've created a package that will take an Excel sheet, do some data conversion so the data types match up and after that I use a Slowly Changing Data component to create the insert/update commands.
This works great. If a customer adds a new row to the Excel sheet or updates an existing row changes are nicely reflected in the database.
But now I€™ve got the following problem. The column names and the order of the columns in the Excel sheet are not standard and in the future it could happen a customer doesn't even use an Excel sheet but something totally different.
Can I use SSIS for this? Is it possible to let the user set the mappings trough some sort of user interface? I€™ve looked at programmatically creating the package but I€™ve got to say that€™s quit hard to do€¦ It would be easier to write the whole thing myself than to create the package trough code ;)
If not I thought about transforming the data in code before I pass it on to the SSIS package in something like XML. That way I can use standard column names and data types.
So how should I solve this problem? Use SSIS or not?
I’m retrieving Yahoo quotes into my database and have run into an issue when the dates sometimes change format in the csv file retrieved.
I am retrieving  yahoo quotes via powershell, then running a package to import data to my table. This generally works expect when the yahoo date format changes.  In the yahoo csv file, the dates normally come through in dd/mm/yyyy format. I find when a quote is old the format changes to mm/dd/yyyy, just for that particular quote.
When this happens, the package fails because the quote date format does not match my destination table format. i.e. mm/dd/yyyy vs dd/mm/yyyy  When this occurs, I would like to skip the records in mm/dd/yyyy format altogether and have the rest of the quotes imported.  One approach I can think of is to import the dates as a text type and do some data validation / conversion once imported but it feels like adding unnecessary steps.
Is there some other way I can achieve this within the process I already have?
I need to periodically import a (HUGE) table of data from an external data source (not SQL Server) into SQL Server, with the following scenarios: Some of the records in the external data source may not exist in SQL.Some of the records in the external data source may have a different value at different imports, but this records are identified univocally by the same primary key in the external datasource and in SQL Server.Some of the records in the external data source may be the same in SQL.
Due to the massive volume of the import, I would like to import only the records which are different from what I have in SQL Server (cases 1 and 2 above). In fact case 2 is the most critical.
I thought of making a query with a left outer join between the data in the external data source table (SOURCE) and the data in the SQL Server table (DESTIN). The join is done on the respective primary keys (composed keys of up to 10 columns) and one of the WHERE conditions will be that the value in SOURCE is different from the value in DESTIN.
The result of this query would be exactly what I need to import. How to do this in SSIS??? I couldn't figure out how to join tables in different data sources yet.
In fact I cannot write a stored procedure to do that, since one of the sources is in a datasources not SQL Server. I have seen the Lookup transformation in this article http://www.sqlis.com/default.aspx?311 but this is not exacltly what I want to do. Another possibility is to use the merge join, but due to the sorting I believe its performances would be terrible!
I am pretty new to SSIS. I am trying to create a package which can accept data in any of several formats. i.e. CSV, Excel, a SQL Server database/table and import the data into my destination database.
So far i've managed to get this working OK. However I am now TOTALLY stuck. I'm currently trying to just concentrate on the data sources being a CSV (using a Flat File Data Source) and/or an Excel Spreadsheet.
I can get the data in and to my destination using a UNION ALL component and mapping the data sources to it so long as both the CSV file and the Excel spreadsheet exist.
My problem is that I need my package to handle the possibility that only the CSV file might exist and there is no Excel spreadsheet. In which case i'd like the package to ignore the Excel datasource completely. Currently either of my data sources do not exist I get errors and the package terminates.
Is there any way in SSIS that I can check all my data sources to see which ones exist (i.e. are valid). If they exist I want to use them. If it doesn't exist i'd like to disgard it (without error - as long as there is a single datasource the package should run)
I've tried using the AcquireConnection method in a script task on each of my connections, hoping that it would error if the file/datasource did not exist. It doesn't though (in the case of an Excel datasource it just creates a empty excel file for me).
The only other option I can come up with are to have seperate packages depending on the type of data we want to import and then run a particular package depending on the format of the source data. This seems a bit long winded. I am pretty sure I must be able to do what I want to achieve but I can't work out how.
I'll be grateful to anyone who can send me any tips/hints/links on how I can achieve this.
Hi, i'm wondering which is the best way to search data in a SQL Server. I reach data using Data Sources and Data Views and also with OLE DB Source with a Data access mode: Named query. I have to write the data into a Flat File. So, does any one knows which is the best practice for this? Or any one of the two are good choices? Thanks for your help.
In my project i want a report. In that report data is getting from more than one data sources(systems). While creating data source view i used named query for both primary and secondary data source. But at the time of crating "Report Model" i am getting below error.
An error occurred while executing a command. Message: Invalid object name 'Table2'. Command: SELECT COUNT(*) FROM (SELECT SerialNum, ModelNum AS com_model FROM Table2) t
Is there any way to create a report with multiple data sources?
I manage to get the SSIS working. Now I would need to do these tasks.
I first want to get data from 2 different sql servers. What would be the best method to accomplish this? Both are in Sql Server 2005.
Secondly I want to make sure if any of the servers couldn't be found on the network or in any case the getting data task failed for any one of them the package won't continue and an email should be send to an email address.
Thirdly If everything is ok then I should combine both and generate one sequence no for them and save them on to another location and then generate a file with modified values.
l have a customer text file.l have imported the file into an sql table. Now l want to validate the data starting with the customer_no and the format being xxl-0001 etc ,phone_no i.e 011 as the code and the number as 9028589 etc , say balance
l want to have ensure that the format of customer_no is XXL- then the number Check the phone number that it has the right number of characters and that it is an interger.
How ould l do this? Should l use a procedure to do this or triggers? How would the code look ? Is there a topic on data validation that can give me pointers? Please assist
I setup this package to import data from a Sharepoint list to a SQL Server data table. The primary key of my SQL table is mapped to the Title column of my Sharepoint list. There is a possibility that duplicate values will be entered in the Title field of the Sharepoint list. So when importing data into my table via SSIS, my package always error-out when there it comes across duplicate values. how you others have managed data integrity when importing from a Sharepoint list with the Title column being mapped to the primary key of a table.
I have one column in SQL Server 2005 of data type VARCHAR(4000).
I have imported sql Server 2005 database data into one mdb file.After importing a data into the mdb file, above column data type converted into the memo type in the Access database.
now when I am trying to import a data from this MS Access File(db1.mdb) into the another SQL Server 2005 database, got the error of Unicode Converting a memo data type conversion in Export/Import data wizard.
Could you please let me know what is the reason?
I know that memo data type does not supported into the SQl Server 2005.
I am with SQL Server 2005 Standard Edition with SP2.
Please help me to understans this issue correctly?
We have a daily process, which copies millions of rows of data from one DB to another over Linked Server. Just checking on the best practise, are there more efficient ways than the Linked server to copy millions of rows of data from one DB to another? I checked bulk insert but that transfers the data from the file to DB not DB to DB.Â
I have created a simple package that uses a sql command to pull data from an oracle database and inserts the data into a sql 2005 table. Some of the data fields that i am pulling from contain two digits after the decimal point, however this data is lost when it gets into sql. I have even tried putting the data into a flat file, and still the data is lost.
In the package I have a ole db source connection which is the oracle database and when i do the preview i see all the data I need. I am very confused and tried a number of things to get the data into sql, but none work. Any ideas would be very helpful.
Hi i have data on a Server in a different database which i like to access from within my ssis job.
I just need to look up information from one table on this database so i can references it. Is there a way of doing this is SSIS. Rather then me having to load the data from one database to another as the data may change.
i tried having 2 sources of data feed into a look up but that does not work..
On my home page I have several different folders to reports which require different data sources. the problem is that within these folders there are multiple copies of the same datasource. is it possible to store all of the datasources in one folder, one location? it would certainly be easier when changes to usernames and passwords need to be modified!
I searched and read about Data Sources and I'm seeing that there is no advantage in using it, which is what I found from playing around with it.
I expected that you would set a global connection in Data Sources and somehow link this to the things in your Connection Manager, giving you one place to switch from one environment to another. But reading the discussions here and playing around with it, this is not the case.
So, why is it there?
Next question.... another thing I gathered so far is something called "Configurations" that will do what I was describing above. Where do I do this?
I'm trying to combine two reports I've created into a form letter. The goal is to have a letter that indicates the students maximum UBSCT score for Math, Reading and Writing. Also in the letter we want to include the student's total number of credits awarded and overall GPA. I not sure if I need to try and combined the two queries or use two different data sources in Reporting Services. I've tried creating one sql statement but the grouping are giving me fits. I fairly new to Reporting Services.
Report 1:
704 Dixie High 11 311880 Student_1 UBSCT Math 176 704 Dixie High 11 311880 Student_1 UBSCT Reading 182 704 Dixie High 11 311880 Student_1 UBSCT Writing 173 704 Dixie High 11 311881 Student_2 UBSCT Math 168 704 Dixie High 11 311881 Student_2 UBSCT Reading 172 704 Dixie High 11 311881 Student_2 UBSCT Writing 165
SELECT track.schoolc as School_Code, school.schname as School, stustat.graden as Grade, studemo.ident as Ident, RTRIM(studemo.lastname) + ', ' + RTRIM(studemo.firstname) AS [Student], stutscors1.testc as Test_Code, left(zsubtest1.descript,20) as [Subject], max(stutscors1.testscore) as Score
FROM stutscors1 INNER JOIN stutests1 ON stutscors1.ststuniq = stutests1.ststuniq INNER JOIN studemo ON stutests1.suniq = studemo.suniq INNER JOIN stustat ON studemo.stuuniq = stustat.stuuniq INNER JOIN track ON stustat.trkuniq = track.trkuniq INNER JOIN school ON track.schoolc = school.schoolc INNER JOIN testdef1 ON stutests1.testuniq = testdef1.testuniq INNER JOIN zsubtest1 ON stutscors1.subtestc = zsubtest1.subtestc and stutscors1.testc = zsubtest1.testc
WHERE stutscors1.testscore <> ' ' and stutscors1.testscore <> '0' and school.schname = 'Dixie High'and stutscors1.testc = 'UBSCT' and stustat.graden = 11
GROUP BY track.schoolc, school.schname, studemo.ident, RTRIM(studemo.lastname) + ', ' + RTRIM(studemo.firstname), stustat.graden, stutscors1.testc, zsubtest1.descript
704 Dixie High 11 Student_1 311880 23.2500 23.2500 87.50300000 3.763569 704 Dixie High 11 Student_2 311881 20.2500 20.2500 76.84300000 3.794716
select stugrp_active.schoolc as School_Code, school.schname as School, stugrp_active.graden as Grade, rtrim(stugrp_active.lastname) + ', ' + rtrim(stugrp_active.firstname) as Student, trnscrpt.suniq as Ident, sum(trnscrpt.gpacratt) as CreditAtt, sum(trnscrpt.gradcrawd) as CreditAwd, round(sum(case when Trnscrpt.GpaCrAtt is null then 0 else Trnscrpt.GpaCrAtt end * gpamarks.gpavallvl0),3) AS TrmGpaPts, (round(sum(case when Trnscrpt.GpaCrAtt is null then 0 else Trnscrpt.GpaCrAtt end * gpamarks.gpavallvl0),3))/(sum(trnscrpt.gradcrawd)) as GPA
from dbo.trnscrpt inner join dbo.stugrp_active on (trnscrpt.suniq = stugrp_active.suniq) INNER JOIN school ON stugrp_active.schoolc = school.schoolc INNER JOIN gpamarks ON trnscrpt.marksetc1 = gpamarks.marksetc AND trnscrpt.markawd1 = gpamarks.mark
where school.schname = 'Dixie High' and stugrp_active.graden = 11 and trnscrpt.graden >= 9
group by stugrp_active.schoolc, school.schname, stugrp_active.graden, rtrim(stugrp_active.lastname) + ', ' + rtrim(stugrp_active.firstname), trnscrpt.suniq
when i m importing data from excel to Sql using DTS the column which has text content was not imported as same in excel sheet. whereas a special character is appearing in between the lines. the text field contains multiple lines but the conetent is imported in single line .
I'm battling with importing sales data from different ERP systems into a SQL table. I have multiple spreadsheets, which have the same sheet name, column names and formats. I use a for each file loop container to loop through the different files. I use variables passed to a script to pull the relevant source information from the file name. I then use the variables to create derived columns in the flow to indicate the datadate and the company division.
My problem is even though all the data and metadata looks the same, there are a couple of columns between the two spread sheets that alway complain about the metadata not matching or a coumn needing to be added to the external metadata column collection and the same coumn name needs to be removed from the external metadata column collection.
These spreadsheets are coming from different countries but I have gone in and adjusted any fromats that are different from the excel spreadsheet used as the template. The same two columns out of 12 complain and I'm not sure what is causing the error. In the error is
Warning: 0x800470C8 at Read excel File, Excel Source [274]: The external metadata column collection is out of synchronization with the data source columns. The column "COGS" needs to be added to the external metadata column collection.
The "external metadata column "COGS " (2512)" needs to be removed from the external metadata column collection.
Eventually, I'll have 3 spreadsheets to load and need to find out why they are not viewed as the same data and metadata when they apear that way on the surface. I've searched numerous blogs out there (Even Jamie's) and can't find anything.
In brief, here is my quandary… I have a GridView on a page with some radio buttons, a couple of check boxes, some drop downs etc. And depending on what the user selects the Grid View needs to show different columns from the database… The easiest way I have found to do this is to have a separate Data Object for each “view�, i.e. each variation of the checkboxes and radio buttons (which determine which dropdowns to use for the variables) and then bind the GridView to the specific Data Source at runtime…
It seemed like a good idea at the time, but now I have over 7 different data connections cluttering up the design view, and more to come and I feel like I am missing a better way to achieve the same results…
Does anyone have some advice for a major novice?
Here’s the beginning of a large set of nested ifs, if this is the way it’s done that’s fine too, I’ll just keep creating data sources, I just get the feeling there must be an easier way to do this..
I'm just wondering if it is bad programming practice to have many data sources on one page. I need a "Matrix" type of page that grabs data from many tables and so far I have 5 data sources. Is this common or is there something I should be doing to make it more efficient?
Can I import in my query a file from other sources (in this case it's a job that has elaborated server database data, but I could be in the need of using for instance Excel files or others) and compare its fields with the tables in my query?
I have a vb program that calls a sp. My vb program connects to DataSource1, Catalog1 and to DataSource2, Catalog2. My sp resides in Catalog1 using a table from catalog1 and a table from catalog2.
If I were to change a data source that many packages use (say, from box "SQL-2" to "SQL-1"), would I have to re-upload the packages that used that data source? Or would the packages already on the server receive that change somehow?
(currently realizing that most of the packages use package-local data connections instead of project-wide data sources...)
I'm new to SQL and DTS packages. I am trying to import data from an excel spreadsheet to an SQL server table via DTS package. It seems that the excel task looks at the first few records in a column to determine the datatype for that column. If the first few records are text, the entire column is imported as text. If numeric, the entire column is imported as numeric. There are about 25,000 records. In one field, the most important one, about half of the records begin with letters and the rest are all numbers. It is the subscriber ID field, and some subscriber IDs are all numbers, some are letters and numbers. The entire column should be imported as text. However, when I run the transform data task from the excel connection, none of the records that are all numbers are imported. I end up correctly importing only 13,000 of the 25,000 records. The rest are imported with the subscriberID field as <NULL>. I tried using the CAST or CONVERT function in the SQL query, but get the error message "Undefined Function."
hello, I create a txt file with a bash script, and i need to use it in a DTS package. But, i don't know how i can specify the type of my column. So in the transformations task, i have an error due to an incompatible type. what can i do to fix this error ? thanks,
I am creating a DTS package that is combining several tables, converting one column of data to a new column removing all special characters, then exporting the unique data based on this column and another column, and the max of other duplicates to a new table.
Now that I have the data in this table, I want to import any data that is not in my main table.
This "CLEANED" table does not have a designated "key" column, but the table I want to import the unique items does have an ID column that is also a primary key column.
DTS seems to want me to have a Key column to reference when importing from the CLEANED table to the MAIN table.
How would I go about checking the MAIN table against the CLEANED table, having DTS import only the unique items from the CLEANED table that are not present in the MAIN table based on three columns? The rest of the columns I want to just extract the MAX data from the duplicates.
Now here is the query I use to extract the unique values from the "CLEANING" table to get the data to the "CLEANED" table, but do not know how to use this to import into the MAIN table using something similar.
Code:
select partno2, MAX (partno) as partno, alt, MAX (C_alt) as C_alt, Max (cmpycd) as cmpycd, MAX (type) as type, compFN, MAX (pndesc) as pndesc, MAX (equipment) as equipment
into tbl_CLEANED from tbl_CLEANING group by partno2, alt, compFN ORDER BY partno, compFN
The three main columns I need to check against are: partno2 alt compFN I have named the columns the same in both tables.
partno2 is the column that has been copied from partno with all special characters & spaces removed. This is the main column I am using as a reference for unique values, then if no match, I have it check against the alt column, then the comFN column. If there are no matches in any of these columns, then I want to extract the data to the MAIN table.
How can I compare these tables and import only unique info to the MAIN table?
In addition, how can I also check items that are the same in both tables and update the MAX info for the other columns (not the three I use for reference - these I need to leave alone) and update those if there is more data in the CLEANED table then in the MAIN table?
I'm coming from ASP and I used to have a global connection string accessible to all of my ASP pages that I'd use for all of my data access. This was really convenient because I could easily switch to a backup or local data source for testing/debug by changing the connection string in one place.
Now, I'm using ASP.NET with Web Matrix and I love the drag and drop functionality but its dropping my connection string all over the place. How can I do this and keep my connection string in one spot so I can have the same convenience of switching data sources.
I am using SQL Server 2012, and I have a query that gets data from 4 different sources and sums them all up into one field to get a total, and right now I am using 4 queries for each data source and using UNION ALLs to combine them and I was wondering if there is a different way to accomplish this without using UNION statements.Here is a sample of my code.
SELECT Resv01 = ISNULL(SUM(Resv01),0) FROM ( SELECT Resv01 = SUM(Reservations)
I have to get all the customer records from one table and compare this records with another table and finally if there is same record I have to perform an update but the record does not exist I have to make an insert. How can I do it with SSIS Except writing sql or tsql scripts ?
