Microsoft OLE DB Provider for DB2 in SSIS vs. ODBC in DTS

I finally made data transfer between AS400 and SQL2005 work by using Microsoft OLE DB provider for DB2. I have tried other methods, some do not have build-in destination, some have unicode conversion problem. The only issue I have with this method is the peformance. For example, 300,000 rows load from AS400 take only 3 minutes with the ODBC connection in DTS, but more than 5 minutes with the microsoft OLD DB provider for DB2 in SSIS.

Does anyone have the same issue or figured out any tweaking that may speed up the transfer?

Thanks.

In the 5 minute example, what was your destination?|||The destination is the same - an OLE DB connection that connect to a SQL 2005 server.|||

Jane2006 wrote:

The destination is the same - an OLE DB connection that connect to a SQL 2005 server.

And are you using the "fast load" option?|||

Since I also need to load data back from SQL2005 to AS400, I could not use ADO.NET/ODBC method unless as someone else suggested, build a component for that missing destination microsoft does not have time to build. Also, the unicode issue drives me crazy. So I decide to use Microsoft OLE DB Provider for DB2.

Based on my experience, only the version released in April 2007 is good on 64-bit machine. The old versions did not work on our 64-bit machine even thought it said it should.

|||

Are you talking about the Data access mode in OLE DB Destination Editor? Yes. I am using the fast load. Actually this is a default unless changed.

|||

Jane2006 wrote:

Since I also need to load data back from SQL2005 to AS400, I could not use ADO.NET/ODBC method unless as someone else suggested, build a component for that missing destination microsoft does not have time to build. Also, the unicode issue drives me crazy. So I decide to use Microsoft OLE DB Provider for DB2.

Based on my experience, only the version released in April 2007 is good on 64-bit machine. The old versions did not work on our 64-bit machine even thought it said it should.

This is irrelevant. On the OLE DB destination, are you using the "Table or view - fast load" option when loading your results from AS400 to SQL Server?|||Yes. I think there must be some settings in Microsoft Provider OLE DB Provider for DB2 that needs to be tuned up. I just don't know where.|||How many rows are you retrieving and what is your MaxInsertCommitSize set to?|||Retrieving 3048740 rows from AS400. Maximum insert commit size is 0.|||Try setting MaxInsertCommitSize to 20000 and see what happens.|||Just give it a try. Not much difference. Still more than 5 minutes.|||Any transformations? Or just source -> destination?

Are you specifying a query in the source connection, or selecting a table from the drop down list?|||The one fundamental difference here is that you are using a Microsoft DB2 driver. It isn't a native AS400 driver. It's a 3rd party driver as far as connecting to the mainframe is concerned. You can download the IBM DB2 driver and you might have luck with that.

This isn't the same as using ODBC, which would (perhaps) use a native driver to connect.|||

No transformation. A straight source to destination. I am using SQL commnd for this particular one.

I have used tables in other scenarions as well. The row sizes are much smaller than this one, but ODBC consistently has better performance, even though only several seconds better.

Methods within an assembly

Is there a way to retrieve the methods within an assembly that are attributed with SqlTrigger or SqlProcedure using T-SQL?

There is not a built-in way to do this, but it is not overly difficult to write a SQLCLR procedure that would do this for you. Miles has an example of a trigger than contains essentially all the code you need to do what you want here: http://blogs.msdn.com/sqlclr/articles/495428.aspx

(Note, the assembly does already have to be loaded into the database in order for you to use Reflection to examine its methods).

Steven

Methods to generate ER diagrams programmatically

Hi All,

Can anyone tell me if its possible to generate ER diagrams programmatically using SQL Server.

Any help would be highly appreciated.

Thanks

-Akash

SQL Server Management Studio has a "Database Diagrams" option under Databases->[database] that may provide a useful visualization of a given SQL Server database.

Hope that helps,

John

Methods to generate ER diagrams programmatically

Hi All,

Can anyone tell me if its possible to generate ER diagrams programmatically using SQL Server.

Any help would be highly appreciated.

Thanks

-Akash

Hi Akash,

I worked on this a couple of years ago with SQL 2000. I wrote an application using Java, then started to rewrite it in VB.NET. I programmatically collected ER data from SQL Server and then used Graphviz to build the diagrams. It worked well, but I never finished it.

Chris

|||

Hi Chris,

Thanks for the immediate response, would be higly obliged if you would be able to demonstate the code for collecting the ER diagram from SQL Server.

-Akash

|||

