Problem in Datatype Date Convertion

Hi,

My source is flat file and my destination is SQL SERVER 2005 using SSIS TOOL.

In my source file i got a date column which is in ISO standards ex: 20050131

I have taken source flat file data type as database date [DT_DBDATE] and in

destination table i declared data type as datetime.

When i start debugging i am getting an error saying that data conversion is not possible.

Can you please help me out how to solve the problem, what data types do i need to take in source and destination and is there any necessity of using Data Conversion Transformation.

If, so please tell me how to do.

With Regards

Satish

What is the full error message? It should tell you in which component the error is occurring.

-Jamie

sql

Problem in bulk insert

Hi All,

I am trying to perform Bulk Insert for an existing folder containing the data files into an existing destination table . I get the following error :

[Bulk Insert Task] Error: An error occurred with the following error message: "Cannot bulk load because the file "C:\PD Data\PDDW_md_costs\Files_to_load" could not be opened. Operating system error code 5(Access is denied.).".

Here Files_to_load is the existing folder that contains the data files. I am not sure why it is saying that i do not have access to it. I have however also tried sharing the folder providing full access to it.

An input i would like to add is that i am performing all these tasks by remote connecting to a server machine that hosts SQL Server 2005 . I also am remote connecting using the administrator id to do these tasks.

If anyone has an idea on why this has occured please do let me know as this would really help solve the problem.

Thanks & regards,

Satya

I am having the same issue. Did anyone ever fiqure this out?

Thanks in advance.

JM

|||Have you tried using the UNC format for the foldername and path?|||

I'm also having this problem. What is UNC format (sorry if that's a stupid question - I'm a newbie)?

I've specified my input file using the connection manager, and selecting the file from the drop down list inside of Integration Services. It makes absolutely no sense to me why it suddenly can't see the file at runtime.

Thanks,

Laurie

|||Also, I should note that the file that I'm trying to access for the bulk insert is on my own local PC, not a remote drive somewhere on the network.|||

UNC path is \\MachineName\filePath. However, I tried this with no luck. Anyone else have any ideas? I have been stuck on this for a week and ordered a book that was no help.

JM

|||

I got past this problem by moving my data file to a shared drive. While I am running Integration Services on my local PC, SQL 2005 itself actually resides on another server on my network. When the package attempted to run, it was running from that remote server, causing the unable to access file error.

The fact that I could see the file and link to it easily with no warning from the Connection manager strikes me as inconsistent design. It should give you an error message or something if the file is going to be inaccessible at runtime. Just my 2 cents worth.

|||One more thing - you also need to use UNC format for the file name and path.|||

>While I am running Integration Services on my local PC, SQL 2005 itself actually resides on another server on my network. When the package attempted to run, it was running from that remote server, causing the unable to access file error.

Is that the case? To run the package remotely you woudl need to be invoking it with SQL Agent.

However, even if the package is actually running locally, if the Bulk Insert Task uses a remote SQL Server, then you are asking that remote SQL Server to perform the bulk insert. That SQL Server must have access to the source file.

You make an interesting point about whether we should validate that remote access at design time. However, the SQL Server Bulk Insert operation does not provide us with an option to validate - we have to invoke the operation to do so, which in turn would execute the Bulk Insert operation.

I'm thinking (before coffee, so don't expect too much) that it would be possible to craft a stored proc which could be called by OnValidate on the BulkInsert task that could effectively validate access to the source file.

Donald Farmer

|||

I have used the UNC path name to specify the folder in which to read. I get the same error. Also, I am not working Remotely. I am accessing directly on the server, actually directly on the desktop.

Every, post I have read has pointed to a permissions issue within the SQL service. Where can I find information on adjusting these permissions?

Also, I am logged in as Administrator when I run the package, I have successfully run the package by specifying a specific file name AND only get the Access Denied error when I choose a folder from which to read. Am I doing something wrong?

I too am writing this before coffee, and not sure if my post makes much sense. But I do thank everyone for the help...jm

|||

Donald,

Thanks for clarifying what was going on. I'm still very new to Integration Services.

|||

Hi,

We are having the same issue. We have two SQL Servers running on the same machine. One is SQLDEV-DBS and it is SQL Server 2000. The other is SQLDEV-DBS\SQL2005 and it is SQL Server 2005. We can bulk insert without issue into the SQL Server 2000 instance but when we try to do a bulk insert into the SQL Server 2005 instance we get the message:

