Problem importing sequential text files

Hello everybody

I have a rather strange problem I guess. Most likely I did something wrong but I can't find it right now. So this is what happens.

Well, I receive a number of text files and I have to import them into SQL server 7. I use Vbscript (ActiveX import) to modify some data before putting it all into my tables. Sql server imports all records perfectly but sometimes the sequential order of the text file is not respected when I look into the table. I'd like to explain this a bit better.

Let's say I have 3 types of records in the textfile. "NATIONAL" , "DISTRICT" and "AREA". The textfiles (fixed columns) look a bit like this.

0123321334253 NATIONAL 432748234
2347234712357 DISTRICT 234852348
2348235724137 DISTRICT 242752348
6975067587684 DISTRICT 664568483
6534534537453 AREA 324537453
6578305607965 AREA 485834690
3247573284920 AREA 453753648
6795673435734 AREA 457352888
4564573486943 AREA 583475386

So the order of the records is really important. Mostly SQL Server imports all records correctly but sometimes (without changing the inputfiles nor the DTS package !!!!) it mixes up the order of the records. After importation into a table it could look like this.

3247573284920 AREA 453753648
6795673435734 AREA 457352888
0123321334253 NATIONAL 432748234
2347234712357 DISTRICT 234852348
6534534537453 AREA 324537453
6578305607965 AREA 485834690
4564573486943 AREA 583475386
2348235724137 DISTRICT 242752348
6975067587684 DISTRICT 664568483

How is that possible? Most likely it's a setting I configured wrongly but what worries me is that mostly everything goes right and without changing anything it could be mixed up.

Can you please help me?

Anyway, thanks a lot!

Greetz,

Bart.SQL Server will maintain the record order as inserted UNLESS you apply a clustered index. I would check for indexes the next time this happens. Can anyone esle in your organization change the schema?

Also, do you use only one process to read the input files? Two process could also produce a mixed order.

Could you add an identity column to preserve the inserted order?|||Hello Paul,

First of all a big thank you for your reply.

Well, let me answer your questions first ;)

I don't use clustered indexes and I don't use multiple read processes. So I only import one text file at a time.

I have also tried to add an identity field to a record. In my ActiveX import script (vbscript) I test on the used level. If it's "NATIONAL" I put "1" into the ID column, when it's "RESEAU" I put "2" into the ID column and so on.

So I import everything into a temporary table and afterwards I put everything with an "ORDER BY ID ASC" into the final table.

You won't believe it, but even after that "ORDER BY" I SOMETIMES, not always, have a wrong order into the final table. So it's possible that in the final table I have such an ID column

1
2
2
2
2
3
3
3
3
3
4
4
3
3
3
5
5
5

:mad: You can surely understand that this irritates me a lot.

How is that possible?

Greetz, :confused:

Bart.|||By definition record order is unimortant in a relational structure. SQL Server does not guarantee row order because internally it stores the data in pages that may or may not be sequential.

Why not just apply the 'ORDER BY' clause when you retrieve the records from the database -- that way it makes no difference which order they are stored. An index on that field would speed this operation up a great deal.

BTW, IDENTITY fields are auto-incrementing -- you should not be trying to insert discrete values.

Regards,
Jason Woosley
SQL Server DBA|||Thanks for your reply Jason.

Your last solution is exactly what I tried last week. And indeed, this works perfectly. I only forgot to post this answer onto the messageboard.

Greetz,

Bart.sql

Problem importing data from linked server

