Changing the name of the Table dynamically inside the SP.

hi All,

I get a daily dump of some data in a table like DumpTable_07182007, where the suffix is the date of the data dumped. Everyday a new table is created with the date as suffix as according to the date like DumpTable_07192007, DumpTable_07202007 etc.We get day before yesterday data on today.i.e. on 20 July we get the data for 18 july in the table DumpTable_07182007 and so on. Similarly on 21 July we will get the data in the table DumpTable_07192007 . I have to create a SP (to include in a job) that pulls some data from these dump tables in such a way that if the SP is fired by a job on 20July , it shud pull the data from DumpTable_07182007 . Similarly if the SP is fired on 21 july , it shud pull the data from DumpTable_07192007 table only . etc.

So my problem is how to change the table name dynamically inside the SP so that whenever the SP is fired on a particular date , it shud pull data from the appropriate table.

I hv something like : select count(*) from DumpTable_07182007 where abcd =1 ,

query inside my SP.

Plz guide me in dynamically changing the tablename in the SP .

Thanks in advance.

Hi,

You can not have a dynamic table name in a query. One way to do this is to generate your query as a text each time, and then execute it using sp_executesql.

If you want to use its output as a table, then you can also create a table-value function, and pass the date to it. It should create a table in script and execute it and return that result.

Zafar|||

Hi,

You have to frame the select query as a string (VARCHAR), and then execute the statement.

By using this logic, you can dynamically modify not only your table name but the entire query.

example: ( similar to this )

DECLARE @.myQuery VARCHAR(200)

SET @.myQuery = 'SELECT COUNT(*) FROM DumTable_' + @.DateParameter

EXECUTE (@.myQuery)

Regards,

Perumal.R,

Prelude Solution Providers India Pvt. Ltd.,

Kotturpuram,

Chennai.

|||

You don't need to use dynamic SQL. You can try the approach below which is more easy to debug and maintain. This requires the caller to have create view permission and I assume this is probably ok in this case since this looks like a batch job on the server. So you can run it under dbo account.

1. Create a SP that create a view dynamically. The view will refer to the DumpTable being loaded. For example:

Code Snippet

create procedure CreateDumpTableView (@.date varchar(10))

as

begin

declare @.tablename nvarchar(130);

set @.tablename = quotename(N'DumpTable_' + @.date);

exec('create view DumpTableRef as select .... from ' + @.tablename);

end

2. Now, in your main SP write your queries against the view and call the the create view SP first when the date changes like:

Code Snippet

create procedure YourSp (@.date varchar(10))

as

begin

exec CreateDumpTableView @.date;

if @.@.error....

select count(*) from DumpTableRef;

end

sql

Changing the current database inside a stored procedure

Hi all,

I need to change the current database from within a stored procedure or something that can be called by a stored procedure. I know the use statement is ineffective in these circumstances. Just need to know if there's a method out there I can employ.

Any help would be really appreciated.

Richyou don't need to change DBs while in a stored procedure just reference the object(s) using the three part object name: <DATABASE>.<OWNER>.<OBJECT NAME>.|||Thanks Paul...one of those times when you miss the blind obvious.

Cheers

Rich

Changing the color of the series label

