Is it possible to read/write a file at privilege?

hello.
I saw some systems which were hacked by sql injection tool
And some files of the systems were changed. I guess the tool tried to
read/write files.
howerver, the user privilege is not 'sa'. Is it possible for user who
is not 'sa' to read/write files?
If it is possible, how can I prevent the tools from reading/writing
files even if my web page is injectable?dodol (Dolka1@.gmail.com) writes:
> I saw some systems which were hacked by sql injection tool
> And some files of the systems were changed. I guess the tool tried to
> read/write files.
> howerver, the user privilege is not 'sa'. Is it possible for user who
> is not 'sa' to read/write files?
It could be another user with sysadmin rights. Or execution rights might
have been granted on xp_cmdshell or sp_OAxxx.

> If it is possible, how can I prevent the tools from reading/writing
> files even if my web page is injectable?
Make sure that xp_cmdshell and the sp_OAxxx procedures are disabled.
Make sure that SQL Server runs on a domain account that has no extra
privileges. The less welcome it is in the rest of the network the better.
But the main line of defence is of course to use stored procedure or
parameterised statements and never interpolate incoming stuff into
query strings.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Is it possible to read Flat File data from a Variable?

Hi,

I'm relatively new to SSIS and I've been tinkering with the XML and Flat File sources.

I noticed that in the XML source it is possible to tell SSIS to read the XML data from a variable. I didn't see a similar option for the Flat File source.

Does anyone know if it is possible to read flat file data from a variable when using the Flat File source?

Thanks,

-dhideal

Simple answer is no. I'd question why you want to do this though as any volume of data in a variable will not perform well.

A Script component could act as a source though and output rows and columns. A Script Task could also be used to write the variable data to a file, and then just use the normal source.

|||

Darren,

Thanks for the quick response.

I realize that performance wont be the best but I was wondering more from the point of view of taking XML data that has been transformed using XSL and feeding that straight into a table.

I realize that I was going down the wrong path as I should not have the XSL convert to a flat file format but keep the data in XML format and then use the XML Source's read from variable capability.

Keeping the data in a variable is going to eat lots of RAM but hopefully avoiding the write/read to disk will give a significant enough performance gain to justify it.

Thanks,

-dhideal

|||

The disk cost may be less than the cost or marshalling data in memory.

I have seen similar situations where it has been cheaper to stage data through files, because that way you can use the pipeline which ideally only works on a buffer full of data at one time (not quite true, but the point is there). Therefore you don't have the pain or finding a lot of memory, you just work in piecmeal sections.

In summary the cost of writing and reading to and from a file can be far outweighed by the overhead of finding a lot of memory in one go. This does of course depend on hardware and data volumes, but you may want to try both methods.

|||

Its also worth saying that raw files can be screamingly fast. I've just constructed a quick and dirty demo where a raw file containing 10000000 records of width 270 bytes (nearly 3GB of data) was read from disk and pushed into a rowcount component in 68seconds. That's fast. When I pushed the same data into another raw file (i.e. writing to disk) it took 175seconds. Again very impressive, all the more so considering the files were on the same physical disk.

Definately worth a look.

-Jamie

|||

Jamie/Darren,

Thanks for the advice. I will definitely try out both options but I believe staying in memory might be better for me because the XML files I'm working with aren't huge but there are just a lot of them.

-dhideal

Is it possible to read Flat File data from a Variable?

Hi,

I'm relatively new to SSIS and I've been tinkering with the XML and Flat File sources.

I noticed that in the XML source it is possible to tell SSIS to read the XML data from a variable. I didn't see a similar option for the Flat File source.

Does anyone know if it is possible to read flat file data from a variable when using the Flat File source?

Thanks,

-dhideal

Simple answer is no. I'd question why you want to do this though as any volume of data in a variable will not perform well.

A Script component could act as a source though and output rows and columns. A Script Task could also be used to write the variable data to a file, and then just use the normal source.

|||

Darren,

Thanks for the quick response.

I realize that performance wont be the best but I was wondering more from the point of view of taking XML data that has been transformed using XSL and feeding that straight into a table.

I realize that I was going down the wrong path as I should not have the XSL convert to a flat file format but keep the data in XML format and then use the XML Source's read from variable capability.

Keeping the data in a variable is going to eat lots of RAM but hopefully avoiding the write/read to disk will give a significant enough performance gain to justify it.

Thanks,

-dhideal

|||

The disk cost may be less than the cost or marshalling data in memory.

I have seen similar situations where it has been cheaper to stage data through files, because that way you can use the pipeline which ideally only works on a buffer full of data at one time (not quite true, but the point is there). Therefore you don't have the pain or finding a lot of memory, you just work in piecmeal sections.

In summary the cost of writing and reading to and from a file can be far outweighed by the overhead of finding a lot of memory in one go. This does of course depend on hardware and data volumes, but you may want to try both methods.

|||

Its also worth saying that raw files can be screamingly fast. I've just constructed a quick and dirty demo where a raw file containing 10000000 records of width 270 bytes (nearly 3GB of data) was read from disk and pushed into a rowcount component in 68seconds. That's fast. When I pushed the same data into another raw file (i.e. writing to disk) it took 175seconds. Again very impressive, all the more so considering the files were on the same physical disk.

Definately worth a look.

-Jamie

|||

Jamie/Darren,

Thanks for the advice. I will definitely try out both options but I believe staying in memory might be better for me because the XML files I'm working with aren't huge but there are just a lot of them.

-dhideal

is it possible to put the raiseerror in a text file or show it to the user after its been

I am in process of building a website where the user can upload files and then those files are loaded in to a sql server database. I am using some sprocs to scrub the data and then insert them into the production database.. And in my sprpc before and after updating or inserting a record or scrubing... i am returning the count by raising an error. or returning the rownumber where the error occured.. is there any way i can get the raise error part or whatever error i get while scrubing the data and relay it back to the user in a user freindly way or in a text file... or the best thing is can i open smalll window where i can show them what processing is goin on and alert them if there are any errors...

Any help will be appreciated.

Regards

Karen

Take a look forSqlException.Errors. Any SqlError object has the number of error and another informations.

PS.: To another databases, take a look forOleDbException.Errors.

|||

Hi Karenros,

Based on my understanding, you want to use Raiserror to generate an error message and pass it to the client user. Client user may get an alerting message or write it to a text file when getting the error message. If I've misunderstood you ,please feel free to tell me, thanks.

You can put your sqlcommand in a try block and in your catch block, write sqlconnection.errors.message to a text file. Please remember do not assign the severity value of your error message more than 19, or else it maybe cause terminate your connection. Sample code is like the following:

 try { con.Open(); cmd.ExecuteNonQuery(); }catch (SqlException ex) {using(StreamWriter sw=new StreamWriter("your text log file path here")) {foreach (SqlError errin ex.Errors) sw.WriteLine(err.Message+"\n"); } }

Hope my suggestion helps

|||

Chen,

Thanks for your answer. yeah and thats exactly that i wanted to do... so that user would know if the import process was successful or not...

I have tried using sqlexception before with no luck.. may be i didnt import the right header files in order for that work and i have also seen on msdn that we need a sqlinfomessage class or something like to do it.. Pls correct me if i am wrong...

anyways i am gonna give it a try and will let you know...

Regards

Karen

|||

Below is an example of how you can use the InfoMessage event handler:

First you'll have to create an event handler for this event like below:

con.Open();
con.InfoMessage +=new SqlInfoMessageEventHandler(con_InfoMessage);// here i've registered for the event
... set up the command object
cmd.NotificationAutoEnlist =true;
cmd.ExecuteNonQuery();

Then you can go on and write any code you want in that event handler method.

private void con_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
// your file writing code goes here. The eventArgs e holds errors, messages, source etc.
 // you can just use e.ToString() and everything is there for you.
}

Hope this will help.

|||

Hi Karenros,

I've tested the sqlexception code on my local machine and it does work fine. So, maybe you have made some mistakes somewhere else.

However, I think you can also trydhimant 's solution. That's really a good method to solve your problem. thanks

is it possible to merge 2 columns into 1 in SQL server

