Fetching Data from a web service

Hello All,

I have to fetch data in an ssis package from a set of web services. what is the best way of doing this?

The web services are session based, this means that multiple calls are needed to complete one operation. like one to log on, second onwards to execute calls, and the last one to log out.

(there is some cookie management also required to logon successfully into the web services).

Should we write a custom task which will fetch the data for us? Or just write a C# component which is invoked from SSIS?

Regards,

Abhishek.

MSDN Student wrote:

Hello All,

I have to fetch data in an ssis package from a set of web services. what is the best way of doing this?

The web services are session based, this means that multiple calls are needed to complete one operation. like one to log on, second onwards to execute calls, and the last one to log out.

(there is some cookie management also required to logon successfully into the web services).

Should we write a custom task which will fetch the data for us? Or just write a C# component which is invoked from SSIS?

Regards,

Abhishek.

Its your decision. If its something that will need to be done in many packages then a custom component is the way to go. If its just for this package, calling from the script task will be adequate.

-Jamie

Feature request: Code packages

Hi. One feature that would be great for SQL Server is the ability to wrap your code (functions, stored procs) in a package, like you can on Oracle. Then when moving code to production, you can just move the package. This might already be possible and I'm not familiar with how to do it. This would make a great database even better. Thanks

Many folks typically do that by using the Source Code Control tool.

