Re: DTS to SSIS lost functionality

#1

Reg Besseling wrote:

Quote:

Hi all

When using DTS importing data from my AS/400 - DB2 data base was a
trivial matter, all I did was select client access 400 ODBC then
select my tables and all was done.

I cannot find anything like this in SSIS. the ODBC connection does
not list the tables in the library thus forces the entry of a SQL
statement then it incorrectly detects the data type of the cols. so I
have to manually change the data types for all the cols.

The OLE DB connection detects 3 tables in the library ( there are
over 200) and we are back again to the ODBC issues

I really want to use SSIS but this is making it extremely painful
(not to mention long winded ) to do.

Can somebody help me with making this easier

Thanks

Reg

I agree with using the OLEDB provider from Microsoft. That solved many
problems for us also.

In our experience it is not the use of a SQL Statement that provides all of
the Field type conversion problems. We get that whether we use a SQL
statement or specify a table. In my opinion it is a bug in the SSIS or
Visual Studio environment.

What we found solves all of those problem is to ALWAYS use the wizard to
create the package. Nearly all packages we build using the wizard have zero
problems with data types. All of those that we created manually forced us
to put an explicit conversion into the Data Conversion Task which is really
a PITA on large tables.

We even find that if we create a package with the wizard and then need to
modify it to include fewer or more fields that we have to use the wizard to
build the new task from scratch. Any attempt to modify the existing package
again forces us to explicitly add conversions to nearly all text fields.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

#2

Rick,

Have you ever compared the Wizard generated package to a hand built to
detect the differences. I see no reason why you could not build the same
package by hand, do you think otherwise? Just interested.

Saying that using the Wizard to do some of the hard work seems like a no
brainier. In case you are not aware, the Wizard uses mapping files to help
it make accurate data type selection between different provider types, and
you are obviously getting the benefit of this and quite like it. It is a
shame some of the source components do not consume the same mapping files
when used directly in the designer.

--
Darren
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
http://www.konesans.com

"Rick Brandt" <rickbrandt2 (AT) hotmail (DOT) com> wrote

Quote:

Reg Besseling wrote:
Hi all

When using DTS importing data from my AS/400 - DB2 data base was a
trivial matter, all I did was select client access 400 ODBC then
select my tables and all was done.

I cannot find anything like this in SSIS. the ODBC connection does
not list the tables in the library thus forces the entry of a SQL
statement then it incorrectly detects the data type of the cols. so I
have to manually change the data types for all the cols.

The OLE DB connection detects 3 tables in the library ( there are
over 200) and we are back again to the ODBC issues

I really want to use SSIS but this is making it extremely painful
(not to mention long winded ) to do.

Can somebody help me with making this easier

Thanks

Reg

I agree with using the OLEDB provider from Microsoft. That solved many
problems for us also.

In our experience it is not the use of a SQL Statement that provides all
of the Field type conversion problems. We get that whether we use a SQL
statement or specify a table. In my opinion it is a bug in the SSIS or
Visual Studio environment.

What we found solves all of those problem is to ALWAYS use the wizard to
create the package. Nearly all packages we build using the wizard have
zero problems with data types. All of those that we created manually
forced us to put an explicit conversion into the Data Conversion Task
which is really a PITA on large tables.

We even find that if we create a package with the wizard and then need to
modify it to include fewer or more fields that we have to use the wizard
to build the new task from scratch. Any attempt to modify the existing
package again forces us to explicitly add conversions to nearly all text
fields.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

#3

Darren Green wrote:

Quote:

Rick,

Have you ever compared the Wizard generated package to a hand built to
detect the differences. I see no reason why you could not build the
same package by hand, do you think otherwise? Just interested.

Saying that using the Wizard to do some of the hard work seems like a
no brainier. In case you are not aware, the Wizard uses mapping files
to help it make accurate data type selection between different
provider types, and you are obviously getting the benefit of this and
quite like it. It is a shame some of the source components do not
consume the same mapping files when used directly in the designer.

Without the wizard we would need to create "Copy of FieldName" aliases and
then set the DataType of each one to "Unicode string [DT-WSTR]". Manually
that meant choosing the appropriate alias from the list of those available
and then manaully choosing the data type from the list of those available.
Doesn't sound like much, but some of these AS400 tables we are warehousing
have well over 100 fields in them (relationally sound they're not).

With the wizard there is no alias created and the data type is automatically
entered. Other than changing the name of the package from the default there
is very little left to do.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

#4

Todd C wrote:

Quote:

Try this with both a wizard in DTS and a wizard in SSIS:

Crate a package that exports data from a SQL table and dumps into an
Excel spreadsheet. Create a second package that takes from that same
spreadsheet and puts it back in the same SQL table. Accept all the
defaults

It's a no-brainer for DTS. The SSIS Wizard gets bound up in unicode vs
non-unicode conversions on the second package.

Took me three days to troubleshoot that one. Now THAT's a PITA.
I don't use any wizards anymore. I use my own Package templates.

I am not normally a big advocate of using wizards and I think it is very
possible that the problems with data typing when NOT using the wizard is
something that is specific to pulling data from an IBM box. The fact that
the opposite might be true and the wizard actually causes problems when
pulling from other sources is easy for me to believe as that has been my
general experience with wizards in the past.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: DTS to SSIS lost functionality

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Re: DTS to SSIS lost functionality - 12-08-2006 , 09:11 AM

Re: DTS to SSIS lost functionality - 12-08-2006 , 10:54 AM

Re: DTS to SSIS lost functionality - 12-08-2006 , 11:29 AM

Re: DTS to SSIS lost functionality - 12-08-2006 , 12:45 PM