Sorry if this is the wrong newsgroup. If so, please point me to the right
one.
We use a linked server to import data from an old SQL 2000 database to a new
SQL 2000 database. We create a linked server like this:
exec sp_addlinkedserver 'OldDatabase', '', 'SQLOLEDB', 'ServerName', ''
exec sp_addlinkedsrvlogin 'OldDatabase', 'false', NULL, 'UserName',
'Password'
Then we execute a series of "INSERT INTO" statements to import data like
this:
INSERT INTO ATABLE( FIELD1, FIELD2, ... )
SELECT T.FIELD1, T.FIELD2, ...
FROM OPENQUERY( OldDatabase, 'SELECT * FROM DatabaseName.dbo.TableName') AS
T
There are about 10 "INSERT INTO" statements and all execute fine except for
one. The failing one (the 7th one) returns this error:
"MSDTC on server 'ServerName' is unavailable."
My DTC service is inactive, but that shouldn't matter since all other 9
statements executed fine. If I enable the service and rerun the failing SQL
statement, it returns this error:
"The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a]."
What's strange is that *all* "INSERT INTO" statements have the same format
as shown above, and only one of them fails. All others after that failing
statement run fine. This also happens using SQL Query Analyzer and happens
no matter how many times I run the script. Can anyone explain to me what's
going on here and how to fix it?
Thanks,
Jon E. Scott
Blue Orb Software
http://www.blueorbsoft.comHi
I can only think that this is a network problem! Check the SQL Server and
Event Logs, try changing protocols and possibly try four part naming instead
of OPENQUERY.
John
"Jon E. Scott" <NOSPAMsupport@.blueorbsoft.comNOSPAM> wrote in message
news:%23VenwxNIFHA.3108@.tk2msftngp13.phx.gbl...
> Sorry if this is the wrong newsgroup. If so, please point me to the right
> one.
> We use a linked server to import data from an old SQL 2000 database to a
> new
> SQL 2000 database. We create a linked server like this:
> exec sp_addlinkedserver 'OldDatabase', '', 'SQLOLEDB', 'ServerName', ''
> exec sp_addlinkedsrvlogin 'OldDatabase', 'false', NULL, 'UserName',
> 'Password'
> Then we execute a series of "INSERT INTO" statements to import data like
> this:
> INSERT INTO ATABLE( FIELD1, FIELD2, ... )
> SELECT T.FIELD1, T.FIELD2, ...
> FROM OPENQUERY( OldDatabase, 'SELECT * FROM DatabaseName.dbo.TableName')
> AS
> T
> There are about 10 "INSERT INTO" statements and all execute fine except
> for
> one. The failing one (the 7th one) returns this error:
> "MSDTC on server 'ServerName' is unavailable."
> My DTC service is inactive, but that shouldn't matter since all other 9
> statements executed fine. If I enable the service and rerun the failing
> SQL
> statement, it returns this error:
> "The operation could not be performed because the OLE DB provider
> 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in th
e
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]."
> What's strange is that *all* "INSERT INTO" statements have the same format
> as shown above, and only one of them fails. All others after that failing
> statement run fine. This also happens using SQL Query Analyzer and
> happens
> no matter how many times I run the script. Can anyone explain to me
> what's
> going on here and how to fix it?
> --
> Thanks,
> Jon E. Scott
> Blue Orb Software
> http://www.blueorbsoft.com
>
>|||Is the server you are linking to running Server 2003?
nivek
"Jon E. Scott" <NOSPAMsupport@.blueorbsoft.comNOSPAM> wrote in message
news:%23VenwxNIFHA.3108@.tk2msftngp13.phx.gbl...
> Sorry if this is the wrong newsgroup. If so, please point me to the right
> one.
> We use a linked server to import data from an old SQL 2000 database to a
> new
> SQL 2000 database. We create a linked server like this:
> exec sp_addlinkedserver 'OldDatabase', '', 'SQLOLEDB', 'ServerName', ''
> exec sp_addlinkedsrvlogin 'OldDatabase', 'false', NULL, 'UserName',
> 'Password'
> Then we execute a series of "INSERT INTO" statements to import data like
> this:
> INSERT INTO ATABLE( FIELD1, FIELD2, ... )
> SELECT T.FIELD1, T.FIELD2, ...
> FROM OPENQUERY( OldDatabase, 'SELECT * FROM DatabaseName.dbo.TableName')
> AS
> T
> There are about 10 "INSERT INTO" statements and all execute fine except
> for
> one. The failing one (the 7th one) returns this error:
> "MSDTC on server 'ServerName' is unavailable."
> My DTC service is inactive, but that shouldn't matter since all other 9
> statements executed fine. If I enable the service and rerun the failing
> SQL
> statement, it returns this error:
> "The operation could not be performed because the OLE DB provider
> 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in th
e
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]."
> What's strange is that *all* "INSERT INTO" statements have the same format
> as shown above, and only one of them fails. All others after that failing
> statement run fine. This also happens using SQL Query Analyzer and
> happens
> no matter how many times I run the script. Can anyone explain to me
> what's
> going on here and how to fix it?
> --
> Thanks,
> Jon E. Scott
> Blue Orb Software
> http://www.blueorbsoft.com
>
>sql