Hi..

is it possible to merge 2 columns into 1 to hold data like this... When the user imports the file in particular file they will be

ACT_ID1 Tot_ACT1 ACT_ID2 TOT_ACT2 ..... until 15

BB 1245.45 CT some amount ....

The 2 letter character may change prob for each file.. at leat i know of somethem may change

So while i transfer the data to the production database can is it possible to do some thing like

COL1 BB 1245.45

COL2 CT 12456.12 etc..

Any help will be appreciated.

Regards

Karen

Hi Karen

You could try two inserts. Do the first one on column 1 and 2 and then do the second one on 3 and 4 in the same destination table. Put them in a stored proc that gets called on the import. You may also want to add a column that identifies which set of columns they are from.

|||

Charles,

Thanks for your answer... I am getting these acronymns from another dbf file which the user imports... so does it makes sense to create a table called acronymns (may change the name later) and have the following fields..

AcryID(int identity) Name Description

so when i am importing the information into the production database i can reference the acronymn from this table by inner joining it and then insert the data into the production database... like the way you suggested...

Is this approach good or would it slow down things...

Any other suggestions are welcome too.

Regards

Karen

|||

Anything you can do to normalize the data is a good thing, in terms of performance, maintenance and scalability.

|||

so do u think the approach is good or bad ?

|||

Yes. Here's an article about normalization:

http://en.wikipedia.org/wiki/Database_normalization

Is it possible to load such XML file using SQLXML BulkLoad?

Hello
I have an XML file:
<?xml version="1.0" encoding="UTF-8"?>
<root>
<Item>
<ItemID>1</ItemID>
<Property1>...</Property1>
<Property2>...</Property2>
<Property3>...</Property3>
</Item>
<Item>
<ItemID>2</ItemID>
<Property1>...</Property1>
<Property2>...</Property2>
<Property3>...</Property3>
<Property4>...</Property3>
..
</Item>
...
</root>
Number of properties for <Item> is not fixed and their names also not
defined (except ItemID). Is it possible to create XSD schema for
importing this into table using SQLXML BulkLoad facility?
Table:
ItemID PropertyName PropertyValue
=================================
1 Property1 ...
1 Property2 ...
1 Property3 ...
2 Property1 ...
2 Property2 ...
2 Property3 ...
2 Property4 ...
...
Thank you
Martin Rakhmanov
jimmers@.yandex.ruYou cannot map names of elements into data using the schema mapping. You
need to use OpenXML for such mappings.
HTH
Michael
"jimmers" <jimmers@.yandex.ru> wrote in message
news:b0ede647.0501200116.5025661d@.posting.google.com...
> Hello
> I have an XML file:
> <?xml version="1.0" encoding="UTF-8"?>
> <root>
> <Item>
> <ItemID>1</ItemID>
> <Property1>...</Property1>
> <Property2>...</Property2>
> <Property3>...</Property3>
> </Item>
> <Item>
> <ItemID>2</ItemID>
> <Property1>...</Property1>
> <Property2>...</Property2>
> <Property3>...</Property3>
> <Property4>...</Property3>
> ...
> </Item>
> ...
> </root>
> Number of properties for <Item> is not fixed and their names also not
> defined (except ItemID). Is it possible to create XSD schema for
> importing this into table using SQLXML BulkLoad facility?
> Table:
> ItemID PropertyName PropertyValue
> =================================
> 1 Property1 ...
> 1 Property2 ...
> 1 Property3 ...
> 2 Property1 ...
> 2 Property2 ...
> 2 Property3 ...
> 2 Property4 ...
> ...
>
> Thank you
> Martin Rakhmanov
> jimmers@.yandex.ru|||No, not this exact data file can be mapped using XSD schema. Use XSLT to
transform this data file to look something like, .
<?xml version="1.0" encoding="UTF-8"?>
<root>
<Item>
<ItemID>1</ItemID>
<Property><Name>Property1</Name><Value>...</Value></Property>
<Property><Name>Property2</Name><Value>...</Value></Property>
<Property><Name>Property3</Name><Value>...</Value></Property>
</Item>
HTH,
Chandra
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:ekbVtMv$EHA.1188@.tk2msftngp13.phx.gbl...
> You cannot map names of elements into data using the schema mapping. You
> need to use OpenXML for such mappings.
> HTH
> Michael
> "jimmers" <jimmers@.yandex.ru> wrote in message
> news:b0ede647.0501200116.5025661d@.posting.google.com...
>|||Hello Michael and Chandra
First of all, thank you for prompt responses.
Unfortunately I cannot do XSLT transformation because input file size is
huge (~300 Mb): I made test with .NET XslTransform class on 100 Mb XML input
file and simple XSLT file. The program executed approximately 10 minutes and
then out-of-memory exception was thrown. On disk I got incomplete 42 Mb
result file. The server has 512 Mb memory, P4 2.8 GHz CPU and runs under
Windows 2003 Server Standard Edition.
Right now I stick with the following solution: console application reads
elements from input file with help of XmlTextReader class until size
threshold is reached and saves them in temporary files. Then each resulting
file contents is passed to stored procedure that utilized OPENXML. In other
words, I had to duplicate SQLXMLBulkLoad functionality in Transactional mode
and then use OPENXML feature.
By the way, what is the meaning of HTH signature?
Thank you
Martin Rakhmanov
jimmers@.yandex.ru
"Chandra Kalyanaraman [MSFT]" wrote:

> No, not this exact data file can be mapped using XSD schema. Use XSLT to
> transform this data file to look something like, .
> <?xml version="1.0" encoding="UTF-8"?>
> <root>
> <Item>
> <ItemID>1</ItemID>
> <Property><Name>Property1</Name><Value>...</Value></Property>
> <Property><Name>Property2</Name><Value>...</Value></Property>
> <Property><Name>Property3</Name><Value>...</Value></Property>
> </Item>
> HTH,
> Chandra
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:ekbVtMv$EHA.1188@.tk2msftngp13.phx.gbl...
>
>|||Hi Martin.
300MB XML file: What on earth do you have in that? :-) XML files should be
kept as small as possible and not be used as a "database replacement". :-)
HTH means: Hope This Helps
Best regards
Michael
"jimmers" <jimmers@.discussions.microsoft.com> wrote in message
news:214EF7CD-F4BB-4E5A-AD6C-1A57CFA00C12@.microsoft.com...
> Hello Michael and Chandra
> First of all, thank you for prompt responses.
> Unfortunately I cannot do XSLT transformation because input file size is
> huge (~300 Mb): I made test with .NET XslTransform class on 100 Mb XML
> input
> file and simple XSLT file. The program executed approximately 10 minutes
> and
> then out-of-memory exception was thrown. On disk I got incomplete 42 Mb
> result file. The server has 512 Mb memory, P4 2.8 GHz CPU and runs under
> Windows 2003 Server Standard Edition.
> Right now I stick with the following solution: console application reads
> elements from input file with help of XmlTextReader class until size
> threshold is reached and saves them in temporary files. Then each
> resulting
> file contents is passed to stored procedure that utilized OPENXML. In
> other
> words, I had to duplicate SQLXMLBulkLoad functionality in Transactional
> mode
> and then use OPENXML feature.
> By the way, what is the meaning of HTH signature?
> Thank you
> Martin Rakhmanov
> jimmers@.yandex.ru
>
> "Chandra Kalyanaraman [MSFT]" wrote:
>|||300 Mb XML file is maximum size, normally it will be about 100 Mb. It is
generated by external system and I cannot affect this unfortunately.
Thank you
Martin
"Michael Rys [MSFT]" wrote:

> Hi Martin.
> 300MB XML file: What on earth do you have in that? :-) XML files should be
> kept as small as possible and not be used as a "database replacement". :-)
> HTH means: Hope This Helps
> Best regards
> Michael
> "jimmers" <jimmers@.discussions.microsoft.com> wrote in message
> news:214EF7CD-F4BB-4E5A-AD6C-1A57CFA00C12@.microsoft.com...
>
>sql

Is it possible to load such XML file using SQLXML BulkLoad?