Like I said, I didnt work all the bugs out, but what I was able to create was an input file for Graphviz. I created a tables collection and added one object for each table. then, each table object had a columns collection which contained one object for each column. I had some additional properties to denote PK and FK columns. I then used SQLDMO to iterate through the SQL Server database and populate my collections of objects. I then wrote a second procedure which built the input file for Graphviz.

Generally the code looks like this:

For each table

add an item to the tables collection

add a columns collection to the table

add each table column to the columns collection

next column

next table

I'll try to post an output from Graphviz if you are interested.

Chris

|||

Hi Akash,

I found my code and checked it out. Currently, it works against both SQL 2000 and SQL 2005. I pointed it to the AdventureWorks sample database on a SQL 2005 box and it worked. If you will give me your email address I will send you the generated JPG output file.

|||

Hi Chris,

It would be really helpful. Do send it on lionhearted.prince@.gmail.com

Thanks

-Akash

|||

Hi Akash,

Was that output file what you were looking for?

|||

Hi Chris,

It was really great to see the output file, but that wasn't what exactly i needed. I need to generate ER diagrams programmatically using SQL Server without using a third party tool.

If you have any pointers to get it done, do share it.

Thanks

-Akash

Methods to generate ER diagrams programmatically

Hi All,

Can anyone tell me if its possible to generate ER diagrams programmatically using SQL Server.

Any help would be highly appreciated.

Thanks

-Akash

Hi Akash,

I worked on this a couple of years ago with SQL 2000. I wrote an application using Java, then started to rewrite it in VB.NET. I programmatically collected ER data from SQL Server and then used Graphviz to build the diagrams. It worked well, but I never finished it.

Chris

|||

Hi Chris,

Thanks for the immediate response, would be higly obliged if you would be able to demonstate the code for collecting the ER diagram from SQL Server.

-Akash

|||

Like I said, I didnt work all the bugs out, but what I was able to create was an input file for Graphviz. I created a tables collection and added one object for each table. then, each table object had a columns collection which contained one object for each column. I had some additional properties to denote PK and FK columns. I then used SQLDMO to iterate through the SQL Server database and populate my collections of objects. I then wrote a second procedure which built the input file for Graphviz.

Generally the code looks like this:

For each table

add an item to the tables collection

add a columns collection to the table

add each table column to the columns collection

next column

next table

I'll try to post an output from Graphviz if you are interested.

Chris

|||

Hi Akash,

I found my code and checked it out. Currently, it works against both SQL 2000 and SQL 2005. I pointed it to the AdventureWorks sample database on a SQL 2005 box and it worked. If you will give me your email address I will send you the generated JPG output file.

|||

Hi Chris,

It would be really helpful. Do send it on lionhearted.prince@.gmail.com

Thanks

-Akash

|||

Hi Akash,

Was that output file what you were looking for?

|||

Hi Chris,

It was really great to see the output file, but that wasn't what exactly i needed. I need to generate ER diagrams programmatically using SQL Server without using a third party tool.

If you have any pointers to get it done, do share it.

Thanks

-Akash

Methods to generate ER diagrams programmatically

Hi All,

Can anyone tell me if its possible to generate ER diagrams programmatically using SQL Server.

Any help would be highly appreciated.

Thanks

-Akash

Hi Akash,

I worked on this a couple of years ago with SQL 2000. I wrote an application using Java, then started to rewrite it in VB.NET. I programmatically collected ER data from SQL Server and then used Graphviz to build the diagrams. It worked well, but I never finished it.

Chris

|||

Hi Chris,

Thanks for the immediate response, would be higly obliged if you would be able to demonstate the code for collecting the ER diagram from SQL Server.

-Akash

|||

Like I said, I didnt work all the bugs out, but what I was able to create was an input file for Graphviz. I created a tables collection and added one object for each table. then, each table object had a columns collection which contained one object for each column. I had some additional properties to denote PK and FK columns. I then used SQLDMO to iterate through the SQL Server database and populate my collections of objects. I then wrote a second procedure which built the input file for Graphviz.

Generally the code looks like this:

For each table

add an item to the tables collection

add a columns collection to the table

add each table column to the columns collection

next column

next table

I'll try to post an output from Graphviz if you are interested.

Chris

|||

Hi Akash,

I found my code and checked it out. Currently, it works against both SQL 2000 and SQL 2005. I pointed it to the AdventureWorks sample database on a SQL 2005 box and it worked. If you will give me your email address I will send you the generated JPG output file.

|||

Hi Chris,

It would be really helpful. Do send it on lionhearted.prince@.gmail.com

Thanks

-Akash

|||

Hi Akash,

Was that output file what you were looking for?

|||

Hi Chris,

It was really great to see the output file, but that wasn't what exactly i needed. I need to generate ER diagrams programmatically using SQL Server without using a third party tool.