Problem importing data from linked server

Sorry if this is the wrong newsgroup. If so, please point me to the right
one.
We use a linked server to import data from an old SQL 2000 database to a new
SQL 2000 database. We create a linked server like this:
exec sp_addlinkedserver 'OldDatabase', '', 'SQLOLEDB', 'ServerName', ''
exec sp_addlinkedsrvlogin 'OldDatabase', 'false', NULL, 'UserName',
'Password'
Then we execute a series of "INSERT INTO" statements to import data like
this:
INSERT INTO ATABLE( FIELD1, FIELD2, ... )
SELECT T.FIELD1, T.FIELD2, ...
FROM OPENQUERY( OldDatabase, 'SELECT * FROM DatabaseName.dbo.TableName') AS
T
There are about 10 "INSERT INTO" statements and all execute fine except for
one. The failing one (the 7th one) returns this error:
"MSDTC on server 'ServerName' is unavailable."
My DTC service is inactive, but that shouldn't matter since all other 9
statements executed fine. If I enable the service and rerun the failing SQL
statement, it returns this error:
"The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a]."
What's strange is that *all* "INSERT INTO" statements have the same format
as shown above, and only one of them fails. All others after that failing
statement run fine. This also happens using SQL Query Analyzer and happens
no matter how many times I run the script. Can anyone explain to me what's
going on here and how to fix it?
Thanks,
Jon E. Scott
Blue Orb Software
http://www.blueorbsoft.com
Hi
I can only think that this is a network problem! Check the SQL Server and
Event Logs, try changing protocols and possibly try four part naming instead
of OPENQUERY.
John
"Jon E. Scott" <NOSPAMsupport@.blueorbsoft.comNOSPAM> wrote in message
news:%23VenwxNIFHA.3108@.tk2msftngp13.phx.gbl...
> Sorry if this is the wrong newsgroup. If so, please point me to the right
> one.
> We use a linked server to import data from an old SQL 2000 database to a
> new
> SQL 2000 database. We create a linked server like this:
> exec sp_addlinkedserver 'OldDatabase', '', 'SQLOLEDB', 'ServerName', ''
> exec sp_addlinkedsrvlogin 'OldDatabase', 'false', NULL, 'UserName',
> 'Password'
> Then we execute a series of "INSERT INTO" statements to import data like
> this:
> INSERT INTO ATABLE( FIELD1, FIELD2, ... )
> SELECT T.FIELD1, T.FIELD2, ...
> FROM OPENQUERY( OldDatabase, 'SELECT * FROM DatabaseName.dbo.TableName')
> AS
> T
> There are about 10 "INSERT INTO" statements and all execute fine except
> for
> one. The failing one (the 7th one) returns this error:
> "MSDTC on server 'ServerName' is unavailable."
> My DTC service is inactive, but that shouldn't matter since all other 9
> statements executed fine. If I enable the service and rerun the failing
> SQL
> statement, it returns this error:
> "The operation could not be performed because the OLE DB provider
> 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]."
> What's strange is that *all* "INSERT INTO" statements have the same format
> as shown above, and only one of them fails. All others after that failing
> statement run fine. This also happens using SQL Query Analyzer and
> happens
> no matter how many times I run the script. Can anyone explain to me
> what's
> going on here and how to fix it?
> --
> Thanks,
> Jon E. Scott
> Blue Orb Software
> http://www.blueorbsoft.com
>
>
|||Is the server you are linking to running Server 2003?
nivek
"Jon E. Scott" <NOSPAMsupport@.blueorbsoft.comNOSPAM> wrote in message
news:%23VenwxNIFHA.3108@.tk2msftngp13.phx.gbl...
> Sorry if this is the wrong newsgroup. If so, please point me to the right
> one.
> We use a linked server to import data from an old SQL 2000 database to a
> new
> SQL 2000 database. We create a linked server like this:
> exec sp_addlinkedserver 'OldDatabase', '', 'SQLOLEDB', 'ServerName', ''
> exec sp_addlinkedsrvlogin 'OldDatabase', 'false', NULL, 'UserName',
> 'Password'
> Then we execute a series of "INSERT INTO" statements to import data like
> this:
> INSERT INTO ATABLE( FIELD1, FIELD2, ... )
> SELECT T.FIELD1, T.FIELD2, ...
> FROM OPENQUERY( OldDatabase, 'SELECT * FROM DatabaseName.dbo.TableName')
> AS
> T
> There are about 10 "INSERT INTO" statements and all execute fine except
> for
> one. The failing one (the 7th one) returns this error:
> "MSDTC on server 'ServerName' is unavailable."
> My DTC service is inactive, but that shouldn't matter since all other 9
> statements executed fine. If I enable the service and rerun the failing
> SQL
> statement, it returns this error:
> "The operation could not be performed because the OLE DB provider
> 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]."
> What's strange is that *all* "INSERT INTO" statements have the same format
> as shown above, and only one of them fails. All others after that failing
> statement run fine. This also happens using SQL Query Analyzer and
> happens
> no matter how many times I run the script. Can anyone explain to me
> what's
> going on here and how to fix it?
> --
> Thanks,
> Jon E. Scott
> Blue Orb Software
> http://www.blueorbsoft.com
>
>