Hello
I have an XML file:
<?xml version="1.0" encoding="UTF-8"?>
<root>
<Item>
<ItemID>1</ItemID>
<Property1>...</Property1>
<Property2>...</Property2>
<Property3>...</Property3>
</Item>
<Item>
<ItemID>2</ItemID>
<Property1>...</Property1>
<Property2>...</Property2>
<Property3>...</Property3>
<Property4>...</Property3>
...
</Item>
...
</root>
Number of properties for <Item> is not fixed and their names also not
defined (except ItemID). Is it possible to create XSD schema for
importing this into table using SQLXML BulkLoad facility?
Table:
ItemID PropertyName PropertyValue
=================================
1 Property1 ...
1 Property2 ...
1 Property3 ...
2 Property1 ...
2 Property2 ...
2 Property3 ...
2 Property4 ...
...
Thank you
Martin Rakhmanov
jimmers@.yandex.ru
You cannot map names of elements into data using the schema mapping. You
need to use OpenXML for such mappings.
HTH
Michael
"jimmers" <jimmers@.yandex.ru> wrote in message
news:b0ede647.0501200116.5025661d@.posting.google.c om...
> Hello
> I have an XML file:
> <?xml version="1.0" encoding="UTF-8"?>
> <root>
> <Item>
> <ItemID>1</ItemID>
> <Property1>...</Property1>
> <Property2>...</Property2>
> <Property3>...</Property3>
> </Item>
> <Item>
> <ItemID>2</ItemID>
> <Property1>...</Property1>
> <Property2>...</Property2>
> <Property3>...</Property3>
> <Property4>...</Property3>
> ...
> </Item>
> ...
> </root>
> Number of properties for <Item> is not fixed and their names also not
> defined (except ItemID). Is it possible to create XSD schema for
> importing this into table using SQLXML BulkLoad facility?
> Table:
> ItemID PropertyName PropertyValue
> =================================
> 1 Property1 ...
> 1 Property2 ...
> 1 Property3 ...
> 2 Property1 ...
> 2 Property2 ...
> 2 Property3 ...
> 2 Property4 ...
> ...
>
> Thank you
> Martin Rakhmanov
> jimmers@.yandex.ru
|||No, not this exact data file can be mapped using XSD schema. Use XSLT to
transform this data file to look something like, .
<?xml version="1.0" encoding="UTF-8"?>
<root>
<Item>
<ItemID>1</ItemID>
<Property><Name>Property1</Name><Value>...</Value></Property>
<Property><Name>Property2</Name><Value>...</Value></Property>
<Property><Name>Property3</Name><Value>...</Value></Property>
</Item>
HTH,
Chandra
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:ekbVtMv$EHA.1188@.tk2msftngp13.phx.gbl...
> You cannot map names of elements into data using the schema mapping. You
> need to use OpenXML for such mappings.
> HTH
> Michael
> "jimmers" <jimmers@.yandex.ru> wrote in message
> news:b0ede647.0501200116.5025661d@.posting.google.c om...
>
|||Hello Michael and Chandra
First of all, thank you for prompt responses.
Unfortunately I cannot do XSLT transformation because input file size is
huge (~300 Mb): I made test with .NET XslTransform class on 100 Mb XML input
file and simple XSLT file. The program executed approximately 10 minutes and
then out-of-memory exception was thrown. On disk I got incomplete 42 Mb
result file. The server has 512 Mb memory, P4 2.8 GHz CPU and runs under
Windows 2003 Server Standard Edition.
Right now I stick with the following solution: console application reads
elements from input file with help of XmlTextReader class until size
threshold is reached and saves them in temporary files. Then each resulting
file contents is passed to stored procedure that utilized OPENXML. In other
words, I had to duplicate SQLXMLBulkLoad functionality in Transactional mode
and then use OPENXML feature.
By the way, what is the meaning of HTH signature?
Thank you
Martin Rakhmanov
jimmers@.yandex.ru
"Chandra Kalyanaraman [MSFT]" wrote:

> No, not this exact data file can be mapped using XSD schema. Use XSLT to
> transform this data file to look something like, .
> <?xml version="1.0" encoding="UTF-8"?>
> <root>
> <Item>
> <ItemID>1</ItemID>
> <Property><Name>Property1</Name><Value>...</Value></Property>
> <Property><Name>Property2</Name><Value>...</Value></Property>
> <Property><Name>Property3</Name><Value>...</Value></Property>
> </Item>
> HTH,
> Chandra
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:ekbVtMv$EHA.1188@.tk2msftngp13.phx.gbl...
>
>
|||Hi Martin.
300MB XML file: What on earth do you have in that? :-) XML files should be
kept as small as possible and not be used as a "database replacement". :-)
HTH means: Hope This Helps
Best regards
Michael
"jimmers" <jimmers@.discussions.microsoft.com> wrote in message
news:214EF7CD-F4BB-4E5A-AD6C-1A57CFA00C12@.microsoft.com...[vbcol=seagreen]
> Hello Michael and Chandra
> First of all, thank you for prompt responses.
> Unfortunately I cannot do XSLT transformation because input file size is
> huge (~300 Mb): I made test with .NET XslTransform class on 100 Mb XML
> input
> file and simple XSLT file. The program executed approximately 10 minutes
> and
> then out-of-memory exception was thrown. On disk I got incomplete 42 Mb
> result file. The server has 512 Mb memory, P4 2.8 GHz CPU and runs under
> Windows 2003 Server Standard Edition.
> Right now I stick with the following solution: console application reads
> elements from input file with help of XmlTextReader class until size
> threshold is reached and saves them in temporary files. Then each
> resulting
> file contents is passed to stored procedure that utilized OPENXML. In
> other
> words, I had to duplicate SQLXMLBulkLoad functionality in Transactional
> mode
> and then use OPENXML feature.
> By the way, what is the meaning of HTH signature?
> Thank you
> Martin Rakhmanov
> jimmers@.yandex.ru
>
> "Chandra Kalyanaraman [MSFT]" wrote:
|||300 Mb XML file is maximum size, normally it will be about 100 Mb. It is
generated by external system and I cannot affect this unfortunately.
Thank you
Martin
"Michael Rys [MSFT]" wrote:

> Hi Martin.
> 300MB XML file: What on earth do you have in that? :-) XML files should be
> kept as small as possible and not be used as a "database replacement". :-)
> HTH means: Hope This Helps
> Best regards
> Michael
> "jimmers" <jimmers@.discussions.microsoft.com> wrote in message
> news:214EF7CD-F4BB-4E5A-AD6C-1A57CFA00C12@.microsoft.com...
>
>

Is it possible to link via ADODB from an Access 2K .mdb file?

Hi all,
I am a newbie to SQL server and I am trying to link via ADODB from an Access
2000 .mdb file in Visual Basic to SQL server but I receive an error during
compilation at the "Dim rs As ADODB.Recordset" statement already.
It works if I do the same from an Access project file.
I assume this is not possible and I need to connect via DAO.
Does this also mean that I do not have the option to lock records at all if
I work
with a .mdb file?
Please help - I am puzzled.
Thanks.
Oliver
Let me give this a try, assuming I understand your scenario correctly.
You have an Access .mdb front-end that you wish to link
programmatically to a SQL Server database. If that is correct, then
you can create the link using a DAO.TableDef, not a recordset. You set
the properties of the TableDef, which include the connection string,
name, etc. The linked table is a Jet object, and DAO is always the
best choice when working with Jet objects. If you wish to create a
recordset based on SQL Server data, then use an ADO recordset. To
summarize: Jet=DAO, SQL Server=ADO.
--Mary
On Fri, 3 Feb 2006 07:41:57 -0800, Oliver <iron@.programmer.com> wrote:

>Hi all,
>I am a newbie to SQL server and I am trying to link via ADODB from an Access
>2000 .mdb file in Visual Basic to SQL server but I receive an error during
>compilation at the "Dim rs As ADODB.Recordset" statement already.
>It works if I do the same from an Access project file.
>I assume this is not possible and I need to connect via DAO.
>Does this also mean that I do not have the option to lock records at all if
>I work
>with a .mdb file?
>Please help - I am puzzled.
>Thanks.
>Oliver
|||Thanks Mary, but is it possible to lock records on SQL server with DAO?
If not I will have to convert my .mdb into a project as I think ADO is only
possible if the Access client application is a project file (.adp extension).
I do not like to do this because I then have about 850 Queries that do not
work anymore! I would then need to convert all queries into stored procedures
and views - is that correct or is there a way around it?
Thanks.
Oliver
"Mary Chipman [MSFT]" wrote:

> Let me give this a try, assuming I understand your scenario correctly.
> You have an Access .mdb front-end that you wish to link
> programmatically to a SQL Server database. If that is correct, then
> you can create the link using a DAO.TableDef, not a recordset. You set
> the properties of the TableDef, which include the connection string,
> name, etc. The linked table is a Jet object, and DAO is always the
> best choice when working with Jet objects. If you wish to create a
> recordset based on SQL Server data, then use an ADO recordset. To
> summarize: Jet=DAO, SQL Server=ADO.
> --Mary
> On Fri, 3 Feb 2006 07:41:57 -0800, Oliver <iron@.programmer.com> wrote:
>
|||Locking records on SQL Server from any client is a BIG mistake. SQLS
is very efficient at holding locks for the minimum amount of time
required. Locking records on the client for long periods of time
causes blocking and deadlocks (scenario--user runs code that locks
records, goes to lunch, leaving records locked). Another process
cannot even SEE the data if you are using the default READ COMMITTED
isolation level (see SQL Books Online for more info).
You should use other methods to control concurrency violations, such
as designing table schema to partition tables so that users don't
access the same record at the same time, using timestamps to detect
concurrency problems, or creating a column in the table that
increments each time a record is updated (you check this value in your
code prior to updating and increment during the update). If you care
about efficiency and network traffic, don't use DAO. Using ADPs will
provide no benefits in your situation--rewriting your DAO as ADO will
be less work. Also, don't use any kind of recordset to update data
unless you are trying to slow your application down. Use UPDATE
statements instead.
--Mary
On Sat, 4 Feb 2006 10:50:11 -0800, Oliver <iron@.programmer.com> wrote:
[vbcol=seagreen]
>Thanks Mary, but is it possible to lock records on SQL server with DAO?
>If not I will have to convert my .mdb into a project as I think ADO is only
>possible if the Access client application is a project file (.adp extension).
>I do not like to do this because I then have about 850 Queries that do not
>work anymore! I would then need to convert all queries into stored procedures
>and views - is that correct or is there a way around it?
>Thanks.
>Oliver
>"Mary Chipman [MSFT]" wrote:
|||Hi Mary, thanks for the tips.
I just thought that it is too much work to convert all the DAO code and all
of the 600 queries that did not convert with the upsizing wizard. The views
are mostly not updateable after upsizing - it seems I will have to rewrite
the whole system and I think Microsoft should have left it to us programmers
to decide if we want to rewrite it all by just allowing record locking in DAO
ODBC links. I spent a whole day yesterday trying out if DAO allows record
locks but it does not (they could at least have mentioned this in the help
system).
After having tried this out I think you are right - there is not other way
than to convert all code into ADO in one go. You mentioned that I should use
UPDATEs instead of recordset updates - do you mean I should use ADO commands
executed from visual basic or should I write update procedures on the server
and call those stored procedures from the visual basic?
Thanks.
Oliver
"Mary Chipman [MSFT]" wrote:

> Locking records on SQL Server from any client is a BIG mistake. SQLS
> is very efficient at holding locks for the minimum amount of time
> required. Locking records on the client for long periods of time
> causes blocking and deadlocks (scenario--user runs code that locks
> records, goes to lunch, leaving records locked). Another process
> cannot even SEE the data if you are using the default READ COMMITTED
> isolation level (see SQL Books Online for more info).
> You should use other methods to control concurrency violations, such
> as designing table schema to partition tables so that users don't
> access the same record at the same time, using timestamps to detect
> concurrency problems, or creating a column in the table that
> increments each time a record is updated (you check this value in your
> code prior to updating and increment during the update). If you care
> about efficiency and network traffic, don't use DAO. Using ADPs will
> provide no benefits in your situation--rewriting your DAO as ADO will
> be less work. Also, don't use any kind of recordset to update data
> unless you are trying to slow your application down. Use UPDATE
> statements instead.
> --Mary
> On Sat, 4 Feb 2006 10:50:11 -0800, Oliver <iron@.programmer.com> wrote:
>
|||I think the reason you may have had trouble discovering how DAO works
with SQL Server in the help files is that there is an assumption that
you will use it only with Jet. It is not intended to work with SQL
Server, so nobody thought to document it. However, you can still use
DAO to execute pass-through queries, which are quite efficient. You
can use existing QueryDef objects and set the .SQL property in DAO
code to a SQL statement or to execute a stored procedure. Or you can
create dynamic pass-through queries that are not persisted in the mdb.
The syntax you use in the .SQL property is T-SQL, not Access SQL. The
reason they are called pass-through queries is that the SQL is not
parsed by Access--it is sent directly to the server. You can also use
ADO commands to execute SQL statements or parameterized stored
procedures. HTH,
--Mary
On Wed, 8 Feb 2006 01:13:27 -0800, Oliver <iron@.programmer.com> wrote:
[vbcol=seagreen]
>Hi Mary, thanks for the tips.
>I just thought that it is too much work to convert all the DAO code and all
>of the 600 queries that did not convert with the upsizing wizard. The views
>are mostly not updateable after upsizing - it seems I will have to rewrite
>the whole system and I think Microsoft should have left it to us programmers
>to decide if we want to rewrite it all by just allowing record locking in DAO
>ODBC links. I spent a whole day yesterday trying out if DAO allows record
>locks but it does not (they could at least have mentioned this in the help
>system).
>After having tried this out I think you are right - there is not other way
>than to convert all code into ADO in one go. You mentioned that I should use
>UPDATEs instead of recordset updates - do you mean I should use ADO commands
>executed from visual basic or should I write update procedures on the server
>and call those stored procedures from the visual basic?
>Thanks.
>Oliver
>"Mary Chipman [MSFT]" wrote:
|||Thanks Mary, in the meantime I found a good link to an old documentation
about the use of ODBCDirect,
http://msdn.microsoft.com/archive/de...l/web/001.asp.
This gives me even the option of pessimistic record locking (I need this
sometimes). I already tried to convert everything into ADO but this is an
endless job with the amount of code and queries I have (I gave up!). Now I
can program new queries as stored procedures and views on the server but
still keep the old queries in Access functional. If a query is too slow I
just convert it as needed. This is a much better way of migration into SQL
server.
Oliver
"Mary Chipman [MSFT]" wrote:

> I think the reason you may have had trouble discovering how DAO works
> with SQL Server in the help files is that there is an assumption that
> you will use it only with Jet. It is not intended to work with SQL
> Server, so nobody thought to document it. However, you can still use
> DAO to execute pass-through queries, which are quite efficient. You
> can use existing QueryDef objects and set the .SQL property in DAO
> code to a SQL statement or to execute a stored procedure. Or you can
> create dynamic pass-through queries that are not persisted in the mdb.
> The syntax you use in the .SQL property is T-SQL, not Access SQL. The
> reason they are called pass-through queries is that the SQL is not
> parsed by Access--it is sent directly to the server. You can also use
> ADO commands to execute SQL statements or parameterized stored
> procedures. HTH,
> --Mary
> On Wed, 8 Feb 2006 01:13:27 -0800, Oliver <iron@.programmer.com> wrote:
>

Is it possible to insert a PDF File into a SQL 2k Table?

Hello,
I want to develop a small PDF Management System for our Web Insurance
Systems and Im wondering if I can use SQL Server to save my generated PDF
Documents. Is it possible? If so is it suggested? Are there any other
alternatives?
Jorge Luzarraga C
Fidens S.A.
321 7610 Anx 23
"I can do it quick. I can do it cheap. I can do it well. Pick any two."Jorge Luzarraga Castro wrote:
> Hello,
> I want to develop a small PDF Management System for our Web Insurance
> Systems and Im wondering if I can use SQL Server to save my generated PDF
> Documents. Is it possible? If so is it suggested? Are there any other
> alternatives?
>
It would be better to store the filenames to the PDF files in the
database. Otherwise, the database could become too large, or if the
database was to be corrupted, all pdf-files could be lost.
Steven|||> Is it possible?
Yes.

> If so is it suggested?
Typically, no.
http://www.aspfaq.com/2149

> Are there any other alternatives?
Yes, store the files in the filesystem, and their paths and other
information about them in the database.
A

Is it possible to have the .rds file in reports folder

Since i have 5 different projects all has the same kind of reports., but calling via 5 different sites all sites on same webserver.

The problem i have is the .rds file name is same in all 5 report projects and it is a shared datasource., now when i compile the report project it will not load or overwrite the .rds file since the name is same, for that reason i tried to have the .rds file in reports folder as a datasource not a shared datasource. but i tried to add it, after i create the datasource, it is automatically going into the shared datasource folder, how can i create a datasource not shared under reports folder.

Thank you very much for all your help and information.

Hello,

From the Project menu, select 'Properties'. In the TargetDataSourceFolder, type the path of where you want the data source to go. From Books Online:

TargetDataSourceFolder

Type the name of the destination folder for publishing the shared data sources that are contained within the project. This value is optional. If you do not specify a folder, the data source is published to the same folder as the report. If the folder does not exist on the report server, Report Designer creates the folder when the reports are published. If a folder is located within another folder, include the path to the folder, starting at the root, for example, Folder1/Folder2/Folder3.

Hope this helps.

Jarret

|||Thank you very much Jarret.....

Is it possible to have SSIS flatten XML data?

Hi,

I have to take a hierarchical XML file and store it into a table.

I'm looking at the XML Source and it shows me all of the various XML tags as separate tables. I know that a lot (if not all) of the data can be denormalized so that I have flattened records in one table instead of multiple tables.

I was hoping the XML source would allow me to designate how to denormalize the data but it doesn't seem like I can without using a bunch of sorts and merges in the data flow to denormalize it which I'm trying to avoid.

Does anyone know if SSIS has any easy way to flatten XML data? I know about XSL transforms but I was hoping that there might be an easier method.

Thanks,

-dhideal

The pivot transform wil denormalise data so perhaps that could help you in some way. Fundamentally though you can't stop the XML source from producing multiple outputs because that is what it does - converts input into something that SSIS understands.

-Jamie

|||Couple of ways to flatten XML in SSIS. First off, flattening it using the stock SSIS source adapter is quite arduous, because of all the sort/merges. The more hierarchical, the worse off the the morass becomes. It rapidly becomes unmaintainable.

4 ways to do this, with various degree of difficulty (a fifth non SSIS solution thrown in for good measure)

1. XSLT, as you mentioned. Not too bad, dependency on your profienciency (and/or antipathy) for XSL.

2. Build a custom source adapter and use .NET 2.0 XPathExpressions to load the pipeline.

3. Build a custom source adapter based on an XmlSerializer:
To do so, get the XSD, convert the XSD to an XmlSerializer calss with xsd.exe as follows
xsd sample_schema.xsd /classes /language:vb

4. Build a custom source adapter based on a dataset
xsd sample_schema.xsd /dataset /language:vb

5. Load it into SQL server and use XQuery (not an SSIS solution)|||

jaegd,

Thanks for the pointers.

It seems though that I'm not understanding something because I looked up the items you mentioned and it doesn't seem like they accomplish what I want. The following is my understanding of things (which is most likely incorrect):

1) XSLT requires the use of XPath to identify the various tags to be processed and the structure of the XSL document will take care of the flattening.

2) Based on some information I found, XPathExpressions are just compiled XPath statements. I don't understand how this would flatten the data. It seems I would have to have some custom code in the source adapter to take the results of the XPathExpressions searches and join them together to flatten the data.

3) Based on what I read, it seems like I would have to deserialize the XML data in order to be able to access it. The deserialization would then restore the original hierarchichal structure so I'm not quite sure I understand how this would flatten the data. It seems like if I could use the serialized version fo the data it might get me what I need.

4) Based on what I read, the dataset would be comprised of datatables and their relationships. It seems like I would then have to identify the relationships in the custom code to identify how the tables should be linked together and then denormalize the data by joining them together in the source adapter. I'm not quite sure how this would be different from using suggestion 5 except for the benefit of not having to load the data into SQL Server first.

5) We're trying to avoid this particular solution for now if we can.

Once again, thanks for the response and the pointers. I'm sure I have misunderstood some of the items as I have not really worked much with XML data prior to this.

-dhideal

|||It seems you understand it quite well. To flatten, though must join and/or aggregate.

Flattening XML is custom, and there is no "push to flatten" task or component in SSIS, for lack of better term.

Now, I have tested all five of these approaches, and used two of them

(XPath,XmlSerializer, leaving out XQuery for the moment). They all

require you to essentially do XML "joins" via custom code, whether that

code is a set of XPath expressions in a custom adapter, a set of

property "gets" in in a custom adapter.

Had Microsoft shipped a client side XQuery implementation, I would have

recommended that, since XQuery allows you to do XML joins, which is

basically what you're looking for.sql

Is it possible to have SSIS flatten XML data?

Hi,

I have to take a hierarchical XML file and store it into a table.

I'm looking at the XML Source and it shows me all of the various XML tags as separate tables. I know that a lot (if not all) of the data can be denormalized so that I have flattened records in one table instead of multiple tables.

I was hoping the XML source would allow me to designate how to denormalize the data but it doesn't seem like I can without using a bunch of sorts and merges in the data flow to denormalize it which I'm trying to avoid.

Does anyone know if SSIS has any easy way to flatten XML data? I know about XSL transforms but I was hoping that there might be an easier method.

Thanks,

-dhideal

The pivot transform wil denormalise data so perhaps that could help you in some way. Fundamentally though you can't stop the XML source from producing multiple outputs because that is what it does - converts input into something that SSIS understands.

-Jamie

|||Couple of ways to flatten XML in SSIS. First off, flattening it using the stock SSIS source adapter is quite arduous, because of all the sort/merges. The more hierarchical, the worse off the the morass becomes. It rapidly becomes unmaintainable.

4 ways to do this, with various degree of difficulty (a fifth non SSIS solution thrown in for good measure)

1. XSLT, as you mentioned. Not too bad, dependency on your profienciency (and/or antipathy) for XSL.

2. Build a custom source adapter and use .NET 2.0 XPathExpressions to load the pipeline.

3. Build a custom source adapter based on an XmlSerializer:
To do so, get the XSD, convert the XSD to an XmlSerializer calss with xsd.exe as follows
xsd sample_schema.xsd /classes /language:vb

4. Build a custom source adapter based on a dataset
xsd sample_schema.xsd /dataset /language:vb

5. Load it into SQL server and use XQuery (not an SSIS solution)|||

jaegd,

Thanks for the pointers.

It seems though that I'm not understanding something because I looked up the items you mentioned and it doesn't seem like they accomplish what I want. The following is my understanding of things (which is most likely incorrect):

1) XSLT requires the use of XPath to identify the various tags to be processed and the structure of the XSL document will take care of the flattening.

2) Based on some information I found, XPathExpressions are just compiled XPath statements. I don't understand how this would flatten the data. It seems I would have to have some custom code in the source adapter to take the results of the XPathExpressions searches and join them together to flatten the data.

3) Based on what I read, it seems like I would have to deserialize the XML data in order to be able to access it. The deserialization would then restore the original hierarchichal structure so I'm not quite sure I understand how this would flatten the data. It seems like if I could use the serialized version fo the data it might get me what I need.

4) Based on what I read, the dataset would be comprised of datatables and their relationships. It seems like I would then have to identify the relationships in the custom code to identify how the tables should be linked together and then denormalize the data by joining them together in the source adapter. I'm not quite sure how this would be different from using suggestion 5 except for the benefit of not having to load the data into SQL Server first.

5) We're trying to avoid this particular solution for now if we can.

Once again, thanks for the response and the pointers. I'm sure I have misunderstood some of the items as I have not really worked much with XML data prior to this.

-dhideal

|||It seems you understand it quite well. To flatten, though must join and/or aggregate.

Flattening XML is custom, and there is no "push to flatten" task or component in SSIS, for lack of better term.

Now, I have tested all five of these approaches, and used two of them

(XPath,XmlSerializer, leaving out XQuery for the moment). They all

require you to essentially do XML "joins" via custom code, whether that

code is a set of XPath expressions in a custom adapter, a set of

property "gets" in in a custom adapter.

Had Microsoft shipped a client side XQuery implementation, I would have

recommended that, since XQuery allows you to do XML joins, which is

basically what you're looking for.

Is it possible to get the .rdl file back from the report server

Hi All
We have uploaded the .rdl file to the report server and some how our dev
team has lost the original .rdl file, is it possible to get back the version
currently uploaded on to the report server.
Please advice
ThanksHi Rahul,
Yes, you can get the RDL from report server.
Goto Report Manager, select Report and then click Properties - In that page
click the Edit button in the Report Definiton area.
Sam
"Rahul Agarwal" <agarwal_rahul@.hotmail.com> wrote in message
news:OjOIFSchEHA.904@.TK2MSFTNGP09.phx.gbl...
> Hi All
> We have uploaded the .rdl file to the report server and some how our dev
> team has lost the original .rdl file, is it possible to get back the
version
> currently uploaded on to the report server.
> Please advice
> Thanks
>

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 possible to fetch the update , insert statement from the log file ?

Is it possible to fetch the historic update , insert statement from the log file ?Not without purchasing some 3rd party software. The only one of which I am aware is a product by Lumigent called (I think) Log Explorer. I have no experience with it, but they have some great customer reviews.

Regards,

Hugh Scott

Originally posted by ligang
Is it possible to fetch the historic update , insert statement from the log file ?

Is it possible to execute DTS in SP?

Hi, i have a DTS then generate a CSV file, is it possible to execute the DTS
within a stored procedure?
Moreover, is it possible to dynamic change the current database in SQL Query
Analyzer by execute a Transact-SQL? As i know,
Use DB1 <== this command can change the current database
but is it possible to do the following:
declare @.dbName as char(255)
set @.dbName = 'DB2'
use @.dbName <== currently, this is not valid
Many thanks
MartinIndirectly yes,
You can execute DTS from a console window meaning it's command based. Now
SQL also have xp_cmdshell to execute commands in console.
So if you combine xp_cmdshell and tell it to execute dtsrun you should get
it working.
Have a look at the dtsrun, and xp_cmdshell in books online(sql help)
Hope it's a pointer in the right direction.
"Atenza" wrote:

> Hi, i have a DTS then generate a CSV file, is it possible to execute the D
TS
> within a stored procedure?
> Moreover, is it possible to dynamic change the current database in SQL Que
ry
> Analyzer by execute a Transact-SQL? As i know,
> Use DB1 <== this command can change the current database
> but is it possible to do the following:
> declare @.dbName as char(255)
> set @.dbName = 'DB2'
> use @.dbName <== currently, this is not valid
>
> Many thanks
> Martin
>
>|||> is it possible to execute the DTS
> within a stored procedure?
http://www.sqldts.com/default.aspx?210

> is it possible to dynamic change the current database in SQL Query
> Analyzer by execute a Transact-SQL?
It's easier to do this in DTS with a parameterized SQL task or Dynamic
Properties task. In Transact SQL you would have to use Dynamic SQL (not
recommended) and wrap all your code in an EXEC statatement.
David Portas
SQL Server MVP
--|||Thanks!!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:A287DCD5-FC0C-48E6-8C6F-0A362941D6AD@.microsoft.com...
> http://www.sqldts.com/default.aspx?210
>
> It's easier to do this in DTS with a parameterized SQL task or Dynamic
> Properties task. In Transact SQL you would have to use Dynamic SQL (not
> recommended) and wrap all your code in an EXEC statatement.
> --
> David Portas
> SQL Server MVP
> --
>|||Thanks!!
"Mal .mullerjannie@.hotmail.com>" <<removethis> wrote in message
news:469E6F3C-B4A6-41B0-A041-1F43E6142197@.microsoft.com...
> Indirectly yes,
> You can execute DTS from a console window meaning it's command based. Now
> SQL also have xp_cmdshell to execute commands in console.
> So if you combine xp_cmdshell and tell it to execute dtsrun you should get
> it working.
> Have a look at the dtsrun, and xp_cmdshell in books online(sql help)
> Hope it's a pointer in the right direction.
> "Atenza" wrote:
>
DTS
Query

Is it possible to execute a container regardless of the checkpoint file?

I have a situation where I need to make sure a task executes regardless of whether the package starts from a checkpoint or not. Is this possible?

Here's the scenario:

I have a package with 3 tasks {TaskA, TaskB, TaskC} that execute serially using OnSuccess precedence constraints. The package is setup to use checkpoints so that if a task fails the package will restart from that failed task TaskA is insignificant here. TaskB fetches some data and puts it in a raw file TaskC inserts that raw file data into a table.

Problem is that the insertion violates an integrity constraint in the database - so it fails. The problem is easily fixed but it needs to be fixed in TaskB because that is where the data is sourced.

So, I need to be able to execute TaskB again, even though it was TaskC that failed. Currently the package restarts from TaskC which reuses the raw file (which has the bad data in it) so the package continues to fail even though the cause of the problem has been fixed.

How do I configure the package in order to execute TaskB again? Is it even possible?

Regards

-Jamie

How about controlling when the checkpoint files get created, and make sure you only do that when you don't have that kind of dependence, i.e. make the task creating the raw file NOT create a checkpoint after it finishes successfully?

/Kristian

|||

That sounds like the right sort of approach but I'm not sure if its possible.

Hoping someone from the dev team replies.

-Jamie

|||

Since the FailPackageOnFailure on the Task needs to be set for the failed task to result in a checkpoint file, I'd play with:

* Don't set FailPackageOnFailure on your last task that uses the raw file.

* Do set FailParentOnFailure on that task to have your higher level sequence container fail, and set FailPackageOnFailure on that sequence container, to have the sequence container generate a checkpoint file, which hopefully will restart from the beginning of the sequence container.

All totally untested, I didn't want to take the fun away ;-)

Cheers/Kristian

|||

I've got a vague recollection of trying to do this once using Sequence containers but couldn't get it to work. In theory it should, but I don't know how to.

Anyone? Anyone?

-Jamie

|||

Can anyone help wih this?

-Jamie

This request comes up more and more. I'm 99% sure it can't be done but wanted confirmation. If it can't be done, consider it a feature request.

|||

In the case where you have tasks B and C, with a precedence constraint from B to C, you can configure your package to restart at B after C fails the package by wrapping B and C in a For Loop Container that contains B->C and iterates over B->C just once. So in your example, you would change:

TaskA -> TaskB -> TaskC

to

TaskA -> For Loop Container[ TaskB->TaskC ]

Then, you'll need to set up your package so that the container executes just once, and the tasks inside the container don't fail the whole package, but instead just fail their container, thereby causing the checkpoint restart to start at the container. In this example, you'd define a variable "i", and make the following property changes:

For Loop Container: FailPackageOnFailure = true, InitExpression="@.i=0", EvalExpression="@.i<1", AssignExpression="@.i=@.i+1"

TaskB: FailPackageOnFailure=false, FailParentOnFailure=true

TaskC: FailPackageOnFailure=false, FailParentOnFailure=true

The For Loop is necessary only because checkpoint restart on a sequence container doesn't currently cause the sequence container's tasks to execute. I've filed an internal bug on this issue, but for now the For Loop approach should work well.

|||

David,

That is a very very good idea. Thank you.

And thank you for filing internally. Do you really think its a bug? I don't think it is. Currently I think it works "as designed" but I happen to think the design is wrong.

Regards

-Jamie