Error: 0xC002F304 at Load Stumast, Bulk Insert Task: An error occurred with the following error message: "Cannot bulk load because the file "\\sqlprd-dbs\sqlimport\DLSTD4.TXT" could not be opened. Operating system error code 5(Access is denied.).".

Both servers are running under the same domain accounts so this is a real brain teaser.

Anyone have any ideas? Which service do we need to check? There are a lot more under 2005 than 2000.

Thanks for any and all help.

|||Hi! i am having the same porblem but could not find a solution to this ......|||Have you had any luck with this. I still cannot get this to work. Thanks!|||The flatfile connection manager in bulk insert task should point to the file containing source data and not the folder. So, set the path to the file.

Problem in bulk insert

Hi All,

I am trying to perform Bulk Insert for an existing folder containing the data files into an existing destination table . I get the following error :

[Bulk Insert Task] Error: An error occurred with the following error message: "Cannot bulk load because the file "C:\PD Data\PDDW_md_costs\Files_to_load" could not be opened. Operating system error code 5(Access is denied.).".

Here Files_to_load is the existing folder that contains the data files. I am not sure why it is saying that i do not have access to it. I have however also tried sharing the folder providing full access to it.

An input i would like to add is that i am performing all these tasks by remote connecting to a server machine that hosts SQL Server 2005 . I also am remote connecting using the administrator id to do these tasks.

If anyone has an idea on why this has occured please do let me know as this would really help solve the problem.

Thanks & regards,

Satya

I am having the same issue. Did anyone ever fiqure this out?

Thanks in advance.

JM

|||Have you tried using the UNC format for the foldername and path?|||

I'm also having this problem. What is UNC format (sorry if that's a stupid question - I'm a newbie)?

I've specified my input file using the connection manager, and selecting the file from the drop down list inside of Integration Services. It makes absolutely no sense to me why it suddenly can't see the file at runtime.

Thanks,

Laurie

|||Also, I should note that the file that I'm trying to access for the bulk insert is on my own local PC, not a remote drive somewhere on the network.|||

UNC path is \\MachineName\filePath. However, I tried this with no luck. Anyone else have any ideas? I have been stuck on this for a week and ordered a book that was no help.

JM

|||

I got past this problem by moving my data file to a shared drive. While I am running Integration Services on my local PC, SQL 2005 itself actually resides on another server on my network. When the package attempted to run, it was running from that remote server, causing the unable to access file error.

The fact that I could see the file and link to it easily with no warning from the Connection manager strikes me as inconsistent design. It should give you an error message or something if the file is going to be inaccessible at runtime. Just my 2 cents worth.

|||One more thing - you also need to use UNC format for the file name and path.|||

>While I am running Integration Services on my local PC, SQL 2005 itself actually resides on another server on my network. When the package attempted to run, it was running from that remote server, causing the unable to access file error.

Is that the case? To run the package remotely you woudl need to be invoking it with SQL Agent.

However, even if the package is actually running locally, if the Bulk Insert Task uses a remote SQL Server, then you are asking that remote SQL Server to perform the bulk insert. That SQL Server must have access to the source file.

You make an interesting point about whether we should validate that remote access at design time. However, the SQL Server Bulk Insert operation does not provide us with an option to validate - we have to invoke the operation to do so, which in turn would execute the Bulk Insert operation.

I'm thinking (before coffee, so don't expect too much) that it would be possible to craft a stored proc which could be called by OnValidate on the BulkInsert task that could effectively validate access to the source file.

Donald Farmer

|||

I have used the UNC path name to specify the folder in which to read. I get the same error. Also, I am not working Remotely. I am accessing directly on the server, actually directly on the desktop.

Every, post I have read has pointed to a permissions issue within the SQL service. Where can I find information on adjusting these permissions?

Also, I am logged in as Administrator when I run the package, I have successfully run the package by specifying a specific file name AND only get the Access Denied error when I choose a folder from which to read. Am I doing something wrong?

I too am writing this before coffee, and not sure if my post makes much sense. But I do thank everyone for the help...jm

|||

Donald,

Thanks for clarifying what was going on. I'm still very new to Integration Services.

|||

Hi,

We are having the same issue. We have two SQL Servers running on the same machine. One is SQLDEV-DBS and it is SQL Server 2000. The other is SQLDEV-DBS\SQL2005 and it is SQL Server 2005. We can bulk insert without issue into the SQL Server 2000 instance but when we try to do a bulk insert into the SQL Server 2005 instance we get the message:

Error: 0xC002F304 at Load Stumast, Bulk Insert Task: An error occurred with the following error message: "Cannot bulk load because the file "\\sqlprd-dbs\sqlimport\DLSTD4.TXT" could not be opened. Operating system error code 5(Access is denied.).".

Both servers are running under the same domain accounts so this is a real brain teaser.

Anyone have any ideas? Which service do we need to check? There are a lot more under 2005 than 2000.

Thanks for any and all help.

|||Hi! i am having the same porblem but could not find a solution to this ......|||Have you had any luck with this. I still cannot get this to work. Thanks!|||The flatfile connection manager in bulk insert task should point to the file containing source data and not the folder. So, set the path to the file.

Problem in bulk insert

Hi All,

I am trying to perform Bulk Insert for an existing folder containing the data files into an existing destination table . I get the following error :

[Bulk Insert Task] Error: An error occurred with the following error message: "Cannot bulk load because the file "C:\PD Data\PDDW_md_costs\Files_to_load" could not be opened. Operating system error code 5(Access is denied.).".

Here Files_to_load is the existing folder that contains the data files. I am not sure why it is saying that i do not have access to it. I have however also tried sharing the folder providing full access to it.

An input i would like to add is that i am performing all these tasks by remote connecting to a server machine that hosts SQL Server 2005 . I also am remote connecting using the administrator id to do these tasks.

If anyone has an idea on why this has occured please do let me know as this would really help solve the problem.

Thanks & regards,

Satya

I am having the same issue. Did anyone ever fiqure this out?

Thanks in advance.

JM

|||Have you tried using the UNC format for the foldername and path?|||

I'm also having this problem. What is UNC format (sorry if that's a stupid question - I'm a newbie)?

I've specified my input file using the connection manager, and selecting the file from the drop down list inside of Integration Services. It makes absolutely no sense to me why it suddenly can't see the file at runtime.

Thanks,

Laurie

|||Also, I should note that the file that I'm trying to access for the bulk insert is on my own local PC, not a remote drive somewhere on the network.|||

UNC path is \\MachineName\filePath. However, I tried this with no luck. Anyone else have any ideas? I have been stuck on this for a week and ordered a book that was no help.

JM

|||

I got past this problem by moving my data file to a shared drive. While I am running Integration Services on my local PC, SQL 2005 itself actually resides on another server on my network. When the package attempted to run, it was running from that remote server, causing the unable to access file error.

The fact that I could see the file and link to it easily with no warning from the Connection manager strikes me as inconsistent design. It should give you an error message or something if the file is going to be inaccessible at runtime. Just my 2 cents worth.

|||One more thing - you also need to use UNC format for the file name and path.|||

>While I am running Integration Services on my local PC, SQL 2005 itself actually resides on another server on my network. When the package attempted to run, it was running from that remote server, causing the unable to access file error.

Is that the case? To run the package remotely you woudl need to be invoking it with SQL Agent.

However, even if the package is actually running locally, if the Bulk Insert Task uses a remote SQL Server, then you are asking that remote SQL Server to perform the bulk insert. That SQL Server must have access to the source file.

You make an interesting point about whether we should validate that remote access at design time. However, the SQL Server Bulk Insert operation does not provide us with an option to validate - we have to invoke the operation to do so, which in turn would execute the Bulk Insert operation.

I'm thinking (before coffee, so don't expect too much) that it would be possible to craft a stored proc which could be called by OnValidate on the BulkInsert task that could effectively validate access to the source file.

Donald Farmer

|||

I have used the UNC path name to specify the folder in which to read. I get the same error. Also, I am not working Remotely. I am accessing directly on the server, actually directly on the desktop.

Every, post I have read has pointed to a permissions issue within the SQL service. Where can I find information on adjusting these permissions?

Also, I am logged in as Administrator when I run the package, I have successfully run the package by specifying a specific file name AND only get the Access Denied error when I choose a folder from which to read. Am I doing something wrong?

I too am writing this before coffee, and not sure if my post makes much sense. But I do thank everyone for the help...jm

|||

Donald,

Thanks for clarifying what was going on. I'm still very new to Integration Services.

|||

Hi,

We are having the same issue. We have two SQL Servers running on the same machine. One is SQLDEV-DBS and it is SQL Server 2000. The other is SQLDEV-DBS\SQL2005 and it is SQL Server 2005. We can bulk insert without issue into the SQL Server 2000 instance but when we try to do a bulk insert into the SQL Server 2005 instance we get the message:

Error: 0xC002F304 at Load Stumast, Bulk Insert Task: An error occurred with the following error message: "Cannot bulk load because the file "\\sqlprd-dbs\sqlimport\DLSTD4.TXT" could not be opened. Operating system error code 5(Access is denied.).".

Both servers are running under the same domain accounts so this is a real brain teaser.

Anyone have any ideas? Which service do we need to check? There are a lot more under 2005 than 2000.

Thanks for any and all help.

|||Hi! i am having the same porblem but could not find a solution to this ......|||Have you had any luck with this. I still cannot get this to work. Thanks!|||The flatfile connection manager in bulk insert task should point to the file containing source data and not the folder. So, set the path to the file.sql

Problem in bulk insert

Hi All,

I am trying to perform Bulk Insert for an existing folder containing the data files into an existing destination table . I get the following error :

[Bulk Insert Task] Error: An error occurred with the following error message: "Cannot bulk load because the file "C:\PD Data\PDDW_md_costs\Files_to_load" could not be opened. Operating system error code 5(Access is denied.).".

Here Files_to_load is the existing folder that contains the data files. I am not sure why it is saying that i do not have access to it. I have however also tried sharing the folder providing full access to it.

An input i would like to add is that i am performing all these tasks by remote connecting to a server machine that hosts SQL Server 2005 . I also am remote connecting using the administrator id to do these tasks.

If anyone has an idea on why this has occured please do let me know as this would really help solve the problem.

Thanks & regards,

Satya

I am having the same issue. Did anyone ever fiqure this out?

Thanks in advance.

JM

|||Have you tried using the UNC format for the foldername and path?|||

I'm also having this problem. What is UNC format (sorry if that's a stupid question - I'm a newbie)?

I've specified my input file using the connection manager, and selecting the file from the drop down list inside of Integration Services. It makes absolutely no sense to me why it suddenly can't see the file at runtime.

Thanks,

Laurie

|||Also, I should note that the file that I'm trying to access for the bulk insert is on my own local PC, not a remote drive somewhere on the network.|||

UNC path is \\MachineName\filePath. However, I tried this with no luck. Anyone else have any ideas? I have been stuck on this for a week and ordered a book that was no help.

JM

|||

I got past this problem by moving my data file to a shared drive. While I am running Integration Services on my local PC, SQL 2005 itself actually resides on another server on my network. When the package attempted to run, it was running from that remote server, causing the unable to access file error.

The fact that I could see the file and link to it easily with no warning from the Connection manager strikes me as inconsistent design. It should give you an error message or something if the file is going to be inaccessible at runtime. Just my 2 cents worth.

|||One more thing - you also need to use UNC format for the file name and path.|||

>While I am running Integration Services on my local PC, SQL 2005 itself actually resides on another server on my network. When the package attempted to run, it was running from that remote server, causing the unable to access file error.

Is that the case? To run the package remotely you woudl need to be invoking it with SQL Agent.

However, even if the package is actually running locally, if the Bulk Insert Task uses a remote SQL Server, then you are asking that remote SQL Server to perform the bulk insert. That SQL Server must have access to the source file.

You make an interesting point about whether we should validate that remote access at design time. However, the SQL Server Bulk Insert operation does not provide us with an option to validate - we have to invoke the operation to do so, which in turn would execute the Bulk Insert operation.

I'm thinking (before coffee, so don't expect too much) that it would be possible to craft a stored proc which could be called by OnValidate on the BulkInsert task that could effectively validate access to the source file.

Donald Farmer

|||

I have used the UNC path name to specify the folder in which to read. I get the same error. Also, I am not working Remotely. I am accessing directly on the server, actually directly on the desktop.

Every, post I have read has pointed to a permissions issue within the SQL service. Where can I find information on adjusting these permissions?

Also, I am logged in as Administrator when I run the package, I have successfully run the package by specifying a specific file name AND only get the Access Denied error when I choose a folder from which to read. Am I doing something wrong?

I too am writing this before coffee, and not sure if my post makes much sense. But I do thank everyone for the help...jm

|||

Donald,

Thanks for clarifying what was going on. I'm still very new to Integration Services.

|||

Hi,

We are having the same issue. We have two SQL Servers running on the same machine. One is SQLDEV-DBS and it is SQL Server 2000. The other is SQLDEV-DBS\SQL2005 and it is SQL Server 2005. We can bulk insert without issue into the SQL Server 2000 instance but when we try to do a bulk insert into the SQL Server 2005 instance we get the message:

Error: 0xC002F304 at Load Stumast, Bulk Insert Task: An error occurred with the following error message: "Cannot bulk load because the file "\\sqlprd-dbs\sqlimport\DLSTD4.TXT" could not be opened. Operating system error code 5(Access is denied.).".

Both servers are running under the same domain accounts so this is a real brain teaser.

Anyone have any ideas? Which service do we need to check? There are a lot more under 2005 than 2000.

Thanks for any and all help.

|||Hi! i am having the same porblem but could not find a solution to this ......|||Have you had any luck with this. I still cannot get this to work. Thanks!|||The flatfile connection manager in bulk insert task should point to the file containing source data and not the folder. So, set the path to the file.

Problem in bulk insert

Hi All,

I am trying to perform Bulk Insert for an existing folder containing the data files into an existing destination table . I get the following error :

[Bulk Insert Task] Error: An error occurred with the following error message: "Cannot bulk load because the file "C:\PD Data\PDDW_md_costs\Files_to_load" could not be opened. Operating system error code 5(Access is denied.).".

Here Files_to_load is the existing folder that contains the data files. I am not sure why it is saying that i do not have access to it. I have however also tried sharing the folder providing full access to it.

An input i would like to add is that i am performing all these tasks by remote connecting to a server machine that hosts SQL Server 2005 . I also am remote connecting using the administrator id to do these tasks.

If anyone has an idea on why this has occured please do let me know as this would really help solve the problem.

Thanks & regards,

Satya

I am having the same issue. Did anyone ever fiqure this out?

Thanks in advance.

JM

|||Have you tried using the UNC format for the foldername and path?|||

I'm also having this problem. What is UNC format (sorry if that's a stupid question - I'm a newbie)?

I've specified my input file using the connection manager, and selecting the file from the drop down list inside of Integration Services. It makes absolutely no sense to me why it suddenly can't see the file at runtime.

Thanks,

Laurie

|||Also, I should note that the file that I'm trying to access for the bulk insert is on my own local PC, not a remote drive somewhere on the network.|||

UNC path is \\MachineName\filePath. However, I tried this with no luck. Anyone else have any ideas? I have been stuck on this for a week and ordered a book that was no help.

JM

|||

I got past this problem by moving my data file to a shared drive. While I am running Integration Services on my local PC, SQL 2005 itself actually resides on another server on my network. When the package attempted to run, it was running from that remote server, causing the unable to access file error.

The fact that I could see the file and link to it easily with no warning from the Connection manager strikes me as inconsistent design. It should give you an error message or something if the file is going to be inaccessible at runtime. Just my 2 cents worth.

|||One more thing - you also need to use UNC format for the file name and path.|||

>While I am running Integration Services on my local PC, SQL 2005 itself actually resides on another server on my network. When the package attempted to run, it was running from that remote server, causing the unable to access file error.

Is that the case? To run the package remotely you woudl need to be invoking it with SQL Agent.

However, even if the package is actually running locally, if the Bulk Insert Task uses a remote SQL Server, then you are asking that remote SQL Server to perform the bulk insert. That SQL Server must have access to the source file.

You make an interesting point about whether we should validate that remote access at design time. However, the SQL Server Bulk Insert operation does not provide us with an option to validate - we have to invoke the operation to do so, which in turn would execute the Bulk Insert operation.

I'm thinking (before coffee, so don't expect too much) that it would be possible to craft a stored proc which could be called by OnValidate on the BulkInsert task that could effectively validate access to the source file.

Donald Farmer

|||

I have used the UNC path name to specify the folder in which to read. I get the same error. Also, I am not working Remotely. I am accessing directly on the server, actually directly on the desktop.

Every, post I have read has pointed to a permissions issue within the SQL service. Where can I find information on adjusting these permissions?

Also, I am logged in as Administrator when I run the package, I have successfully run the package by specifying a specific file name AND only get the Access Denied error when I choose a folder from which to read. Am I doing something wrong?

I too am writing this before coffee, and not sure if my post makes much sense. But I do thank everyone for the help...jm

|||

Donald,

Thanks for clarifying what was going on. I'm still very new to Integration Services.

|||

Hi,

We are having the same issue. We have two SQL Servers running on the same machine. One is SQLDEV-DBS and it is SQL Server 2000. The other is SQLDEV-DBS\SQL2005 and it is SQL Server 2005. We can bulk insert without issue into the SQL Server 2000 instance but when we try to do a bulk insert into the SQL Server 2005 instance we get the message:

Error: 0xC002F304 at Load Stumast, Bulk Insert Task: An error occurred with the following error message: "Cannot bulk load because the file "\\sqlprd-dbs\sqlimport\DLSTD4.TXT" could not be opened. Operating system error code 5(Access is denied.).".

Both servers are running under the same domain accounts so this is a real brain teaser.

Anyone have any ideas? Which service do we need to check? There are a lot more under 2005 than 2000.

Thanks for any and all help.

|||Hi! i am having the same porblem but could not find a solution to this ......|||Have you had any luck with this. I still cannot get this to work. Thanks!|||The flatfile connection manager in bulk insert task should point to the file containing source data and not the folder. So, set the path to the file.

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 copying database to new server!

I am trying to copy a
dbase from one server to another so first created a backup file from source
server,then copied it to destination server. Then created an empty dbase
with same
name on destination server, then selected backup and created a backup file
dbase.bak. I then replaced the backup
file on the destination server with the one from the source server and tried
to restore
from that file. I get the following error, can not find file id 3 on device
c:\program files\...Backup\dbase.bak
thanks.
Paul G
Software engineer.
Your problem is that the original file destination of the database is
stored in the backup file. SO it can be that the stored destination
folder isn=B4t present in your restore system. With specifying a new
restore destination while issueing the command (via GUI or via TSQL
[RESTORE DATABASE]) that should do the trick.
HTH, Jens Suessmeyer.
|||Hi thanks for the response. I tried RESTORE database 'name' from disk
='c:\filebak' from sql analyzer simular to what you suggested and it worked.
Paul G
Software engineer.
"Jens" wrote:

> Your problem is that the original file destination of the database is
> stored in the backup file. SO it can be that the stored destination
> folder isn′t present in your restore system. With specifying a new
> restore destination while issueing the command (via GUI or via TSQL
> [RESTORE DATABASE]) that should do the trick.
> HTH, Jens Suessmeyer.
>

problem copying database to new server!

I am trying to copy a
dbase from one server to another so first created a backup file from source
server,then copied it to destination server. Then created an empty dbase
with same
name on destination server, then selected backup and created a backup file
dbase.bak. I then replaced the backup
file on the destination server with the one from the source server and tried
to restore
from that file. I get the following error, can not find file id 3 on device
c:\program files\...Backup\dbase.bak
thanks.
--
Paul G
Software engineer.Your problem is that the original file destination of the database is
stored in the backup file. SO it can be that the stored destination
folder isn=B4t present in your restore system. With specifying a new
restore destination while issueing the command (via GUI or via TSQL
[RESTORE DATABASE]) that should do the trick.
HTH, Jens Suessmeyer.|||Hi thanks for the response. I tried RESTORE database 'name' from disk
='c:\filebak' from sql analyzer simular to what you suggested and it worked.
--
Paul G
Software engineer.
"Jens" wrote:
> Your problem is that the original file destination of the database is
> stored in the backup file. SO it can be that the stored destination
> folder isn´t present in your restore system. With specifying a new
> restore destination while issueing the command (via GUI or via TSQL
> [RESTORE DATABASE]) that should do the trick.
> HTH, Jens Suessmeyer.
>

problem copying database to new server!

I am trying to copy a
dbase from one server to another so first created a backup file from source
server,then copied it to destination server. Then created an empty dbase
with same
name on destination server, then selected backup and created a backup file
dbase.bak. I then replaced the backup
file on the destination server with the one from the source server and tried
to restore
from that file. I get the following error, can not find file id 3 on device
c:\program files\...Backup\dbase.bak
thanks.
--
Paul G
Software engineer.Your problem is that the original file destination of the database is
stored in the backup file. SO it can be that the stored destination
folder isn=B4t present in your restore system. With specifying a new
restore destination while issueing the command (via GUI or via TSQL
[RESTORE DATABASE]) that should do the trick.
HTH, Jens Suessmeyer.|||Hi thanks for the response. I tried RESTORE database 'name' from disk
='c:\filebak' from sql analyzer simular to what you suggested and it worked.
--
Paul G
Software engineer.
"Jens" wrote:

> Your problem is that the original file destination of the database is
> stored in the backup file. SO it can be that the stored destination
> folder isn′t present in your restore system. With specifying a new
> restore destination while issueing the command (via GUI or via TSQL
> [RESTORE DATABASE]) that should do the trick.
> HTH, Jens Suessmeyer.
>