I am using SQL Server 2000 8.00.760 SP3
I am writing a TSQL Script to compare two databases and report any difference.
I am modifying the script sp_comparedb originally written by Viktor Gorodnichenko.
His script only compares the table schema. I am adding ability to compare indexes,
Primary Keys and Foreign keys. All is working well except at one place.
DESC Key. If an index is declared with a DESC column, then the following two
cases behave differently.
use database_name
select INDEXKEY_PROPERTY(id,indid,colid,'IsDescending')
from dbo.sysindexkeys
where id = object_id('any_table_name')
returns 1 for those columns of an index which has DESC clause.
Now the problem is that I don't use the database. Since the script
compares two different databases, it runs in the master databases
and loads all information from the databases_to_be_compared
into temp tables using EXEC call. So this is what I do
use master
set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
'select INDEXKEY_PROPERTY(id,indid,colid,''IsDescending'') from ' +
@.dbname + '.dbo.sysindexkeys'
When this query runs, it returns null for all columns.
This can easily be tested in Query Analyser as follows
use master
select INDEXKEY_PROPERTY(id,indid,colid,'IsDescending')
from different_database.dbo.sysindexkeys
where id = object_id('different_database.dbo.any_table_name')
Compare the results with the first query. While the first will return 0 or 1 for descending,
the second will always return NULL.
INDEXKEY_PROPERTY (and all similar functions) still works locally to the
database you are in, and in your case that is master.
What you can do is use sp_executesql to get the proper database context:
set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
'EXEC ' +
@.dbname + '..sp_executesql ''select
INDEXKEY_PROPERTY(id,indid,colid,''''IsDescending' ''') from
dbo.sysindexkeys'''
(Not sure if I did all the quotes right there)
Jacco Schalkwijk
SQL Server MVP
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3gthaoFea9jbU1@.individual.net...
>I am using SQL Server 2000 8.00.760 SP3
> I am writing a TSQL Script to compare two databases and report any
> difference.
> I am modifying the script sp_comparedb originally written by Viktor
> Gorodnichenko.
> His script only compares the table schema. I am adding ability to compare
> indexes,
> Primary Keys and Foreign keys. All is working well except at one place.
> DESC Key. If an index is declared with a DESC column, then the following
> two
> cases behave differently.
> use database_name
> select INDEXKEY_PROPERTY(id,indid,colid,'IsDescending')
> from dbo.sysindexkeys
> where id = object_id('any_table_name')
> returns 1 for those columns of an index which has DESC clause.
> Now the problem is that I don't use the database. Since the script
> compares two different databases, it runs in the master databases
> and loads all information from the databases_to_be_compared
> into temp tables using EXEC call. So this is what I do
> use master
> set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
> 'select
> INDEXKEY_PROPERTY(id,indid,colid,''IsDescending'') from ' +
> @.dbname + '.dbo.sysindexkeys'
> When this query runs, it returns null for all columns.
> This can easily be tested in Query Analyser as follows
> use master
> select INDEXKEY_PROPERTY(id,indid,colid,'IsDescending')
> from different_database.dbo.sysindexkeys
> where id = object_id('different_database.dbo.any_table_name')
> Compare the results with the first query. While the first will return 0 or
> 1 for descending,
> the second will always return NULL.
>
>
|||Thanks. Your suggestion works.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote in message
news:eVkQtYcbFHA.2980@.TK2MSFTNGP10.phx.gbl...
> INDEXKEY_PROPERTY (and all similar functions) still works locally to the database you are
> in, and in your case that is master.
> What you can do is use sp_executesql to get the proper database context:
> set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
> 'EXEC ' +
> @.dbname + '..sp_executesql ''select
> INDEXKEY_PROPERTY(id,indid,colid,''''IsDescending' ''') from dbo.sysindexkeys'''
> (Not sure if I did all the quotes right there)
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Data Cruncher" <dcruncher4@.netscape.net> wrote in message
> news:3gthaoFea9jbU1@.individual.net...
>
Showing posts with label compare. Show all posts
Showing posts with label compare. Show all posts
Wednesday, March 7, 2012
is it a bug
I am using SQL Server 2000 8.00.760 SP3
I am writing a TSQL Script to compare two databases and report any differenc
e.
I am modifying the script sp_comparedb originally written by Viktor Gorodni
chenko.
His script only compares the table schema. I am adding ability to compare in
dexes,
Primary Keys and Foreign keys. All is working well except at one place.
DESC Key. If an index is declared with a DESC column, then the following two
cases behave differently.
use database_name
select INDEXKEY_PROPERTY(id,indid,colid,'IsDesc
ending')
from dbo.sysindexkeys
where id = object_id('any_table_name')
returns 1 for those columns of an index which has DESC clause.
Now the problem is that I don't use the database. Since the script
compares two different databases, it runs in the master databases
and loads all information from the databases_to_be_compared
into temp tables using EXEC call. So this is what I do
use master
set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
'select INDEXKEY_PROPERTY(id,indid,colid,''IsDes
cending'') from ' +
@.dbname + '.dbo.sysindexkeys'
When this query runs, it returns null for all columns.
This can easily be tested in Query Analyser as follows
use master
select INDEXKEY_PROPERTY(id,indid,colid,'IsDesc
ending')
from different_database.dbo.sysindexkeys
where id = object_id('different_database.dbo.any_table_name')
Compare the results with the first query. While the first will return 0 or 1
for descending,
the second will always return NULL.INDEXKEY_PROPERTY (and all similar functions) still works locally to the
database you are in, and in your case that is master.
What you can do is use sp_executesql to get the proper database context:
set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
'EXEC ' +
@.dbname + '..sp_executesql ''select
INDEXKEY_PROPERTY(id,indid,colid,''''IsD
escending'''') from
dbo.sysindexkeys'''
(Not sure if I did all the quotes right there)
Jacco Schalkwijk
SQL Server MVP
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3gthaoFea9jbU1@.individual.net...
>I am using SQL Server 2000 8.00.760 SP3
> I am writing a TSQL Script to compare two databases and report any
> difference.
> I am modifying the script sp_comparedb originally written by Viktor
> Gorodnichenko.
> His script only compares the table schema. I am adding ability to compare
> indexes,
> Primary Keys and Foreign keys. All is working well except at one place.
> DESC Key. If an index is declared with a DESC column, then the following
> two
> cases behave differently.
> use database_name
> select INDEXKEY_PROPERTY(id,indid,colid,'IsDesc
ending')
> from dbo.sysindexkeys
> where id = object_id('any_table_name')
> returns 1 for those columns of an index which has DESC clause.
> Now the problem is that I don't use the database. Since the script
> compares two different databases, it runs in the master databases
> and loads all information from the databases_to_be_compared
> into temp tables using EXEC call. So this is what I do
> use master
> set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
> 'select
> INDEXKEY_PROPERTY(id,indid,colid,''IsDes
cending'') from ' +
> @.dbname + '.dbo.sysindexkeys'
> When this query runs, it returns null for all columns.
> This can easily be tested in Query Analyser as follows
> use master
> select INDEXKEY_PROPERTY(id,indid,colid,'IsDesc
ending')
> from different_database.dbo.sysindexkeys
> where id = object_id('different_database.dbo.any_table_name')
> Compare the results with the first query. While the first will return 0 or
> 1 for descending,
> the second will always return NULL.
>
>|||Thanks. Your suggestion works.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message
news:eVkQtYcbFHA.2980@.TK2MSFTNGP10.phx.gbl...
> INDEXKEY_PROPERTY (and all similar functions) still works locally to the d
atabase you are
> in, and in your case that is master.
> What you can do is use sp_executesql to get the proper database context:
> set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
> 'EXEC ' +
> @.dbname + '..sp_executesql ''select
> INDEXKEY_PROPERTY(id,indid,colid,''''IsD
escending'''') from dbo.sysindexke
ys'''
> (Not sure if I did all the quotes right there)
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Data Cruncher" <dcruncher4@.netscape.net> wrote in message
> news:3gthaoFea9jbU1@.individual.net...
>
I am writing a TSQL Script to compare two databases and report any differenc
e.
I am modifying the script sp_comparedb originally written by Viktor Gorodni
chenko.
His script only compares the table schema. I am adding ability to compare in
dexes,
Primary Keys and Foreign keys. All is working well except at one place.
DESC Key. If an index is declared with a DESC column, then the following two
cases behave differently.
use database_name
select INDEXKEY_PROPERTY(id,indid,colid,'IsDesc
ending')
from dbo.sysindexkeys
where id = object_id('any_table_name')
returns 1 for those columns of an index which has DESC clause.
Now the problem is that I don't use the database. Since the script
compares two different databases, it runs in the master databases
and loads all information from the databases_to_be_compared
into temp tables using EXEC call. So this is what I do
use master
set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
'select INDEXKEY_PROPERTY(id,indid,colid,''IsDes
cending'') from ' +
@.dbname + '.dbo.sysindexkeys'
When this query runs, it returns null for all columns.
This can easily be tested in Query Analyser as follows
use master
select INDEXKEY_PROPERTY(id,indid,colid,'IsDesc
ending')
from different_database.dbo.sysindexkeys
where id = object_id('different_database.dbo.any_table_name')
Compare the results with the first query. While the first will return 0 or 1
for descending,
the second will always return NULL.INDEXKEY_PROPERTY (and all similar functions) still works locally to the
database you are in, and in your case that is master.
What you can do is use sp_executesql to get the proper database context:
set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
'EXEC ' +
@.dbname + '..sp_executesql ''select
INDEXKEY_PROPERTY(id,indid,colid,''''IsD
escending'''') from
dbo.sysindexkeys'''
(Not sure if I did all the quotes right there)
Jacco Schalkwijk
SQL Server MVP
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3gthaoFea9jbU1@.individual.net...
>I am using SQL Server 2000 8.00.760 SP3
> I am writing a TSQL Script to compare two databases and report any
> difference.
> I am modifying the script sp_comparedb originally written by Viktor
> Gorodnichenko.
> His script only compares the table schema. I am adding ability to compare
> indexes,
> Primary Keys and Foreign keys. All is working well except at one place.
> DESC Key. If an index is declared with a DESC column, then the following
> two
> cases behave differently.
> use database_name
> select INDEXKEY_PROPERTY(id,indid,colid,'IsDesc
ending')
> from dbo.sysindexkeys
> where id = object_id('any_table_name')
> returns 1 for those columns of an index which has DESC clause.
> Now the problem is that I don't use the database. Since the script
> compares two different databases, it runs in the master databases
> and loads all information from the databases_to_be_compared
> into temp tables using EXEC call. So this is what I do
> use master
> set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
> 'select
> INDEXKEY_PROPERTY(id,indid,colid,''IsDes
cending'') from ' +
> @.dbname + '.dbo.sysindexkeys'
> When this query runs, it returns null for all columns.
> This can easily be tested in Query Analyser as follows
> use master
> select INDEXKEY_PROPERTY(id,indid,colid,'IsDesc
ending')
> from different_database.dbo.sysindexkeys
> where id = object_id('different_database.dbo.any_table_name')
> Compare the results with the first query. While the first will return 0 or
> 1 for descending,
> the second will always return NULL.
>
>|||Thanks. Your suggestion works.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message
news:eVkQtYcbFHA.2980@.TK2MSFTNGP10.phx.gbl...
> INDEXKEY_PROPERTY (and all similar functions) still works locally to the d
atabase you are
> in, and in your case that is master.
> What you can do is use sp_executesql to get the proper database context:
> set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
> 'EXEC ' +
> @.dbname + '..sp_executesql ''select
> INDEXKEY_PROPERTY(id,indid,colid,''''IsD
escending'''') from dbo.sysindexke
ys'''
> (Not sure if I did all the quotes right there)
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Data Cruncher" <dcruncher4@.netscape.net> wrote in message
> news:3gthaoFea9jbU1@.individual.net...
>
is it a bug
I am using SQL Server 2000 8.00.760 SP3
I am writing a TSQL Script to compare two databases and report any difference.
I am modifying the script sp_comparedb originally written by Viktor Gorodnichenko.
His script only compares the table schema. I am adding ability to compare indexes,
Primary Keys and Foreign keys. All is working well except at one place.
DESC Key. If an index is declared with a DESC column, then the following two
cases behave differently.
use database_name
select INDEXKEY_PROPERTY(id,indid,colid,'IsDescending')
from dbo.sysindexkeys
where id = object_id('any_table_name')
returns 1 for those columns of an index which has DESC clause.
Now the problem is that I don't use the database. Since the script
compares two different databases, it runs in the master databases
and loads all information from the databases_to_be_compared
into temp tables using EXEC call. So this is what I do
use master
set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
'select INDEXKEY_PROPERTY(id,indid,colid,''IsDescending'') from ' +
@.dbname + '.dbo.sysindexkeys'
When this query runs, it returns null for all columns.
This can easily be tested in Query Analyser as follows
use master
select INDEXKEY_PROPERTY(id,indid,colid,'IsDescending')
from different_database.dbo.sysindexkeys
where id = object_id('different_database.dbo.any_table_name')
Compare the results with the first query. While the first will return 0 or 1 for descending,
the second will always return NULL.INDEXKEY_PROPERTY (and all similar functions) still works locally to the
database you are in, and in your case that is master.
What you can do is use sp_executesql to get the proper database context:
set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
'EXEC ' +
@.dbname + '..sp_executesql ''select
INDEXKEY_PROPERTY(id,indid,colid,''''IsDescending'''') from
dbo.sysindexkeys'''
(Not sure if I did all the quotes right there)
--
Jacco Schalkwijk
SQL Server MVP
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3gthaoFea9jbU1@.individual.net...
>I am using SQL Server 2000 8.00.760 SP3
> I am writing a TSQL Script to compare two databases and report any
> difference.
> I am modifying the script sp_comparedb originally written by Viktor
> Gorodnichenko.
> His script only compares the table schema. I am adding ability to compare
> indexes,
> Primary Keys and Foreign keys. All is working well except at one place.
> DESC Key. If an index is declared with a DESC column, then the following
> two
> cases behave differently.
> use database_name
> select INDEXKEY_PROPERTY(id,indid,colid,'IsDescending')
> from dbo.sysindexkeys
> where id = object_id('any_table_name')
> returns 1 for those columns of an index which has DESC clause.
> Now the problem is that I don't use the database. Since the script
> compares two different databases, it runs in the master databases
> and loads all information from the databases_to_be_compared
> into temp tables using EXEC call. So this is what I do
> use master
> set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
> 'select
> INDEXKEY_PROPERTY(id,indid,colid,''IsDescending'') from ' +
> @.dbname + '.dbo.sysindexkeys'
> When this query runs, it returns null for all columns.
> This can easily be tested in Query Analyser as follows
> use master
> select INDEXKEY_PROPERTY(id,indid,colid,'IsDescending')
> from different_database.dbo.sysindexkeys
> where id = object_id('different_database.dbo.any_table_name')
> Compare the results with the first query. While the first will return 0 or
> 1 for descending,
> the second will always return NULL.
>
>|||Thanks. Your suggestion works.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote in message
news:eVkQtYcbFHA.2980@.TK2MSFTNGP10.phx.gbl...
> INDEXKEY_PROPERTY (and all similar functions) still works locally to the database you are
> in, and in your case that is master.
> What you can do is use sp_executesql to get the proper database context:
> set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
> 'EXEC ' +
> @.dbname + '..sp_executesql ''select
> INDEXKEY_PROPERTY(id,indid,colid,''''IsDescending'''') from dbo.sysindexkeys'''
> (Not sure if I did all the quotes right there)
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Data Cruncher" <dcruncher4@.netscape.net> wrote in message
> news:3gthaoFea9jbU1@.individual.net...
>>I am using SQL Server 2000 8.00.760 SP3
>> I am writing a TSQL Script to compare two databases and report any difference.
>> I am modifying the script sp_comparedb originally written by Viktor Gorodnichenko.
>> His script only compares the table schema. I am adding ability to compare indexes,
>> Primary Keys and Foreign keys. All is working well except at one place.
>> DESC Key. If an index is declared with a DESC column, then the following two
>> cases behave differently.
>> use database_name
>> select INDEXKEY_PROPERTY(id,indid,colid,'IsDescending')
>> from dbo.sysindexkeys
>> where id = object_id('any_table_name')
>> returns 1 for those columns of an index which has DESC clause.
>> Now the problem is that I don't use the database. Since the script
>> compares two different databases, it runs in the master databases
>> and loads all information from the databases_to_be_compared
>> into temp tables using EXEC call. So this is what I do
>> use master
>> set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
>> 'select INDEXKEY_PROPERTY(id,indid,colid,''IsDescending'') from '
>> +
>> @.dbname + '.dbo.sysindexkeys'
>> When this query runs, it returns null for all columns.
>> This can easily be tested in Query Analyser as follows
>> use master
>> select INDEXKEY_PROPERTY(id,indid,colid,'IsDescending')
>> from different_database.dbo.sysindexkeys
>> where id = object_id('different_database.dbo.any_table_name')
>> Compare the results with the first query. While the first will return 0 or 1 for
>> descending,
>> the second will always return NULL.
>>
>>
>
I am writing a TSQL Script to compare two databases and report any difference.
I am modifying the script sp_comparedb originally written by Viktor Gorodnichenko.
His script only compares the table schema. I am adding ability to compare indexes,
Primary Keys and Foreign keys. All is working well except at one place.
DESC Key. If an index is declared with a DESC column, then the following two
cases behave differently.
use database_name
select INDEXKEY_PROPERTY(id,indid,colid,'IsDescending')
from dbo.sysindexkeys
where id = object_id('any_table_name')
returns 1 for those columns of an index which has DESC clause.
Now the problem is that I don't use the database. Since the script
compares two different databases, it runs in the master databases
and loads all information from the databases_to_be_compared
into temp tables using EXEC call. So this is what I do
use master
set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
'select INDEXKEY_PROPERTY(id,indid,colid,''IsDescending'') from ' +
@.dbname + '.dbo.sysindexkeys'
When this query runs, it returns null for all columns.
This can easily be tested in Query Analyser as follows
use master
select INDEXKEY_PROPERTY(id,indid,colid,'IsDescending')
from different_database.dbo.sysindexkeys
where id = object_id('different_database.dbo.any_table_name')
Compare the results with the first query. While the first will return 0 or 1 for descending,
the second will always return NULL.INDEXKEY_PROPERTY (and all similar functions) still works locally to the
database you are in, and in your case that is master.
What you can do is use sp_executesql to get the proper database context:
set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
'EXEC ' +
@.dbname + '..sp_executesql ''select
INDEXKEY_PROPERTY(id,indid,colid,''''IsDescending'''') from
dbo.sysindexkeys'''
(Not sure if I did all the quotes right there)
--
Jacco Schalkwijk
SQL Server MVP
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3gthaoFea9jbU1@.individual.net...
>I am using SQL Server 2000 8.00.760 SP3
> I am writing a TSQL Script to compare two databases and report any
> difference.
> I am modifying the script sp_comparedb originally written by Viktor
> Gorodnichenko.
> His script only compares the table schema. I am adding ability to compare
> indexes,
> Primary Keys and Foreign keys. All is working well except at one place.
> DESC Key. If an index is declared with a DESC column, then the following
> two
> cases behave differently.
> use database_name
> select INDEXKEY_PROPERTY(id,indid,colid,'IsDescending')
> from dbo.sysindexkeys
> where id = object_id('any_table_name')
> returns 1 for those columns of an index which has DESC clause.
> Now the problem is that I don't use the database. Since the script
> compares two different databases, it runs in the master databases
> and loads all information from the databases_to_be_compared
> into temp tables using EXEC call. So this is what I do
> use master
> set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
> 'select
> INDEXKEY_PROPERTY(id,indid,colid,''IsDescending'') from ' +
> @.dbname + '.dbo.sysindexkeys'
> When this query runs, it returns null for all columns.
> This can easily be tested in Query Analyser as follows
> use master
> select INDEXKEY_PROPERTY(id,indid,colid,'IsDescending')
> from different_database.dbo.sysindexkeys
> where id = object_id('different_database.dbo.any_table_name')
> Compare the results with the first query. While the first will return 0 or
> 1 for descending,
> the second will always return NULL.
>
>|||Thanks. Your suggestion works.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote in message
news:eVkQtYcbFHA.2980@.TK2MSFTNGP10.phx.gbl...
> INDEXKEY_PROPERTY (and all similar functions) still works locally to the database you are
> in, and in your case that is master.
> What you can do is use sp_executesql to get the proper database context:
> set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
> 'EXEC ' +
> @.dbname + '..sp_executesql ''select
> INDEXKEY_PROPERTY(id,indid,colid,''''IsDescending'''') from dbo.sysindexkeys'''
> (Not sure if I did all the quotes right there)
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Data Cruncher" <dcruncher4@.netscape.net> wrote in message
> news:3gthaoFea9jbU1@.individual.net...
>>I am using SQL Server 2000 8.00.760 SP3
>> I am writing a TSQL Script to compare two databases and report any difference.
>> I am modifying the script sp_comparedb originally written by Viktor Gorodnichenko.
>> His script only compares the table schema. I am adding ability to compare indexes,
>> Primary Keys and Foreign keys. All is working well except at one place.
>> DESC Key. If an index is declared with a DESC column, then the following two
>> cases behave differently.
>> use database_name
>> select INDEXKEY_PROPERTY(id,indid,colid,'IsDescending')
>> from dbo.sysindexkeys
>> where id = object_id('any_table_name')
>> returns 1 for those columns of an index which has DESC clause.
>> Now the problem is that I don't use the database. Since the script
>> compares two different databases, it runs in the master databases
>> and loads all information from the databases_to_be_compared
>> into temp tables using EXEC call. So this is what I do
>> use master
>> set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
>> 'select INDEXKEY_PROPERTY(id,indid,colid,''IsDescending'') from '
>> +
>> @.dbname + '.dbo.sysindexkeys'
>> When this query runs, it returns null for all columns.
>> This can easily be tested in Query Analyser as follows
>> use master
>> select INDEXKEY_PROPERTY(id,indid,colid,'IsDescending')
>> from different_database.dbo.sysindexkeys
>> where id = object_id('different_database.dbo.any_table_name')
>> Compare the results with the first query. While the first will return 0 or 1 for
>> descending,
>> the second will always return NULL.
>>
>>
>
Monday, February 20, 2012
Is anyone using C# and the SSIS Objects to Develop/Modify Packages?
Anyone out there developing or modifying packages w/ C# and the SSIS objects that I can compare notes with?
Thanks!
Done a bit, loading and poking around and some creating directly in code.|||Hi Darren ...
I've taken a look around SQLIS. Very helpful indeed!
Do you have any examples of something as simple as the following, using C# and the object model:
Source: SQL Server A, Table Foo
Transformations: None
Target SQL Server B, Table FooTwo
The real trick with the above is that at runtime I won't know what the tables are. I need to work w/ a SQL String and the re-initializemetadata stuff.
Looking for any help here.
Thanks!
|||I would probably rebuild the package each time, and Books Online covers this quite well I think.
Start with ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/0ca03712-a82e-4aa7-949b-f869a8936ddf.htm, then move onto the related Data Flow section.
The Data Flow sample includes setting the SQL and calling RMD, see "Adding and Configuring a Component".
|||Thank you very much for the suggestions!
I've looked over these items in the past and to be honest I think they are a bit weak in terms of examples, etc. My experience has been that some of the docs on this go into great detail, but they don't give you "the big picture." I got a copy of Professional SQL Server 2005 Integration Services (wrox) and this has been a help, but I'm still looking for more examples.
Any other suggestions?
All the best,
DB
|||Chapters 14 and 15 of that book is where you want to head. I've already been through Chapter 14 and it was a really great tutorial.
Darren wrote chapter 15 and his partner Allan Mitchell wrote chapter 14 and they're probably too modest to sing their own praises ...so I'll do it for them.
-Jamie
|||Thank you for the plug, but those chapters are more about building components, not components. Actually if you do understand how to build components, and the workings of adding and removing columns which they cover, then it will help your understanding for when building packages as well, I know it does for me. You do similar things in your component as when building packages, such as working on the managed interface wrapper, and selecting columns.
I don't know of anymore examples, I have always managed to do what I need quite effectively on what is above. You have the basics of building packages, so now everything else is just the nuances for the task or component type, which is more about understanding that component as opposed to general building package knowledge. Perhaps you could post individual problems as you get them and we'll see if we can help.
|||Thanks to both of you for the supportive words. I'm doing my best to solve this problem. I'm sure once I get a few components to work it becomes more of a cookie cutter process.
Here's an example of a problem I'm trying to solve using the Object Model. If anyone has a code sample that does some or all of these simple steps from end-to-end that would be an extreme help! It *seems* that this should be so simple!
In a nutshell:
I'm using C# and the SSIS object modle to try to create a single package that at runtime can look up a table and list of columns from a source SQL Server. With this table and column list in hand, I simply want to move the table's source data in its identical format as quickly as possible to another destination SQL Server into an identical table.
The reason I'm using the C# object model approach is so that I can build one package/application that can be used to move data for many different tables, determined at runtime, without having to have multiple packages.
In other words, if I have 300 tables to move data from SQL Server A to SQL Server B, I want one package, not 300. Earlier posts in this forum said to accomplish this requires C# and the object model. Reference: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=112283&SiteID=1
In the final product there will be one transformation, but I'd be happy right now just to get all the column outputs and inputs working correctly.
So far I'm having success with:
- Creating the initial package
- Creating the MainPipe
- Creating source and target OLE-DB Connection Managers
- Setting up a source component (see issues below)
- Doing an Instantiate and ProvideComponentProperties
- Associating the source component with the source connection manager
- Using SetComponentProperties to set the SQL statement (see issues below)
- Connecting to the source and running reinitializemetadata
- Repeating the above for the destination component
- Setting up a path between the two
Here are the issues that I'm pretty sure are screwing me up. I'm having a hard time with:
1. With the source component, all I want is "select col1, col2 from foo". I'm pretty sure that I'm missing some important steps to tell SSIS the specific list of columns that I want to have in my source component output. I am running the reinitializemetadata step, but I don't *think* this is enough by itself. I'm pretty sure there are some commands related to IDTSOutput90 but I'm having a hard time figuring out how to accomplish this step by step. I haven't been able to follow BOL to put this together.
2. I'm also missing the corresponding IDTSInput90 techniques to identify the input columns coming into my destination component. I looked at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/ef258c93-446f-44e6-b040-8164315b58ee.htm
as a reference, but this didn't seem to help (translation: too complex for me to understand without some assistance, and pilot error.) The idea of having this as a dynamic list would be extremely helpful for my particular problem.
3. OK, once my data shows up at my destination component if possible I need to UPSERT the data. A code example on this would be helpful ... how to set up the SQL statements for the SetComponentProperty.
4. Finally, I've been trying to figure out the right combination of AccessMode integer values that match SQL statements and such used by SetComponentProperty. For example:
mySrcDTInstance.SetComponentProperty("CommandTimeout", 0);
mySrcDTInstance.SetComponentProperty("OpenRowset", "[dbo].[test]");
mySrcDTInstance.SetComponentProperty("AccessMode", 0);
I've run every search I could think of and have posted the question before: What are the proper combinations of AccessMode integer values and SQL statments, OpenRowset, etc.
Any samples or suggestions or doc references I can delve into would be a GREAT help!!!!!
DB
|||Hi Doug B,
We are facing the same problem here. We need to move 200 tables with Data Flow Task. I was wondering if you figure out a solution since the last posting.
Mathieu
|||Doug B wrote:
I'm using C# and the SSIS object modle to try to create a single package that at runtime can look up a table and list of columns from a source SQL Server. With this table and column list in hand, I simply want to move the table's source data in its identical format as quickly as possible to another destination SQL Server into an identical table.
I haven't looked at your post in detail but I think I should pick you up one one point here. You say you want to "create a single package that at runtime can look up a table and list of columns from <somewhere>". I think your approach here is slightly awry. Yes, you need to write dotnet code to do this. But in your sentance here it sounds as though you want to SSIS to be a host for that dotnet code - and I think that is wrong. Sure, write a dotnet app that uses the SSIS object model to create a package based on some defined metadata - but there's no need to run that code actually within a SSIS package. Why not jsut run it from the command-line?
Remember that it is not possible for a SSIS package to change itself using the object model. You could do this in DTS, but not in SSIS.
I hope the subtle distinction is clear here.
-Jamie
|||3. OK, once my data shows up at my destination component if possible I need to UPSERT the data. A code example on this would be helpful ... how to set up the SQL statements for the SetComponentProperty.
That's simply not possible just in a destination component. If you want to do an upsert then you will need to compare the pipeline data with the destination and that needs to happen upstream of the destination component. I've talked about this technique more here:
Checking if a row exists and if it does, has it changed?
http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx
-Jamie
|||1. With the source component, all I want is "select col1, col2 from foo". I'm pretty sure that I'm missing some important steps to tell SSIS the specific list of columns that I want to have in my source component output. I am running the reinitializemetadata step, but I don't *think* this is enough by itself. I'm pretty sure there are some commands related to IDTSOutput90 but I'm having a hard time figuring out how to accomplish this step by step. I haven't been able to follow BOL to put this together.
I vaguely remember trying this myself once. You can't just give the source adapter a SQL statement and let it work out the metadata itself. Although this is what it appears as if the Source Adapters do in the SSIS Designer UI, it isn't actually the case. The adapters still have to interogate the source to find the metadata - you have to do the same (Unless you have the column data types in your metadata store).
-Jamie
|||Hi Mathieu ...
Unfortunately we were not able to get this to work. Perhaps someone with more C# experience would have had better luck.
All the best,
Doug
|||Hi everyone,
I've been working on this issue since one week.
Finally, I built a c# class that generate a package with the 200 Dataflow Task (source, lookup, destination) with the SSIS object API.
It is not that complicated except that the api is quite undocumented...
My program is doing the following task :
#1 - From a table, I get the table source (TableName) and the destination (tableName) with the load order
#2 - Using Package API, I create a package from a template
#3 - With the load order, I create a DataFlow Task using API that contain :
A) Source
B) LookUp
C) Destination
We will save some precious time on refactoring aspect using this class...
Jamie : Nice website... I used it since I'm working with SSIS...I get some practical information...Keep on good work...
Doug_b : About IDTSOutput90, this is an example for a simple DataFlow Task.
I hope It can help you
#region Map MetaData
public void MetaDataMapping(IDTSComponentMetaData90 dstMetaData, IDTSDesigntimeComponent90 dstComp)
{
IDTSInput90 destinationInput = dstMetaData.InputCollection[0];
int destinationInputID = destinationInput.ID;
IDTSVirtualInput90 destinationVirtualInput = destinationInput.GetVirtualInput();
foreach (IDTSVirtualInputColumn90 virtualInputColumn in destinationVirtualInput.VirtualInputColumnCollection)
{
// This will create an input column on the component.
dstComp.SetUsageType(destinationInputID,destinationVirtualInput,virtualInputColumn.LineageID,DTSUsageType.UT_READONLY);
// Get input column.
IDTSInputColumn90 inputColumn = destinationInput.InputColumnCollection.GetInputColumnByLineageID(virtualInputColumn.LineageID);
// Getting the corresponding external column.
// Ex : We will use the column name as the basis for matching data flow columns to external columns.
IDTSExternalMetadataColumn90 externalColumn = destinationInput.ExternalMetadataColumnCollection[virtualInputColumn.Name];
// Tell the component how to map.
dstComp.MapInputColumn(destinationInputID,inputColumn.ID,externalColumn.ID);
}
}
#endregion
|||
Hi,
I tried to create SSIS package programatically,I successfully added OLE DB source & destination but
got problem in adding lookup transformation.
How can I get reference table column output.& how can I set join column property for specific column
plz help.
Is anyone using C# and the SSIS Objects to Develop/Modify Packages?
Anyone out there developing or modifying packages w/ C# and the SSIS objects that I can compare notes with?
Thanks!
Done a bit, loading and poking around and some creating directly in code.|||Hi Darren ...
I've taken a look around SQLIS. Very helpful indeed!
Do you have any examples of something as simple as the following, using C# and the object model:
Source: SQL Server A, Table Foo
Transformations: None
Target SQL Server B, Table FooTwo
The real trick with the above is that at runtime I won't know what the tables are. I need to work w/ a SQL String and the re-initializemetadata stuff.
Looking for any help here.
Thanks!
|||I would probably rebuild the package each time, and Books Online covers this quite well I think.
Start with ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/0ca03712-a82e-4aa7-949b-f869a8936ddf.htm, then move onto the related Data Flow section.
The Data Flow sample includes setting the SQL and calling RMD, see "Adding and Configuring a Component".
|||Thank you very much for the suggestions!
I've looked over these items in the past and to be honest I think they are a bit weak in terms of examples, etc. My experience has been that some of the docs on this go into great detail, but they don't give you "the big picture." I got a copy of Professional SQL Server 2005 Integration Services (wrox) and this has been a help, but I'm still looking for more examples.
Any other suggestions?
All the best,
DB
|||Chapters 14 and 15 of that book is where you want to head. I've already been through Chapter 14 and it was a really great tutorial.
Darren wrote chapter 15 and his partner Allan Mitchell wrote chapter 14 and they're probably too modest to sing their own praises ...so I'll do it for them.
-Jamie
|||Thank you for the plug, but those chapters are more about building components, not components. Actually if you do understand how to build components, and the workings of adding and removing columns which they cover, then it will help your understanding for when building packages as well, I know it does for me. You do similar things in your component as when building packages, such as working on the managed interface wrapper, and selecting columns.
I don't know of anymore examples, I have always managed to do what I need quite effectively on what is above. You have the basics of building packages, so now everything else is just the nuances for the task or component type, which is more about understanding that component as opposed to general building package knowledge. Perhaps you could post individual problems as you get them and we'll see if we can help.
|||Thanks to both of you for the supportive words. I'm doing my best to solve this problem. I'm sure once I get a few components to work it becomes more of a cookie cutter process.
Here's an example of a problem I'm trying to solve using the Object Model. If anyone has a code sample that does some or all of these simple steps from end-to-end that would be an extreme help! It *seems* that this should be so simple!
In a nutshell:
I'm using C# and the SSIS object modle to try to create a single package that at runtime can look up a table and list of columns from a source SQL Server. With this table and column list in hand, I simply want to move the table's source data in its identical format as quickly as possible to another destination SQL Server into an identical table.
The reason I'm using the C# object model approach is so that I can build one package/application that can be used to move data for many different tables, determined at runtime, without having to have multiple packages.
In other words, if I have 300 tables to move data from SQL Server A to SQL Server B, I want one package, not 300. Earlier posts in this forum said to accomplish this requires C# and the object model. Reference: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=112283&SiteID=1
In the final product there will be one transformation, but I'd be happy right now just to get all the column outputs and inputs working correctly.
So far I'm having success with:
- Creating the initial package
- Creating the MainPipe
- Creating source and target OLE-DB Connection Managers
- Setting up a source component (see issues below)
- Doing an Instantiate and ProvideComponentProperties
- Associating the source component with the source connection manager
- Using SetComponentProperties to set the SQL statement (see issues below)
- Connecting to the source and running reinitializemetadata
- Repeating the above for the destination component
- Setting up a path between the two
Here are the issues that I'm pretty sure are screwing me up. I'm having a hard time with:
1. With the source component, all I want is "select col1, col2 from foo". I'm pretty sure that I'm missing some important steps to tell SSIS the specific list of columns that I want to have in my source component output. I am running the reinitializemetadata step, but I don't *think* this is enough by itself. I'm pretty sure there are some commands related to IDTSOutput90 but I'm having a hard time figuring out how to accomplish this step by step. I haven't been able to follow BOL to put this together.
2. I'm also missing the corresponding IDTSInput90 techniques to identify the input columns coming into my destination component. I looked at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/ef258c93-446f-44e6-b040-8164315b58ee.htm
as a reference, but this didn't seem to help (translation: too complex for me to understand without some assistance, and pilot error.) The idea of having this as a dynamic list would be extremely helpful for my particular problem.
3. OK, once my data shows up at my destination component if possible I need to UPSERT the data. A code example on this would be helpful ... how to set up the SQL statements for the SetComponentProperty.
4. Finally, I've been trying to figure out the right combination of AccessMode integer values that match SQL statements and such used by SetComponentProperty. For example:
mySrcDTInstance.SetComponentProperty("CommandTimeout", 0);
mySrcDTInstance.SetComponentProperty("OpenRowset", "[dbo].[test]");
mySrcDTInstance.SetComponentProperty("AccessMode", 0);
I've run every search I could think of and have posted the question before: What are the proper combinations of AccessMode integer values and SQL statments, OpenRowset, etc.
Any samples or suggestions or doc references I can delve into would be a GREAT help!!!!!
DB
|||Hi Doug B,
We are facing the same problem here. We need to move 200 tables with Data Flow Task. I was wondering if you figure out a solution since the last posting.
Mathieu
|||I'm using C# and the SSIS object modle to try to create a single package that at runtime can look up a table and list of columns from a source SQL Server. With this table and column list in hand, I simply want to move the table's source data in its identical format as quickly as possible to another destination SQL Server into an identical table.
I haven't looked at your post in detail but I think I should pick you up one one point here. You say you want to "create a single package that at runtime can look up a table and list of columns from <somewhere>". I think your approach here is slightly awry. Yes, you need to write dotnet code to do this. But in your sentance here it sounds as though you want to SSIS to be a host for that dotnet code - and I think that is wrong. Sure, write a dotnet app that uses the SSIS object model to create a package based on some defined metadata - but there's no need to run that code actually within a SSIS package. Why not jsut run it from the command-line?
Remember that it is not possible for a SSIS package to change itself using the object model. You could do this in DTS, but not in SSIS.
I hope the subtle distinction is clear here.
-Jamie
|||3. OK, once my data shows up at my destination component if possible I need to UPSERT the data. A code example on this would be helpful ... how to set up the SQL statements for the SetComponentProperty.
That's simply not possible just in a destination component. If you want to do an upsert then you will need to compare the pipeline data with the destination and that needs to happen upstream of the destination component. I've talked about this technique more here:
Checking if a row exists and if it does, has it changed?
http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx
-Jamie
|||1. With the source component, all I want is "select col1, col2 from foo". I'm pretty sure that I'm missing some important steps to tell SSIS the specific list of columns that I want to have in my source component output. I am running the reinitializemetadata step, but I don't *think* this is enough by itself. I'm pretty sure there are some commands related to IDTSOutput90 but I'm having a hard time figuring out how to accomplish this step by step. I haven't been able to follow BOL to put this together.
I vaguely remember trying this myself once. You can't just give the source adapter a SQL statement and let it work out the metadata itself. Although this is what it appears as if the Source Adapters do in the SSIS Designer UI, it isn't actually the case. The adapters still have to interogate the source to find the metadata - you have to do the same (Unless you have the column data types in your metadata store).
-Jamie
|||Hi Mathieu ...
Unfortunately we were not able to get this to work. Perhaps someone with more C# experience would have had better luck.
All the best,
Doug
|||Hi everyone,
I've been working on this issue since one week.
Finally, I built a c# class that generate a package with the 200 Dataflow Task (source, lookup, destination) with the SSIS object API.
It is not that complicated except that the api is quite undocumented...
My program is doing the following task :
#1 - From a table, I get the table source (TableName) and the destination (tableName) with the load order
#2 - Using Package API, I create a package from a template
#3 - With the load order, I create a DataFlow Task using API that contain :
A) Source
B) LookUp
C) Destination
We will save some precious time on refactoring aspect using this class...
Jamie : Nice website... I used it since I'm working with SSIS...I get some practical information...Keep on good work...
Doug_b : About IDTSOutput90, this is an example for a simple DataFlow Task.
I hope It can help you
#region Map MetaData
public void MetaDataMapping(IDTSComponentMetaData90 dstMetaData, IDTSDesigntimeComponent90 dstComp)
{
IDTSInput90 destinationInput = dstMetaData.InputCollection[0];
int destinationInputID = destinationInput.ID;
IDTSVirtualInput90 destinationVirtualInput = destinationInput.GetVirtualInput();
foreach (IDTSVirtualInputColumn90 virtualInputColumn in destinationVirtualInput.VirtualInputColumnCollection)
{
// This will create an input column on the component.
dstComp.SetUsageType(destinationInputID,destinationVirtualInput,virtualInputColumn.LineageID,DTSUsageType.UT_READONLY);
// Get input column.
IDTSInputColumn90 inputColumn = destinationInput.InputColumnCollection.GetInputColumnByLineageID(virtualInputColumn.LineageID);
// Getting the corresponding external column.
// Ex : We will use the column name as the basis for matching data flow columns to external columns.
IDTSExternalMetadataColumn90 externalColumn = destinationInput.ExternalMetadataColumnCollection[virtualInputColumn.Name];
// Tell the component how to map.
dstComp.MapInputColumn(destinationInputID,inputColumn.ID,externalColumn.ID);
}
}
#endregion
|||
Hi,
I tried to create SSIS package programatically,I successfully added OLE DB source & destination but
got problem in adding lookup transformation.
How can I get reference table column output.& how can I set join column property for specific column
plz help.
Is anyone using C# and the SSIS Objects to Develop/Modify Packages?
Anyone out there developing or modifying packages w/ C# and the SSIS objects that I can compare notes with?
Thanks!
Done a bit, loading and poking around and some creating directly in code.|||Hi Darren ...
I've taken a look around SQLIS. Very helpful indeed!
Do you have any examples of something as simple as the following, using C# and the object model:
Source: SQL Server A, Table Foo
Transformations: None
Target SQL Server B, Table FooTwo
The real trick with the above is that at runtime I won't know what the tables are. I need to work w/ a SQL String and the re-initializemetadata stuff.
Looking for any help here.
Thanks!
|||I would probably rebuild the package each time, and Books Online covers this quite well I think.
Start with ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/0ca03712-a82e-4aa7-949b-f869a8936ddf.htm, then move onto the related Data Flow section.
The Data Flow sample includes setting the SQL and calling RMD, see "Adding and Configuring a Component".
|||Thank you very much for the suggestions!
I've looked over these items in the past and to be honest I think they are a bit weak in terms of examples, etc. My experience has been that some of the docs on this go into great detail, but they don't give you "the big picture." I got a copy of Professional SQL Server 2005 Integration Services (wrox) and this has been a help, but I'm still looking for more examples.
Any other suggestions?
All the best,
DB
|||Chapters 14 and 15 of that book is where you want to head. I've already been through Chapter 14 and it was a really great tutorial.
Darren wrote chapter 15 and his partner Allan Mitchell wrote chapter 14 and they're probably too modest to sing their own praises ...so I'll do it for them.
-Jamie
|||Thank you for the plug, but those chapters are more about building components, not components. Actually if you do understand how to build components, and the workings of adding and removing columns which they cover, then it will help your understanding for when building packages as well, I know it does for me. You do similar things in your component as when building packages, such as working on the managed interface wrapper, and selecting columns.
I don't know of anymore examples, I have always managed to do what I need quite effectively on what is above. You have the basics of building packages, so now everything else is just the nuances for the task or component type, which is more about understanding that component as opposed to general building package knowledge. Perhaps you could post individual problems as you get them and we'll see if we can help.
|||Thanks to both of you for the supportive words. I'm doing my best to solve this problem. I'm sure once I get a few components to work it becomes more of a cookie cutter process.
Here's an example of a problem I'm trying to solve using the Object Model. If anyone has a code sample that does some or all of these simple steps from end-to-end that would be an extreme help! It *seems* that this should be so simple!
In a nutshell:
I'm using C# and the SSIS object modle to try to create a single package that at runtime can look up a table and list of columns from a source SQL Server. With this table and column list in hand, I simply want to move the table's source data in its identical format as quickly as possible to another destination SQL Server into an identical table.
The reason I'm using the C# object model approach is so that I can build one package/application that can be used to move data for many different tables, determined at runtime, without having to have multiple packages.
In other words, if I have 300 tables to move data from SQL Server A to SQL Server B, I want one package, not 300. Earlier posts in this forum said to accomplish this requires C# and the object model. Reference: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=112283&SiteID=1
In the final product there will be one transformation, but I'd be happy right now just to get all the column outputs and inputs working correctly.
So far I'm having success with:
- Creating the initial package
- Creating the MainPipe
- Creating source and target OLE-DB Connection Managers
- Setting up a source component (see issues below)
- Doing an Instantiate and ProvideComponentProperties
- Associating the source component with the source connection manager
- Using SetComponentProperties to set the SQL statement (see issues below)
- Connecting to the source and running reinitializemetadata
- Repeating the above for the destination component
- Setting up a path between the two
Here are the issues that I'm pretty sure are screwing me up. I'm having a hard time with:
1. With the source component, all I want is "select col1, col2 from foo". I'm pretty sure that I'm missing some important steps to tell SSIS the specific list of columns that I want to have in my source component output. I am running the reinitializemetadata step, but I don't *think* this is enough by itself. I'm pretty sure there are some commands related to IDTSOutput90 but I'm having a hard time figuring out how to accomplish this step by step. I haven't been able to follow BOL to put this together.
2. I'm also missing the corresponding IDTSInput90 techniques to identify the input columns coming into my destination component. I looked at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/ef258c93-446f-44e6-b040-8164315b58ee.htm
as a reference, but this didn't seem to help (translation: too complex for me to understand without some assistance, and pilot error.) The idea of having this as a dynamic list would be extremely helpful for my particular problem.
3. OK, once my data shows up at my destination component if possible I need to UPSERT the data. A code example on this would be helpful ... how to set up the SQL statements for the SetComponentProperty.
4. Finally, I've been trying to figure out the right combination of AccessMode integer values that match SQL statements and such used by SetComponentProperty. For example:
mySrcDTInstance.SetComponentProperty("CommandTimeout", 0);
mySrcDTInstance.SetComponentProperty("OpenRowset", "[dbo].[test]");
mySrcDTInstance.SetComponentProperty("AccessMode", 0);
I've run every search I could think of and have posted the question before: What are the proper combinations of AccessMode integer values and SQL statments, OpenRowset, etc.
Any samples or suggestions or doc references I can delve into would be a GREAT help!!!!!
DB
|||Hi Doug B,
We are facing the same problem here. We need to move 200 tables with Data Flow Task. I was wondering if you figure out a solution since the last posting.
Mathieu
|||I'm using C# and the SSIS object modle to try to create a single package that at runtime can look up a table and list of columns from a source SQL Server. With this table and column list in hand, I simply want to move the table's source data in its identical format as quickly as possible to another destination SQL Server into an identical table.
I haven't looked at your post in detail but I think I should pick you up one one point here. You say you want to "create a single package that at runtime can look up a table and list of columns from <somewhere>". I think your approach here is slightly awry. Yes, you need to write dotnet code to do this. But in your sentance here it sounds as though you want to SSIS to be a host for that dotnet code - and I think that is wrong. Sure, write a dotnet app that uses the SSIS object model to create a package based on some defined metadata - but there's no need to run that code actually within a SSIS package. Why not jsut run it from the command-line?
Remember that it is not possible for a SSIS package to change itself using the object model. You could do this in DTS, but not in SSIS.
I hope the subtle distinction is clear here.
-Jamie
|||3. OK, once my data shows up at my destination component if possible I need to UPSERT the data. A code example on this would be helpful ... how to set up the SQL statements for the SetComponentProperty.
That's simply not possible just in a destination component. If you want to do an upsert then you will need to compare the pipeline data with the destination and that needs to happen upstream of the destination component. I've talked about this technique more here:
Checking if a row exists and if it does, has it changed?
http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx
-Jamie
|||1. With the source component, all I want is "select col1, col2 from foo". I'm pretty sure that I'm missing some important steps to tell SSIS the specific list of columns that I want to have in my source component output. I am running the reinitializemetadata step, but I don't *think* this is enough by itself. I'm pretty sure there are some commands related to IDTSOutput90 but I'm having a hard time figuring out how to accomplish this step by step. I haven't been able to follow BOL to put this together.
I vaguely remember trying this myself once. You can't just give the source adapter a SQL statement and let it work out the metadata itself. Although this is what it appears as if the Source Adapters do in the SSIS Designer UI, it isn't actually the case. The adapters still have to interogate the source to find the metadata - you have to do the same (Unless you have the column data types in your metadata store).
-Jamie
|||Hi Mathieu ...
Unfortunately we were not able to get this to work. Perhaps someone with more C# experience would have had better luck.
All the best,
Doug
|||Hi everyone,
I've been working on this issue since one week.
Finally, I built a c# class that generate a package with the 200 Dataflow Task (source, lookup, destination) with the SSIS object API.
It is not that complicated except that the api is quite undocumented...
My program is doing the following task :
#1 - From a table, I get the table source (TableName) and the destination (tableName) with the load order
#2 - Using Package API, I create a package from a template
#3 - With the load order, I create a DataFlow Task using API that contain :
A) Source
B) LookUp
C) Destination
We will save some precious time on refactoring aspect using this class...
Jamie : Nice website... I used it since I'm working with SSIS...I get some practical information...Keep on good work...
Doug_b : About IDTSOutput90, this is an example for a simple DataFlow Task.
I hope It can help you
#region Map MetaData
public void MetaDataMapping(IDTSComponentMetaData90 dstMetaData, IDTSDesigntimeComponent90 dstComp)
{
IDTSInput90 destinationInput = dstMetaData.InputCollection[0];
int destinationInputID = destinationInput.ID;
IDTSVirtualInput90 destinationVirtualInput = destinationInput.GetVirtualInput();
foreach (IDTSVirtualInputColumn90 virtualInputColumn in destinationVirtualInput.VirtualInputColumnCollection)
{
// This will create an input column on the component.
dstComp.SetUsageType(destinationInputID,destinationVirtualInput,virtualInputColumn.LineageID,DTSUsageType.UT_READONLY);
// Get input column.
IDTSInputColumn90 inputColumn = destinationInput.InputColumnCollection.GetInputColumnByLineageID(virtualInputColumn.LineageID);
// Getting the corresponding external column.
// Ex : We will use the column name as the basis for matching data flow columns to external columns.
IDTSExternalMetadataColumn90 externalColumn = destinationInput.ExternalMetadataColumnCollection[virtualInputColumn.Name];
// Tell the component how to map.
dstComp.MapInputColumn(destinationInputID,inputColumn.ID,externalColumn.ID);
}
}
#endregion
|||
Hi,
I tried to create SSIS package programatically,I successfully added OLE DB source & destination but
got problem in adding lookup transformation.
How can I get reference table column output.& how can I set join column property for specific column
plz help.
Subscribe to:
Posts (Atom)