October 4, 2016 at 12:00 pm
I have a package which laods data to excel ,but my manger wants to force column explicitly to string type before we export to excel.
So users just open and see the column and not do anything on excel file
October 4, 2016 at 12:07 pm
You can use a Derived Column transformation task between source and destination, or you can modify SQL to cast the column as specific data type.
For example: CAST( col1 as varchar(20) )
You may also want to convert NULL values to empty string ''.
ISNULL( CAST( col1 as varchar(20) ), '' )
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 4, 2016 at 12:11 pm
komal145 (10/4/2016)
I have a package which laods data to excel ,but my manger wants to force column explicitly to string type before we export to excel.So users just open and see the column and not do anything on excel file
Does the column contain data which Excel is interpreting as numeric?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 4, 2016 at 12:12 pm
i want everything to string , so it just prints the columns to excel. I am using data conversion task , DT_str? or DT_WSTR?
little confused which to use.
October 4, 2016 at 12:13 pm
Yes..there is data of date type , numeric too
October 4, 2016 at 12:16 pm
You could try adding a single quote, as the first character, to each value exported. When Excel sees a single quote as the first character of a cell it treats the data as text, and hides the single quote.
You'll have to convert each value to a string before you and add the single quote.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 4, 2016 at 12:19 pm
how can i do that?
October 4, 2016 at 12:31 pm
komal145 (10/4/2016)
how can i do that?
Derived Column:
"'"+ (DT_STR, 50,1252) [column]
for a varchar(50)-type column, for example.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 4, 2016 at 12:45 pm
[Derived Column [24]] Error: An error occurred while attempting to perform data conversion. The source type could not be converted to the destination type.
I keep getting this error again and again even after i used data conversion task to convert it after derived column.
October 4, 2016 at 12:53 pm
komal145 (10/4/2016)
[Derived Column [24]] Error: An error occurred while attempting to perform data conversion. The source type could not be converted to the destination type.I keep getting this error again and again even after i used data conversion task to convert it after derived column.
Please give me an example of
a) The source data type, and
b) The source data
for which you are receiving this error.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 4, 2016 at 1:00 pm
Source
[ItemID] [bigint] NULL
[State] [nvarchar](255)
[Trade Date] [date] NULL,
[invoice_id] [bigint] NULL,
FirstnameLast NameItemIDE-mail AddressAddress 2CityStateZipinvoice_idTrade DateKit Type Required
komal jawal3200011564abcd@gmail.com1234 berookdtAPT 123friscdc7534522343554444442016-09-29TGSAPmT01
October 4, 2016 at 1:06 pm
komal145 (10/4/2016)
Source datatype:[First Name] [nvarchar](255) NULL,
[Last Name] [nvarchar](255) NULL,
[ItemID] [bigint] NULL,
[E-mail] [nvarchar](255) NULL,
[Address] [nvarchar](255) NULL,
[Address 2] [nvarchar](255) NULL,
[City] [nvarchar](255) NULL,
[State] [nvarchar](255) NULL,
[Zip] [nvarchar](255) NULL,
[Trade Date] [date] NULL,
[invoice_id] [bigint] NULL,
[Kit Type Required] [nvarchar](255) NULL,
[CancelDate] [date] NULL,
[Fileid] [int] NULL
FirstnameLast NameItemIDE-mail AddressAddress 2CityStateZipinvoice_idTrade DateKit Type Required
komal jawal3200011564abcd@gmail.com1234 berookdtAPT 123friscdc7534522343554444442016-09-29TGSAPmT01
I don't want to try them all. Which one fails?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 4, 2016 at 1:09 pm
the one with date and int. I am using dataconversion task and chanhing everything to dt_wstr ...
overlapped columns. is there a way they do not overlap?
October 4, 2016 at 1:30 pm
komal145 (10/4/2016)
the one with date and int. I am using dataconversion task and chanhing everything to dt_wstr ...overlapped columns. is there a way they do not overlap?
Are you asking whether it is possible to amend the width of columns as displayed in Excel?
Remember that Excel is a spreadsheet, not a database. There is not really any such thing as a datatype in Excel, merely a display format.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 4, 2016 at 1:33 pm
the columns are overlapped in excel. so is there a way taht it is not overlapped while exporting to excel through ssis?
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply