Running an IS package in SQL Server Agent

Hi,

I have created a package which gets data from SQL server Source to another SQL Server Database and I execute that using SQL Server Agent with the following command:

DTExec /f "C:\Package.dtsx"

According to my logs, I have an error:

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "cdp1.MSCRM" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

From BIDS, it doesn't have any error. I only get this when running from SQL Agent.

The way I get the .dtsx file is that, I just BUILD the project from BIDS and use the .dtsx from \bin\Deployment folder to assign to SQL Job.

Have I done it the correct way? thanks a lot!

cherriesh

What type of connection is cdp1.MSCRM?

|||

Hi,

This solves the problem:

http://support.microsoft.com/kb/912911

I just assigned a credential and proxy account and used that in the jobstep.

cherriesh

Running an IS package in SQL Server Agent

Hi,

I have created a package which gets data from SQL server Source to another SQL Server Database and I execute that using SQL Server Agent with the following command:

DTExec /f "C:\Package.dtsx"

According to my logs, I have an error:

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "cdp1.MSCRM" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

From BIDS, it doesn't have any error. I only get this when running from SQL Agent.

The way I get the .dtsx file is that, I just BUILD the project from BIDS and use the .dtsx from \bin\Deployment folder to assign to SQL Job.

Have I done it the correct way? thanks a lot!

cherriesh

What type of connection is cdp1.MSCRM?

|||

Hi,

This solves the problem:

http://support.microsoft.com/kb/912911

I just assigned a credential and proxy account and used that in the jobstep.

cherriesh

Run Variable in DataReader Source?

Hi all,
I am trying to have a DataReader Source that can run a variable which I used to store the SQL statement. For example, I have:
Variable #1
Variable name: tablename
Data Type: string
Value: "name_of_table"
Variable #2
Variable name: sql_stmt
Data Type: string
Value: "SELECT * FROM " + @.tablename
I want to use DataReader Source to run Variable #2 in the SqlCommand that connects to an ODBC connection. If it is possible by any way, please let me know. Thanks in advance.
Daren
Use a property expression on the SqlCommand of the DataReader Source. Expressions for Data Flow components can be found on the Data Flow task itself. Select the Data Flow and open the VS Properties grid. Then select expressions.|||Hi Darren,
I still do not get how to use Variables in DataReader Source? Can you lead me to an example or show me a simple example on how to get it to work on AdventureWorks database.
Thank you.
Daren
|||

A variable can be used in an expression.

A variable can be an expression in so much as you can set the EvaluateAsExpression property to True, and the Expression property to an expression. The value of the variable will then be the evaluated expression result.

The DataReader source as a SqlCommand property, and that supports expresisons as well, this is called a property expression. If you don't know what this is, please look it up in Books Online. So the SqlCommand can be a literal, or an expression which itself can reference a variable. That variable can be set via EvaluateAsExpression as well.

These topics are well documented in Books Online. Try these for a start-