(You do use a Source Code tool, don't you?)

Otherwise, in order to post a suggestion that will be considered by the SQL Server development team, visit this link.

Suggestions for SQL Server

http://connect.microsoft.com/sqlserver

Feature Pack for SP2

I installed SP2 of SQL2005 onto my development machine for testing before using it on the live server.

I saw that MSXML 6 was part of the package and was updated, but what about the OLEDB for MS OLAP 9 driver? Will we see a Feature Pack SP2 soon? Or is it even available and I am just blind?

Or did the OLE DB for OLAP thing not change at all? What version is the latest for this component, and maybe even more important: Where does the dll hide? System32 I suppose, but...

Thanks for pointing out!

Feature pack is part of SP2 package. Here is the link to the new feature pack http://www.microsoft.com/downloads/details.aspx?familyid=50b97994-8453-4998-8226-fa42ec403d17&displaylang=en

You can also find the link on the main page of the SP2 http://www.microsoft.com/sql/sp2.mspx.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Fast BCP

Can someone tell me the conditions that should be
fulfilled for a FAST BCP to happen
when i create a DTS package and that has Transform data
Task which inserts data into a table from select query is
that done via BCP
SanjayLook in BooksOnLine under BCP - Minimally logged load.
--
Andrew J. Kelly
SQL Server MVP
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:024401c3456b$c1e25a40$a101280a@.phx.gbl...
> Can someone tell me the conditions that should be
> fulfilled for a FAST BCP to happen
> when i create a DTS package and that has Transform data
> Task which inserts data into a table from select query is
> that done via BCP
> Sanjay
>|||This is a bit confusing
It says that Target table should have 0 rows
Is this true
Also TABLOCK hint should be specified
Is this true too
Sanjay
>--Original Message--
>Look in BooksOnLine under BCP - Minimally logged load.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Sanjay" <sanjayg@.hotmail.com> wrote in message
>news:024401c3456b$c1e25a40$a101280a@.phx.gbl...
>> Can someone tell me the conditions that should be
>> fulfilled for a FAST BCP to happen
>> when i create a DTS package and that has Transform data
>> Task which inserts data into a table from select query
is
>> that done via BCP
>> Sanjay
>
>.
>|||It only needs 0 rows if there are existing indexes on the table. If the
table has indexes it must be empty to do a minimally logged load. You can
drop the indexes and recreate them afterwards though. A table lock is
needed as well. This shouldn't be an issue if your loading that many rows
in you certainly don't want other users in there.
--
Andrew J. Kelly
SQL Server MVP
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:8be401c34577$ae5ab1f0$a401280a@.phx.gbl...
> This is a bit confusing
> It says that Target table should have 0 rows
> Is this true
> Also TABLOCK hint should be specified
> Is this true too
> Sanjay
> >--Original Message--
> >Look in BooksOnLine under BCP - Minimally logged load.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Sanjay" <sanjayg@.hotmail.com> wrote in message
> >news:024401c3456b$c1e25a40$a101280a@.phx.gbl...
> >> Can someone tell me the conditions that should be
> >> fulfilled for a FAST BCP to happen
> >>
> >> when i create a DTS package and that has Transform data
> >> Task which inserts data into a table from select query
> is
> >> that done via BCP
> >>
> >> Sanjay
> >>
> >
> >
> >.
> >

Failure writing properties running SSIS package from a Web Service

I am attempting to run an SSIS package from a web service. Right now both the service and package are on my local machine which is running XP. I have accessed the web service from a client application in debug mode. I am not sure if it is actually running under aspnet_wp.exe because it is XP and a development environment? (separate question)? The package fails with a series of OnError messages similar to:

The result of the expression ""/c DEL /F /Q \"" + @.DeployFolder + "\\catalog.diff.lz\""" on property "Arguments" cannot be written to the property. The expression was evaluated, but cannot be set on the property.

An initial supposition is that the permissions of the web service are inadequate for the package. I have the authentication as "Windows" and <identity impersonate="true" /> in the Web.Config file. When I break in the debugger the Environment.UserName and Environment.UserDomainName are mine and I am an Admin on the box.
the authorization is 'deny users="?".

The article that describes basic implementation of this in a Web Service states:

With its default settings for authentication and authorization, a Web

service generally does not have sufficient permissions to access SQL

Server or the file system to load and execute packages. You may have to

assign appropriate permissions to the Web service by configuring its

authentication and authorization settings in the web.config

file and assigning database and file system permissions as appropriate.

A complete discussion of Web, database, and file system permissions is

beyond the scope of this topic.

And how!

Note that the load is fine and that this is a run time error and that the package runs correctly when run manually from SQL Server using the 'run package' menu item in the Object Explorer tree of the SQL Server Management Console.

I need to know if this is an ASP.NET issue per se or XP or if this is even a security issue. And how to solve it! This is critical path so an expeditious reply with a solution would be greatly appreciated.Can't believe I left this out but the the web service is running under Integrated Windows authentication.

Failure Workflow Does Not Fire

Hello,
I have a SQL Server 2000 DTS package in which the first step executes a batch file. The batch file contains FTP commands that log into an FTP server, and pull down whatever file is there.

I set up a failure workflow to send an email if the step fails. When I have a SQL Server job run this package, and there is no file to dowload, the whole package fails without the failure workflow result firing.

For the step (DTSStep_DTSCreateProcessTask_1), I have the 'FailPackageOnError' property set to -1. In the package properties, I have the check box for 'Fail Package on First Error' cleared.

What do I need to do so that the failure workflow occurs when the step fails?

Thank you for your help!

cdun2Are you sure the fact that no file is there will cause the step to error out?|||u need to trap errors from external programs like bat files to have the failed workflow activated. aslo not all external programs returns error code to the calling application. for bat files, u will have to set errorlevel to make the calling program understand the success/failure

for example when using xp_cmdshell, this will call the failure workflow, if present

declare @.err int
exec @.err = master..xp_cmdshell 'C:\xx.bat'
if @.err = 1
RAISERROR ('err',16,1)

the above code will not fire the failure workflow if u just execute
exec master..xp_cmdshell 'C:\xx.bat'
and even if the xx.bat is not present in folder C:\|||Thank you for your help!
cdun2|||Another option is to create an operator when you scheduled the job and an e-mail will be sent out if the job fails.

Good luck

Failure to run when using DTC

Does anyone hany any experience of using SSIS with MS DTC?
I have a package that runs successfully. When I switch it to using transactions (i.e. TransactionOption=Required) it fails. I get the following messages in the log:
-Starting distributed transaction for this container.
-Failed to acquire connection "<connection-manager-name>". Connection may not be configured correctly or you may not have the right permissions on this connection.
-Aborting the current distributed transaction.

So - it seems it is having trouble enlisting that connection manager in a distributed transaction. My connection manager points at a SQL Server database on a different server to where I am running the package.
The connection string on the connection manager is: Data Source=ABENTAH3D02\DEVDBINSTANCE;Initial Catalog=CUESeerMetadata;Provider=SQLOLEDB.1;Integrated Security=SSPI;Auto Translate=False;

OK, so I kind of know where the error is but I don't know exactly what's causing it or how to go about diagnosing the problem. I don't have much experience of MS DTC although I have used it from SSIS in the past successfully.

Does anyone know the pre-requisites for using MS DTC?
How can I go about finding the problem?

Thanks
JamieThis sounds like a DTC issue with your permissions.
I'm not sure what the problem is, but here are a few things to check:
Are you able to enlist in a transaction on the remote machine where the SServer is? Run the package on that machine if you can. Is it successful there?
Is DTC started and running on both machines?
Do you have the priviledges to access the connection on the remote machine?
You might check the Windows Event Viewer on both machines to see if there are any messages that may give you more information about the problem.
HTH,
K|||I don't have the ability to run the package on the SQL box - or more accurately don't have time to install all the pre-reqs- but I'm pretyt sure that it IS a problem enlisting remote connections in the txn. i.e. I have a local connection in my package which enlists in the transaction without a problem.

In my local dtc trace log I have the following messages (many times)
"transaction got begun, description : '<NULL>"
"received request to abort the transaction from beginner"
"transaction is aborting"
"transaction has been aborted"

Nothing in the event viewer.

I'm working on it being a priviledges problem! Unfortunately I have no experience with DTC so its a slow process.

-Jamie|||Jamie, is DTC running on the server machine, i.e. ABENTAH3D02?

This can be found out through the following steps:
1. Open "Control Panel"
2. Open "Administrative Tools"
3. Open "Component Services"
4. Expand "Computer", right-click on "My Computer", switch to page "MSDTC"

Look for Service Control Status. Is it "Started"?|||

Runying Mao wrote:

Jamie, is DTC running on the server machine, i.e. ABENTAH3D02?

This can be found out through the following steps:
1. Open "Control Panel"
2. Open "Administrative Tools"
3. Open "Component Services"
4. Expand "Computer", right-click on "My Computer", switch to page "MSDTC"

Look for Service Control Status. Is it "Started"?

Yeah it is. First thing I checked |||

Ok. Good.

Then in the same page "MSDTC", click "Security Configuration". There are "Allow Inbound" and "Allow Outbound" in Transaction Manager Communication. Are they both checked?

|||

Runying Mao wrote:

Ok. Good.

Then in the same page "MSDTC", click "Security Configuration". There are "Allow Inbound" and "Allow Outbound" in Transaction Manager Communication. Are they both checked?

Yes they are!

Can the choice of logon account affect it? Currently it is set to "NT Authority\NetworkService" on both machines.

I've also tried selecting "No authentication required" on the server. It didn't make any difference.

In fact, here are all the settings on the server (as taken from the event log):

MS DTC started with the following settings (OFF = 0 and ON = 1):
Security Configuration:
Network Administration of Transactions = 1,
Network Clients = 1,
Inbound Distributed Transactions using Native MSDTC Protocol = 1,
Outbound Distributed Transactions using Native MSDTC Protocol = 1,
Transaction Internet Protocol (TIP) = 0,
XA Transactions = 0
Filtering Duplicate events = 1
And here are the local settings:

MS DTC started with the following settings:
Security Configuration (OFF = 0 and ON = 1):
Network Administration of Transactions = 1,
Network Clients = 1,
Inbound Distributed Transactions using Native MSDTC Protocol = 1,
Outbound Distributed Transactions using Native MSDTC Protocol = 1,
Transaction Internet Protocol (TIP) = 0,
XA Transactions = 0

-Jamie|||IGNORE THIS!!!!

I've got it working. You've got to turn Windows Firewall off

http://support.microsoft.com/default.aspx/kb/839279
YEEESSSSS!!!!

(Very happy right now )

-Jamie|||

This DTC seems not to work...

I have a package with a single data flow that copies a table from Informix to SQL Server. It finishes "all green", but ask me if I got any data on the target table...

Failure to run when using DTC

Does anyone hany any experience of using SSIS with MS DTC?
I have a package that runs successfully. When I switch it to using transactions (i.e. TransactionOption=Required) it fails. I get the following messages in the log:
-Starting distributed transaction for this container.
-Failed to acquire connection "<connection-manager-name>". Connection may not be configured correctly or you may not have the right permissions on this connection.
-Aborting the current distributed transaction.

So - it seems it is having trouble enlisting that connection manager in a distributed transaction. My connection manager points at a SQL Server database on a different server to where I am running the package.
The connection string on the connection manager is: Data Source=ABENTAH3D02\DEVDBINSTANCE;Initial Catalog=CUESeerMetadata;Provider=SQLOLEDB.1;Integrated Security=SSPI;Auto Translate=False;

OK, so I kind of know where the error is but I don't know exactly what's causing it or how to go about diagnosing the problem. I don't have much experience of MS DTC although I have used it from SSIS in the past successfully.

Does anyone know the pre-requisites for using MS DTC?
How can I go about finding the problem?

Thanks
JamieThis sounds like a DTC issue with your permissions.
I'm not sure what the problem is, but here are a few things to check:
Are you able to enlist in a transaction on the remote machine where the SServer is? Run the package on that machine if you can. Is it successful there?
Is DTC started and running on both machines?
Do you have the priviledges to access the connection on the remote machine?
You might check the Windows Event Viewer on both machines to see if there are any messages that may give you more information about the problem.
HTH,
K|||I don't have the ability to run the package on the SQL box - or more accurately don't have time to install all the pre-reqs- but I'm pretyt sure that it IS a problem enlisting remote connections in the txn. i.e. I have a local connection in my package which enlists in the transaction without a problem.

In my local dtc trace log I have the following messages (many times)
"transaction got begun, description : '<NULL>"
"received request to abort the transaction from beginner"
"transaction is aborting"
"transaction has been aborted"

Nothing in the event viewer.

I'm working on it being a priviledges problem! Unfortunately I have no experience with DTC so its a slow process.

-Jamie|||Jamie, is DTC running on the server machine, i.e. ABENTAH3D02?

This can be found out through the following steps:
1. Open "Control Panel"
2. Open "Administrative Tools"
3. Open "Component Services"
4. Expand "Computer", right-click on "My Computer", switch to page "MSDTC"

Look for Service Control Status. Is it "Started"?|||

Runying Mao wrote:

Jamie, is DTC running on the server machine, i.e. ABENTAH3D02?

This can be found out through the following steps:
1. Open "Control Panel"
2. Open "Administrative Tools"
3. Open "Component Services"
4. Expand "Computer", right-click on "My Computer", switch to page "MSDTC"

Look for Service Control Status. Is it "Started"?

Yeah it is. First thing I checked |||

Ok. Good.

Then in the same page "MSDTC", click "Security Configuration". There are "Allow Inbound" and "Allow Outbound" in Transaction Manager Communication. Are they both checked?

|||

Runying Mao wrote:

Ok. Good.

Then in the same page "MSDTC", click "Security Configuration". There are "Allow Inbound" and "Allow Outbound" in Transaction Manager Communication. Are they both checked?

Yes they are!

Can the choice of logon account affect it? Currently it is set to "NT Authority\NetworkService" on both machines.

I've also tried selecting "No authentication required" on the server. It didn't make any difference.

In fact, here are all the settings on the server (as taken from the event log):

MS DTC started with the following settings (OFF = 0 and ON = 1):
Security Configuration:
Network Administration of Transactions = 1,
Network Clients = 1,
Inbound Distributed Transactions using Native MSDTC Protocol = 1,
Outbound Distributed Transactions using Native MSDTC Protocol = 1,
Transaction Internet Protocol (TIP) = 0,
XA Transactions = 0
Filtering Duplicate events = 1
And here are the local settings:

MS DTC started with the following settings:
Security Configuration (OFF = 0 and ON = 1):
Network Administration of Transactions = 1,
Network Clients = 1,
Inbound Distributed Transactions using Native MSDTC Protocol = 1,
Outbound Distributed Transactions using Native MSDTC Protocol = 1,
Transaction Internet Protocol (TIP) = 0,
XA Transactions = 0

-Jamie|||IGNORE THIS!!!!

I've got it working. You've got to turn Windows Firewall off

http://support.microsoft.com/default.aspx/kb/839279
YEEESSSSS!!!!

(Very happy right now )

-Jamie|||

This DTC seems not to work...

I have a package with a single data flow that copies a table from Informix to SQL Server. It finishes "all green", but ask me if I got any data on the target table...

Failure saving package

Hello All,

I have a package in the SQL 2000 environment that works fine. I have migrated this DTS Package to a SSIS Package using the Visual Studio 2005.

After that, i made some changes in this package and now I'm trying to save it but I am facing with this error message below:

TITLE: Microsoft Visual Studio

Failure saving package.

ADDITIONAL INFORMATION:

Invalid at the top level of the document.
(Microsoft OLEDB Persistence Provider)

Invalid at the top level of the document.
(Microsoft OLEDB Persistence Provider)

BUTTONS:

OK

Could someone please help me with this ?

Thanks in advance.

Thiago

Has anybody seen this issue resolved? I am experiencing the exact same issue.|||

hi guys,

Probably not very helpful for you but a few months ago I suffered the same and my solution was make a DTSX from the beginning...

|||

That's what I am doing at present but there are a couple of hundred of these that I need to change. If I have to build them all from scratch the project time is going to blow out considerably. The migration is not perfect (as I expected) but it does considerably speed up the conversion process.

The funny thing is that a couple seem to have converted cleanly but the majority haven't.

I will be putting in a support issue with Microsoft and I will report back with any findings.

|||

Everybody,

If you have any recordset object exists/present in your migrated DTS workflow, please manually convert those variables datatype into Object (If you are not able to modify the recordset variables, please re-create the variables with Object datatype). After changing all the recordset variables, most probabily you should be able to save the migrated SSIS workflow.

Thanks.

Regards,

Ezhilarasan Maharajan

|||

This seems to have solved my problem but I have some further testing to do.

Great catch. Thanks for responding to this.

|||I've finished my testing and this has defintely resolved my problem. I also updated my MS support issue with this information so hopefully a KB article and a fix in a future SP will be forthcoming.|||Thank you! It helps!

Failure saving package

Hello All,

I have a package in the SQL 2000 environment that works fine. I have migrated this DTS Package to a SSIS Package using the Visual Studio 2005.

After that, i made some changes in this package and now I'm trying to save it but I am facing with this error message below:

TITLE: Microsoft Visual Studio

Failure saving package.

ADDITIONAL INFORMATION:

Invalid at the top level of the document.
(Microsoft OLEDB Persistence Provider)

Invalid at the top level of the document.
(Microsoft OLEDB Persistence Provider)

BUTTONS:

OK

Could someone please help me with this ?

Thanks in advance.

Thiago

Has anybody seen this issue resolved? I am experiencing the exact same issue.|||

hi guys,

Probably not very helpful for you but a few months ago I suffered the same and my solution was make a DTSX from the beginning...

|||

That's what I am doing at present but there are a couple of hundred of these that I need to change. If I have to build them all from scratch the project time is going to blow out considerably. The migration is not perfect (as I expected) but it does considerably speed up the conversion process.

The funny thing is that a couple seem to have converted cleanly but the majority haven't.

I will be putting in a support issue with Microsoft and I will report back with any findings.

|||

Everybody,

If you have any recordset object exists/present in your migrated DTS workflow, please manually convert those variables datatype into Object (If you are not able to modify the recordset variables, please re-create the variables with Object datatype). After changing all the recordset variables, most probabily you should be able to save the migrated SSIS workflow.

Thanks.

Regards,

Ezhilarasan Maharajan

|||

This seems to have solved my problem but I have some further testing to do.

Great catch. Thanks for responding to this.

|||I've finished my testing and this has defintely resolved my problem. I also updated my MS support issue with this information so hopefully a KB article and a fix in a future SP will be forthcoming.|||Thank you! It helps!

Failure saving package

Hello All,

I have a package in the SQL 2000 environment that works fine. I have migrated this DTS Package to a SSIS Package using the Visual Studio 2005.

After that, i made some changes in this package and now I'm trying to save it but I am facing with this error message below:

TITLE: Microsoft Visual Studio

Failure saving package.

ADDITIONAL INFORMATION:

Invalid at the top level of the document.
(Microsoft OLEDB Persistence Provider)

Invalid at the top level of the document.
(Microsoft OLEDB Persistence Provider)

BUTTONS:

OK

Could someone please help me with this ?

Thanks in advance.

Thiago

Has anybody seen this issue resolved? I am experiencing the exact same issue.|||

hi guys,

Probably not very helpful for you but a few months ago I suffered the same and my solution was make a DTSX from the beginning...

|||

That's what I am doing at present but there are a couple of hundred of these that I need to change. If I have to build them all from scratch the project time is going to blow out considerably. The migration is not perfect (as I expected) but it does considerably speed up the conversion process.

The funny thing is that a couple seem to have converted cleanly but the majority haven't.

I will be putting in a support issue with Microsoft and I will report back with any findings.

|||

Everybody,

If you have any recordset object exists/present in your migrated DTS workflow, please manually convert those variables datatype into Object (If you are not able to modify the recordset variables, please re-create the variables with Object datatype). After changing all the recordset variables, most probabily you should be able to save the migrated SSIS workflow.

Thanks.

Regards,

Ezhilarasan Maharajan

|||

This seems to have solved my problem but I have some further testing to do.

Great catch. Thanks for responding to this.

|||I've finished my testing and this has defintely resolved my problem. I also updated my MS support issue with this information so hopefully a KB article and a fix in a future SP will be forthcoming.|||Thank you! It helps!

Failure Precedence Constraints stopped working in my package

I had a Send email task linked to my Sequence Containers in my package and it was working fine. Everytime the container fails it would send an email to myself.

At some point all Failure constraints stopped working. Failure constraints work if I add brand new tasks, but with the existing tasks, they don't work. The Task which fails, turns red and execution stops. Next failure task is not executed.

I am not sure what triggered it to stop working. I cannot get anything on the log

Any help is appreciated.

I've resolved this issue.

I've added an Onerror Event Handler and changed the failure constaints to Logical Or.

It is working now...

|||

This is not consistent at all...

Next day failure precedences are not working again. For workaround I created onerror event handlers for each container to send an email. This time I got lock errors on the errDescription variable.

Failure Details?

Where is, (or even does it exists) the best place to look for some details on when package execution fails if running as a scheduled job. Obviously when you run from the command line or in VS, there is plenty of output detail on progress and on the source of errors, but when you run it as a scheduled job, it just says step 1 failed in the sql server log, and package foo failed in the NT application log . Is there anywhere to find this info or do we need to build error traps into the package to write stuff out somewhere?

THX

Davepackage logging looks like a candidate?

http://support.microsoft.com/kb/918760|||

drmcl wrote:

package logging looks like a candidate?

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

Its the ONLY candidate.

You should also be aware of this:

Job History and Job Step Sub-Systems
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html)

-Jamie