I designed a package in SSIS that loads data from an Excel source into a oledb SQL database table.
The problem I have is that two of the columns that are supposed to be nvarchar type columns sometimes contain numerical strings. These values causes an error when 'Allow Null' is not set and loads as NULL if 'Allow Null' is set.
How can I force these values into the table?
I have a script transformation object in the package with this code:
If IsNumeric(Row.OCCode) Then
Row.ItemCode = CStr(Row.OCCode)
Else
Row.ItemCode = Row.OCCode
End If
ItemCode is of type unicode string and the column in the SQL DB Table it's mapped to is of type nvarchar
Iam currently loading the data using dts by way of exporting the tables in textfile and then importing it at the destination. But this takes hours to do that. So i would like know best way for a big database.
I have searched the forum for the code and found one. But, I encounter a problem which i can't understand.Can anyone help me with this?I encounter a "Keyword not supported: Provider"But then i have try to take out the provider and the result is they ask for a provider.Help!! ' Create the connection object for the Excel file Dim excelConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source=" & filepath & ";" & "Extended Properties=Excel 8.0;") excelConn.Open() ' Get the name of the Excel spreadsheet Dim schemaTable As DataTable = excelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _ New Object() {Nothing, Nothing, Nothing, "TABLE"}) Dim excelSheetName As String = schemaTable.Rows(0).ItemArray(2) ' Create the connection object for the SQL Server database Dim sqlConn As New SqlConnection(strSqlConnString) sqlConn.Open() Try ' Create new OleDbDataAdapter that is used to build a DataSet Dim excelDataAdapter As New OleDbDataAdapter("SELECT * FROM [" _ & excelSheetName & "]", excelConn) Dim excelDataSet As New DataSet ' Treat newly added rows as inserted rows, so they will all ' be inserted into SQL table excelDataAdapter.AcceptChangesDuringFill = False excelDataAdapter.Fill(excelDataSet, tablename) Dim excelTable As DataTable = excelDataSet.Tables(tablename) ' Create new SqlDataAdapter that is used to build a DataSet Dim sqlDataAdapter As New SqlDataAdapter("select * from " & tablename, sqlConn) Dim sqlDataSet As New DataSet sqlDataAdapter.Fill(sqlDataSet, tablename) Dim sqlTable As DataTable = sqlDataSet.Tables(tablename) ' Loop through each column name in the Excel DataSet and make sure it matches a ' column name in the SQL Server DataSet Dim excelCol, sqlCol As DataColumn Dim allColsCorrect, matchFound As Boolean allColsCorrect = True For Each excelCol In excelTable.Columns matchFound = False For Each sqlCol In sqlTable.Columns If excelCol.ColumnName.ToLower.Equals(sqlCol.ColumnName.ToLower) Then matchFound = True Exit For End If Next sqlCol 'CloseMonth is a field in the Excel sheet, but not in SQL DB, so just ignore If matchFound = False Then If Not (excelCol.ColumnName.ToLower.Equals("CloseMonth".ToLower)) Then Response.Write("<br>**Column '" & excelCol.ColumnName & _ "' in Excel file does not exist in SQL Server table.") allColsCorrect = False End If End If Next excelCol ' If all columns in Excel table match those in SQL table, then delete current ' contents of the SQL table and insert the data from the Excel table If allColsCorrect = True Then Dim deleteCommand As New SqlCommand("TRUNCATE TABLE " & tablename, sqlConn) deleteCommand.ExecuteNonQuery() Response.Write("- Deleted old data from SQL Server table.<br>") ' Create the CommandBuilder object to create the Transact SQL (TSQL) commands ' that are necessary to update and to insert records into the data source. Dim x As SqlCommandBuilder = New SqlCommandBuilder(sqlDataAdapter) Try 'sqlDataAdapter.ContinueUpdateOnError = True sqlDataAdapter.Update(excelDataSet, tablename) Response.Write("- Updated data in SQL Server table.<br>") Catch Exc As Exception Response.Write("<br>Error(message): " & Exc.Message) End Try Else Response.Write("<br>(The spreadsheet could not be loaded into the table " & _ "because of the above errors.)<br>") End If Catch Exc As Exception Response.Write("<br>Error: " & Exc.Message) End Try ' Clean up objects. excelConn.Close() sqlConn.Close()
Client side: 1. my DSN is CMDB.dsn on a mapped network drive(only way for now that I get a connection) 2. My client does not fail to connect, but I am having a hard time linking my database tables to each other. Through Access 03. 3. My client is remote 4. I can ping the server, 5. but I cannot telnet 6. My application is Access 2003, 7. I am on a different domain 9. I have table names that match, 10. I do not force encryption.
Server side: 1. SQL SERVER 2005 2. Standard Edition 3. TCPIP 4. The server starts fine 5. The SQL browser is enabled YES 6. Domain Account 7. Not applicable 8. NO
Platform: 1. Client runs Windows XP, Server runs Windows Server 2003
My question then is this: Does anyone know of code that can be used to import data from a CSV file to a server on a different domain? This is tricky cause the connection is not really allowing this kind of access. Which is my next question: How do you get these two applications to connect? I don't want to do it with ADP, and linking the tables I always seem to have to place the DSN on a mapped network drive to get results. Then after that I go to link tables from the SQL Server and the tables I created there are no where to be found. Why is this happening? It gives me a list of System tables but none of my
Need to change the datatype of existing column which has huge data.
I'm performing below steps
1. Create new column with correct datatype in the same table 2. copy data into new column 3. drop indexes on column 4. <<<>>> now the existing column also has many SP dependent and I do not wish to drop them. 5. rename existing column to xxx 6. rename new column to correct column 7. drop old column 8. make required indexes
Hi There are so many records to upload into sql server DB.we have to use scripts for this?I don't know about script.How it will be usefull and how to implement ?please help me.Thanksswapprose.
Hello!! i need to upload a csv file to sql server, i am trying to load using bcp or bulk load, but the files has null columns, so i guess, that's why the program is displaying error.
my table is as follows:
PK COLUMN TYPE SIZE NULLABLE ----------------------------------------------- PK Object_id int 4 NO SO varchar 4 NO FISY smallint 2 NO Segment_id varchar 3 NO SubSeg_id varchar 3 NO Line_id varchar 4 NO Level smallint 2 NO Type varchar 1 NO WWB_id varchar 3 YES SC_id varchar 6 YES PL_id varchar 9 YES PS_id varchar 12 YES PSS_id varchar 18 YES Material_id varchar 18 YES WWCC_id varchar 2 YES
THE OBJECT ID IS THE IDENTIFIER, SO IT SHOULD UPLOAD AUTOMATICALLY
Hello,I have problem:My *.txt file is like it:"12345612345678123abcdefabcdefghabc" etc.i want upload data into table (for example TEST) i want to sql read thisfile and automatically upload to table.(as job for example)but i have 3 columns and i dont know how to separate this text to 3 diffrenttext columns1 column | second column | third column-----------------------------------------123456 | 12345678 | 123abcdef | abcdefgh |abcPLEASE HELP ME, i dont know how to do it.Robert Kloma
[Source - Samp_txt [1]] Error: Data conversion failed. The data conversion for column "Column 2" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
The column width for column2 is 60 and i have cross checked several times that i have created the column width as 60 in SQL Server table for the corresponding field.
When I tried to upload uploaded data, RDA throws an exception and removed that data row from main table and insert into Error table. But this behavior is inconsistence like some time I got that exception but some times I don€™t.
Actually the problem is, some times RDA does updating data on server where I excepting insertion should be perform. I eager to know about when RDA does perform INSERT operation AND when does perform UPDATE operation on server data.
I've been working on an application that uploads an RDL to Reporting Services programmatically. I'm having problem with the data source properties for my uploaded report. The datasource for my report is custom datasource. It work s fine if the report still did not exist. But if the report already exist, after I upload the report to Reporting Services, the function will overwrite the RDL but it did not overwrite the Data source type correctly. Other setting like connection string is overwritten correctly.
For example, I already have RDL ABC with Data source type SQL, when I try to overwrite the existing RDL ABC which have Data source type Assembly, it still hold the SQL value for the data source type. But in the RDL itself (I checked in the rdl script in <DataProvider> tag), the value already overwritten to Assembly.
This problem also happen when I try to upload RDL using Report Manager. The Data source type are not overwritten by the action.
What should I do? Why it did not overwrite the datasource type? Maybe I do anything wrong here. I dont know what. Hope someone can help.
Does anyone know how to upload (bulk) data from a client (written in Excel VBA) to a remote SQL2000 database? Of coarse I tried "INSERT INTO" and rst.addnew but I noticed this is much, much slower as downloading from the same remote database.
I have found a problem with my SSIS package. The package takes a Excel File using the Excel Source/Excel connection manager objects. There's a Data Conversion Object which sets all the fields to be DT_STR and then I import the data into a Holding table using a OLEDB Destination Object.
My Excel file contains a list of Code/Description pairs. The problem I have is that I can have data such as the following
CODE
A101
A102
A103
12
1.2i
What seems to be happening is that when the data is imported into the holding table and the first rows are Alphanumeric then the fields where there's a code such as 12 get converted to NULL for some reason. If all the codes are the same type (either Alphanumeric or numericit doesn't matter, as long as they are all the same) the import works perfectly well.
I need to be able to import all of these codes without SSIS converting some to NULL. Has anyone come across this problem before? If so, is there something I am doing wrong or a workaround?
I needed to recreate/publish a DDL and data file(of a database) that was produced using Database Publishing Wizard. The size of the file is 4G. I receive the following error when I try to open it up in the new query window of sql server 2005 (sp2)for execution.
"The operation could not be completed. Not enough storage is available to complete this operation."
I know my server has 829G of free storage space available. i receive no errors when I open up a smaller file also created by database publishing wizard.
i am trying to upload images from an asp.net or vb.net front end to a sql server db. I can upload the image fine if I first save the image to a file and then upload but my question is whether I can upload the image without first saving the image file. What i would like to do is to capture an image with a digital camera and without saving it first (an extra step for the user), upload it to a sql server db.
i have developed a website using asp.net, c# with SQL SERVER EXPRESS EDITION 2005. The database is being used both for retreival and updation purpose. the website is working accordingly when i m running it on my system, ie., data is getting retreived from the database and it is also getting updated on button click. But when i m uloading my site on the httpdocs folder of web server, connectivity with database is failing miserably, ie neither getting retreived from the database nor getting updated. The error message displayed by the web server is given underneath. i have used Grid view for displaying data from the database and Details view for updating the database. i have used window authentication for connecting with the database.Please help me with finding out a solution for it. give me proper explanation and i need to do Server Error in '/' Application.
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Please bear with me because I am brand new to SQL Server, and I may not be using the correct wording to explain everything...
I`m using Access 97 to upload a table to a SQL Server 6.5 database. I also have SQL Enterprise Manager. The allocated space on the SQL Server for my database is 20 MB and the space for my database log file is 4 MB. The first time I tried to upload a table to the database, I got the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server] Can`t allocate space for object "syslogs` in database `testpropcontdb` because the `logsegment` segment is full. If you ran out of space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment (#1105)
I went into the Enterprise Manager and right clicked on my database and selected edit. It shows that I have 19.89 of the 20 MB free, and all 4 MB of my log space free.
When I select GET EXTERNAL DATA -> LINK TABLES from the FILE menu in Access, and link to the table I just uploaded to SQL Server, the structure (field names and attributes) is there, even though I got that error message earlier. There is just no data being uploaded.
Could someone please point me in the right direction? I`ve been reading help files and searching the net, but I haven`t figured out what is causing this error message.
Hi, I'm developing a website using VWD 2005 Express, which needs a Membership system and a products database. Using the VWD inherent Web Site Administration tool makes the membership set up easy but by default it creates a 'Local' SQL database in a folder named 'Apps_Data' in the VWD project. My question is: considering I will need to upload my web site to a Hosting Service, would it be better to change the default so that the membership systems, and the product database I need, are created in an SQL 'Server' database instead of a 'Local' database? The hosting service I am considering using is having a bit of a problem understanding this question, I hope it makes sense to someone or am I asking a stupid question? Regards Sean.
I would like some help in uploading a database from my harddrive to a server using sql server management studio express.
If I try to attach it to the server, this program only looks at the files on this server and not on my harddrive. So how can I copy a sql database to my folder on the server.
I am sure it must be a simple problem, but I've been bizzy with it for about a day now. So, please advice.
I am new with Visual Web Developer SQL Express and for a start I have been using the VWDHosting.net (Trial hosting for VWD 2005 Express) with a test website using login and membership controls. I followed the excellent instruction in the User Guide (for transferring data and adapting the connectionstring) and it worked succesfully. However, I have problems getting the same test website installed with my hosting provider.The VWDhosting.net has a special functionality for uploading the contents of a local database to a database created on the server. In the Restore database section is a field the "Restore database from SQL Server 2005 mdf file". After selecting in that field your local database, you can click the "Attach"-button and then the contents of the local database are transferred to the database on the server. With my hosting provider (using SQL Server 2000) I have to use the same procedure: creating a MS SQL database on the server and then transfer the contents of my local database. How can I do this? (My hosting provider does not offer me a similar "Attach" button).Do I have to use SQL Server Management Studio?Kees
I am very frustrated. Everything works on the local host but when I upload to server I can login to the admin role I created, but when I try to access pages that have role priveleges I get the following error: The SSE Provider did not find the database file specified in the connection string. At the configured trust level (below High trust level), the SSE provider can not automatically create the database file. The ASPNETDB.MDF database was uploaded using the Database Publishing Wizard. Please help!
All right, I've got a small sample of database-driven code running nicely on my own computer using an MS SQL database with code something like this to connect to it. (passwords and user ID's changed to protect the innocent)
Now, I want it to run on a web server, but there doesn't seem to be any simple "click here to upload your database without tearing your hair out in frustration" button. Only an option to "add ODBC data source" which has the following fields: data source name(dsn): ________ ODBC Driver Name: SQL Server (this value cannot be changed) DSN Description: ________ Associated SQL Server:_________ Default Database Name:_________
Without any option to upload any database files, though if I go into file manager, I notice that there is an MSSQL folder, which I assume I should be uploading the database into. But which file(s) from my database should I upload and what should I fill the blanks above in with? Thanks in advance. Do I have to change the connection code?
I need to call a stored procedure to insert data into a table in SQL Server from SSIS data flow task. I am currently trying to use OLe Db Destination, but I am not sure how to map inputs to OLE DB Destination to my stored procedure insert. Thanks
I am transferring DB2 data to SQL server 2005 in ssis. the connection i I am using is the IBM OLEDB driver for DB2 which uses the ODBC connection on the machine. The issue is that while transferring data i get errors reg unicode vs non unicode data. the db2 data is showing unicode data, but when i see the datatype in the db2 database it is a varchar. so when i transfer the data to a varchar in sql server 2005, it errors out....meesageis unicode vs non unicode data. Why is the data from db2 coming across as unicode string. Also the transfer works fine in SQL server 2000, why is is not working in sql server 2005. Please help !!!!
Ok, we have built a data mart using SSIS etc...for transformations and loading.
Our biggest single problem we have currently is loading data from an Oracle server to our SQL server. Some tables from oracle run fine when retrieving the data but there is one particular table that just doesn't load fast enough (9 million records take over 12 hours). It seems that we are idling alot and its not always running.
I am getting this error when connecting to Oracle db. I tried using Microsoft OLEDB provider for Oracle it give me error and tells me the error could not be retrieved from Oracle. When I try the Native OLDDB provider for Oracle I get
Warning at {0F67F2FA-E3F8-4F44-93EC-47D513A34FD4} [Orcale Database WPHP2 [1]]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
Error at Copy DSS_SJV_Volume_History [DTS.Pipeline]: The "output column "COMP_UNIQUE_ID" (2007)" has a precision that is not valid. The precision must be between 1 and 38.
I've encountered a few problems using SSIS against non-SQL Server data sources and was hoping that others might have some experience. Google searches and browsing MSDN hasn't led me to a solution, so any advice on the following is appreciated:
1) When using the "Data Source Views" wizard to add a data source view from an ODBC data source, only tables appear in the object listing. Views do not. (I've also observed this with SQL Server 2005 databases as well, but it's a bigger issue when you can't use the native SqlClient, as is the case for many ODBC-only databases.) ODBC traces show that both table and view metadata is being returned to SSIS correctly, so it appears as though SSIS is filtering out views from the object listing in the wizard.
2) When creating named queries against (non-SQL Server) ODBC data sources, SSIS appears to use the SQL Server SQL syntax for referencing schemas/objects (e.g. "SELECT * FROM [schema].[table]", rather than "SELECT * FROM schema.table"). This isn't valid SQL in many databases. Am I missing something? Is there a way to change this through some configuration setting?