If you have any pointers to get it done, do share it.

Thanks

-Akash

methods of storing the Connection String in package configuration

hi!

I want to store the value of the Connection String in an Common table exisiting for the whole project and retrieve it for the whole package to Run. I want to know if there is a way of doing this.

I do not want to have a config file which is created trhough the package configuration.

In case of Parent package variable the Value of the variable needs to exist which again needs to execute the SQL Task which requires a connection string.

In case of Environment variabIe.I have very less knowledge about the environment variable . Even if we store it? how can we access it and change it in client place.

In case of SQL server . Can we store in an existing table. what is the configuration filter? what is the vlaue for it.

I do not want to execute any command line.............................

Thanks,

jazz

Well, I have not done this but I think you could simply have a parent package variable that uses a SQL Task to load the connection string for the child packages (obviously you need an initial connection string to load the config in the first place so I am assuming you are looking for a connection string to a second database.).

Once you do that you can use a script task in each of the child packages to explicitly set the Dts.Connections("ConnectionName").ConnectionString property manually based on the parent variable. You can use parent package configurations to map a parent variable to the child package so the designer can see the variable used.

Hope this helps...

|||

Can i execute a stored preocedure to get the connection string from the database while installing the pacakge which can set the value of the parent package variables.

Where i can use the Parent Package Varaible in Package Configuration to be accessed by the Child Packages.

is this ok......

Thanks

Methods of #temp table creation.

Hello,
What is the difference between the two?
(a) Explicitly create a temp table using "create table #tableName...".
After it has been created, populate it using an explicit "insert into
#tableName".
Table creation and population of records take place in 2 t-sql statements.
(b) Let the #tableName get created on the fly when using a "select top 10
cid into #tableName from storesLink".
Table creation and population of records take place in a single t-sql
statement.
Both of them achieve the same result.
But, what is the difference in performance?
Are there any other points that I should keep in mind when adopting any of
the above two approaches ?
Cheers!
SQLCatzThe second one causes locking on some of the system tables and should
be avoided. Use the first one or better yet, use the table variable.
Aramid
On Wed, 6 Apr 2005 23:29:03 -0700, "SQLCatz"
<SQLCatz@.discussions.microsoft.com> wrote:
>Hello,
>What is the difference between the two?
>(a) Explicitly create a temp table using "create table #tableName...".
>After it has been created, populate it using an explicit "insert into
>#tableName".
>Table creation and population of records take place in 2 t-sql statements.
>(b) Let the #tableName get created on the fly when using a "select top 10
>cid into #tableName from storesLink".
>Table creation and population of records take place in a single t-sql
>statement.
>Both of them achieve the same result.
>But, what is the difference in performance?
>Are there any other points that I should keep in mind when adopting any of
>the above two approaches ?
>Cheers!
>SQLCatz
>|||they are about the same in performance if you don't have recompile. For
recompile info, see:
http://support.microsoft.com/default.aspx?scid=kb;en-us;q243586
--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:9FBD6D28-361B-460A-B47B-7CBD7DE86430@.microsoft.com...
> Hello,
> What is the difference between the two?
> (a) Explicitly create a temp table using "create table #tableName...".
> After it has been created, populate it using an explicit "insert into
> #tableName".
> Table creation and population of records take place in 2 t-sql statements.
> (b) Let the #tableName get created on the fly when using a "select top 10
> cid into #tableName from storesLink".
> Table creation and population of records take place in a single t-sql
> statement.
> Both of them achieve the same result.
> But, what is the difference in performance?
> Are there any other points that I should keep in mind when adopting any of
> the above two approaches ?
> Cheers!
> SQLCatz
>|||On Wed, 6 Apr 2005 23:29:03 -0700, SQLCatz wrote:
(snip)
>Are there any other points that I should keep in mind when adopting any of
>the above two approaches ?
Hi SQLCatz,
If you use CREATE TABLE, you can add constraints and indexes right away,
or you can chooose to add them after the INSERT. If you use SELECT INTO,
you can only add the constraints and indexes later.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Methods of #temp table creation.

Hello,
What is the difference between the two?
(a) Explicitly create a temp table using "create table #tableName...".
After it has been created, populate it using an explicit "insert into
#tableName".
Table creation and population of records take place in 2 t-sql statements.
(b) Let the #tableName get created on the fly when using a "select top 10
cid into #tableName from storesLink".
Table creation and population of records take place in a single t-sql
statement.
Both of them achieve the same result.
But, what is the difference in performance?
Are there any other points that I should keep in mind when adopting any of
the above two approaches ?
Cheers!
SQLCatz
The second one causes locking on some of the system tables and should
be avoided. Use the first one or better yet, use the table variable.
Aramid
On Wed, 6 Apr 2005 23:29:03 -0700, "SQLCatz"
<SQLCatz@.discussions.microsoft.com> wrote:

