Weird Excel Source Behavior!
Jul 31, 2007
Guys,
I have some data in an excel sheet. Some of the columns have a few NULL values for certain amount of rows till is gets data. What makes it so weird is that when priviewing this in the wizard, the whole column is filled with NULL values when the number of leading NULLs is quite large. When NULLs are quite a few, the column works fine!! Can anyone explains this? We tried some manual work to cut some of the rows from below and put them at the start and it worked! It's so strange though this behavior.
Shiko
View 8 Replies
ADVERTISEMENT
Aug 29, 2007
I'm seeing some strange behavior from a stored procedure of mine. It essentially grabs a bunch of rows using a fairly simple JOIN....here's the from statement:
Code Snippet
FROM Payment PY (NOLOCK)
JOIN (SELECT DISTINCT
PY.AccountPaymentId,
ROW_NUMBER() OVER(ORDER BY PY.AccountPaymentId ASC) AS RowNum
FROM Payment PY (NOLOCK)) AS SQ
ON (SQ.AccountPaymentId = PY.AccountPaymentId)
INNER JOIN Payee PE ON PE.PayeeId = PY.PayeeId INNER JOIN
Party PT ON PE.PartyId = PT.PartyId INNER JOIN
Distribution DS ON PY.DistributionId = DS.DistributionId LEFT OUTER JOIN
Account AC ON DS.AccountId = AC.AccountId INNER JOIN
clm CM ON PE.clm_no = cm.clm_no LEFT OUTER JOIN
PartyAddress PA ON PY.PartyAddressId = PA.PartyAddressId AND
PT.PartyId = PA.PartyId
WHERE RowNum BETWEEN (((@Page * @PageSize) - @PageSize) + 1) AND ((@Page * @PageSize) - @PageSize) + @PageSize
and ((@PayeeName IS NULL) OR (PT.[Name] LIKE '%' + @PayeeName + '%'))
AND ((@AccountId IS NULL) OR (AC.AccountId = @AccountId))
AND ((@DistributionId IS NULL) OR (DS.DistributionId = @DistributionId))
AND ((@PaymentDate IS NULL) OR (DATEADD(day, DATEDIFF(day, 0, PY.PaymentDate), 0) = DATEADD(day, DATEDIFF(day, 0, @PaymentDate), 0))) -- Ignores the time
AND ((@PaymentNumber IS NULL) OR (PY.AccountPaymentId = @PaymentNumber))
AND ((@IsReconciled IS NULL) OR (PY.ReconciledInd = @IsReconciled))
AND ((@AmountIssued IS NULL) OR (PY.PaymentAmount = @AmountIssued))
AND ((@AmountPaid IS NULL) OR (PY.AccountPaidAmount = @AmountPaid))
AND ((@IssueStatus IS NULL) OR (PY.PaymentStatusEnumItemId = @IssueStatus))
AND ((@AccountStatus IS NULL) OR (PY.AccountStatusEnumItemId = @AccountStatus))
AND ((@IsReissued IS NULL) OR (PY.ReissuedInd = @IsReissued))
ORDER BY AccountPaymentID ASC
When I pass a 1 for the @IsReconciled parameter, I get the right number of rows back - 9779. But when I pass a 0 (zero), i get no rows back, although there are 222 rows which satisfy the condition.
Is there somethig I'm overlooking (I don't think I am...)? I don't know whay 1 works and 0 wouldn't...
FYI - the @IsReconciled parameter is set to NULL at the outset of the procedure -
@IsReconciled Bit = Null
View 1 Replies
View Related
Aug 15, 2007
We have an interesting problem. We are attempting to migrate from sql 2000 to sql 2005. the schema we have is exactly the same. the new 2005 box is more powerful than our 2000 box.
here is our schema:
tbl_Items
ItemID int pk
ReferenceID int
sessionid varchar(255)
StatusID int
tbl_ItemsStatus
statusid int pk
isinternalstatus bit
there is an index on (ReferenceID, SessionID, StatusID) and (SessionID, StatusID)
this is the query:
DECLARE @referenceid INTEGER
SET @referenceid = 1019
SELECT MAX(i2.itemid)
FROM tbl_Items i2 (NOLOCK)
JOIN tbl_ItemsStatus s (NOLOCK)
ON i2.StatusID = s.StatusID
WHERE
s.IsInternalStatus = 0
AND i2.referenceid = @referenceid
AND i2.sessionid IN (
SELECT i3.sessionid
FROM tbl_Items i3 (NOLOCK)
WHERE
i3.referenceid = @referenceid
AND i3.status <> 7
AND i3.status <> 8
AND i3.status <> 10
AND i3.itemid IN (
SELECT max(i4.itemid)
FROM tbl_Items i4 (NOLOCK)
WHERE i4.referenceid = @referenceid
GROUP BY i4.sessionid
)
AND i3.itemid NOT IN (
SELECT MAX(i7.itemid )
FROM tbl_Items i7 (NOLOCK)
WHERE
i7.referenceid = @referenceid
AND i7.SessionID IN (
SELECT i5.SessionID
FROM tbl_Items i5 (NOLOCK)
WHERE
i5.status <> 11
AND i5.referenceid = @referenceid
AND i5.itemid IN (
SELECT MAX(i6.itemid)
FROM tbl_Items i6 (NOLOCK)
WHERE
i6.referenceid = @referenceid
AND i6.status IN (7,11,8)
GROUP BY i6.sessionid
)
)
GROUP BY i7.SessionID
)
)
GROUP BY i2.sessionid
we know this query is pretty bad and can be optimized. however, if we run this query as is on 2005 it takes about 2 hours to run...if we run the exact same query on 2000 it takes 9 seconds.
so this query on 2005 if run takes 2 hours..however, if we omit the s.IsInternalStatus = 0 or the i2.referenceid = @referenceid line it takes about 9 seconds.
why would this be? it makes no sense why omitting one of those where clauses would increase the performance of the query by 2 hours? we know its a bad query...but this doesnt make sense.
any one else run into this problem?
View 1 Replies
View Related
Jul 23, 2005
Hi!I'm studying to have my MCSE 70-228 certification and I'm trying somethings with backing up transaction logs and shrinking it.Here's what I do:There is no activity in the database by the way.I have a transaction log of 1792 kb...I do the following command:BACKUP LOG TestDB TO TestDBBackupDBCC SHRINKFILE ('TestDB_Log',0)The transaction log is now 1280 kbI do the same command and finally my transaction log is now 1024kb...Any idea why it didn't shrink it at 1024 kb the first time?Thanks!Jeff
View 1 Replies
View Related
Aug 2, 2006
I was able to successfully create a database maintenance plan for SQL Server 2000 Transaction Log Shipping for a few databases a few weeks ago. Yesterday, I created a few more but to my surprise, I can no longer do it. I can create a maintenance plan but the job it creates does not start even if I force the job to start. I did exactly the same thing as what I did (as I document everything I do) before but no luck.
Has anybody had this experience before?
View 5 Replies
View Related
Sep 10, 2007
Hi,
I had created 2 packages... one is the parent package and contains a 50 iterations loop running a secondary package for each iteration... i had reached the following conclusion:
My package takes an average of 5 seconds from the time it ends executing one iteration and starts another... after about 30 iterations... my average time between end and start increases significantly to about 12 secs or even more...
All packages have delay validation set to false, and receive several variables from the parent package... Has for the logging, it is done to files based on a path coming from a variable in the parent package.
To execute the parent package i am using dtexecui.exe and i consider this behavior rather strange... had anyone experienced the same? Can anyone test this?
My environment is a 4 x64 processors with 8gb memory, so i guess its good enough to get 0 secs from end to start
I already consider the first 5 seconds weird...
Best Regards,
View 4 Replies
View Related
Mar 27, 2007
I have a script component in a data flow that is exhibiting some strange behavior. In the PreExecute event of the data flow, I stuff a recordset into a variable that is declared at the data flow scope. Within the data flow, I use a script component to read in the data from the recordset.
Example:
Dim olead As New Data.OleDb.OleDbDataAdapter
Dim dt1 As New System.Data.DataTable
Dim row As System.Data.DataRow
olead.Fill(dt1, Me.Variables.rsIntRateStrata)
If I display the count of the records in the data table dt1, it shows 42 rows, which is correct. Run the package, everything runs as expected. So far, so good.
Now, I set up another source/destination within the same data flow, as well as a script component between them, same as the first flow described above. Now my data flow has two parallel flows (different source & destinations). I copy the same script logic from the first flow into the second. Run the package- no errors, everything is fine... except when I inspect the data, it looks like the transformation isn't working correctly in the second script.
So I display a messagebox of each script component during run time. The first component displays 42 records, while the second displays 0 records? Same variable. Same data flow.
So I delete the first (original) flow from my data flow. Run the package again. Now the messagebox says 42.
What is happening here? Do I have to create two variables to duplicate the same recordset if I need to use it multiple times within the same data flow? Is this a bug?
-Kory
View 26 Replies
View Related
Feb 7, 2005
I have a query that is behaving a little a weird. here is the example:
i have 1 table in this table i have 2 columns wich are date and time
DATE_DEBUT_PERIODE_FISCALE DATE_FIN_PERIODE_FISCALE
------------------------------ ---------------------------
1/27/1997 2/27/1997
1/1/2005 2/6/2005
here is my query:
BEGIN
declare @datefin_flag datetime, @strip datetime
SELECT @strip = dateadd(d,datediff(d,0,getdate()),0)
SELECT @datefin_flag = DATE_FIN_PERIODE_FISCALE FROM DM_LKP_CALENDRIER_PERIODE_F
WHERE DATE_DEBUT_PERIODE_FISCALE < @strip AND DATE_FIN_PERIODE_FISCALE = @strip
--select @datefin_flag
--select @strip
IF(@datefin_flag != @strip)
RAISERROR('You cant run this',16,1)
END
Well this Query should return the raiserror it returns completes successfuly
since todays date is not the same as the date in the database.
if you select @datefin_flag it returns NULL and if you select @strip it brings back todays date how can NULL be equal to to todays date assuming that todays date is equal to NULL. ?
View 7 Replies
View Related
Mar 7, 2007
Hello again!
Once again I got stuck with a weird problem and I´m dependent on you.
I built a Data Flow task in which I did an OLE-DB Source Adapter with a SQL-Query as Source.
The Query goes on a Linked Server which connects to a Informix Table via ODBC. When I enter the Query and klick "OK" or try to go on the "Columns"-Tab, the BIDS freezes completely and the "Delay Notification Message" appears in the System Tray. When I check the Activity Monitor of my SQL Server, there´s a process marked as "Runnable" (green) which contains the Query
"select collationname(0x0904D00034)"
This one is oviously run by Visual Studio and doesen´t execute properly. I waited for an hour or so, but nothing changes. I´m running SQL Server 2005 SP2 (9.00.3042)
Any help would be greatly appreciated!!!
Thanks in advance!
View 2 Replies
View Related
Jul 25, 2007
my package contains
Two connections ==> one is using OLEDB connecting to SQL server 2005 and the other using ADO.NET's ODBC option to connect to mysql;
Two "Execute SQL Task" ==> one gets maximum ID(bigint) from a SQL server table and the other gets the maximum ID(unsigned) from a mysql's table and bind them to two variables ID1(string) and ID2 (int 64). http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1902297&SiteID=17&mode=1 ;
And one DataFlow whose data source is DataReader Source which queries mysql "select * from table where ID > cast(@ID1 as signed) and ID <= cast(@ID2 as signed) ". To debug, I put a DataViewer between Data Flow Source and Data Flow Destination.
Run the package, I found no data coming into DataViewer and no error message as well. However if I remove the "<=" sign and change the DataReader Source query to "select * from table where ID > cast(@ID1 as signed)", It works perfectly. Initially I thought it's variable @ID2's issue. But if I replace "<=" sign with ">=" sign in the query ==> "select * from table where ID >= (cast(@ID2 as signed) -100)", there is no problem. After tried many times, I found that query just doesn't work with "<" sign or "between". Plus the result of running package tells it's successful but just no records got inserted into Data Flow Destination.
Anyone could help? Thanks!
View 3 Replies
View Related
Nov 19, 2007
I am using a Excel Source to get the data from an excel file to sql server 2005 table. A couple columns are coming in a double precision float, but some values have characters in them, but those values are coming out as null, even though I changed the datatype from float to unicode string. Any inputs on resolving this will be much appreciated.
Thanks,
Manisha
View 4 Replies
View Related
Feb 22, 2006
I am trying to get the contents of the Excel Files dynamically and dumping into the SQL Database using SSIS. Through WMI Event Watcher, I could find when one or more Excel files dumped in a particular folder and using ForEach Loop Container I was able to take all the filenames and pass it through Variables. But at the same time in the Data Flow, I have to pass each Sheet of an Excel File to the Excel Source control and export the data to my SQL Database using OLEDB Destination.
For that I need to get the names of each sheets in an Excel File and pass it to the Excel Source Control through variables. But when I give Data Access Mode as "Table name or view name variable" and provide the variable name in that, then it is giving an error message as "A destination table name has not been provided".
And at the same time, Since I was not able to provide an static Filename (as I am passing through Variables), when I tried to map the columns in the OleDB Destination, it is not allowing me to map the columns.
So all these things I should do at Run-time using Variables in SSIS. I don't want to hard-code any filenames or Sheet names. If any one of you have a solution, please share with me.
Thanks & Regards,
Prakash Srinivasan
View 3 Replies
View Related
Sep 18, 2007
Hello,
I have a problem with retreving a excel data through excel source component.
I have source component as Excel Source which will connect to my .xls sheet.
To retrieve the values from the sheet i am using a query as,
"SELECT F14,F3 FROM [Charac Defn & Assgnment$]"
The column F14 is not formatted so that the format of the cell is "General" I have a different type of values in the F14 column such as "PE","PES",15,20,20.00,8888.9999 etc..
While i click on preview button of Excel source it shows only the text values and not the int or decimal values, its returning NULL for those cells. I tried to use convert function, its throwing an error as
TITLE: Microsoft Visual Studio
------------------------------
There was an error displaying the preview.
------------------------------
ADDITIONAL INFORMATION:
Undefined function 'Convert' in expression. (Microsoft JET Database Engine)
Is there any other function to change the format of the cell or i need to some thing else
Please help me how to solve this issue.
View 6 Replies
View Related
Mar 13, 2008
Hi,
I am creating an SSIS package witha a Dataflow task, which reads from an Excel source and then uses script component to dumpt the data to multiple tables in Sql Server database
I need to some how make my Excel source dynamic, that is my excel template which i would be using to map the excel columns to script component's input columns would be dynamic..
In other words, I should be able to define the Excel Source, Column Mapping Information, Precedence constraint to the Script component dynamically
Please suggest how could i accomplish this
Regards,
Kalyan
View 8 Replies
View Related
Jul 6, 2015
While importing data from Excel source , some column is getting null value even though excel column has value.To Resolve the issue we tried with
HKEY_LOCAL_MACHINESOFTWAREWow6432NodeMicrosoftOffice14.0Access Connectivity EngineEnginesExcel
1.Change the Value of the Row TypeGuessRows from 8 (Default value) to 0 and ImportMixedType = text
• xls
HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet4.0EnginesExcel
1.Change the Value of the Row TypeGuessRows from 8 (Default value) to 0 and ImportMixedType = text
the connection string of the excel
UPPER(REVERSE(SUBSTRING( REVERSE(@[User::VarInputExcelFile]), 1, 5) ) ) == ".XLSX" ? "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::VarInputExcelFile] + ";Extended Properties="Excel 12.0;HDR=Yes;IMEX=1";":"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" + @[User::VarInputExcelFile] + ";Extended Properties="EXCEL 8.0;HDR=Yes;IMEX=1";"
by doing the above setting also , the column is coming as null from excel source even though there is data in excel.
View 2 Replies
View Related
Jul 20, 2006
I have the Excel Connection Manager and Source to read the contents from an Excel file. For some reason couple of numeric fields from the Excel worksheet are brought over as nulls even though they have a value of 300 and 150. I am not sure why this is happening. I looked into the format of the fields and they are set to General in Excel, I tried setting them to numeric and that did not help.
All the other content from the excel file is coming thru except for the 2 numeric fields.
I tried to bring the contents from the excel source to a text file in csv format and for some reason the 2 numeric fields came out as blank.
Any inputs on getting this addressed will be much appreciated.
Thanks,
Manisha
View 5 Replies
View Related
Feb 3, 1999
I'm trying to write a DTS package that reads data from an excel spreadsheet. I'm having a problem getting all the data from the spreadsheet, seems that OLE DB is "too" smart. There is one column that has either numeric values or text values in its row cells. When I browse the spreadsheet in DTS (transform properties, browse button) I only see the text values. OLE DB has placed nulls or blanks into the cells with the numeric values. If I edit the spreadsheet to change the column header to contain a number, then the browse window shows only the numeric values and blanks out the text values. Any suggestion on how to get OLE DB/DTS to treat the numeric values as text? In the spreadsheet, I've tried changing the cell formats to text and to general. This had no effect.
View 1 Replies
View Related
May 3, 2006
Hi,
My OLE DB Source and Excel desintation values all will be assigned during the run time but it does work during design time but as on runtime columns are different. That's why it does not work.
Here is what I want to accomplish, I have table which contains all my report which needs to dumped to excel at the month end.
SQL Task using ADO enumrator read one record(one report), Give that record to For Each contair which Create the Excel file on the fly using one of variable from my table and uses a stored procedure to dump data to excel using Dataflow Task.
xlsQuery
CREATE TABLE `Sheet1` ( `FiscalYear` Short, `FiscalPeriod` Byte, `STORE #` Short, `Total Markups` Decimal(15,2), `Less Markdown SubTotal` Decimal(15,2), `Total Markup` Decimal(15,2) ) GO
sqlQuery
Exec Report.MyReport 1
Does it mean for 10 reports, I have to create 10 different data flow tasks, or it can be done using one data flow tasks but changing columns on the run time.
Please Help
Thanks
Shafiq
View 10 Replies
View Related
Sep 5, 2006
I want to query my excel source sheet with a paramater so i create a sql statement like
select * from xxx where name like 'fred%'
Then I want to parameterise it with
select * from xxx where name like @Name or select * from xxx where name like ?
both allow me to map the Parameter neither syntax works in the data flow or preview but ? still works in the build query window !
Anyone know the correct Param declaration for the Excel Source (i believe it should be oledb @Name declaration ?
is this a bug or am i missing something here ...
Cheers
Colin
View 3 Replies
View Related
Dec 28, 2007
Dear Friends,
I need to import data from several excel files. How can I configure excel source object to dinamically import each file?
The name of the file will be in a parameter of ssis package and this name change frequently, and ach time the filename change I dont want to change the configuration on the excel source?
What you sugest?
Shoul I use a script component as source?!
Regards!
View 18 Replies
View Related
May 19, 2007
Hi everyone!
I am trying to import data into my sqlserver 2005 database from an Excel 2000 file. The database is empty. I am using the worksheets from the file to create the tables and copy the rows. I am getting follwing errors:
- Pre-execute (Error)
Messages
Error 0xc0202009: {674E15E4-102E-4935-90A2-8B1FFFEFB11D}: An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".(SQL Server Import and Export Wizard)
Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the connection manager "SourceConnectionExcel" failed with error code 0xC0202009.(SQL Server Import and Export Wizard)
Error 0xc004701a: Data Flow Task: component "Source 64 - vw_TempOrderDetails" (5280) failed the pre-execute phase and returned error code 0xC020801C.(SQL Server Import and Export Wizard)
Any suggestion is most welcome.
Regards
View 5 Replies
View Related
Jan 31, 2007
Hi:
I use a SSIS package to loop thro a folder and load data from multiple excel files to a SQL2005 table. Works fine except when an excel has a missing col.
Col names in xls are always a subset of col names in the table. The missing cols are random, else I would just have made another package:-)
Once a missing column is found, I get runtime and design time errors, and metadata problems. How can a get SSIS to ignore missing columns?
TIA
View 3 Replies
View Related
Jan 20, 2006
Hi,
I use an excel datasource to populate some simple dimensions, but when i extract the excel file i get alot of blank rows from the excel files...
How can i overcome this issue? Is this normal?
I never had problems like this using DTS in the 2000 version
Best Regards,
Luis Simões
View 6 Replies
View Related
Oct 17, 2007
Hi,
I am using Excel datasource and Excel destination in a simple SSIS package which i created. I have got two queries regarding this
How does excel datasouce/destination accept .xlsx files, since i observed that they accept only .xsl file
Does excel destination ascept .xlsm files, because i have some macros enabled in destination
Thanks in advance for advice
Warm Regards,
gchanduu.
View 3 Replies
View Related
Nov 27, 2007
Hi,
I have an Excel spreasheet - the first column is text, the second numbers, the third a mix of the two. If I point An Excel Source at this in my data flow, it will import the first two columns without problem, but not the third: all cells containing text are being imported as nulls, but those containing numbers are imported just fine.
Even if the numbers are stored as text, they are converted into numbers at import and genuine text is still discarded. It's treated as if the entire column is numeric if there's just one numeric value in it.
I can get around this by creating a .csv or .txt file from the excel file, but that will add an extra layer of admin to this process and I'm tryuing to make it as seamless as possible
View 3 Replies
View Related
Oct 4, 2007
Hello,
I'm a SSIS beginner.
I need to create users in my database from an Excel file source.
I have an Excel source with user name and email.
I would like to use SSIS to go though the Excel file and execute a store procedure (create_user @name @email @password OUTPUT) for each row and then create an output file with the new created password (a random password is created for each user and the stored procedure has an output @password)
I tried to "plug" an "Excel source" to an "Execute SQL task" which execute my store procedure.
But as a SSIS beginner I don't really understand how to pass the parameters (name and email in the Excel file) to my stored procedure. There is the concept of variable a for each loop thing... but i dont really know how to start.
I would be greatfull if someone could tell me a solution to my problem.
Cheers
Fabrice
View 4 Replies
View Related
Jun 20, 2007
Hi:
I import data from multiple excel files into SQL DB. I have trouble with fields that could contain >255 chars.
If I have the col type = DT_Ntext in my Data Flow, the package fails for files that do not have any values >255 chars.
If I have the external coltype=dt_wstr and the output coltype=dt_wstr(4000) the package fails if the file contains any value >255 chars.(Implicit conversion does not occur, as expected).
I worked around by adding a dummy first row with >255 chars.
Is there a way to use a cast function to solve this prob? I tried using Select dt_ntext(fieldname) from Sheet1$, but that does not work.
Is there some clean way to get around this problem?
TIA
Kar
View 4 Replies
View Related
Jan 2, 2008
Dear Friends,
I currently have my excel source dynamic for the connectionstring. Using a global variable with the filename and using the expressions of this control to dynamically have the connecttionstring. My problem is that i need tto read always the first workseet and the name changes frequently and generates an erro in SSIS.
how can I set the excel source to goes only for the first sheet independently of the worksheet name?!
Regards!!
View 5 Replies
View Related
Dec 20, 2007
I have Excel file with data and I need load these data to database. One of the colume consit following data:
1
2
3
4
test5
6
7
8
9
but when I created connection (Excel Source) and click Preview... in Excel Source Editor, I got following data:
1
2
3
4
NULL
6
7
8
9
Why? How can I get all data as string?
View 1 Replies
View Related
Feb 27, 2007
I've a package that has a excel source. But i'm having a strange problem with it. One of the columns in the source file have a lot of null values but not all of them. But when i run the package a put a data viewer right after the source and i can see that it's showing that the few fields that should have values are also null. I've tried a lot of things but they didn't work. I need some help and fast if possible.
Example: Source file.xls
Name Grade OtherGrade
John 30 30.23
In the DataViewer
Name Grade OtherGrade
John 30 NULL
thanks
Adriano Coura
View 10 Replies
View Related
Oct 2, 2007
Hello there,
how do i write a select query where i can pass parameter.for example
select * from employee where id = @empID
does not work when i use excel as my data source and type is OLE DB
View 1 Replies
View Related
Aug 29, 2006
Hi all,
I've had little success gooling/searching for this (so far).
Given a simple spreadsheet:
StoreNumber StoreName
1
UPDStoreName_1
2
UPDStoreName_2
3
UPDStoreName_3
4
NEWStoreName_4
I want to have an SSIS package that will update a table: mystores (storenumber int, storename nvarchar(255))
StoreNumber StoreName
1
StoreName_1
2
StoreName_2
3
StoreName_3
5 StoreName_5
.. what I need to do is insert the new, update the existing and leave the remaining unchanged. i.e. :
StoreNumber StoreName
1
UPDStoreName_1
2
UPDStoreName_2
3
UPDStoreName_3
4
NEWStoreName_4
5 StoreName_5
(the UPD and NEW are added to simplify the example).
Now the default action of an excel source into an ole db destination is
an insert into the table - so PK constraints causes failures.
Now, given that the table is referred to by other table, and is in a
24x7 website, how do I change the SSIS package such that, on a
row-by-row basis, anUpSert (update or insert) is performed?
The only idea I have so far is:
create temp table
insert excel data into temp table
iterate through the table, using if exists ... update else insert logic <-- this to be done in a SP
Isn't there a better way?
View 7 Replies
View Related
Apr 17, 2008
Hi
I have an excel source which is a 41 column sheet. The excel filepath is stored in a table and captured into a variable. The excel source import is contained within a foreach loop and will loop through each file and continue until all the excel files are processed. It works fine until it gets to the last file. The import then fails with the following error:
The column "F42" needs to be added to the external metadata column collection.
The column "F43" needs to be added to the external metadata column collection.
The column "F44" needs to be added to the external metadata column collection.
The column "F45" needs to be added to the external metadata column collection.
The column "F46" needs to be added to the external metadata column collection.
The column "F47" needs to be added to the external metadata column collection.
Now when i open the excel sheet and hit CTRL+END the cursor goes to a column 6 to the right of the last column with data in it, effectively column 47 where column 41 is the end of my data.
I guess that the jet engine is trying to import these additional columns but because i am not expecting them there is no destination set up for them in the OLEDB destination and susequently the metadata needs to be added. I do not want to do this as these are excel files originating from the client and i cannot control how many additional columns they are going to "add".
Does anyone have any ideas as to how i can solve this? Is there a way of identifying the last column with data and only importing those columns?
Thanks in advance for any help or experience of this issue
View 2 Replies
View Related