I am able to change the colors of the series inside the graph, however I
would like to make one data point stand out based on a parameter that is
passed in. I have already set up the color schemes for the series to do this
inside the chart but would like the label to make that distinction as well.
For example I would like to set the series label to red for one data point
while all of the other labels would be black. Is this possible?
ThanksIf you have at least RS 2000 SP1 installed, you can edit the data point
label properties (font, color, etc.) and use expressions to determine the
color based on certain conditions.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Todd" <Todd@.discussions.microsoft.com> wrote in message
news:EAEF7051-1909-4CD8-93D8-F6842C2887B9@.microsoft.com...
>I am able to change the colors of the series inside the graph, however I
> would like to make one data point stand out based on a parameter that is
> passed in. I have already set up the color schemes for the series to do
> this
> inside the chart but would like the label to make that distinction as
> well.
> For example I would like to set the series label to red for one data point
> while all of the other labels would be black. Is this possible?
> Thanks|||I am running with SP2 and have attempted to put in the code to make this
happen but it renders all of the labels with the color not just the one that
I am looking for. I have tested the snippet of code elsewhere and it works
fine. It seems as if the chart rendering looks at the first attribute passed
for the series labels and stops looking at the attributes for the rest of the
data points.
"Robert Bruckner [MSFT]" wrote:
> If you have at least RS 2000 SP1 installed, you can edit the data point
> label properties (font, color, etc.) and use expressions to determine the
> color based on certain conditions.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Todd" <Todd@.discussions.microsoft.com> wrote in message
> news:EAEF7051-1909-4CD8-93D8-F6842C2887B9@.microsoft.com...
> >I am able to change the colors of the series inside the graph, however I
> > would like to make one data point stand out based on a parameter that is
> > passed in. I have already set up the color schemes for the series to do
> > this
> > inside the chart but would like the label to make that distinction as
> > well.
> >
> > For example I would like to set the series label to red for one data point
> > while all of the other labels would be black. Is this possible?
> >
> > Thanks
>
>|||Does the chart have a series grouping?
If yes, then assuming you have a series grouping called "ProductCategory",
you have to use a style color expression with an aggregate function:
= iif(First(Fields!Abc.Value, "ProductCategory") > 10, "Red", Nothing)
The important part is the aggregate scope which has to be identical to the
chart series grouping name. Just using the First aggregate without the scope
will give you incorrect results, because the aggregate will be just scoped
for every chart datapoint (and therefore null if you don't have any
datapoints for a particular series group / category group combination).
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Todd" <Todd@.discussions.microsoft.com> wrote in message
news:3048F968-1AA1-474D-9EEA-4C985F5DC1DA@.microsoft.com...
>I am running with SP2 and have attempted to put in the code to make this
> happen but it renders all of the labels with the color not just the one
> that
> I am looking for. I have tested the snippet of code elsewhere and it
> works
> fine. It seems as if the chart rendering looks at the first attribute
> passed
> for the series labels and stops looking at the attributes for the rest of
> the
> data points.
> "Robert Bruckner [MSFT]" wrote:
>> If you have at least RS 2000 SP1 installed, you can edit the data point
>> label properties (font, color, etc.) and use expressions to determine the
>> color based on certain conditions.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Todd" <Todd@.discussions.microsoft.com> wrote in message
>> news:EAEF7051-1909-4CD8-93D8-F6842C2887B9@.microsoft.com...
>> >I am able to change the colors of the series inside the graph, however I
>> > would like to make one data point stand out based on a parameter that
>> > is
>> > passed in. I have already set up the color schemes for the series to
>> > do
>> > this
>> > inside the chart but would like the label to make that distinction as
>> > well.
>> >
>> > For example I would like to set the series label to red for one data
>> > point
>> > while all of the other labels would be black. Is this possible?
>> >
>> > Thanks
>>|||No, The chart does not have series grouping. I also wonder if I have
communicated this effectively. I am talking about the labels on the x-axis.
I am only concerned with the series labels not data point labels. Does this
help to clarify?
"Robert Bruckner [MSFT]" wrote:
> Does the chart have a series grouping?
> If yes, then assuming you have a series grouping called "ProductCategory",
> you have to use a style color expression with an aggregate function:
> = iif(First(Fields!Abc.Value, "ProductCategory") > 10, "Red", Nothing)
> The important part is the aggregate scope which has to be identical to the
> chart series grouping name. Just using the First aggregate without the scope
> will give you incorrect results, because the aggregate will be just scoped
> for every chart datapoint (and therefore null if you don't have any
> datapoints for a particular series group / category group combination).
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Todd" <Todd@.discussions.microsoft.com> wrote in message
> news:3048F968-1AA1-474D-9EEA-4C985F5DC1DA@.microsoft.com...
> >I am running with SP2 and have attempted to put in the code to make this
> > happen but it renders all of the labels with the color not just the one
> > that
> > I am looking for. I have tested the snippet of code elsewhere and it
> > works
> > fine. It seems as if the chart rendering looks at the first attribute
> > passed
> > for the series labels and stops looking at the attributes for the rest of
> > the
> > data points.
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> >> If you have at least RS 2000 SP1 installed, you can edit the data point
> >> label properties (font, color, etc.) and use expressions to determine the
> >> color based on certain conditions.
> >>
> >> -- Robert
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "Todd" <Todd@.discussions.microsoft.com> wrote in message
> >> news:EAEF7051-1909-4CD8-93D8-F6842C2887B9@.microsoft.com...
> >> >I am able to change the colors of the series inside the graph, however I
> >> > would like to make one data point stand out based on a parameter that
> >> > is
> >> > passed in. I have already set up the color schemes for the series to
> >> > do
> >> > this
> >> > inside the chart but would like the label to make that distinction as
> >> > well.
> >> >
> >> > For example I would like to set the series label to red for one data
> >> > point
> >> > while all of the other labels would be black. Is this possible?
> >> >
> >> > Thanks
> >>
> >>
> >>
>
>|||Sorry, the individual x-axis labels share the same color and font settings.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Todd" <Todd@.discussions.microsoft.com> wrote in message
news:7391723B-1CAB-4990-A5A7-E41A41DAF65E@.microsoft.com...
> No, The chart does not have series grouping. I also wonder if I have
> communicated this effectively. I am talking about the labels on the
> x-axis.
> I am only concerned with the series labels not data point labels. Does
> this
> help to clarify?
> "Robert Bruckner [MSFT]" wrote:
>> Does the chart have a series grouping?
>> If yes, then assuming you have a series grouping called
>> "ProductCategory",
>> you have to use a style color expression with an aggregate function:
>> = iif(First(Fields!Abc.Value, "ProductCategory") > 10, "Red", Nothing)
>> The important part is the aggregate scope which has to be identical to
>> the
>> chart series grouping name. Just using the First aggregate without the
>> scope
>> will give you incorrect results, because the aggregate will be just
>> scoped
>> for every chart datapoint (and therefore null if you don't have any
>> datapoints for a particular series group / category group combination).
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Todd" <Todd@.discussions.microsoft.com> wrote in message
>> news:3048F968-1AA1-474D-9EEA-4C985F5DC1DA@.microsoft.com...
>> >I am running with SP2 and have attempted to put in the code to make this
>> > happen but it renders all of the labels with the color not just the one
>> > that
>> > I am looking for. I have tested the snippet of code elsewhere and it
>> > works
>> > fine. It seems as if the chart rendering looks at the first attribute
>> > passed
>> > for the series labels and stops looking at the attributes for the rest
>> > of
>> > the
>> > data points.
>> >
>> > "Robert Bruckner [MSFT]" wrote:
>> >
>> >> If you have at least RS 2000 SP1 installed, you can edit the data
>> >> point
>> >> label properties (font, color, etc.) and use expressions to determine
>> >> the
>> >> color based on certain conditions.
>> >>
>> >> -- Robert
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>> >> "Todd" <Todd@.discussions.microsoft.com> wrote in message
>> >> news:EAEF7051-1909-4CD8-93D8-F6842C2887B9@.microsoft.com...
>> >> >I am able to change the colors of the series inside the graph,
>> >> >however I
>> >> > would like to make one data point stand out based on a parameter
>> >> > that
>> >> > is
>> >> > passed in. I have already set up the color schemes for the series
>> >> > to
>> >> > do
>> >> > this
>> >> > inside the chart but would like the label to make that distinction
>> >> > as
>> >> > well.
>> >> >
>> >> > For example I would like to set the series label to red for one data
>> >> > point
>> >> > while all of the other labels would be black. Is this possible?
>> >> >
>> >> > Thanks
>> >>
>> >>
>> >>
>>sql