Problem importing data from linked server

Sorry if this is the wrong newsgroup. If so, please point me to the right
one.
We use a linked server to import data from an old SQL 2000 database to a new
SQL 2000 database. We create a linked server like this:
exec sp_addlinkedserver 'OldDatabase', '', 'SQLOLEDB', 'ServerName', ''
exec sp_addlinkedsrvlogin 'OldDatabase', 'false', NULL, 'UserName',
'Password'
Then we execute a series of "INSERT INTO" statements to import data like
this:
INSERT INTO ATABLE( FIELD1, FIELD2, ... )
SELECT T.FIELD1, T.FIELD2, ...
FROM OPENQUERY( OldDatabase, 'SELECT * FROM DatabaseName.dbo.TableName') AS
T
There are about 10 "INSERT INTO" statements and all execute fine except for
one. The failing one (the 7th one) returns this error:
"MSDTC on server 'ServerName' is unavailable."
My DTC service is inactive, but that shouldn't matter since all other 9
statements executed fine. If I enable the service and rerun the failing SQL
statement, it returns this error:
"The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a]."
What's strange is that *all* "INSERT INTO" statements have the same format
as shown above, and only one of them fails. All others after that failing
statement run fine. This also happens using SQL Query Analyzer and happens
no matter how many times I run the script. Can anyone explain to me what's
going on here and how to fix it?
--
Thanks,
Jon E. Scott
Blue Orb Software
http://www.blueorbsoft.comHi
I can only think that this is a network problem! Check the SQL Server and
Event Logs, try changing protocols and possibly try four part naming instead
of OPENQUERY.
John
"Jon E. Scott" <NOSPAMsupport@.blueorbsoft.comNOSPAM> wrote in message
news:%23VenwxNIFHA.3108@.tk2msftngp13.phx.gbl...
> Sorry if this is the wrong newsgroup. If so, please point me to the right
> one.
> We use a linked server to import data from an old SQL 2000 database to a
> new
> SQL 2000 database. We create a linked server like this:
> exec sp_addlinkedserver 'OldDatabase', '', 'SQLOLEDB', 'ServerName', ''
> exec sp_addlinkedsrvlogin 'OldDatabase', 'false', NULL, 'UserName',
> 'Password'
> Then we execute a series of "INSERT INTO" statements to import data like
> this:
> INSERT INTO ATABLE( FIELD1, FIELD2, ... )
> SELECT T.FIELD1, T.FIELD2, ...
> FROM OPENQUERY( OldDatabase, 'SELECT * FROM DatabaseName.dbo.TableName')
> AS
> T
> There are about 10 "INSERT INTO" statements and all execute fine except
> for
> one. The failing one (the 7th one) returns this error:
> "MSDTC on server 'ServerName' is unavailable."
> My DTC service is inactive, but that shouldn't matter since all other 9
> statements executed fine. If I enable the service and rerun the failing
> SQL
> statement, it returns this error:
> "The operation could not be performed because the OLE DB provider
> 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]."
> What's strange is that *all* "INSERT INTO" statements have the same format
> as shown above, and only one of them fails. All others after that failing
> statement run fine. This also happens using SQL Query Analyzer and
> happens
> no matter how many times I run the script. Can anyone explain to me
> what's
> going on here and how to fix it?
> --
> Thanks,
> Jon E. Scott
> Blue Orb Software
> http://www.blueorbsoft.com
>
>|||Is the server you are linking to running Server 2003?
nivek
"Jon E. Scott" <NOSPAMsupport@.blueorbsoft.comNOSPAM> wrote in message
news:%23VenwxNIFHA.3108@.tk2msftngp13.phx.gbl...
> Sorry if this is the wrong newsgroup. If so, please point me to the right
> one.
> We use a linked server to import data from an old SQL 2000 database to a
> new
> SQL 2000 database. We create a linked server like this:
> exec sp_addlinkedserver 'OldDatabase', '', 'SQLOLEDB', 'ServerName', ''
> exec sp_addlinkedsrvlogin 'OldDatabase', 'false', NULL, 'UserName',
> 'Password'
> Then we execute a series of "INSERT INTO" statements to import data like
> this:
> INSERT INTO ATABLE( FIELD1, FIELD2, ... )
> SELECT T.FIELD1, T.FIELD2, ...
> FROM OPENQUERY( OldDatabase, 'SELECT * FROM DatabaseName.dbo.TableName')
> AS
> T
> There are about 10 "INSERT INTO" statements and all execute fine except
> for
> one. The failing one (the 7th one) returns this error:
> "MSDTC on server 'ServerName' is unavailable."
> My DTC service is inactive, but that shouldn't matter since all other 9
> statements executed fine. If I enable the service and rerun the failing
> SQL
> statement, it returns this error:
> "The operation could not be performed because the OLE DB provider
> 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]."
> What's strange is that *all* "INSERT INTO" statements have the same format
> as shown above, and only one of them fails. All others after that failing
> statement run fine. This also happens using SQL Query Analyzer and
> happens
> no matter how many times I run the script. Can anyone explain to me
> what's
> going on here and how to fix it?
> --
> Thanks,
> Jon E. Scott
> Blue Orb Software
> http://www.blueorbsoft.com
>
>

Problem exporting data using Excel destination (wrong format)

Hi there,

I have designed a package that works perfectly well, exporting data to an excel file from an ole db source. The problem is that in the excel destination file, columns of data that originally were numbers, are formatted as text. It would be just annoying if it weren't because I use those figures in a pivot table that operates with them.

Any idea on how to tell Excel that those columns are numbers?

Thx in advance

What is the original data type in the OLE DB source of those columns? If they are strings you may need tu use the Data Conversion transformation. Does the excel file already exists? if so, what are the format of the columns?

I run a quick test loading 100 rows od AdventureWorks.Sales.SalesOrderDetail table into an Excel file and all the data types mapped fine. The only thing is that I used the Excel Destination in BIDS to create the file and the destination tab using the 'Name of the excel sheet' -->New... button; it generated an statement like:

CREATE TABLE `Excel Destination` (
`SalesOrderID` INTEGER,
`SalesOrderDetailID` INTEGER,
`CarrierTrackingNumber` NVARCHAR(25),
`OrderQty` SMALLINT,
`ProductID` INTEGER,
`SpecialOfferID` INTEGER,
`UnitPrice` MONEY,
`UnitPriceDiscount` MONEY,
`LineTotal` NUMERIC (38,6),
`rowguid` UNIQUEIDENTIFIER,
`ModifiedDate` DATETIME
)

I only had to adjust the Numeric data type precision and I worked just fine.

Rafael Salas

|||

Thx for your answer Rafael.

The numeric data from the ole db data source is in four-byte signed int [DT_I4]. I have a data conversion between the ole db data source and the excel destination and I've tried to both leave the numeric fields unchanged and "convert" them into the same type. Regarding the excel file, I use a template pre-formatted that i copy into a new file through a system file task in the control flow. I've tried several formatting in the columns (general, numeric, etc...) to no avail...

I've also tried in the excel destination to create the worksheets in which I export the data through that "new button" and with a command very similar to the one you posted, with type for the numeric columns as INTEGER. If it worked for you I'm really confused then :/, I was beginning to think it could be a bug in excel.

Thx again

|||

Loslor,

I just looked into the details of mi excel destination file and compared how the data types from BIDS got mapped. I found that all my DT_I4, DT_I2, DT_WSTR and Numeric in BDIS have 'general' when I looked into the format of the cells in the Excel file. I also used the data in the excel file to build a pivot table and worked fine. Are you getting the same behavior?

