ability to use variables in package configurations and set their values at runtime

Hi,

My scenario:

I am using a FTP Connection Manager and the configuration setting for it is being set in the package configuration xml file. So the xml file contains the Ftpserver, FTp server username and password. The package is picking up the values from the xml file and is executing successfully. I have to do this because I was not able to provide an expression to the Connection Manager Server Password property.

Now, I want to pick up the ftp details from a database table and set it in the xml file during runtime. Is this possible? OR something like using the

<Configuration ConfiguredType="Property" Path="\Package.Connections[FTP Connection Manager].Properties[ServerPassword]" ValueType="Variable"><ConfiguredValue>@.[user::FtpPassword]</ConfiguredValue></Configuration>

Kindly look at the items in bold. Is this possible? Then I can set the value of the variable in the package before the FTP connection manager task is executed.

Thanks for all the help.

$wapnil

You could use a configuration storage as DB.

Or you can use Script task to query the DB and set the FTP connection properties or Variable values at runtime

Or even Execute SQL task to get the variable values from DB|||

Thanks

Reply to the options that you have provided

1) We want to use XML for the configuration. Just something which we want to follow if we could.

2) We are trying to minimuize the script tasks in the package by bringing the configuration details outside of the package and storing it in XML.

3) same as 1

Lets take a FTP connection Manager for example. The details of the FTP server are present in the database and I have also created variables for that but atleast I am not able to set the FTP Server password using a variable so I resorted to using the xml file and putting in the password there.

Now if I want to connect to multiple FTP server using the same package and the connection details are there in the database then I can pull out the connection details using an Execute SQL task but then how to update the xml files with values......knowing that the FTP server password can be only be set dynamically using the xml file.

Correct me if I am wrong. is storing the variable in the DB the only solution.

Thanks,

$wapnil

|||

spattewar wrote:

Now if I want to connect to multiple FTP server using the same package and the connection details

Are you trying to connect multiple FTP servers at the same time or are you trying to run the package in different environments?

For example if I would need to pull info from 3 different FTP servers - I would create 3 different configuration files:
1 for Development
1 for staging
and 1 for Production site|||

I am trying to connect to multiple FTP servers. But it can be at the same time or in sequence. But I am planning to use a single package for this task.

Thanks for your response.

$wapnil

|||Why don't you use Parent Package Configurations instead? A simple 2-package setup could work where:
Your mother package cycles through a list of FTP server configurations or whatever is needed.|||

That looks like a good idea.

But there is one concern. Even though I pass the configuration as a parent package variable to the child package, will I be able to set the FTP ServerPassword property of the FTP connection manager using a variable. I doubt I can do that, then it boils down to the same thing picking up the package configuration from the xml file or a database, here we would prefer xml file.

Maybe we can do this by.

1) Running the master package which will have a script task to update a xml configuration file.

2) Pass that xml file as a configuration parameter to the child package and execute the child package.

Thanks for your response.

$wapnil

|||I don't have any reason to believe that you can't pass the password or even the whole connectionstring as a variable.

However do note that if your variable values refuse to change (although I don't see any reason why they won't) you might have to tweak the ProtectionLevel of the package to DontSaveSensitive or some other value that allows you to modify your connectionstring variable.

HTHsql

A way to get Table shema as xml ?

Dear all,
Is there a way to get from SQL table and XML shema file (XSD) that can be
read afterwards from a .NEt application ?
I know that I could read frommy ASP.NET code the whole table structure but
having the local xsd file would be faster for reading
regards
serge
Hello serge,

> Is there a way to get from SQL table and XML shema file (XSD) that can
> be read afterwards from a .NEt application ?
> I know that I could read frommy ASP.NET code the whole table structure
> but having the local xsd file would be faster for reading
AFAIK, not directly. One of the things I've done in the past is generate
information about the schema from the metadata. Something like this:
alter function dbo.GetColumnsForTable(@.TableObjectID int)
returns xml
as begin
declare @.rv xml
set @.rv = (select
c.column_id'@.position'
, c.name'name'
, y.name'dataType'
, c.max_length'maxLength'
, c.precision'precision'
, c.scale'scale'
, c.collation_name'collationName'
, c.is_nullable'nullable'
, c.is_rowguidcol'isRowGUID'
, c.is_identity'isIdentity'
, c.is_computed'isComputed'
, x.name
from sys.columns c
join sys.types y on c.system_type_id = y.system_type_id
left join sys.xml_schema_collections x on c.xml_collection_id = x.xml_collection_id
where c.object_id = @.TableObjectID
for xml path('column'),type)
return @.rv
end
go
select t.name'name',
dbo.GetColumnsForTable(t.object_id) as 'table/columns'
from sys.tables t
for xml path('table'),root('tables')
go
While its not a schema per se, you can get a lot of information doing this
kind of coding.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