I previously marked David's response as an answer but I've temporarily unmarked it so that this [Microsoft follow-up] tag filters thru.

Please could someone address my question in this thread.

|||

Sorry Jamie, I must have missed the follow up question.

At least to myself and others on the team, it seems fairly inconsistent to restart a container without restarting its contained task, and the fact that the For loop container behaves differently than the Sequence container in this regard makes it clear that there is something to be addressed.

The team is discussing the issues and inconsistencies around checkpoints now to determine what needs to be corrected and how it should behave.

-David

|||

Excellent. Could you keep us updated?

Thanks David.

-Jamie

|||Sure thing, Jamie.|||

David Noor (Microsoft) wrote:

The team is discussing the issues and inconsistencies around checkpoints now to determine what needs to be corrected and how it should behave.

Hi David,

What was the outcome of this? Is the behaviour going to be changed?

-Jamie

|||

David Noor (Microsoft) wrote:

In the case where you have tasks B and C, with a precedence constraint from B to C, you can configure your package to restart at B after C fails the package by wrapping B and C in a For Loop Container that contains B->C and iterates over B->C just once. So in your example, you would change:

TaskA -> TaskB -> TaskC

to

TaskA -> For Loop Container[ TaskB->TaskC ]

Then, you'll need to set up your package so that the container executes just once, and the tasks inside the container don't fail the whole package, but instead just fail their container, thereby causing the checkpoint restart to start at the container. In this example, you'd define a variable "i", and make the following property changes:

For Loop Container: FailPackageOnFailure = true, InitExpression="@.i=0", EvalExpression="@.i<1", AssignExpression="@.i=@.i+1"

TaskB: FailPackageOnFailure=false, FailParentOnFailure=true

TaskC: FailPackageOnFailure=false, FailParentOnFailure=true

The For Loop is necessary only because checkpoint restart on a sequence container doesn't currently cause the sequence container's tasks to execute. I've filed an internal bug on this issue, but for now the For Loop approach should work well.

All,

I have done a video demoing David's workaround here: http://blogs.conchango.com/jamiethomson/archive/2007/05/11/SSIS-Nugget_3A00_--Ignore-a-checkpoint.aspx

-Jamie

|||Hi Jamie,

There are a number of checkpoint issues that we've collected, and work needs to be done to make them behave consistently in all cases, including this. I can't commit to a date/release, but I'd expect checkpoints to be made more consistent in a future release, and for these issues to be cleared up.

-David|||

David Noor (Microsoft) wrote:

Hi Jamie,

There are a number of checkpoint issues that we've collected, and work needs to be done to make them behave consistently in all cases, including this. I can't commit to a date/release, but I'd expect checkpoints to be made more consistent in a future release, and for these issues to be cleared up.

-David

ARRGHHH! That sounds like "not in Katmai" to me.

Thanks for the reply anyway David.

-Jamie

sql

Is it possible to execute a container regardless of the checkpoint file?

I have a situation where I need to make sure a task executes regardless of whether the package starts from a checkpoint or not. Is this possible?

Here's the scenario:

I have a package with 3 tasks {TaskA, TaskB, TaskC} that execute serially using OnSuccess precedence constraints. The package is setup to use checkpoints so that if a task fails the package will restart from that failed task TaskA is insignificant here. TaskB fetches some data and puts it in a raw file TaskC inserts that raw file data into a table.

Problem is that the insertion violates an integrity constraint in the database - so it fails. The problem is easily fixed but it needs to be fixed in TaskB because that is where the data is sourced.

So, I need to be able to execute TaskB again, even though it was TaskC that failed. Currently the package restarts from TaskC which reuses the raw file (which has the bad data in it) so the package continues to fail even though the cause of the problem has been fixed.

How do I configure the package in order to execute TaskB again? Is it even possible?

Regards

-Jamie

How about controlling when the checkpoint files get created, and make sure you only do that when you don't have that kind of dependence, i.e. make the task creating the raw file NOT create a checkpoint after it finishes successfully?

/Kristian

|||

That sounds like the right sort of approach but I'm not sure if its possible.

Hoping someone from the dev team replies.

-Jamie

|||

Since the FailPackageOnFailure on the Task needs to be set for the failed task to result in a checkpoint file, I'd play with:

* Don't set FailPackageOnFailure on your last task that uses the raw file.

* Do set FailParentOnFailure on that task to have your higher level sequence container fail, and set FailPackageOnFailure on that sequence container, to have the sequence container generate a checkpoint file, which hopefully will restart from the beginning of the sequence container.

All totally untested, I didn't want to take the fun away ;-)

Cheers/Kristian

|||

I've got a vague recollection of trying to do this once using Sequence containers but couldn't get it to work. In theory it should, but I don't know how to.

Anyone? Anyone?

-Jamie

|||

Can anyone help wih this?

-Jamie

This request comes up more and more. I'm 99% sure it can't be done but wanted confirmation. If it can't be done, consider it a feature request.

|||

In the case where you have tasks B and C, with a precedence constraint from B to C, you can configure your package to restart at B after C fails the package by wrapping B and C in a For Loop Container that contains B->C and iterates over B->C just once. So in your example, you would change:

TaskA -> TaskB -> TaskC

to

TaskA -> For Loop Container[ TaskB->TaskC ]

Then, you'll need to set up your package so that the container executes just once, and the tasks inside the container don't fail the whole package, but instead just fail their container, thereby causing the checkpoint restart to start at the container. In this example, you'd define a variable "i", and make the following property changes:

For Loop Container: FailPackageOnFailure = true, InitExpression="@.i=0", EvalExpression="@.i<1", AssignExpression="@.i=@.i+1"

TaskB: FailPackageOnFailure=false, FailParentOnFailure=true

TaskC: FailPackageOnFailure=false, FailParentOnFailure=true

The For Loop is necessary only because checkpoint restart on a sequence container doesn't currently cause the sequence container's tasks to execute. I've filed an internal bug on this issue, but for now the For Loop approach should work well.

|||

David,

That is a very very good idea. Thank you.

And thank you for filing internally. Do you really think its a bug? I don't think it is. Currently I think it works "as designed" but I happen to think the design is wrong.

Regards

-Jamie

I previously marked David's response as an answer but I've temporarily unmarked it so that this [Microsoft follow-up] tag filters thru.

Please could someone address my question in this thread.

|||

Sorry Jamie, I must have missed the follow up question.

At least to myself and others on the team, it seems fairly inconsistent to restart a container without restarting its contained task, and the fact that the For loop container behaves differently than the Sequence container in this regard makes it clear that there is something to be addressed.

The team is discussing the issues and inconsistencies around checkpoints now to determine what needs to be corrected and how it should behave.

-David

|||

Excellent. Could you keep us updated?

Thanks David.

-Jamie

|||Sure thing, Jamie.|||

David Noor (Microsoft) wrote:

The team is discussing the issues and inconsistencies around checkpoints now to determine what needs to be corrected and how it should behave.

Hi David,

What was the outcome of this? Is the behaviour going to be changed?

-Jamie

|||

David Noor (Microsoft) wrote:

In the case where you have tasks B and C, with a precedence constraint from B to C, you can configure your package to restart at B after C fails the package by wrapping B and C in a For Loop Container that contains B->C and iterates over B->C just once. So in your example, you would change:

TaskA -> TaskB -> TaskC

to

TaskA -> For Loop Container[ TaskB->TaskC ]

Then, you'll need to set up your package so that the container executes just once, and the tasks inside the container don't fail the whole package, but instead just fail their container, thereby causing the checkpoint restart to start at the container. In this example, you'd define a variable "i", and make the following property changes:

For Loop Container: FailPackageOnFailure = true, InitExpression="@.i=0", EvalExpression="@.i<1", AssignExpression="@.i=@.i+1"

TaskB: FailPackageOnFailure=false, FailParentOnFailure=true

TaskC: FailPackageOnFailure=false, FailParentOnFailure=true

The For Loop is necessary only because checkpoint restart on a sequence container doesn't currently cause the sequence container's tasks to execute. I've filed an internal bug on this issue, but for now the For Loop approach should work well.

All,