CREATE TABLE `Excel Destination` (
`SalesOrderID` INTEGER, --> mapped from an DT_I4 in the dataflow; loaded in excel as 'General' (when opening the execel file and look a the format of the cell)
`SalesOrderDetailID` INTEGER,

`CarrierTrackingNumber` NVARCHAR(25), --> mapped from an DT_WSTR in the dataflow; loaded in excel as 'General' (when opening the execel file and look a the format of the cell)
`OrderQty` SMALLINT, --> mapped from an DT_I2 in the dataflow; loaded in excel as 'General' (when opening the execel file and look a the format of the cell)
`ProductID` INTEGER,
`SpecialOfferID` INTEGER,
`UnitPrice` MONEY, --> mapped from an DT_CY in the dataflow; loaded in excel as 'Currency' (when opening the execel file and look a the format of the cell)
`UnitPriceDiscount` MONEY,
`LineTotal` NUMERIC (38,6), --> mapped from a Numeric in the dataflow; loaded in excel as 'General' (when opening the execel file and look a the format of the cell)
`rowguid` UNIQUEIDENTIFIER,--> mapped from an DT_DBTIMESTAMP in the dataflow; loaded in excel as 'Date' (when opening the execel file and look a the format of the cell)
`ModifiedDate` DATETIME
)

Rafael Salas

|||

Hi again,

Yes, my excell is doing the same. After exporting the data, all the cells have the format "General", though they are still treated as text. In fact, the numeric columns display a warning saying that "The number in the cell is formatted as text or preceded by apostrophe" (the last thing obviously being not true) and asks me if i want to reformat the cells, wich sounds a bit like a joke to me. The pivot table is still not getting right the data.

Thx Rafael

|||

Then it seems there is an issue in how excel treats the format 'general' in your side. As I told you I was able to generate the Pivot table regardless of the 'general' formating.

Sorry if I didnt hel you

Rafael Salas

|||

I'm beginning to fight in that front since I don't see any problem in the package.

Thx a lot for your time Rafael

Problem exporting data using Excel destination (wrong format)

Hi there,

I have designed a package that works perfectly well, exporting data to an excel file from an ole db source. The problem is that in the excel destination file, columns of data that originally were numbers, are formatted as text. It would be just annoying if it weren't because I use those figures in a pivot table that operates with them.

Any idea on how to tell Excel that those columns are numbers?

Thx in advance

What is the original data type in the OLE DB source of those columns? If they are strings you may need tu use the Data Conversion transformation. Does the excel file already exists? if so, what are the format of the columns?

I run a quick test loading 100 rows od AdventureWorks.Sales.SalesOrderDetail table into an Excel file and all the data types mapped fine. The only thing is that I used the Excel Destination in BIDS to create the file and the destination tab using the 'Name of the excel sheet' -->New... button; it generated an statement like:

CREATE TABLE `Excel Destination` (
`SalesOrderID` INTEGER,
`SalesOrderDetailID` INTEGER,
`CarrierTrackingNumber` NVARCHAR(25),
`OrderQty` SMALLINT,
`ProductID` INTEGER,
`SpecialOfferID` INTEGER,
`UnitPrice` MONEY,
`UnitPriceDiscount` MONEY,
`LineTotal` NUMERIC (38,6),
`rowguid` UNIQUEIDENTIFIER,
`ModifiedDate` DATETIME
)

I only had to adjust the Numeric data type precision and I worked just fine.

Rafael Salas

|||

Thx for your answer Rafael.

The numeric data from the ole db data source is in four-byte signed int [DT_I4]. I have a data conversion between the ole db data source and the excel destination and I've tried to both leave the numeric fields unchanged and "convert" them into the same type. Regarding the excel file, I use a template pre-formatted that i copy into a new file through a system file task in the control flow. I've tried several formatting in the columns (general, numeric, etc...) to no avail...

I've also tried in the excel destination to create the worksheets in which I export the data through that "new button" and with a command very similar to the one you posted, with type for the numeric columns as INTEGER. If it worked for you I'm really confused then :/, I was beginning to think it could be a bug in excel.

Thx again

|||

Loslor,