A way to get Table shema as xml ?

Dear all,
Is there a way to get from SQL table and XML shema file (XSD) that can be
read afterwards from a .NEt application ?
I know that I could read frommy ASP.NET code the whole table structure but
having the local xsd file would be faster for reading
regards
sergeHello serge,

> Is there a way to get from SQL table and XML shema file (XSD) that can
> be read afterwards from a .NEt application ?
> I know that I could read frommy ASP.NET code the whole table structure
> but having the local xsd file would be faster for reading
AFAIK, not directly. One of the things I've done in the past is generate
information about the schema from the metadata. Something like this:
alter function dbo.GetColumnsForTable(@.TableObjectID int)
returns xml
as begin
declare @.rv xml
set @.rv = (select
c.column_id '@.position'
, c.name 'name'
, y.name 'dataType'
, c.max_length 'maxLength'
, c.precision 'precision'
, c.scale 'scale'
, c.collation_name 'collationName'
, c.is_nullable 'nullable'
, c.is_rowguidcol 'isRowGUID'
, c.is_identity 'isIdentity'
, c.is_computed 'isComputed'
, x.name
from sys.columns c
join sys.types y on c.system_type_id = y.system_type_id
left join sys.xml_schema_collections x on c.xml_collection_id = x.xml_collec
tion_id
where c.object_id = @.TableObjectID
for xml path('column'),type)
return @.rv
end
go
select t.name 'name',
dbo.GetColumnsForTable(t.object_id) as 'table/columns'
from sys.tables t
for xml path('table'),root('tables')
go
While its not a schema per se, you can get a lot of information doing this
kind of coding.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

a small problem

hi people,
my query goes something like this...
select a.*,b.*,c.* from table1 a ,table2 b,table3 c where a.col1 = b.col2
and a.col1 = c.col4 for xml auto, elements,base binary64
everything works fine except tht the elements of tables table2,table3 are
being added as child/nested nodes...instead of coming in the main flow..
ex- this is the output iam getting
<a>
<a-fld1>...</a-fld1>
<a-fld2>...</a-fld2>
<a-fld3>...</a-fld3>
<b>
<b-fld1>...</b-fld1>
<b-fld2>...</b-fld2>
<b-fld3>...</b-fld3>
<c>
<c-fld1>...</c-fld1>
<c-fld2>...</c-fld2>
<c-fld3>...</c-fld3>
</c>
</b
</a>
but i want only one top level & one child level...like
<a>
<a-fld1>...</a-fld1>
<a-fld2>...</a-fld2>
<a-fld3>...</a-fld3>
<b>
<b-fld1>...</b-fld1>
<b-fld2>...</b-fld2>
<b-fld3>...</b-fld3>
<c-fld1>...</c-fld1>
<c-fld2>...</c-fld2>
<c-fld3>...</c-fld3>
.....
.....
</b>
</a>....is it possible...please suggest metry Using XML Explicit|||xml explicit needs that all the select statements have the joining
columns...but my joining columns as not the same in all the tables as u can
see
its where a.col1 = b.col2
and a.col1 = c.col4 for xml auto, elements,base binary64
not
where a.col1 = b.col1
and a.col1 = c.col1 for xml auto, elements,base binary64...
so problem with it...atleast please tell me wether we can construct xml
from raw string in SQL Stored procedures...i will read all the neccessary
data and format it accordingly in xml
"Omnibuzz" wrote:

> try Using XML Explicit|||Can you post the DDL, insert script for some sample data and the expected
result for the sample data?
Also I presume u have SQL Server 2000 right?|||It's hard to come up with a good answer if we can't see the tables, so pleas
e
post DDL (and maybe sample data).
ML
http://milambda.blogspot.com/|||Anyways.. try this and let me know if this is what you wanted...
create table xmltbl1
(t1col1 int, t1col2 varchar(10))
create table xmltbl2
(t2col1 int, t2col2 varchar(10))
create table xmltbl3
(t3col1 int, t3col2 varchar(10),t3col3 int)
insert into xmltbl1 values (1,'abcde')
insert into xmltbl2 values (1,'fghij')
insert into xmltbl3 values (2,'klmno',1)
select a.*,b.* from xmltbl1 a,
(select b.* ,c.* from xmltbl2 b,xmltbl3 c
where b.t2col1 = c.t3col3) as b
where a.t1col1 = b.t2col1
for xml auto, elements,BINARY BASE64

A quicker way of performing this XML query?

Hi all

I have the following query that makes up part of a table-value-function i've written in SQL 2K5.

XML Query

SELECT CONVERT(XML, objectdata).value('(/xmlData/IsTrue)[1]', 'bit') as IsTrue
from myTable
where idfield = [@.FunctionTable].ID

Until adding this query, the function ran in under 60 seconds. Adding this query has added an extra 120 seconds to the function execution time.

This query is called around 200 times in the function as part of an update:

UPDATE @.FunctionTable set ...

....

, FieldValue = (SELECT CONVERT(XML, objectdata).value('(/xmlData/IsTrue)[1]', 'bit') as IsTrue
from myTable
where idfield = [@.FunctionTable].ID)

Is there a more performant way to do the same XML lookup?

Many Thanks

Are there any XML indexes created?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Probabaly not, would this index be placed upon the source field?
|||

You can create an index on the field where you do the XQuery.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

A quicker way of performing this XML query?

Hi all

I have the following query that makes up part of a table-value-function i've written in SQL 2K5.

XML Query

SELECT CONVERT(XML, objectdata).value('(/xmlData/IsTrue)[1]', 'bit') as IsTrue
from myTable
where idfield = [@.FunctionTable].ID

Until adding this query, the function ran in under 60 seconds. Adding this query has added an extra 120 seconds to the function execution time.

This query is called around 200 times in the function as part of an update:

UPDATE @.FunctionTable set ...

....

, FieldValue = (SELECT CONVERT(XML, objectdata).value('(/xmlData/IsTrue)[1]', 'bit') as IsTrue
from myTable
where idfield = [@.FunctionTable].ID)

Is there a more performant way to do the same XML lookup?

Many Thanks

Are there any XML indexes created?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Probabaly not, would this index be placed upon the source field?
|||

You can create an index on the field where you do the XQuery.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

A question about sqlxml

Who use sqlxml .net?

How can I write code like this:

select count(1) as count from Orders for xml auto

That's error.

And what is the correct sqlxml code?

Tks.

Hi,

the code works fine with SQL2005 but fails on SQL2K

Eralper

http://www.kodyaz.com

|||

You could write it like below for SQL Server 2000:

select count from (select count(1) as count from Orders) as Orders for xml auto

A newbie need help for SQL XML

I am trying to set up my SQL server so that I can get recordset in XML format
over HTTP.
In a seperate IIS server (with SQL tools):
1) I created a directory (C:\nwind) and underneath created C:\nwind\template
and C:\nwind\schema.
2) Opened the IIS virtual dir. for SQL (IISsql) and created a virtual
directory nwind and pointed to C:\nwind.
3) Set the security to either sql or windows.
4)Selected the SQL server and appropriate database.
5)Checked allow POST and allow URL query.
6) Created virtual names
template->type-template->location-C:\nwind\template, dbobject->type-dbobject,
schema->type-schema->location-C:\nwind\schema.
I have both a windows account and sql account "test" that has access to
northwind database. When I type the following in a workstation:
http://IISserver/nwind?sql=SELECT * FROM Employees FOR XML AUTO&root=root
I get the:
HTTP Error 404 - File or directory not found.
Internet Information Services (IIS)
But when I ran the IIS in XP machine I didn't have any problem. Any help is
greatly appreciated. Thanks
What OS is this machine running?
Thanks,
Irwin
Irwin Dolobowsky
Program Manager, SqlXml
http://blogs.msdn.com/irwando
This posting is provided "AS IS" with no warranties, and confers no rights.
"SN" <SN@.discussions.microsoft.com> wrote in message
news:4FD030C3-B878-4B2E-B8FA-5B3505756371@.microsoft.com...
>I am trying to set up my SQL server so that I can get recordset in XML
>format
> over HTTP.
> In a seperate IIS server (with SQL tools):
> 1) I created a directory (C:\nwind) and underneath created
> C:\nwind\template
> and C:\nwind\schema.
> 2) Opened the IIS virtual dir. for SQL (IISsql) and created a virtual
> directory nwind and pointed to C:\nwind.
> 3) Set the security to either sql or windows.
> 4)Selected the SQL server and appropriate database.
> 5)Checked allow POST and allow URL query.
> 6) Created virtual names
> template->type-template->location-C:\nwind\template,
> dbobject->type-dbobject,
> schema->type-schema->location-C:\nwind\schema.
> I have both a windows account and sql account "test" that has access to
> northwind database. When I type the following in a workstation:
> http://IISserver/nwind?sql=SELECT * FROM Employees FOR XML AUTO&root=root
> I get the:
> HTTP Error 404 - File or directory not found.
> Internet Information Services (IIS)
> But when I ran the IIS in XP machine I didn't have any problem. Any help
> is
> greatly appreciated. Thanks
|||It's IIS6.0/Windows 2003.
"Irwin Dolobowsky [MSFT]" wrote:

> What OS is this machine running?
> --
> Thanks,
> Irwin
> Irwin Dolobowsky
> Program Manager, SqlXml
> http://blogs.msdn.com/irwando
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "SN" <SN@.discussions.microsoft.com> wrote in message
> news:4FD030C3-B878-4B2E-B8FA-5B3505756371@.microsoft.com...
>
>
|||Check the docs, there are some steps for getting it running on Win2k3. The
"Guidelines and Limitations" section.
Thanks,
Irwin
Irwin Dolobowsky
Program Manager, SqlXml
http://blogs.msdn.com/irwando
This posting is provided "AS IS" with no warranties, and confers no rights.
"SN" <SN@.discussions.microsoft.com> wrote in message
news:23043A59-00DB-464F-A58B-504E3EAE751E@.microsoft.com...[vbcol=seagreen]
> It's IIS6.0/Windows 2003.
> "Irwin Dolobowsky [MSFT]" wrote:

A Microsoft Native XML Database Someday?

Hi,
I was wondering if you might provide some idea of whether Microsoft is
considering building a native Xml database as Xml has become an important
part of so many of its products. I know about SQL 2005 and its method for
Xml handling, but couldn't that product family be expanded to include
something that is Xml only? Perhaps during the data-enhancements planned for
C#.
Thanks,
James
PS - Please give Anders a hug from us C# developers, he's a godsend.Hi James.
I doubt that we will ever create an XML only database system. But if you
give us more encouraging feedback, we will work on making SQL Server even
more usable as an XML database system :-).
Best regards
Michael
PS: We have forwarded the hugs to Anders :-).
"James White" <james@.rationalpath.com> wrote in message
news:u%23$PMrwrFHA.3352@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I was wondering if you might provide some idea of whether Microsoft is
> considering building a native Xml database as Xml has become an important
> part of so many of its products. I know about SQL 2005 and its method for
> Xml handling, but couldn't that product family be expanded to include
> something that is Xml only? Perhaps during the data-enhancements planned
> for
> C#.
> Thanks,
> James
> PS - Please give Anders a hug from us C# developers, he's a godsend.
>
>

A Microsoft Native XML Database Someday?

Hi,
I was wondering if you might provide some idea of whether Microsoft is
considering building a native Xml database as Xml has become an important
part of so many of its products. I know about SQL 2005 and its method for
Xml handling, but couldn't that product family be expanded to include
something that is Xml only? Perhaps during the data-enhancements planned for
C#.
Thanks,
James
PS - Please give Anders a hug from us C# developers, he's a godsend.
Hi James.
I doubt that we will ever create an XML only database system. But if you
give us more encouraging feedback, we will work on making SQL Server even
more usable as an XML database system :-).
Best regards
Michael
PS: We have forwarded the hugs to Anders :-).
"James White" <james@.rationalpath.com> wrote in message
news:u%23$PMrwrFHA.3352@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I was wondering if you might provide some idea of whether Microsoft is
> considering building a native Xml database as Xml has become an important
> part of so many of its products. I know about SQL 2005 and its method for
> Xml handling, but couldn't that product family be expanded to include
> something that is Xml only? Perhaps during the data-enhancements planned
> for
> C#.
> Thanks,
> James
> PS - Please give Anders a hug from us C# developers, he's a godsend.
>
>