Is it possible to ftp files using code in a SSIS Script Task?

Is it possible to ftp files using code in a Script Task? I need to read the contents of an xml file and if it has a a specific file name in there then I ftp the corresponding pdf file which is at the same location as the xml file. However I cannot do this using the provided FTP Task in SSIS, I would need to use code to do this as there are close to 50 xml files which I need to read and upload the corresponding pdf file file it meets a certain criteria.

I do not see a way of looping thru all the files in a folder unless I do this in a Script task. Any inputs or alternative comments on doing this will be appreciated.

Thanks,

MShah

In the Control Flow you should be able to use a ForEach loop to loop over all the files. Then you can use a script task (XML task might also work but not sure) to extract the information needed to create the path & filename to FTP.

You would store the create filename in a Variable and then do the FTP task in the loop to upload the files. Anyone know if the FTP task will connect to FTP once in this type of loop?

Fred

sql

Is it a good to replace SQL script files with XML files?

I am thinking about replacing the INSERT data script
files that I have with XML files. This way I can open the XML
file using an XML Editor and see the values in a GRID and
make changes easier.

Do you see any problem with this approach?

I managed to put together some code that is exporting
a SQL table with its data to an XML file and also a code
that reads the XML file's data and inserts it into a table.

Now I am researching on XSD, td:datatype, DTD...
(I am new to XML) in order to figure out how I can
use a single xml file that will hold both the sql server
fields, the datatypes and their values.

If you have links to some sample code that has anything
to do with the datatype export and import I am working
on, can you please share them with me?

Most importantly what do you think about the idea of using
XML files vs sql scripts?

Thank youserge (sergea@.nospam.ehmail.com) writes:
> I am thinking about replacing the INSERT data script
> files that I have with XML files. This way I can open the XML
> file using an XML Editor and see the values in a GRID and
> make changes easier.
> Do you see any problem with this approach?

I know too little XML to say that whether this is good or bad. I didn't
know that there were XML Editors where you could edit grid cells.

I recognize the problem, though, because we have plenty of such files in
our shop. Our solution to the problem is Excel. (Which can be saved as
XML, but we don't do that currently.) Then we have a tool that reads the
Excel book and generates an INSERT-file from it. That file, by the way, does
not include any INSERT statements, but calls to a stored procedure that
will insert or update (or delete), so that the files easily can be rerun.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I have only started learning XML 3-4 days ago. I ran into a
newsgroup post by chance where someone was using XML
to transfer data to SQL Server.

http://visualbasic.ittoolbox.com/gr...rver-l&i=780204

So that made me wonder why I wouldn't do that?
I've been working on this since then and slowly learning more
about SELECT * FROM TABLE FOR XML AUTO, XML,
DTD, XSD files, now I need to learn XDR, I think XDR is
similar to XSD but seems to be aimed for SQL Server.
I'll post some questions on microsoft.public.xml and hopefully
I'll get some answers from people who have already done what
I am trying to do.

But one question I have is if you are using Excel, are you using
it only for the INSERT data part? What about using the same
or another Excel file to hold the table's column names and data
types?

At this point in time (with my very little knowledge of XML) I
believe it wouldn't be a good idea to replace the sql files holding
the table structures with XML files holding the equivalent in terms
of the columns and its data types. I think that is more difficult
for someone to make table changes.

Here are three links for free XML Editors.
http://www.xmlcooktop.com/

I like these two as they will show you the data in grids:

http://symbolclick.com/index.htm
http://www.xmlfox.com/download.htm

Thanks

> I know too little XML to say that whether this is good or bad. I didn't
> know that there were XML Editors where you could edit grid cells.
> I recognize the problem, though, because we have plenty of such files in
> our shop. Our solution to the problem is Excel. (Which can be saved as
> XML, but we don't do that currently.) Then we have a tool that reads the
> Excel book and generates an INSERT-file from it. That file, by the way,
> does
> not include any INSERT statements, but calls to a stored procedure that
> will insert or update (or delete), so that the files easily can be rerun.|||serge (sergea@.nospam.ehmail.com) writes:
> But one question I have is if you are using Excel, are you using
> it only for the INSERT data part? What about using the same
> or another Excel file to hold the table's column names and data
> types?

I might be misunderstanding your questions, but for that purpose a
data-modelling tool is much better in my opinion. In our shop we
use PowerDesigner from Sybase.

(Incidently, you can save the data model in XML format. But the main
point with that is if you keep the model under verison control, you
can use a standard diff tool to see the differences between two versions.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>I might be misunderstanding your questions, but for that purpose a
>data-modelling tool is much better in my opinion. In our shop we
>use PowerDesigner from Sybase.

For some reason my Outlook Express is not downloading your last
post.

I checked the demo of PowerDesigner from Sybase. Modeling tool
is something I will have to look into in the next weeks/months.

Thanks

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...
>

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...
>

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.
>>
>>
>

Is capturesql the only way to script a drop using smo?

Is capturesql the only way to script a drop using smo?

You can set the ScriptingOptions.ScriptDrops to true and then pass it to Script(ScriptingOptions). This will return you the script to drop the object.

Thanks,
Kuntal

|||Aha...thanks. I knew there had to be an easy way to do this!