Data Export Wizard - Drop Table option not available

I was using the Export Data Wizard to transfer my data to hosting provider, however the option to drop detsination table first is greyed out. Any Idea? Als Why do I need to do in for each table by clicking Edit? Is there a global setting like in the old Wizard?

Make sure your source and destination databases are different.

Cheers,
Dan

|||

Different server, but same name of database. I may have some restrictions on the destination server, since it is hosting environment. However I can create tables.

Also How to to this globally for all tables at once, instead of clicking Options one by one?

|||What could be the reason that the option "drop and recrete destination table" is grayed out in the Export Wizard (after clicking table edit button)? Is this realted to security?|||

Does anyone have a solution for this?

As soon as the database name is the same this option is disabled? Is this a bug? I have full permissions so it can't be a security issue.

sql

Data Export Wizard - Drop Table option not available

I was using the Export Data Wizard to transfer my data to hosting provider, however the option to drop detsination table first is greyed out. Any Idea? Als Why do I need to do in for each table by clicking Edit? Is there a global setting like in the old Wizard?

Make sure your source and destination databases are different.

Cheers,
Dan

|||

Different server, but same name of database. I may have some restrictions on the destination server, since it is hosting environment. However I can create tables.

Also How to to this globally for all tables at once, instead of clicking Options one by one?

|||What could be the reason that the option "drop and recrete destination table" is grayed out in the Export Wizard (after clicking table edit button)? Is this realted to security?|||

Does anyone have a solution for this?

As soon as the database name is the same this option is disabled? Is this a bug? I have full permissions so it can't be a security issue.

Data converted when loaded into SQL 2k5 table

Hi All

Data in access is converted when loaded into SQL 2k5

00 >> 0

01 >> 1

03 >> 3

I am using SSIS import wizard to load data from MS Access ’03 into a SQL 2k5 database.Some data of the data is converted, or the leading zero is deleted when loaded into sql table.

The data type on the source field is byte with a 00 format. The data type in 2k5 is tinyint.

I need some help with getting the data to load into 2k5 exactly as it appears in access.

Thanks for you help.

Nats

Hi Nats

One quick question - how is the data going to be used once it has been imported? To store data in the format that you specified then you'll have to decare the column as a text-based datatype, such as VARCHAR, which is not necessarily the best option for numerical data.

If you're going to perform calculations on the data then it might be better to store the data as TINYINT then manipulate the formatting when you want to return / display the data.

e.g.

DECLARE @.int INT

SET @.int = 1

SELECT '00' + RIGHT(CAST(@.int AS VARCHAR(1)), 2)

...will return a text string of '01' even though @.int is of type integer.

Chris

|||

I don't think it will be used in any calculation.

Thanks,

Data converted when loaded into SQL 2k5 table

Hi All

Data in access is converted when loaded into SQL 2k5

00 >> 0

01 >> 1

03 >> 3

I am using SSIS import wizard to load data from MS Access ’03 into a SQL 2k5 database.Some data of the data is converted, or the leading zero is deleted when loaded into sql table.

The data type on the source field is byte with a 00 format. The data type in 2k5 is tinyint.

I need some help with getting the data to load into 2k5 exactly as it appears in access.

Thanks for you help.

Nats

Hi Nats

One quick question - how is the data going to be used once it has been imported? To store data in the format that you specified then you'll have to decare the column as a text-based datatype, such as VARCHAR, which is not necessarily the best option for numerical data.

If you're going to perform calculations on the data then it might be better to store the data as TINYINT then manipulate the formatting when you want to return / display the data.

e.g.

DECLARE @.int INT

SET @.int = 1

SELECT '00' + RIGHT(CAST(@.int AS VARCHAR(1)), 2)

...will return a text string of '01' even though @.int is of type integer.

Chris

|||

I don't think it will be used in any calculation.

Thanks,

Data Conversion Components & Code Page issue

I am using the SSIS wizard to pull data from DB2 z/os to sql server. The data flow task that is created converts the data to DT_STR Ansi 1252 before storing to sql server database. The package is blowing up on in the data conversion component...no match in found in target code page...for my city name field.

My old dts wizard didn't have this problem. The forums seem to indicate that SSIS is no longer doing some of the implicit conversions that DTS did and I may have to do more than one conversion.

What format type/code page do I use for the other conversion? The code page for my DB2 data source is 37.

I've tried several scenarios and none of them have worked. Any hints?

Quick and dirty work around was to change the destination column to nchar.|||

This one is a good workaround if you don't mind your strings being Unicode at the destination.

If you would rather keep your ANSI strings, then you should go to the package set the appropriate code page in the data conversion transform and set the appropriate collation in the CREATE TABLE statement.

Thanks.

|||

I tried that and couldn't get it to work.

My problem isn't a foreign language issue, but a special character issue. My old DTS packages accepted the special characters without a fuss but I'm told SSIS isn't doing the implicit conversion that the old DTS use to do.

Obviously, I can't seem to figure out which code page to use. And where do I set all of these code pages? My input data is coming from DB2 z/OS which is using code page 37. Do I set the code page of the source component to 37? The destination? The transform component? I tried setting the code page with multiple scenarios...none worked.

There is no where that I can find that explains how to do a code page transformation except in the most general terms.

PS. Wouldn't I only change the collation for a foreign language?

Customizing Report Model AutoGeneration

Is there any way to customize the expressions that are generated when using the AutoGenerate wizard to produce a model in Visual/BI Studio? There are certain expressions that my team always has to generate for nearly every datetime attribute. Adding the expressions manually is very tedious since the expression window does not allow simple copy & paste.

There is no supported way to customize the fields created by the model generation wizard. However, if you poke around a little on your file system (for instance, under C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ReportModelProjects), you might find an undocumented file that, when modified, seems to influence the output of the model generation wizard. Remember, though, you're on your own. :)

Customizing Report Model AutoGeneration

Is there any way to customize the expressions that are generated when using the AutoGenerate wizard to produce a model in Visual/BI Studio? There are certain expressions that my team always has to generate for nearly every datetime attribute. Adding the expressions manually is very tedious since the expression window does not allow simple copy & paste.

There is no supported way to customize the fields created by the model generation wizard. However, if you poke around a little on your file system (for instance, under C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ReportModelProjects), you might find an undocumented file that, when modified, seems to influence the output of the model generation wizard. Remember, though, you're on your own. :)