DTS Help With Uploading An Excel Spreadsheet

Apr 4, 2007

Can somebody please tell me what I am doing wrong or need to do to resolve my issue. I having problems with one of the columns in an excel spreadsheet that I am trying to upload into the system. One of the columns contain values of both text and numbers such as 'ABC123', 'ABC124', '123456' etc. When I try uploading the sheet into SQL Server 2000 using DTS, the system removes all characters from the one column that I am having problems with. So entry 'ABC123' for example would be truncated to '123'. I tried formatting the column that I am having trouble with in excel to 'General' format as well as tried to transform the column to type varchar in SQL Server while using the DTS wizard but still had no luck. The problem is that SQL server thinks that the column is a float type column from the source and therefore truncates any characters.

View 2 Replies


ADVERTISEMENT

DTS Transfer Into A Excel Spreadsheet

Dec 28, 2000

I need to take my 'table stats'every week and put them into an excel spreadsheet so that I can track the changes of my table sizes over time (basically I am watching to see how many rows are in each table). What I was planning on doing was to create a view of my table stats that I could then use DTS to transfer on a weekly basis into my excel spreadsheet. I have only used DTS a couple of times, and I have not ever tried it with excel. Now the problem: Everytime my DTS job runs it appends the data to the end of the origional columns that were created. Since my database has over 5000 tables these columns grow quite quickly. What I would like to do is set it up so that everytime the job runs it puts the new data into new columns in the same worksheet of my excel file. Is this possible? Any suggestions?

View 1 Replies View Related

DTS From MS SQL To Excel Spreadsheet Issue

Jan 9, 2007

I'm getting an issue on a MS SQL DTS package that is doing a simple export from a MS SQL table to and Excel spreadsheet. I have three of these running but one is failing. I’m using DTSRun to run all three of these DTS packages. The only recent change was to the DTS package to fix the first step to delete the data in the spreadsheet tab named “Results”. The process works correctly in development (on different servers). The same active directory ID is being used on all three DTS packages and all three do the same i.e. export data to an excel spreadsheet in the same file location but with different names. I’ve Google’d this but only came across access issues which does not make since since it is writing the other two spreadsheets just fine. Curious.

Error I See:

Running DTS package with passed variables
...
DTSRun: Loading...

DTSRun: Executing...

DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1

DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1

DTSRun OnStart: Drop table Results Step

DTSRun OnError: Drop table Results Step, Error = -2147217911 (80040E09)

Error string: Cannot modify the design of table 'Results'. It is in a read-only database.

Error source: Microsoft JET Database Engine

Help file:

Help context: 5003027

Error Detail Records:

Error: -2147217911 (80040E09); Provider Error: -538642193 (DFE4F8EF)

Error string: Cannot modify the design of table 'Results'. It is in a read-only database.

Error source: Microsoft JET Database Engine
Help file:
Help context: 5003027

Any ideas would be great.

Thanks.
Jim

View 3 Replies View Related

Looping Through An Excel Spreadsheet

Feb 23, 2006

Being new to SSIS I wish to loop through a series of excel spreadsheets and within each workbook loop through each sheet. I am aware of the For Each container but how can the each sheet in the workbook be referenced?

Steve

View 42 Replies View Related

XML Task And Excel Spreadsheet

Feb 21, 2007

Hello,

I have some XML that I'm passing through a variable into the XML task in SSIS. I also have an xsd file that I'm using and I want to validate (I think) that XML and have the XML task output an excel document. I've got the xsd file all set up in the "second operand" part of the XML task and the XML I'm passing in as a variable and that's in the "input" part of the XML task. My question is are there any tricks to make an excel document with these two things? Is there something I need to do in the xsd file to tell it that I want excel? Below is my XML and xsd:

XML:

<person>

<person_id>117</person_id>

<last_name>Von Neumann</last_name>

<first_name>Marina</first_name>

<jobs>

<job>

<job_key>1</job_key>

<from_month>06</from_month>

<from_year>1987</from_year>

<company_id>30358</company_id>

</job>

<job>

<job_key>2</job_key>

<from_month>06</from_month>

<from_year>1985</from_year>

<company_id>30358</company_id>

</job>

<job>

<job_key>3</job_key>

<from_month>06</from_month>

<from_year>1979</from_year>

<company_id>30358</company_id>