Changing local variable inside query

/*Given*/
CREATE TABLE [_T1sub] (
[PK] [int] IDENTITY (1, 1) NOT NULL ,
[FK] [int] NULL ,
[St] [char] (2) NULL ,
[Wt] [int] NULL ,
CONSTRAINT [PK__T1sub] PRIMARY KEY CLUSTERED
(
[PK]
) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO _T1sub (FK,St,Wt) VALUES (1,'id',10)
INSERT INTO _T1sub (FK,St,Wt) VALUES (2,'nv',20)
INSERT INTO _T1sub (FK,St,Wt) VALUES (3,'wa',30)
/*
Is something like the following possible.
The point is to change the value of the variable
inside the query and use it in the calculated field.
This doesn't compile of course, but is there
a way to accomplish the same thing?
*/
DECLARE @.ndx int
SET @.ndx = 1
SELECT
(a.FK+ (CASE WHEN @.ndx > 0
THEN (SELECT @.ndx = b.Wt
FROM _T1sub b
WHERE b.Wt = a.Wt)
ELSE 0 END)
) as FKplusWT
FROM _T1sub a
/*Output would look like this:*/
FKplusWT
11
22
33
/*
I know, I can get this output just by adding
FK+WT. This is not about that.
This is about setting vars inside a query
*/
thanks, Otto Porter
On Sat, 02 Oct 2004 12:21:54 -0600, Otto Porter wrote:
(snip)
Hi Otto,
I just answered this question in comp.databases.ms-sqlserver. Please do
not post the same question independently to multiple newsgroups.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Changing local variable inside query

/*Given*/
CREATE TABLE [_T1sub] (
[PK] [int] IDENTITY (1, 1) NOT NULL ,
[FK] [int] NULL ,
[St] [char] (2) NULL ,
[Wt] [int] NULL ,
CONSTRAINT [PK__T1sub] PRIMARY KEY CLUSTERED
(
[PK]
) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO _T1sub (FK,St,Wt) VALUES (1,'id',10)
INSERT INTO _T1sub (FK,St,Wt) VALUES (2,'nv',20)
INSERT INTO _T1sub (FK,St,Wt) VALUES (3,'wa',30)
/*
Is something like the following possible.
The point is to change the value of the variable
inside the query and use it in the calculated field.
This doesn't compile of course, but is there
a way to accomplish the same thing?
*/
DECLARE @.ndx int
SET @.ndx = 1
SELECT
(a.FK+ (CASE WHEN @.ndx > 0
THEN (SELECT @.ndx = b.Wt
FROM _T1sub b
WHERE b.Wt = a.Wt)
ELSE 0 END)
) as FKplusWT
FROM _T1sub a
/*Output would look like this:*/
FKplusWT
--
11
22
33
/*
I know, I can get this output just by adding
FK+WT. This is not about that.
This is about setting vars inside a query
*/
thanks, Otto PorterOn Sat, 02 Oct 2004 12:21:54 -0600, Otto Porter wrote:
(snip)
Hi Otto,
I just answered this question in comp.databases.ms-sqlserver. Please do
not post the same question independently to multiple newsgroups.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Changing labels inside the rdl on runtime

Hi
Is it possible to change labels inside a report before rendering it? I need
to localize some labels before the report is shown in my
WinForms-Application. I do not want to have one report for each language.
Sometimes I need to change some more things in the report not only labels.
So, how can I get access to all the controls (lists, textboxes, subreports
etc.) of my report?
Thanks
MichaelThe approach I took was a custom assembly with a resource file. I park the
language identifier in a property then use an accessor method in the
assembly to fetch the string from the correct resource file. You can pass
in the ReportItems object into a custom assembly or code behind. If the
report has a page with no rows I've had trouble with this and wasn't able to
figure it out.
Thanks,
Steve MunLeeuw
"Michael Reukauff" <mreukauff@.gmx.net> wrote in message
news:O$Tk2LdpGHA.4996@.TK2MSFTNGP05.phx.gbl...
> Hi
> Is it possible to change labels inside a report before rendering it? I
> need to localize some labels before the report is shown in my
> WinForms-Application. I do not want to have one report for each language.
> Sometimes I need to change some more things in the report not only labels.
> So, how can I get access to all the controls (lists, textboxes, subreports
> etc.) of my report?
> Thanks
> Michael
>

Changing DataTypes in an Excel Data Source

Hello.

I'm importing some data from an excel file to sql server 2005.

I created an Excel Data Source inside my Data Flow Task but it is assuming that the source columns DataType is double-precision float [DT_R8]. It isn't, even though some rows may containg numeric string in the column's cell.

If I go to the Data Sources advanded editor, and modify the data type property of the column, SSIS complains the the error output and the source output are not of the same DataType. If I try to change the error output's data type in the advanced editor I get this error: "Property Value". The deailed error states:

Error at MOVIM 04 [MOVIM 04 [1]]: The data type for "output "Excel Source Error Output" (10)" cannot be modified in the error "output column "Agente Protector" (7662)".

Error at MOVIM 04 [MOVIM 04 [1]]: Failed to set property "DataType" on "output column "Agente Protector" (7662)".

If i let SSIS correct the error by itself, it changed the dource column back to double-precision float [DT_R8].

Is there any way to get arround this?

Thanks in advance,

Hugo Oliveira

Hi,

By default SSIS will consider first 8 rows to determine the data type of each column. Refer http://support.microsoft.com/kb/189897/en-us regarding this. U can add "IMEX=1; MAXROWSTOSCAN=0" to your excel connection string to get around the problem. Hope it will work.

|||

Hello.

I've tried your tip and it's wotking well now.

Thanks,

Hugo Oliveira

|||

Adding IMEX=1 to the connection string made the following Error message to appear.

"Coulnd not find Installable ISAM"

I've been to http://support.microsoft.com/kb/209805 and the path in the registry key is correct has they say in the article.

Did anyone had the same problem ?

|||OK, IMEX=1 should be added to the extended properties of the Connection String.|||

Hi Thiru_ and Hugo

I added IMEX=1 in connection string!

When i have a column with simple data, for example:

A column with integer and string values it functioned correctly, but if will have columns with differents formatted cells the IMEX parameter doesn't function returning one data type default.

Some idea for this problem?

Thanks!

Andr Rentes
Brazil

PS. Hugo você brasileiro? Se for entre em contato para trocar idias sobre o SSIS, n?o achei nenhum fórum brasileiro sobre o mesmo. Meu email rentes @. gmail.com

|||

Hi,

I have a problem similar to this one. My excel contains data of the "general" type, mixing in the same column data that are by nature chars and ints: ex: 1, 2, ..., "5+". The automatic indentation of excel shows that implicitly the "5+" is treated as a char and the rest as numbers.

Depending on the first value and the IMEX setting, I can make SSIS consider the values of one of these 2 datatypes. When I use DT_NUMERIC I lose the "5+" which seems logical. But when I convert everything to char, even unicode DT_WSTR, I would have excpected that BOTH the numeric and string values are converted. But then I only read "5+" and not the rest.

I don't find a way either to read a column in twice, once with one data type and once with another.

Does anybody no a way around this?

Many thanks,

Jan

Changing DataTypes in an Excel Data Source

Hello.

I'm importing some data from an excel file to sql server 2005.

I created an Excel Data Source inside my Data Flow Task but it is assuming that the source columns DataType is double-precision float [DT_R8]. It isn't, even though some rows may containg numeric string in the column's cell.

If I go to the Data Sources advanded editor, and modify the data type property of the column, SSIS complains the the error output and the source output are not of the same DataType. If I try to change the error output's data type in the advanced editor I get this error: "Property Value". The deailed error states:

Error at MOVIM 04 [MOVIM 04 [1]]: The data type for "output "Excel Source Error Output" (10)" cannot be modified in the error "output column "Agente Protector" (7662)".

Error at MOVIM 04 [MOVIM 04 [1]]: Failed to set property "DataType" on "output column "Agente Protector" (7662)".

If i let SSIS correct the error by itself, it changed the dource column back to double-precision float [DT_R8].

Is there any way to get arround this?

Thanks in advance,

Hugo Oliveira

Hi,

By default SSIS will consider first 8 rows to determine the data type of each column. Refer http://support.microsoft.com/kb/189897/en-us regarding this. U can add "IMEX=1; MAXROWSTOSCAN=0" to your excel connection string to get around the problem. Hope it will work.

|||

Hello.

I've tried your tip and it's wotking well now.

Thanks,

Hugo Oliveira

|||

Adding IMEX=1 to the connection string made the following Error message to appear.

"Coulnd not find Installable ISAM"

I've been to http://support.microsoft.com/kb/209805 and the path in the registry key is correct has they say in the article.

Did anyone had the same problem ?

|||OK, IMEX=1 should be added to the extended properties of the Connection String.|||

Hi Thiru_ and Hugo

I added IMEX=1 in connection string!

When i have a column with simple data, for example:

A column with integer and string values it functioned correctly, but if will have columns with differents formatted cells the IMEX parameter doesn't function returning one data type default.

Some idea for this problem?

Thanks!

Andr Rentes
Brazil

PS. Hugo você brasileiro? Se for entre em contato para trocar idias sobre o SSIS, n?o achei nenhum fórum brasileiro sobre o mesmo. Meu email rentes @. gmail.com

|||

Hi,

I have a problem similar to this one. My excel contains data of the "general" type, mixing in the same column data that are by nature chars and ints: ex: 1, 2, ..., "5+". The automatic indentation of excel shows that implicitly the "5+" is treated as a char and the rest as numbers.

Depending on the first value and the IMEX setting, I can make SSIS consider the values of one of these 2 datatypes. When I use DT_NUMERIC I lose the "5+" which seems logical. But when I convert everything to char, even unicode DT_WSTR, I would have excpected that BOTH the numeric and string values are converted. But then I only read "5+" and not the rest.

I don't find a way either to read a column in twice, once with one data type and once with another.

Does anybody no a way around this?

Many thanks,

Jan

Changing DataTypes in an Excel Data Source

Hello.

I'm importing some data from an excel file to sql server 2005.

I created an Excel Data Source inside my Data Flow Task but it is assuming that the source columns DataType is double-precision float [DT_R8]. It isn't, even though some rows may containg numeric string in the column's cell.

If I go to the Data Sources advanded editor, and modify the data type property of the column, SSIS complains the the error output and the source output are not of the same DataType. If I try to change the error output's data type in the advanced editor I get this error: "Property Value". The deailed error states:

Error at MOVIM 04 [MOVIM 04 [1]]: The data type for "output "Excel Source Error Output" (10)" cannot be modified in the error "output column "Agente Protector" (7662)".

Error at MOVIM 04 [MOVIM 04 [1]]: Failed to set property "DataType" on "output column "Agente Protector" (7662)".

If i let SSIS correct the error by itself, it changed the dource column back to double-precision float [DT_R8].

Is there any way to get arround this?

Thanks in advance,

Hugo Oliveira

Hi,

By default SSIS will consider first 8 rows to determine the data type of each column. Refer http://support.microsoft.com/kb/189897/en-us regarding this. U can add "IMEX=1; MAXROWSTOSCAN=0" to your excel connection string to get around the problem. Hope it will work.

|||

Hello.

I've tried your tip and it's wotking well now.

Thanks,

Hugo Oliveira

|||

Adding IMEX=1 to the connection string made the following Error message to appear.

"Coulnd not find Installable ISAM"

I've been to http://support.microsoft.com/kb/209805 and the path in the registry key is correct has they say in the article.

Did anyone had the same problem ?

|||OK, IMEX=1 should be added to the extended properties of the Connection String.|||

Hi Thiru_ and Hugo

I added IMEX=1 in connection string!

When i have a column with simple data, for example:

A column with integer and string values it functioned correctly, but if will have columns with differents formatted cells the IMEX parameter doesn't function returning one data type default.

Some idea for this problem?

Thanks!

Andr Rentes
Brazil

PS. Hugo você brasileiro? Se for entre em contato para trocar idias sobre o SSIS, n?o achei nenhum fórum brasileiro sobre o mesmo. Meu email rentes @. gmail.com

|||

Hi,

I have a problem similar to this one. My excel contains data of the "general" type, mixing in the same column data that are by nature chars and ints: ex: 1, 2, ..., "5+". The automatic indentation of excel shows that implicitly the "5+" is treated as a char and the rest as numbers.

Depending on the first value and the IMEX setting, I can make SSIS consider the values of one of these 2 datatypes. When I use DT_NUMERIC I lose the "5+" which seems logical. But when I convert everything to char, even unicode DT_WSTR, I would have excpected that BOTH the numeric and string values are converted. But then I only read "5+" and not the rest.

I don't find a way either to read a column in twice, once with one data type and once with another.

Does anybody no a way around this?

Many thanks,

Jan

Changing DataTypes in an Excel Data Source

Hello.

I'm importing some data from an excel file to sql server 2005.

I created an Excel Data Source inside my Data Flow Task but it is assuming that the source columns DataType is double-precision float [DT_R8]. It isn't, even though some rows may containg numeric string in the column's cell.

If I go to the Data Sources advanded editor, and modify the data type property of the column, SSIS complains the the error output and the source output are not of the same DataType. If I try to change the error output's data type in the advanced editor I get this error: "Property Value". The deailed error states:

Error at MOVIM 04 [MOVIM 04 [1]]: The data type for "output "Excel Source Error Output" (10)" cannot be modified in the error "output column "Agente Protector" (7662)".

Error at MOVIM 04 [MOVIM 04 [1]]: Failed to set property "DataType" on "output column "Agente Protector" (7662)".

If i let SSIS correct the error by itself, it changed the dource column back to double-precision float [DT_R8].

Is there any way to get arround this?

Thanks in advance,

Hugo Oliveira

Hi,

By default SSIS will consider first 8 rows to determine the data type of each column. Refer http://support.microsoft.com/kb/189897/en-us regarding this. U can add "IMEX=1; MAXROWSTOSCAN=0" to your excel connection string to get around the problem. Hope it will work.

|||

Hello.

I've tried your tip and it's wotking well now.

Thanks,

Hugo Oliveira

|||

Adding IMEX=1 to the connection string made the following Error message to appear.

"Coulnd not find Installable ISAM"

I've been to http://support.microsoft.com/kb/209805 and the path in the registry key is correct has they say in the article.

Did anyone had the same problem ?

|||OK, IMEX=1 should be added to the extended properties of the Connection String.|||

Hi Thiru_ and Hugo

I added IMEX=1 in connection string!

When i have a column with simple data, for example:

A column with integer and string values it functioned correctly, but if will have columns with differents formatted cells the IMEX parameter doesn't function returning one data type default.

Some idea for this problem?

Thanks!

Andr Rentes
Brazil

PS. Hugo você brasileiro? Se for entre em contato para trocar idias sobre o SSIS, n?o achei nenhum fórum brasileiro sobre o mesmo. Meu email rentes @. gmail.com

|||

Hi,

I have a problem similar to this one. My excel contains data of the "general" type, mixing in the same column data that are by nature chars and ints: ex: 1, 2, ..., "5+". The automatic indentation of excel shows that implicitly the "5+" is treated as a char and the rest as numbers.

Depending on the first value and the IMEX setting, I can make SSIS consider the values of one of these 2 datatypes. When I use DT_NUMERIC I lose the "5+" which seems logical. But when I convert everything to char, even unicode DT_WSTR, I would have excpected that BOTH the numeric and string values are converted. But then I only read "5+" and not the rest.

I don't find a way either to read a column in twice, once with one data type and once with another.

Does anybody no a way around this?

Many thanks,

Jan