I have done a video demoing David's workaround here: http://blogs.conchango.com/jamiethomson/archive/2007/05/11/SSIS-Nugget_3A00_--Ignore-a-checkpoint.aspx

-Jamie

|||Hi Jamie,

There are a number of checkpoint issues that we've collected, and work needs to be done to make them behave consistently in all cases, including this. I can't commit to a date/release, but I'd expect checkpoints to be made more consistent in a future release, and for these issues to be cleared up.

-David|||

David Noor (Microsoft) wrote:

Hi Jamie,

There are a number of checkpoint issues that we've collected, and work needs to be done to make them behave consistently in all cases, including this. I can't commit to a date/release, but I'd expect checkpoints to be made more consistent in a future release, and for these issues to be cleared up.

-David

ARRGHHH! That sounds like "not in Katmai" to me.

Thanks for the reply anyway David.

-Jamie

Is it possible to execute a container regardless of the checkpoint file?

I have a situation where I need to make sure a task executes regardless of whether the package starts from a checkpoint or not. Is this possible?

Here's the scenario:

I have a package with 3 tasks {TaskA, TaskB, TaskC} that execute serially using OnSuccess precedence constraints. The package is setup to use checkpoints so that if a task fails the package will restart from that failed task TaskA is insignificant here. TaskB fetches some data and puts it in a raw file TaskC inserts that raw file data into a table.

Problem is that the insertion violates an integrity constraint in the database - so it fails. The problem is easily fixed but it needs to be fixed in TaskB because that is where the data is sourced.

So, I need to be able to execute TaskB again, even though it was TaskC that failed. Currently the package restarts from TaskC which reuses the raw file (which has the bad data in it) so the package continues to fail even though the cause of the problem has been fixed.

How do I configure the package in order to execute TaskB again? Is it even possible?

Regards

-Jamie

Hi, i.ve the same problem.
I have a Container with 3 task Task A, Task B and Task C.
If one of the task fail, the container must be restart from the task A. Have you find a solution?

Is it possible to execute a container regardless of the checkpoint file?

I have a situation where I need to make sure a task executes regardless of whether the package starts from a checkpoint or not. Is this possible?

Here's the scenario:

I have a package with 3 tasks {TaskA, TaskB, TaskC} that execute serially using OnSuccess precedence constraints. The package is setup to use checkpoints so that if a task fails the package will restart from that failed task TaskA is insignificant here. TaskB fetches some data and puts it in a raw file TaskC inserts that raw file data into a table.

Problem is that the insertion violates an integrity constraint in the database - so it fails. The problem is easily fixed but it needs to be fixed in TaskB because that is where the data is sourced.

So, I need to be able to execute TaskB again, even though it was TaskC that failed. Currently the package restarts from TaskC which reuses the raw file (which has the bad data in it) so the package continues to fail even though the cause of the problem has been fixed.

How do I configure the package in order to execute TaskB again? Is it even possible?

Regards

-Jamie

How about controlling when the checkpoint files get created, and make sure you only do that when you don't have that kind of dependence, i.e. make the task creating the raw file NOT create a checkpoint after it finishes successfully?

/Kristian

|||

That sounds like the right sort of approach but I'm not sure if its possible.

Hoping someone from the dev team replies.

-Jamie

|||

Since the FailPackageOnFailure on the Task needs to be set for the failed task to result in a checkpoint file, I'd play with:

* Don't set FailPackageOnFailure on your last task that uses the raw file.

* Do set FailParentOnFailure on that task to have your higher level sequence container fail, and set FailPackageOnFailure on that sequence container, to have the sequence container generate a checkpoint file, which hopefully will restart from the beginning of the sequence container.

All totally untested, I didn't want to take the fun away ;-)

Cheers/Kristian

|||

I've got a vague recollection of trying to do this once using Sequence containers but couldn't get it to work. In theory it should, but I don't know how to.

Anyone? Anyone?

-Jamie

|||

Can anyone help wih this?

-Jamie

This request comes up more and more. I'm 99% sure it can't be done but wanted confirmation. If it can't be done, consider it a feature request.

|||

In the case where you have tasks B and C, with a precedence constraint from B to C, you can configure your package to restart at B after C fails the package by wrapping B and C in a For Loop Container that contains B->C and iterates over B->C just once. So in your example, you would change:

TaskA -> TaskB -> TaskC

to

TaskA -> For Loop Container[ TaskB->TaskC ]

Then, you'll need to set up your package so that the container executes just once, and the tasks inside the container don't fail the whole package, but instead just fail their container, thereby causing the checkpoint restart to start at the container. In this example, you'd define a variable "i", and make the following property changes:

For Loop Container: FailPackageOnFailure = true, InitExpression="@.i=0", EvalExpression="@.i<1", AssignExpression="@.i=@.i+1"

TaskB: FailPackageOnFailure=false, FailParentOnFailure=true

TaskC: FailPackageOnFailure=false, FailParentOnFailure=true

The For Loop is necessary only because checkpoint restart on a sequence container doesn't currently cause the sequence container's tasks to execute. I've filed an internal bug on this issue, but for now the For Loop approach should work well.

|||

David,

That is a very very good idea. Thank you.

And thank you for filing internally. Do you really think its a bug? I don't think it is. Currently I think it works "as designed" but I happen to think the design is wrong.

Regards

-Jamie

I previously marked David's response as an answer but I've temporarily unmarked it so that this [Microsoft follow-up] tag filters thru.

Please could someone address my question in this thread.

|||

Sorry Jamie, I must have missed the follow up question.

At least to myself and others on the team, it seems fairly inconsistent to restart a container without restarting its contained task, and the fact that the For loop container behaves differently than the Sequence container in this regard makes it clear that there is something to be addressed.

The team is discussing the issues and inconsistencies around checkpoints now to determine what needs to be corrected and how it should behave.

-David

|||

Excellent. Could you keep us updated?

Thanks David.

-Jamie

|||Sure thing, Jamie.|||

David Noor (Microsoft) wrote:

The team is discussing the issues and inconsistencies around checkpoints now to determine what needs to be corrected and how it should behave.

Hi David,

What was the outcome of this? Is the behaviour going to be changed?

-Jamie

|||

David Noor (Microsoft) wrote:

In the case where you have tasks B and C, with a precedence constraint from B to C, you can configure your package to restart at B after C fails the package by wrapping B and C in a For Loop Container that contains B->C and iterates over B->C just once. So in your example, you would change:

TaskA -> TaskB -> TaskC

to

TaskA -> For Loop Container[ TaskB->TaskC ]

Then, you'll need to set up your package so that the container executes just once, and the tasks inside the container don't fail the whole package, but instead just fail their container, thereby causing the checkpoint restart to start at the container. In this example, you'd define a variable "i", and make the following property changes:

For Loop Container: FailPackageOnFailure = true, InitExpression="@.i=0", EvalExpression="@.i<1", AssignExpression="@.i=@.i+1"

TaskB: FailPackageOnFailure=false, FailParentOnFailure=true

TaskC: FailPackageOnFailure=false, FailParentOnFailure=true

The For Loop is necessary only because checkpoint restart on a sequence container doesn't currently cause the sequence container's tasks to execute. I've filed an internal bug on this issue, but for now the For Loop approach should work well.

All,

I have done a video demoing David's workaround here: http://blogs.conchango.com/jamiethomson/archive/2007/05/11/SSIS-Nugget_3A00_--Ignore-a-checkpoint.aspx

-Jamie

|||Hi Jamie,

There are a number of checkpoint issues that we've collected, and work needs to be done to make them behave consistently in all cases, including this. I can't commit to a date/release, but I'd expect checkpoints to be made more consistent in a future release, and for these issues to be cleared up.

-David|||

David Noor (Microsoft) wrote:

Hi Jamie,

There are a number of checkpoint issues that we've collected, and work needs to be done to make them behave consistently in all cases, including this. I can't commit to a date/release, but I'd expect checkpoints to be made more consistent in a future release, and for these issues to be cleared up.

-David

ARRGHHH! That sounds like "not in Katmai" to me.

Thanks for the reply anyway David.

-Jamie