</job>

<job>

<job_key>4</job_key>

<from_month>09</from_month>

<from_year>1992</from_year>

<company_id>8766</company_id>

</job>

<job>

<job_key>5</job_key>

<from_month>06</from_month>

<from_year>1962</from_year>

<company_id>8822</company_id>

</job>

<job>

<job_key>6</job_key>

<from_month />

<from_year />

<company_id>8822</company_id>

</job>

</jobs>

</person>

XSD:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"><xsd:element nillable="true" name="person"><xsd:complexType><xsd:sequence minOccurs="0"><xsd:element minOccurs="0" nillable="true" type="xsd:integer" name="person_id" form="unqualified"></xsd:element><xsd:element minOccurs="0" nillable="true" type="xsd:string" name="last_name" form="unqualified"></xsd:element><xsd:element minOccurs="0" nillable="true" type="xsd:string" name="first_name" form="unqualified"></xsd:element><xsd:element minOccurs="0" nillable="true" name="jobs" form="unqualified"><xsd:complexType><xsd:sequence minOccurs="0"><xsd:element minOccurs="0" maxOccurs="unbounded" nillable="true" name="job" form="unqualified"><xsd:complexType><xsd:sequence minOccurs="0"><xsd:element minOccurs="0" nillable="true" type="xsd:integer" name="job_key" form="unqualified"></xsd:element><xsd:element minOccurs="0" nillable="true" type="xsd:string" name="from_month" form="unqualified"></xsd:element><xsd:element minOccurs="0" nillable="true" type="xsd:string"
name="from_year" form="unqualified"></xsd:element><xsd:element minOccurs="0" nillable="true" type="xsd:integer" name="company_id" form="unqualified"></xsd:element></xsd:sequence></xsd:complexType></xsd:element></xsd:sequence></xsd:complexType></xsd:element></xsd:sequence></xsd:complexType></xsd:element></xsd:schema>




Thanks,

Phil

View 7 Replies View Related

Import Excel Spreadsheet To Sql Database

Mar 26, 2008

Hi there,
 
              I am able to import an excel spreadsheet into a table in sql server 2005 using SqlBulkCopy. The only thing that bothers me here is how to check duplicate entries and throw an error to the user regarding the duplicate entries. In the table in sql, there is no primary keys. There are five columns and the way I will have to find the duplicates is to match all those 5 columns. Since the excel spreadsheet can have 40 to 500 entries, how can I check those dupes.

View 1 Replies View Related

Getting An Excel Spreadsheet Into An SQL Server Table

Nov 6, 2003

Hi everyone,

I've got an Excel Spreadsheet with 5 columns of data which I need to get into an SQL Table. There's 13,000 rows in this Spreadsheet so manually doing it is out of the question.

How can I go about doing this?

Any help will be appreciated!

Thanks,

Andrew

View 4 Replies View Related

DTS To Excel And Full Spreadsheet Error?

Jul 16, 2001

Hey guys,

I'm exporting via dts to an excel spreadsheet. However my database has more than 65000 records. DTS croacks and shoots out that there are too many records in the spreadsheet.

What do I do? Any way to go around that? These are my daily hit logs that are recorded and they tend to get big.


Ilya Zherebetskiy

View 1 Replies View Related

&#34;exporting&#34;? SQL Data To An Excel Spreadsheet

Aug 7, 2002

I am new to SQL and can do queries OK on SQLTalk. I need to know if there is a script to retrieve data and then export to an Excel spreadsheet for internal company use. Is there such a beast and is this the right place to look???

View 1 Replies View Related

Importing An Excel Spreadsheet Into SQL Server

Aug 9, 2007

Hi,
I'm trying to import an excel file into SQL sever(using an insert statement), i'm creating a DTS package (in enterprise manager) and have VB Script. When i parse it, i get no errors, but when i run the package it says that it ran successfully but nothing happens, it doesnt insert into the table, even though i tested the insert statement. Can anyone help me?? Here's the code:

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()
on error resume next
Set objxl = CreateObject("Excel.Application")
objxl.Visible = False


Dim xlFile
xlFile = "C:Datafile.xls"
Set objWkb = objxl.Workbooks.Open(xlFile)

'' Connecting to SQL Server
set cn = server.CreateObject("ADODB.Connection")

Dim serverName
serverName = "myserver2"