I just looked into the details of mi excel destination file and compared how the data types from BIDS got mapped. I found that all my DT_I4, DT_I2, DT_WSTR and Numeric in BDIS have 'general' when I looked into the format of the cells in the Excel file. I also used the data in the excel file to build a pivot table and worked fine. Are you getting the same behavior?

CREATE TABLE `Excel Destination` (
`SalesOrderID` INTEGER, --> mapped from an DT_I4 in the dataflow; loaded in excel as 'General' (when opening the execel file and look a the format of the cell)
`SalesOrderDetailID` INTEGER,

`CarrierTrackingNumber` NVARCHAR(25), --> mapped from an DT_WSTR in the dataflow; loaded in excel as 'General' (when opening the execel file and look a the format of the cell)
`OrderQty` SMALLINT, --> mapped from an DT_I2 in the dataflow; loaded in excel as 'General' (when opening the execel file and look a the format of the cell)
`ProductID` INTEGER,
`SpecialOfferID` INTEGER,
`UnitPrice` MONEY, --> mapped from an DT_CY in the dataflow; loaded in excel as 'Currency' (when opening the execel file and look a the format of the cell)
`UnitPriceDiscount` MONEY,
`LineTotal` NUMERIC (38,6), --> mapped from a Numeric in the dataflow; loaded in excel as 'General' (when opening the execel file and look a the format of the cell)
`rowguid` UNIQUEIDENTIFIER,--> mapped from an DT_DBTIMESTAMP in the dataflow; loaded in excel as 'Date' (when opening the execel file and look a the format of the cell)
`ModifiedDate` DATETIME
)

Rafael Salas

|||

Hi again,

Yes, my excell is doing the same. After exporting the data, all the cells have the format "General", though they are still treated as text. In fact, the numeric columns display a warning saying that "The number in the cell is formatted as text or preceded by apostrophe" (the last thing obviously being not true) and asks me if i want to reformat the cells, wich sounds a bit like a joke to me. The pivot table is still not getting right the data.

Thx Rafael

|||

Then it seems there is an issue in how excel treats the format 'general' in your side. As I told you I was able to generate the Pivot table regardless of the 'general' formating.

Sorry if I didnt hel you

Rafael Salas

|||

I'm beginning to fight in that front since I don't see any problem in the package.

Thx a lot for your time Rafael

|||

I'm having the same problem using Excel 2003 in SSIS 2005 and wonder if you discovered the solution. I'd appreciate any tips you may have. Thanks.

Dan

Problem creating view for cube

Anyone see a problem with creating this view for a cube??

The error I get seems to be in line 8 with the sum function, but I see nothing wrong with it.

CREATE VIEW CarCube AS
SELECT CASE WHEN (GROUPING(CarType) = 1) THEN 'ALL'
ELSE ISNULL(CarType, 'Not Known')
END AS CarType,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'Not Known')
END AS Color
SUM(Amount) AS TotalAmt
FROM Inventory
GROUP BY CarType, Color WITH CUBEYou need a comma after Color on the line before|||lol, sometimes the simplest things are the hardest to find.

Thanks|||The people who are really good at finding at simple screw ups are the ones who are really good at making them. ;-)

problem counting multiple occurrances of a pair of entries

Apologies for the previous post - I hit the wrong mouse button

I was having problems writing up this query

I had a table with columns A B C i needed to write up a query that would count all occurrances of unique A,B pair entries in the table

for eg:

A B C
red pink x
red pink y
green blue z
red pink a
green yello b
green blue c

The query should return

red pink 3
green blue 2
green yello 1

I hope that helps... any help would be appreciated

Thanks in advanceOK...really simple now...just cut and paste in to QA

USE Northwind
GO

CREATE TABLE myTable99 (A varchar(10),B varchar(10),C varchar(10))
GO

INSERT INTO myTable99 (A,B,C)
SELECT 'red', 'pink', 'x' UNION ALL
SELECT 'red', 'pink', 'y' UNION ALL
SELECT 'green','blue', 'z' UNION ALL
SELECT 'red', 'pink', 'a' UNION ALL
SELECT 'green','yello', 'b' UNION ALL
SELECT 'green','blue', 'c'

SELECT A,B,COUNT(*)
FROM myTable99
GROUP BY A,B
GO

DROP TABLE myTable99
GO