DataReader Source (http://msdn2.microsoft.com/en-us/library/ms137897(SQL.90).aspx) - "This source includes the SQLCommand custom property. This property can be updated by a property expression when the package is loaded. For more information, see Integration Services Expression Reference, Using Property Expressions in Packages, and Source Custom Properties."

Using Property Expressions to Specify Property Values for Data Flow Objects (http://msdn2.microsoft.com/en-us/library/ms136104.aspx)

|||Ok, thanks Darren. I am gonna try it out to see if it works...
Daren
|||Hi Darren,
Sorry to bother you again, for the following sentence:
So the SqlCommand can be a literal, or an expression which itself can reference a variable. That variable can be set via EvaluateAsExpression as well.
do you mean I can write expression in the SqlCommand? like "Select * from "+@.tablename? as this is how I write in the expression for a variable. If this is not the way to write it, can you show me an example on how to do it? because the online book is not helping much without any example.
Thanks in advance.
Daren
|||

I will tell you how I run expressions on my datareaders. I am by no means stating that the following is the best way, but it's the only way I know of and it works for me. If you have not already found the datareader expression box, which is definitely not placed intuitively given that most other expressions are found on the tasks themselves, do the following:
- Once inside your dataflow task, click on the workarea (to make sure you haven't highlighted anything within the dataflow)
- Look in the properties window (mine found on bottom right)
- Scroll down in your properties to the Expressions box, and click on the ellipses
- Once there, looking at the properties available in the drop down box you will see any datareader.SqlCommand that you currently have in your dataflow. From there click on the corresponding expression ellipses.
- Here is where you can set queries similar to the one you posted above.
Beware the expressions are very picky with data types if you haven't already found that out, so you'll have to use the casting terminology to cast variables to a string if they aren't already. Here's an example for you, where I'm casting variables that are of type DateTime:

"SELECT * FROM DATE_TBL WHERE DATE BETWEEN '" + (DT_WSTR, 30) @.[User::IVR_START_DATETIME] + "' AND '" + (DT_WSTR, 30) @.[User::IVR_END_DATETIME] + "'"

Hope this helps, I know the pain I went through finding out that datareaders could use expressions, especially back when there was little info available.

Adrian

|||

Adrian is right that you have to be wary of data types - its easy to get confused. Always remember that all you are doing when you are building your SQL statement is concatenating strings. So, make sure everything is converted to a string (i.e. DT_STR or DT_WSTR) before trying to concatenate.

Not sure I agree that the expressions interface isn't placed intuitively but I'll bow to Adrian's opinion on that one :)

-Jamie

|||Hi Adrian, sorry for this late reply, I was not around for the past few days doing something else. But thanks for the little guide of yours, it helps me a lot. Also I will take note on the data types there. Thanks again.
Daren
|||

Adrian, I have a similar cast of a DateTime variable to a string, but in attempting to get part of the date (for example, the Year), SSIS returns an invalid cast.

Example code: (DT_WSTR,4)YEAR(@.[User::DateToImport])

This evaluates perfectly in the Expression Builder, but upon running the project, the following errors are returned:

Error: The function "YEAR" does not support the data type "DT_WSTR" for parameter number 1. The type of the parameter could not be implicitly cast into a compatible type for the function. To perform this operation, the operand needs to be explicitly cast with a cast operator.

Error: The expression "(DT_WSTR, 4) YEAR(@.[User::DateToImport])" on property "ConnectionString" cannot be evaluated. Modify the expression to be valid.

So, there's a bit of a disconnect here somewhere...

|||What type is the variable User::DateToImport? The error says to me that it is String (DT_WSTR). It needs to be a DateTime.|||It is very much a DateTime type.|||

Take a read of this: http://blogs.conchango.com/jamiethomson/archive/2005/10/11/2261.aspx

It may be a pointer as to the problem. Basically there is bug that means a datatime variable can hold a value which isn't a datetime.

-Jamie

|||

Thank you Adrian.
I have been looking for this answer for about 2 weeks now. Your post was the only one that i have found that told me that i need to specify my variable in the Expression ellipses. I have been trying unsuccesfully to put my varaible in the [DataReader Source].[SQL Command] property.

Run Variable in DataReader Source?

Hi all,
I am trying to have a DataReader Source that can run a variable which I used to store the SQL statement. For example, I have:
Variable #1
Variable name: tablename
Data Type: string
Value: "name_of_table"
Variable #2
Variable name: sql_stmt
Data Type: string
Value: "SELECT * FROM " + @.tablename
I want to use DataReader Source to run Variable #2 in the SqlCommand that connects to an ODBC connection. If it is possible by any way, please let me know. Thanks in advance.
Daren
Use a property expression on the SqlCommand of the DataReader Source. Expressions for Data Flow components can be found on the Data Flow task itself. Select the Data Flow and open the VS Properties grid. Then select expressions.|||Hi Darren,
I still do not get how to use Variables in DataReader Source? Can you lead me to an example or show me a simple example on how to get it to work on AdventureWorks database.
Thank you.
Daren
|||

A variable can be used in an expression.

A variable can be an expression in so much as you can set the EvaluateAsExpression property to True, and the Expression property to an expression. The value of the variable will then be the evaluated expression result.

The DataReader source as a SqlCommand property, and that supports expresisons as well, this is called a property expression. If you don't know what this is, please look it up in Books Online. So the SqlCommand can be a literal, or an expression which itself can reference a variable. That variable can be set via EvaluateAsExpression as well.

These topics are well documented in Books Online. Try these for a start-

DataReader Source (http://msdn2.microsoft.com/en-us/library/ms137897(SQL.90).aspx) - "This source includes the SQLCommand custom property. This property can be updated by a property expression when the package is loaded. For more information, see Integration Services Expression Reference, Using Property Expressions in Packages, and Source Custom Properties."

Using Property Expressions to Specify Property Values for Data Flow Objects (http://msdn2.microsoft.com/en-us/library/ms136104.aspx)

|||Ok, thanks Darren. I am gonna try it out to see if it works...
Daren
|||Hi Darren,
Sorry to bother you again, for the following sentence:
So the SqlCommand can be a literal, or an expression which itself can reference a variable. That variable can be set via EvaluateAsExpression as well.
do you mean I can write expression in the SqlCommand? like "Select * from "+@.tablename? as this is how I write in the expression for a variable. If this is not the way to write it, can you show me an example on how to do it? because the online book is not helping much without any example.
Thanks in advance.
Daren
|||

I will tell you how I run expressions on my datareaders. I am by no means stating that the following is the best way, but it's the only way I know of and it works for me. If you have not already found the datareader expression box, which is definitely not placed intuitively given that most other expressions are found on the tasks themselves, do the following:
- Once inside your dataflow task, click on the workarea (to make sure you haven't highlighted anything within the dataflow)
- Look in the properties window (mine found on bottom right)
- Scroll down in your properties to the Expressions box, and click on the ellipses
- Once there, looking at the properties available in the drop down box you will see any datareader.SqlCommand that you currently have in your dataflow. From there click on the corresponding expression ellipses.
- Here is where you can set queries similar to the one you posted above.
Beware the expressions are very picky with data types if you haven't already found that out, so you'll have to use the casting terminology to cast variables to a string if they aren't already. Here's an example for you, where I'm casting variables that are of type DateTime:

"SELECT * FROM DATE_TBL WHERE DATE BETWEEN '" + (DT_WSTR, 30) @.[User::IVR_START_DATETIME] + "' AND '" + (DT_WSTR, 30) @.[User::IVR_END_DATETIME] + "'"

Hope this helps, I know the pain I went through finding out that datareaders could use expressions, especially back when there was little info available.

Adrian

|||

Adrian is right that you have to be wary of data types - its easy to get confused. Always remember that all you are doing when you are building your SQL statement is concatenating strings. So, make sure everything is converted to a string (i.e. DT_STR or DT_WSTR) before trying to concatenate.

Not sure I agree that the expressions interface isn't placed intuitively but I'll bow to Adrian's opinion on that one :)

-Jamie

|||Hi Adrian, sorry for this late reply, I was not around for the past few days doing something else. But thanks for the little guide of yours, it helps me a lot. Also I will take note on the data types there. Thanks again.
Daren
|||

Adrian, I have a similar cast of a DateTime variable to a string, but in attempting to get part of the date (for example, the Year), SSIS returns an invalid cast.

Example code: (DT_WSTR,4)YEAR(@.[User::DateToImport])

This evaluates perfectly in the Expression Builder, but upon running the project, the following errors are returned:

Error: The function "YEAR" does not support the data type "DT_WSTR" for parameter number 1. The type of the parameter could not be implicitly cast into a compatible type for the function. To perform this operation, the operand needs to be explicitly cast with a cast operator.

Error: The expression "(DT_WSTR, 4) YEAR(@.[User::DateToImport])" on property "ConnectionString" cannot be evaluated. Modify the expression to be valid.

So, there's a bit of a disconnect here somewhere...

|||What type is the variable User::DateToImport? The error says to me that it is String (DT_WSTR). It needs to be a DateTime.|||It is very much a DateTime type.|||

Take a read of this: http://blogs.conchango.com/jamiethomson/archive/2005/10/11/2261.aspx

It may be a pointer as to the problem. Basically there is bug that means a datatime variable can hold a value which isn't a datetime.

-Jamie

|||

Thank you Adrian.
I have been looking for this answer for about 2 weeks now. Your post was the only one that i have found that told me that i need to specify my variable in the Expression ellipses. I have been trying unsuccesfully to put my varaible in the [DataReader Source].[SQL Command] property.

sql

Run time jobs

A job is created to fetch data from source server and populate the
destination server. This job has the owner as windows authentication(user
account). This main job inturn creates sub jobs with owner as sa.
when i run the sub jobs from query analyser it works fine. But when ran from
sql server agent/jobs it is failing with the following error.
Msg 18456, Sev 14: Login failed for user 'CHNDomain\CHNSHL23456$'. [SQLS
TATE
28000]
Msg 7312, Sev 14: [SQLSTATE 01000].
[Note: CHNSHL23456$ is the destination system number]
when same job ran from query analyser that was logged using 'sa' account
fails with the following error.
Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'sa'.
[OLE/DB provider returned message: Invalid connection string attribute]
The windows authenticated user ID exists in both source server/destination
server users list.
Pls hlp to get rid of this error.
Regards,
SSKSSK
Does the login have full pemissions? What is an account SQL Server Agent run
under?
"SSK" <SSK@.discussions.microsoft.com> wrote in message
news:CC9BF3BD-5394-40B1-A8E2-7D1DAC73BBA2@.microsoft.com...
>A job is created to fetch data from source server and populate the
> destination server. This job has the owner as windows authentication(user
> account). This main job inturn creates sub jobs with owner as sa.
> when i run the sub jobs from query analyser it works fine. But when ran
> from
> sql server agent/jobs it is failing with the following error.
> Msg 18456, Sev 14: Login failed for user 'CHNDomain\CHNSHL23456$'.
> [SQLSTATE
> 28000]
> Msg 7312, Sev 14: [SQLSTATE 01000].
> [Note: CHNSHL23456$ is the destination system number]
> when same job ran from query analyser that was logged using 'sa' account
> fails with the following error.
> Server: Msg 18456, Level 14, State 1, Line 1
> Login failed for user 'sa'.
> [OLE/DB provider returned message: Invalid connection string attribute
]
> The windows authenticated user ID exists in both source server/destination
> server users list.
> Pls hlp to get rid of this error.
> Regards,
> SSK
>|||What account is the SQL Agent service running under ? Jobs owned by sa will
access resources under the context of the SQL Agent service account.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"SSK" <SSK@.discussions.microsoft.com> wrote in message
news:CC9BF3BD-5394-40B1-A8E2-7D1DAC73BBA2@.microsoft.com...
>A job is created to fetch data from source server and populate the
> destination server. This job has the owner as windows authentication(user
> account). This main job inturn creates sub jobs with owner as sa.
> when i run the sub jobs from query analyser it works fine. But when ran
> from
> sql server agent/jobs it is failing with the following error.
> Msg 18456, Sev 14: Login failed for user 'CHNDomain\CHNSHL23456$'.
> [SQLSTATE
> 28000]
> Msg 7312, Sev 14: [SQLSTATE 01000].
> [Note: CHNSHL23456$ is the destination system number]
> when same job ran from query analyser that was logged using 'sa' account
> fails with the following error.
> Server: Msg 18456, Level 14, State 1, Line 1
> Login failed for user 'sa'.
> [OLE/DB provider returned message: Invalid connection string attribute
]
> The windows authenticated user ID exists in both source server/destination
> server users list.
> Pls hlp to get rid of this error.
> Regards,
> SSK
>|||The SQL server agent is started by Local System account.
The login account has admin privileges on source and destination server als
o.
"Jasper Smith" wrote:

> What account is the SQL Agent service running under ? Jobs owned by sa wil
l
> access resources under the context of the SQL Agent service account.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "SSK" <SSK@.discussions.microsoft.com> wrote in message
> news:CC9BF3BD-5394-40B1-A8E2-7D1DAC73BBA2@.microsoft.com...
>
>|||Local System does not have access to network resources. If
you are accessing other servers, you need to use a domain
account for the service with the appropriate permissions.
-Sue
On Wed, 24 Aug 2005 23:33:09 -0700, SSK
<SSK@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>The SQL server agent is started by Local System account.
>The login account has admin privileges on source and destination server al
so.
>"Jasper Smith" wrote:
>

Run an SP without adding anything to the transaction log?

Hi, I have an SP that will basically copy one table's data to another with
some transformation involved. The source table over 1 billion rows. The
machine has limited disk space and the transaction log will grow too large
and cause trouble.
Is there a way to run this SP and not have the transactions logged for it?
Thanks,
ADAMWhat kind of transformation?
If you can use a select statement to bcp the data to a file, then you can
change the recovery model (first make a full db backup) to bulk-logged, and
use bcp utility or bulk insert statement to insert the data into the second
table. Change back the recovery model to full when you finish.
AMB
"adami" wrote:

> Hi, I have an SP that will basically copy one table's data to another with
> some transformation involved. The source table over 1 billion rows. The
> machine has limited disk space and the transaction log will grow too large
> and cause trouble.
> Is there a way to run this SP and not have the transactions logged for it?
> Thanks,
> ADAM|||Hi,
Set the database into Simple recovery Recovery model. After that execute the
Insert statment in batches of 1000 or 2000. Commit the transaction after
each batch. This will clear the Transaction log and will ensure that LDF
will not grow.
Thanks
Hari
SQL Server MVP
"adami" <adami@.discussions.microsoft.com> wrote in message
news:44698021-D132-412F-8BC2-0C47D08AA976@.microsoft.com...
> Hi, I have an SP that will basically copy one table's data to another with
> some transformation involved. The source table over 1 billion rows. The
> machine has limited disk space and the transaction log will grow too large
> and cause trouble.
> Is there a way to run this SP and not have the transactions logged for it?
> Thanks,
> ADAM|||To start, read up the setting the database's recovery model to simple, even
if temporarily for this process. Also, you could see if setting the database
to SINGLE_USER mode during the process is possible. Dropping indexes from
the destination table prior to the inserts and then re-creating them again
afterward will also reduce the number of transaction, speed things up and
reduce index fragmentation.
I have a data warehousing project where I have a similar situation, except
my row volume is only around 10,000,000 instead of 1,000,000,000, but still
a dump load of data. What I actually did was to create a view of the source
table which performs the needed transformations, and a DTS package exports
the view to a tab delimited text file. Once the export is complete, I bulk
copy (BCP.EXE) the text file into the destination table. Read up on the bulk
copy feature and how it can be used to move data with minimal logging. Of
everything I tried, this was by far the fastest.
"adami" <adami@.discussions.microsoft.com> wrote in message
news:44698021-D132-412F-8BC2-0C47D08AA976@.microsoft.com...
> Hi, I have an SP that will basically copy one table's data to another with
> some transformation involved. The source table over 1 billion rows. The
> machine has limited disk space and the transaction log will grow too large
> and cause trouble.
> Is there a way to run this SP and not have the transactions logged for it?
> Thanks,
> ADAM

Run 2nd SQL in stored proc

I have a stored procedure (see below) that inserts records into a history
table from a source table (PayTotals). After that is successful, I would
like to run a delete statement "DELETE FROM dbo.PayTotals WHERE PaidYr =
@.PaidYr" but only if the previous INSERT is successful. What would I need
to add to the proc below to make that happen? Thanks.
David
CREATE PROCEDURE [mc_inshstPayTotals]
(@.PaidYr [smallint])
AS INSERT INTO dbo.hstPayTotals
([EmployerCaseNumber],
[EmployerMax],
[PayFirst],
[PaySecond],
[PaidYr],
[PaidMo])
SELECT
EmployerCaseNumber,
EmployerMax,
PayFirst,
PaySecond,
PaidYr,
PaidMo
FROM dbo.PayTotals
WHERE PaidYr = @.PaidYr
GOWhat does "successful" mean? If it means exactly one row is inserted:
IF @.@.ROWCOUNT = 1
BEGIN
DELETE ...
END
Otherwise, you'll have to be more specific...
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:OdaU4pF8FHA.2616@.TK2MSFTNGP15.phx.gbl...
>I have a stored procedure (see below) that inserts records into a history
>table from a source table (PayTotals). After that is successful, I would
>like to run a delete statement "DELETE FROM dbo.PayTotals WHERE PaidYr =
>@.PaidYr" but only if the previous INSERT is successful. What would I need
>to add to the proc below to make that happen? Thanks.
> David
> CREATE PROCEDURE [mc_inshstPayTotals]
> (@.PaidYr [smallint])
> AS INSERT INTO dbo.hstPayTotals
> ([EmployerCaseNumber],
> [EmployerMax],
> [PayFirst],
> [PaySecond],
> [PaidYr],
> [PaidMo])
> SELECT
> EmployerCaseNumber,
> EmployerMax,
> PayFirst,
> PaySecond,
> PaidYr,
> PaidMo
> FROM dbo.PayTotals
> WHERE PaidYr = @.PaidYr
> GO
>|||The system variable @.@.rowcount will return the number of rows affected (in
this case inserted). Also, there is the variable @.@.error that contains <> 0
in the event of an error.
For example:
insert into ...
if @.@.rowcount > 0
begin
..
end
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:OdaU4pF8FHA.2616@.TK2MSFTNGP15.phx.gbl...
>I have a stored procedure (see below) that inserts records into a history
>table from a source table (PayTotals). After that is successful, I would
>like to run a delete statement "DELETE FROM dbo.PayTotals WHERE PaidYr =
>@.PaidYr" but only if the previous INSERT is successful. What would I need
>to add to the proc below to make that happen? Thanks.
> David
> CREATE PROCEDURE [mc_inshstPayTotals]
> (@.PaidYr [smallint])
> AS INSERT INTO dbo.hstPayTotals
> ([EmployerCaseNumber],
> [EmployerMax],
> [PayFirst],
> [PaySecond],
> [PaidYr],
> [PaidMo])
> SELECT
> EmployerCaseNumber,
> EmployerMax,
> PayFirst,
> PaySecond,
> PaidYr,
> PaidMo
> FROM dbo.PayTotals
> WHERE PaidYr = @.PaidYr
> GO
>|||The INSERT will always handle thousands of records, so I would think
that if the @.@.rowcount was > 0 then it worked, correct?
David
*** Sent via Developersdex http://www.examnotes.net ***|||JT,
If there IS an error, won't the @.@.rowcount = 0?
Thanks.
*** Sent via Developersdex http://www.examnotes.net ***|||Yes, I would expect that to be the case.
Just because @.@.error = 0, it doesn't necessarily mean that 0 rows were
inserted, becuase the select query may return no rows or a trigger on
hstPayTotals may rollback the insert. Therefore, you will want to at least
check the status of @.@.rowcount.
"David" <daman@.lifetime.com> wrote in message
news:unZmE0F8FHA.1864@.TK2MSFTNGP12.phx.gbl...
> JT,
> If there IS an error, won't the @.@.rowcount = 0?
> Thanks.
>
> *** Sent via Developersdex http://www.examnotes.net ***|||> The INSERT will always handle thousands of records, so I would think
> that if the @.@.rowcount was > 0 then it worked, correct?
Well, again, just because you inserted a bunch of rows doesn't necessarily
mean they were the right ones. :-)
But yes, if all you care about is that at least one row was inserted, then
checking for a positive @.@.ROWCOUNT should suffice.|||>I have a stored procedure (see below) that inserts records into a history
>table from a source table (PayTotals). After that is successful, I would
>like to run a delete statement "DELETE FROM dbo.PayTotals WHERE PaidYr =
>@.PaidYr" but only if the previous INSERT is successful. What would I need
>to add to the proc below to make that happen? Thanks.
I must be missing something in the midst of all this discussion of
@.@.ROWCOUNT and @.@.ERROR. Perhaps I'm being retarded today, but why not just
do this:
BEGIN TRANSACTION
INSERT stuff
DELETE stuff
COMMIT TRANSACTION
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane|||"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:%23kJ4e7G8FHA.2616@.TK2MSFTNGP15.phx.gbl...
> I must be missing something in the midst of all this discussion of
> @.@.ROWCOUNT and @.@.ERROR. Perhaps I'm being retarded today, but why not
> just do this:
> BEGIN TRANSACTION
> INSERT stuff
> DELETE stuff
> COMMIT TRANSACTION
It depends what you want to do if the INSERT fails.
Do you still want to DELETE?
And if the INSERT succedes and the DELETE fails.
Do you still want to Commit the INSERT.
Normally you don't.
So after each one, check @.@.rowcount and @.@.errors and Commit only if both
succede.
Else, Rollback.

RSS feed with ISO8859 encoding

We have an XML source that reads an RSS feed. The document uses ISO8859 encoding, which causes an error in SSIS: "System does not support ISO8859 encoding". Is there any way around this?

Thanks,
Dirk

Is there no one who can assist me with this topic?
Is it maybe possible to get a list of supported encodings from somewhere?

Best regard,
Dirk|||Likely this is by design.
I will get back to you after some more confirmation.

Thanks|||

Andy Conrad (XmlDataReader dev) confirmed
"I don’t think System.Xml supports that encoding. Only the encodings in system.text.encoding are supported. See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemtextencodingclasstopic.asp
"
Thanks

|||AFAIK, there is no such thing as ISO-8859 encoding.
There are several very different encodings defined by ISO 8859
standard, e.g. iso-8859-1 (Latin 1), iso-8859-5 (Cyrillic), etc.
I believe any of these are supported.

I could not check the task now, but tested that
Encoding.GetEncoding("ISO-8859-1");
works fine, while
Encoding.GetEncoding("ISO8859");
throws an error "'ISO8859' is not a supported encoding name".

So the "ISO8859" without suffix is not a legal encoding,
the feed is not correct.|||You were all right. I had the code ISO8859-15 in my feed, but as described the code is not supported. I didn't knew the importance of the "-xx" at the end so I did not post my extension "-15".

After some mails with the developer of the feed I was able to get the RSS feed now with "UTF-8" encoding - This works perfect!

Thanks for the help!