strCS = "Provider=SQLOLEDB; Data Source=myserver2;Initial
Catalog=mycat; Integrated Security=SSPI"

cn.ConnectionString = strCS
On Error Resume Next
cn.Open
Set objsht = objWkb.Worksheets.Open("Sheet1")
Dim client_name, rb, date_rvd, LOB
Dim sql
Dim row, sequence
row = 2

client_name = Trim(objsht.Cells(row, 2).Value)
Do While IsNull(client_name) = False And client_name <> ""
'client_name = Trim(objsht.Cells(row, 2))
rb = Trim(objsht.Cells(row, 4).value)
date_rvd = Trim(objsht.Cells(row, 6).value)
LOB = "WCS"

sql = "INSERT INTO TEMP_TEST (CLIENT_NAME, RB, DATE_REVIEWED, LOB) VALUES (" & _
" '" & client_name & "' ,'" & rb & "', " & date_rvd & ", '" & LOB & "');"


row = row + 1
MsgBox (sql)
client_name = Trim(objsht.Cells(row, 2).value)
cn.Execute (sql)

Loop

if err.count = 0 then
Main = DTSTaskExecResult_Success
else
Main = DTSTaskExecResult_Failure
end if
End Function

View 14 Replies View Related

Question About Deleting A Spreadsheet In Excel Using DTS

Feb 5, 2004

Dear all,

i'm using DTS to create a procedure. what i wanna do is to pump data to the excel, however i need to re-use the same excel file everytime, ie:i need to delete all fields in the spreadsheet except the colume name and then pump all data in again. i know how to pump the data in, however i dun know how to simply clear the existing data in the spreadsheet, can anyone help me, big thx ~!

Kam2

View 3 Replies View Related

Linked Server - Excel Spreadsheet

May 12, 2008

I am trying to set up a linked server in SQL Server 2005 to link to an excel spreadsheet.

-I am selecting Jet 4.0 as the provider
-Product name is Excel
-Data Source is the path on our network to the excel file: N:Devon54034.0 Engineering4.01 ProcessLinelistIFCLDT Field.xls
-Provider String is Excel 8.0
-Security | Login not defined is set to Be made using the login's current security context.

The Excel file is an Excel 2003 spreadsheet. The worksheet is titled Pages

I have a query window open in SQL Server Management Studio and the following is my select statement:

SELECT * FROM DEVON_LINE_LIST...Pages$

I get the following error message:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "DEVON_LINE_LIST" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "DEVON_LINE_LIST" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "DEVON_LINE_LIST".

I get similar error messages no matter which security settings I pick.

Any thought as to what I can try to get this to work?

View 3 Replies View Related

Way To Import Data From CSV Or Excel Spreadsheet

Nov 4, 2013

I am looking for a way to import data from a CSV or Excel spread sheet and add the data directly into an Extended field instead of a regular field in the table. for example: let's say I have a comma delimited field with the following info:

NDC_M_FORMULARY,CUSTOM_EXTSIG,Custom EXT SIG
NDC_M_FORMULARY,DRUG_CODE,Alternate key, user defined
NDC_M_FORMULARY,CHARGE_CODE,From the Charge code table

The first column is the table name
Second Column is the Column name in the table
The third column contains the description that I would like to store in the Value in the Extended Property Name "MS_Description"

BTW,I did find the following T-SQL which returns the Extended description for a specific Extended Property

Here it is:

SELECT
[Table Name] = i_s.TABLE_NAME,
[Column Name] = i_s.COLUMN_NAME,
[Description] = s.value
FROM
INFORMATION_SCHEMA.COLUMNS i_s
LEFT OUTER JOIN

[code]...

View 1 Replies View Related

NEW SQL- How To Export Table To Excel Spreadsheet?

Dec 18, 2005

Hi All,

I am new to SQL program. I did little management for SQL 2000 before.
I need to export from a table or view to excel spreadsheet for company's marketing resourece. Is there any easy simple way to do it?

Thanks all for your help.

Perry Yang

View 11 Replies View Related

SSIS And Excel Spreadsheet Manipulation

Mar 7, 2008