>Hello,
>What is the difference between the two?
>(a) Explicitly create a temp table using "create table #tableName...".
>After it has been created, populate it using an explicit "insert into
>#tableName".
>Table creation and population of records take place in 2 t-sql statements.
>(b) Let the #tableName get created on the fly when using a "select top 10
>cid into #tableName from storesLink".
>Table creation and population of records take place in a single t-sql
>statement.
>Both of them achieve the same result.
>But, what is the difference in performance?
>Are there any other points that I should keep in mind when adopting any of
>the above two approaches ?
>Cheers!
>SQLCatz
>
|||they are about the same in performance if you don't have recompile. For
recompile info, see:
http://support.microsoft.com/default...;en-us;q243586
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:9FBD6D28-361B-460A-B47B-7CBD7DE86430@.microsoft.com...
> Hello,
> What is the difference between the two?
> (a) Explicitly create a temp table using "create table #tableName...".
> After it has been created, populate it using an explicit "insert into
> #tableName".
> Table creation and population of records take place in 2 t-sql statements.
> (b) Let the #tableName get created on the fly when using a "select top 10
> cid into #tableName from storesLink".
> Table creation and population of records take place in a single t-sql
> statement.
> Both of them achieve the same result.
> But, what is the difference in performance?
> Are there any other points that I should keep in mind when adopting any of
> the above two approaches ?
> Cheers!
> SQLCatz
>
|||On Wed, 6 Apr 2005 23:29:03 -0700, SQLCatz wrote:
(snip)
>Are there any other points that I should keep in mind when adopting any of
>the above two approaches ?
Hi SQLCatz,
If you use CREATE TABLE, you can add constraints and indexes right away,
or you can chooose to add them after the INSERT. If you use SELECT INTO,
you can only add the constraints and indexes later.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Methods of #temp table creation.

Hello,
What is the difference between the two?
(a) Explicitly create a temp table using "create table #tableName...".
After it has been created, populate it using an explicit "insert into
#tableName".
Table creation and population of records take place in 2 t-sql statements.
(b) Let the #tableName get created on the fly when using a "select top 10
cid into #tableName from storesLink".
Table creation and population of records take place in a single t-sql
statement.
Both of them achieve the same result.
But, what is the difference in performance?
Are there any other points that I should keep in mind when adopting any of
the above two approaches ?
Cheers!
SQLCatzThe second one causes locking on some of the system tables and should
be avoided. Use the first one or better yet, use the table variable.
Aramid
On Wed, 6 Apr 2005 23:29:03 -0700, "SQLCatz"
<SQLCatz@.discussions.microsoft.com> wrote:

>Hello,
>What is the difference between the two?
>(a) Explicitly create a temp table using "create table #tableName...".
>After it has been created, populate it using an explicit "insert into
>#tableName".
>Table creation and population of records take place in 2 t-sql statements.
>(b) Let the #tableName get created on the fly when using a "select top 10
>cid into #tableName from storesLink".
>Table creation and population of records take place in a single t-sql
>statement.
>Both of them achieve the same result.
>But, what is the difference in performance?
>Are there any other points that I should keep in mind when adopting any of
>the above two approaches ?
>Cheers!
>SQLCatz
>|||they are about the same in performance if you don't have recompile. For
recompile info, see:
http://support.microsoft.com/defaul...b;en-us;q243586
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:9FBD6D28-361B-460A-B47B-7CBD7DE86430@.microsoft.com...
> Hello,
> What is the difference between the two?
> (a) Explicitly create a temp table using "create table #tableName...".
> After it has been created, populate it using an explicit "insert into
> #tableName".
> Table creation and population of records take place in 2 t-sql statements.
> (b) Let the #tableName get created on the fly when using a "select top 10
> cid into #tableName from storesLink".
> Table creation and population of records take place in a single t-sql
> statement.
> Both of them achieve the same result.
> But, what is the difference in performance?
> Are there any other points that I should keep in mind when adopting any of
> the above two approaches ?
> Cheers!
> SQLCatz
>|||On Wed, 6 Apr 2005 23:29:03 -0700, SQLCatz wrote:
(snip)
>Are there any other points that I should keep in mind when adopting any of
>the above two approaches ?
Hi SQLCatz,
If you use CREATE TABLE, you can add constraints and indexes right away,
or you can chooose to add them after the INSERT. If you use SELECT INTO,
you can only add the constraints and indexes later.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)