I am using SSIS to export data from a table to an Excel spreadsheet. This all seems to work put just fine. The user would like a data in column B1 to say when the spreadsheet was created. Is there a way within SSIS to do this. I was looking at using a .NET script but it accesses the spreadsheet as a table so I do not know how to insert data above the headings in row 1. I believe the OleDB provider using column 1 as it column names for the table. Maybe I am just going about the whole think wrong?

Thanks

View 5 Replies View Related

OleDB Update To Excel Spreadsheet

Mar 12, 2008

Is there a limit to the number of fields that can be set in an OleDB Update Statement?

This works with 6 fields:
cmd.CommandText = "Update [Sheet2$A2:AP2] Set F1 = '1', F2 = '35062', F3 = '6', F4 = '620000.0000', F5 = '200000.0000', F6 = '700000.0000'"

This Fails with 7 fields:
cmd.CommandText = "Update [Sheet2$A2:AP2] Set F1 = '1', F2 = '35062', F3 = '6', F4 = '620000.0000', F5 = '200000.0000', F6 = '700000.0000', F7 = '123'"

The range should be plenty big with A2:AP2. In the end I'm trying to push 42 fields.


The complete segment is:

Dim ExcelConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ExcelFileName & ";" & _
"Extended Properties=""Excel 8.0;HDR=NO"""
Dim conn As New System.Data.OleDb.OleDbConnection(ExcelConnection)
Dim cmd As New System.Data.OleDb.OleDbCommand()

conn.Open()
cmd.Connection = conn

cmd.CommandText = "Update [Sheet2$A2:AP2] Set F1 = '1', F2 = '35062', F3 = '6', F4 = '620000.0000', F5 = '200000.0000', F6 = '700000.0000', F7 = '1234'"

cmd.ExecuteNonQuery()

View 1 Replies View Related

Comparing Table With Excel Spreadsheet

Aug 23, 2007

Hi all,


I have two tables in SQL Server 2005 and excel sheet ( Office 2003).


The colums of excel sheet are: name ,ssn, flagbit ( Note: Excel sheet contains data already)

Columns of table_one and table_two are: name ,ssn


I want to compare the ssn field from table_one and ssn in excelsheet_one, if it matches , then flagbit in excelsheet_one should say"T1".


If i compare ssn field from table_two and ssn in excelsheet_one, if it matches, the flagbit in excelsheet_one should say "T2".


Ex:

Table_one (input) ---- excelsheet_one ( Output)

ssn name ---- ssn name flagbit

11 NYC ---- 11 NYC T1


Both the tables refers same excelsheet_one and have to update the same flag bit column in excelsheet.

Basically, i want to compare table and excel sheet, then if it matches, then update excel sheet.



Does anybody how to do this.


Any help will be greatly appreciated.

Thanks

View 4 Replies View Related

Need Help For Uploading Data From Excel To SQL Server

Jul 11, 2005

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()

View 1 Replies View Related

Import Partial Sql Table From Excel Spreadsheet

Oct 30, 2006

I have this situation that I need to read a spreadsheet with user names into a sql table where user name is just one of the columns. I tried using oledb connection to read the spreadsheet and sqlbulkcopy to import into sql table. There was no error, but the data wasn't imported into sql. Does anyone have any suggestion what I did wrong or what is the right way of doing this? Thanks a lot. Mia

View 2 Replies View Related

Import Data From Excel Spreadsheet Into A Table

Jul 17, 2013

I am using the import wizard in SQL Server 2008 R to import data from an Excel spreadsheet into a table I have created.

The spreadsheet contains 3 columns that SQL recognises as DOUBLE and they contain a 1 or 0. What data type do the corresponding fields in SQL table need to be? I have tried BIT, INT and FLOAT but keep getting an error (can't view details of the error because I get chucked out every time the error pops up). I know the problem is with the DOUBLE data because when I 'ignore' those columns the import works fine.

View 2 Replies View Related

Exported Queries To Excel An Spreadsheet File

Jul 23, 2005

Is it possible to export the results of a query using SQL Server querymanager to an excel spreadsheet file.Thanks in advance.

View 2 Replies View Related

Can SQLServer Produce Excel Spreadsheet Output ?

Jul 23, 2005

Deaa group,I am using SQLServer 2000 in an XP Sp2. I would like to do thefollowing:I have a program running on a database server that generates some datawhich are loaded to the database. This program is used in a webapplication, invoked by some java program and JSP scripts. (I amfrontend illiterated.)The question is, is it possible to write a stored procedure to generateoutput in excel spreadsheet? So that user could call this procedureand get spreadsheet output on the client side.Any pointer to a solution would be immensely apprecaited.thanks,charia

View 2 Replies View Related

Exporting Cursor's Output To Excel Spreadsheet

Apr 15, 2007

Hi,



I have a this table....

CREATE TABLE RCSAdvantage

(Resident_No int ,

FirstName varchar(50),

LastName Varchar(50),

DOB datetime,

Tel char(10),

source varchar(10))





Records....



INSERT INTO RCSAdvantage VALUES('123','Mike','Bhatt','12/12/2003','123','RCSA')

INSERT INTO RCSAdvantage VALUES('TM123','Mike','Bhatt','12/12/2003','456','TRIMICRO')

INSERT INTO RCSAdvantage VALUES('INR234','Mike','Bhatt','12/12/2003','890','INSIGHT')

INSERT INTO RCSAdvantage VALUES('INR234','John','Bhatt','12/12/2003','890','INSIGHT')







I needed to run following cursor and get the result exported to excel file. But Cursor retrives two resultset and while exporting to excel spreadsheet , it is the only first resultset without second resultset. How can it be exported to excel as a single resultset combined of first and second one.



/* Suppress counts from being displayed*/

SET NOCOUNT ON

/*Declare the variables */

DECLARE @cnt int,@FirstName varchar(15),@LastName varchar(15),@DOB datetime

/*Declare Cursor */

DECLARE RCSA_C CURSOR LOCAL

FOR SELECT COUNT(*), FirstName, LastName, DOB

FROM RCSAdvantage

GROUP BY FirstName,LastName,DOB

HAVING (COUNT(*)>1)

ORDER BY FirstName

/*Open the cursor */

OPEN RCSA_C



/*Get the resultset from the first row of the cursor*/

FETCH NEXT FROM RCSA_C INTO @cnt,@FirstName,@LastName,@DOB

WHILE @@fetch_status=0

BEGIN

SELECT * FROM RCSAdvantage

WHERE FirstName=@FirstName

AND LastName=@LastName AND DOB=@DOB

/*Get the next row */

FETCH NEXT FROM RCSA_C INTO @cnt,@FirstName,@LastName,@DOB

END

/*Close the cursor */

DEALLOCATE RCSA_C

--SELECT * FROM RCSAdvantage







Please help

View 1 Replies View Related

Dynamically Creating A New Excel Spreadsheet In SSIS

Feb 25, 2008

Hi everybody, i'm a newbie to SSIS and I'm having a problem dynamically creating a new excel spreadsheet in SSIS.
What I need to do is be able to dynamically create a brand new Excel spreadsheet after a data flow task completes.

Any help would be most appreciated. Thank you.

View 1 Replies View Related

How To Export SQL Data To Excel SpreadSheet Using SQL Query?

Aug 2, 2006

Hi

Im using this query to select ,calculate and format data like Refer here for more understanding:-


Select DateAdd(Hour, DateDiff(Hour, 0, RowDateTime), 0) As RowDateTime,
Avg(Meter1) As Meter1,
Avg(Meter2) As Meter2,
Avg(Meter3) As Meter3
From TableName
Group By DateAdd(Hour, DateDiff(Hour, 0, RowDateTime), 0)


I want the output of the query to be written in the excel Sheet.

Your help will be highly appreciated.

View 13 Replies View Related

Report Issue When Exporting To Excel Spreadsheet

May 12, 2008

I created a report using visual studio 2005. Everything works fine except when users export it to excel. I have a column that links the user to another report but when they try to access it from the spreadsheet it is using localhost instead of the report server. Is there an easy way to fix this instead of updating each cell? Any help is appreciated. Thanks.

View 2 Replies View Related

Import Data To SQL Server From Excel Spreadsheet

Aug 22, 2006

Hi all,

Firstly, i'm new to integration services and have only done a little with DTS jobs.

I'm trying to create an integration services project which will import data from an two worksheets in an Excel spreadsheet to two different tables in a database. I'm looking at only one table at present to make things a little more understandable.

One stipulation i have is that i need to be able to specify a variable value and insert that as an additional column in the database. I have and Excel source and a SQL destination both of which have been set up with there specific connection managers. I also have a variable which i add in using the derived column task.

When i try to debug this i am getting a few problems. I think these may be to do with the fact that although the worksheet in Excel has 20 rows (1st column shows these numbers) i only want those rows with data in them. If i preview the excel table it shows all the rows including those with null columns. Is there some sort of way that i can only get the rows that have data in the columns after the row number. I.e. can i select rows that do not have a second column value = to NULL.

I hope this makes sense and that someone can help me out with this problem.

All help is greatly appreciated.

Cheers,

Grant


P.S.

Apologies. I have this resolved now. I didn't see the option to use a SQL command as apposed to a table or view when setting up the Excel source.

I am still however getting the following errors which i'd appreciate some help on:

Error: 0xC0202009 at Data Flow Task, Excel Source [1]: An OLE DB error has occurred. Error code: 0x80040E21.
Error: 0xC0208265 at Data Flow Task, Excel Source [1]: Failed to retrieve long data for column "Rework Entry Information (BE SPECIFIC)".
Error: 0xC020901C at Data Flow Task, Excel Source [1]: There was an error with output column "Rework Entry Information" (170) on output "Excel Source Output" (9). The column status returned was: "DBSTATUS_UNAVAILABLE".
Error: 0xC0209029 at Data Flow Task, Excel Source [1]: The "output column "Rework Entry Information" (170)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "Rework Entry Information" (170)" specifies failure on error. An error occurred on the specified object of the specified component.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "Excel Source" (1) returned error code 0xC0209029.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.

Any help on this would be greatly appreciated.

Grant

View 5 Replies View Related

Modify Excel Spreadsheet On The Web Page...doable?

Feb 3, 2007

Hi there,

I am making an web application and it involves a spreadsheet object...

now i manage to show the spreadsheet on the web page by using

Response.ContentType = "application/vnd.ms-excel"

Response.Write(sheet.XMLData)

The requirement from my clients is that they wish to modify the spreadsheet on the web -> and then click the save button -> update the database

Can anyone here help me with that? i am using vb.net and the application will be use in LAN

View 1 Replies View Related

How To Import Data From Excel Spreadsheet With More Than 255 Columns!

Nov 24, 2007



Hi

I get the following error when I use SQL Server 2005 Import/Export wizard to extract more than 255 columns from an excel file;

TITLE: SQL Server Import and Export Wizard
------------------------------
The preview data could not be retrieved.
------------------------------
ADDITIONAL INFORMATION:
Too many fields defined. (Microsoft JET Database Engine)
------------------------------
BUTTONS:
OK
------------------------------


DOES ANYONE KNOWS THE WORKAROUND?

Early Thanks,
Salman Shehbaz.

View 1 Replies View Related

Using SSIS To Perform A Data Import Of An Excel Spreadsheet

Oct 15, 2007

I am new to SSIS. 
I am interested in using SSIS to import an excel spreadsheet into a SQL server database. My biggest concern is how to handle/manage errors that might occur when the import process occurs. Can anyone give me any guidance on this?
 I could write some C# code to do the import and to create a custom .txt file listing errors that occur on import. Using C# code to do the import seems like I would just be reinvinting the wheel so to speak.

View 3 Replies View Related

Getting Nulls In SQL2005 Table While Importing From EXCEL Spreadsheet

Jul 19, 2007

I am trying to import an Excel Spreadsheet into SQL2005. There is a column in the spreadsheet that has character values, and numbers. I have formatted the numbers as text on the spreadsheet. I have declared the column on the table as char/varchar/nchar, but whatever I do, the numbers don't get imported into the table, but show up as nulls. Any idea why?



Thanks

Mangala

View 1 Replies View Related

SQL Server Report Issue When Exporting To Excel Spreadsheet.

May 14, 2008

I created a report using visual studio 2005. Everything works fine except when users export it to excel. I have a column (ID) that links the user to different reports depending on what type of ID it is. I'm using the "Jump to Report" option and have created a custom function. When they export the report to an excel spreadsheet and click on the link, it's using localhost instead of the report server name. Is there an easy way to fix this instead of updating each cell with the report server? Thanks.

View 4 Replies View Related

Copy Data From Excel Spreadsheet And Paste To Table

Aug 2, 2007

I have an excel spreadsheet and I want to transport its data into a table. I have tried copying and pasting but it doesn't work. Any ideas?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved