Integration services(data flow error)

when executing my data flow package that contains only one source and one destination

OLE db source -> SQL server destination

the following errors occurs in my output

Error: 0xC0202009 at Data Flow Task(infraction action), SQL Server Destination [3600]: An OLE DB error has occurred. Error code: 0x80040E14.

Error: 0xC0202071 at Data Flow Task(infraction action), SQL Server Destination [3600]: Unable to prepare the SSIS bulk insert for data insertion.

Error: 0xC004701A at Data Flow Task(infraction action), DTS.Pipeline: component "SQL Server Destination" (3600) failed the pre-execute phase and returned error code 0xC0202071.

i've checked the structure of my source and destination table but nothing seems to be wrong

if someone have ever faced these errors help me :D

Are you running the package on the same machine as the SQL Server instance that you are inserting into?

If not, it won't work. Use OLE DB Destination instead.

-Jamie

|||I have the same problem and everything is on the same machine. I am transfering from records from multiple tables in one database to a single table in another database. The destinations all have table lock OFF and it usually works. However, recently, I started getting this message. Also, if I rerun it, it happens on different tasks (the tasks are simultaneous 2 at a time).|||

Hi,

I'm having this issue as well, i.e. a package using the "SQL Server Destination" data flow component, loading a server on the same machine (a named instance) that the SSIS package is executing fails with an "Unable to prepare the SSIS bulk insert for data insertion." error. I suspect the windows account executing the package or the SQL login it's mapped to don't have some required permission, but I've tried making the sql account dbo, bulkadmin etc, and added to the local administrators group with no luck. I've seen this reported several times, but no solution?

Dave

|||

Hi guys,

I've mentioned some pre-requisites to getting SQL Server Destination working here:

Destination Adapter Comparison
(http://blogs.conchango.com/jamiethomson/archive/2006/08/14/SSIS_3A00_-Destination-Adapter-Comparison.aspx)

which may or may not be useful. I hope it is.

-Jamie

Integration services(data flow error)

when executing my data flow package that contains only one source and one destination

OLE db source -> SQL server destination

the following errors occurs in my output

Error: 0xC0202009 at Data Flow Task(infraction action), SQL Server Destination [3600]: An OLE DB error has occurred. Error code: 0x80040E14.

Error: 0xC0202071 at Data Flow Task(infraction action), SQL Server Destination [3600]: Unable to prepare the SSIS bulk insert for data insertion.

Error: 0xC004701A at Data Flow Task(infraction action), DTS.Pipeline: component "SQL Server Destination" (3600) failed the pre-execute phase and returned error code 0xC0202071.

i've checked the structure of my source and destination table but nothing seems to be wrong

if someone have ever faced these errors help me :D

Are you running the package on the same machine as the SQL Server instance that you are inserting into?

If not, it won't work. Use OLE DB Destination instead.

-Jamie

|||I have the same problem and everything is on the same machine. I am transfering from records from multiple tables in one database to a single table in another database. The destinations all have table lock OFF and it usually works. However, recently, I started getting this message. Also, if I rerun it, it happens on different tasks (the tasks are simultaneous 2 at a time).|||

Hi,

I'm having this issue as well, i.e. a package using the "SQL Server Destination" data flow component, loading a server on the same machine (a named instance) that the SSIS package is executing fails with an "Unable to prepare the SSIS bulk insert for data insertion." error. I suspect the windows account executing the package or the SQL login it's mapped to don't have some required permission, but I've tried making the sql account dbo, bulkadmin etc, and added to the local administrators group with no luck. I've seen this reported several times, but no solution?

Dave

|||

Hi guys,

I've mentioned some pre-requisites to getting SQL Server Destination working here:

Destination Adapter Comparison
(http://blogs.conchango.com/jamiethomson/archive/2006/08/14/SSIS_3A00_-Destination-Adapter-Comparison.aspx)

which may or may not be useful. I hope it is.

-Jamie

Integration services(data flow error)

when executing my data flow package that contains only one source and one destination

OLE db source -> SQL server destination

the following errors occurs in my output

Error: 0xC0202009 at Data Flow Task(infraction action), SQL Server Destination [3600]: An OLE DB error has occurred. Error code: 0x80040E14.

Error: 0xC0202071 at Data Flow Task(infraction action), SQL Server Destination [3600]: Unable to prepare the SSIS bulk insert for data insertion.

Error: 0xC004701A at Data Flow Task(infraction action), DTS.Pipeline: component "SQL Server Destination" (3600) failed the pre-execute phase and returned error code 0xC0202071.

i've checked the structure of my source and destination table but nothing seems to be wrong

if someone have ever faced these errors help me :D

Are you running the package on the same machine as the SQL Server instance that you are inserting into?

If not, it won't work. Use OLE DB Destination instead.

-Jamie

|||I have the same problem and everything is on the same machine. I am transfering from records from multiple tables in one database to a single table in another database. The destinations all have table lock OFF and it usually works. However, recently, I started getting this message. Also, if I rerun it, it happens on different tasks (the tasks are simultaneous 2 at a time).|||

Hi,

I'm having this issue as well, i.e. a package using the "SQL Server Destination" data flow component, loading a server on the same machine (a named instance) that the SSIS package is executing fails with an "Unable to prepare the SSIS bulk insert for data insertion." error. I suspect the windows account executing the package or the SQL login it's mapped to don't have some required permission, but I've tried making the sql account dbo, bulkadmin etc, and added to the local administrators group with no luck. I've seen this reported several times, but no solution?

Dave

|||

Hi guys,

I've mentioned some pre-requisites to getting SQL Server Destination working here:

Destination Adapter Comparison
(http://blogs.conchango.com/jamiethomson/archive/2006/08/14/SSIS_3A00_-Destination-Adapter-Comparison.aspx)

which may or may not be useful. I hope it is.

-Jamie

Integration Services won't install

I am installing Visual Studio 2005 Professional on Windows XP.

I have finished installing Visual Studio 2005 Professional along with Service Pack 1. Now I am installing the Developer version of SQL 2005 that comes with Visual Studio 2005 Professional .

During the installation of SQL Server 2005, I got the following error during
the integration services installation portion:

Failed to install and configure assemblies: c:\program files\microsoft sql
server\90\dts\tasks\microsoft.sqlserver.msmqtask.d ll in the COM+ catalog.
Error -2146233087

Error message: unknown error 0x80131501
Error description: You must have administrative credentials to perform this
task.

I'm logged into Windows as Administrator.

This machine did previously have VS 2005 Standard and SQL 2005 Standard loaded. The software was RTM software as provided by Microsoft at their launch event. I did uninstall both products before attempting the upgrade.

Any help would be much appreciated.

Hi,

Refer below links for workaround

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=136467&SiteID=1

http://geekswithblogs.net/waterbaby/archive/2006/08/03/87048.aspx

HTH

Hemantgiri S. Goswami

|||

This is an issue when Microsoft.SqlServer.MSMQTask is left behind in COM+ catalog. You can workaround it by:

1. Open Administrative Tools\Component Services

2. Go to Component Services\Computers\My Computer\COM+ Applications

3. Delete Microsoft.SqlServer.MSMQTask

Integration Services Samples

Hi All,

I have installed SQL 2005, but there is not a,

Integration Services Samples.

The SQL 2005 manuals says:

"By default, the Integration Services samples are installed onto the local hard disk drive in the following folder: C:\Program Files\Microsoft SQL Server\90\Samples"

But there is not "Samples" dir inder C:\Program Files\Microsoft SQL Server\90\

Where I could get the samples ?

Sergiy

You may not have selected the Samples during the install. Quite sensibly they are not there by default.

So you could re-reun setup and under Custom/Advanced select samples, or download the most up to date samples. With SQL 2005 the Docs and Samples team have been releasing updates which is rather nice. The latest can be found as part of the overall SP2 release (http://www.microsoft.com/sql/sp2.mspx), and the direct link to he samples - http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

Integration Services on a Multi-Instance SQL Server Cluster (Best Practice)

Hi
I have a 2-node active-passive SQL cluster (with SSIS manually
clustered in the SQL resource group). I want to convert to active-
active by installing a named instance on the second node. Am I correct
in saying that the new virtual server cannot have its own SSIS
instance but must instead utilise the SSIS instance (& the MSDB
database) of the first (original) virtual server (because there can
only be one instance of SSIS on a node) ?
Having said this, should I move the SSIS resources into (preferably) a
dedicated SSIS resource group or (alternatively) the cluster/quorum
group, moving the xml config file and the Packages folder to the
associated physical disk for that group ?
tia
brynjon
SSIS is typically set up as a local instance for each cluster node. Note
that each node must be installed independently. You can cluster the
resulting SSIS installations and refer to them a a cluster resource.
Here is Kirk's blog entry on how to do that:
http://sqljunkies.com/WebLog/knight_reign/archive/2005/07/06/16015.aspx
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"brynjon" <bryn.jones@.cheshire.gov.uk> wrote in message
news:1178014798.452341.88160@.p77g2000hsh.googlegro ups.com...
> Hi
> I have a 2-node active-passive SQL cluster (with SSIS manually
> clustered in the SQL resource group). I want to convert to active-
> active by installing a named instance on the second node. Am I correct
> in saying that the new virtual server cannot have its own SSIS
> instance but must instead utilise the SSIS instance (& the MSDB
> database) of the first (original) virtual server (because there can
> only be one instance of SSIS on a node) ?
> Having said this, should I move the SSIS resources into (preferably) a
> dedicated SSIS resource group or (alternatively) the cluster/quorum
> group, moving the xml config file and the Packages folder to the
> associated physical disk for that group ?
> tia
> brynjon
>

Integration services notification services

Does anybody know of a notification services using SSIS? Is it SendMail or otherwise? Is there a step-by-step practice on how to create one?Not sure I understand your question? Are you looking to trigger an SSIS package from Notification Services, or are you want to send a notification from the SSIS package?|||I'm hoping it's the later one - send the notification from the SSIS package.|||You can send an email by using the Send Mail Task in SSIS. Is that not what you are looking for?|||

That would possibly the same as DTS! Do you know of any step--by-step article on how to do this in SSIS?

Just out of curiosity, if I were to use SQL Server's Notification services, I'd be make use of SSRS(reporting services) as well, wouldn't I?

|||

If you search the forum for "Send Mail", you should find some links. Also, it's really not a very complicated task to use.

I'm not sure why you are tying Notifiation Services to Reporting Services (or Integration Services, for that matter). They are seperate products, and while they can interact with each other, they do it the same way any external application would interact. For example, SSIS can interact with Reporting Services directly by calling the web service to generate reports (just like any other external application).

|||The reason I mentioned reporting services because I read at a MSDN article that Analysis services uses Repoorting server for Notification Services.|||

onamika wrote:

The reason I mentioned reporting services because I read at a MSDN article that Analysis services uses Repoorting server for Notification Services.

Neither of which are related to SSIS though.

From SSIS look at using the Send Mail task, or using a Web Service to handle notifications.|||Frankly, I don't see any SendMail tasks example or how create an example. Maybe it's because I'm faily new to MSDN.|||

http://forums.microsoft.com/MSDN/Search/Search.aspx?words=Send+Mail&localechoice=9&SiteID=1&searchscope=forumscope&ForumID=80

The second result returned has a walkthrough of using a Send Mail Task inside a For..Each loop. I didn't go through the rest, but I'm sure there are more examples.

|||

onamika wrote:

Frankly, I don't see any SendMail tasks example or how create an example. Maybe it's because I'm faily new to MSDN.

BOL has some information on the Send Mail task inside SSIS.

http://msdn2.microsoft.com/en-us/library/ms142165.aspx|||

onamika wrote:

The reason I mentioned reporting services because I read at a MSDN article that Analysis services uses Repoorting server for Notification Services.

Perhaps the nomenclature is a problem here. Perhaps it should be "notification services" rather than (the product) "Notification Services".

I'd like to read that MSDN article because if that is what it says then it is lying. Analysis Services does not automatically use Reporting Services for anything. You can deliver reports on top of Analysis Services using Reporting Services - but that is something else entirely.

-Jamie

|||

onamika wrote:

Frankly, I don't see any SendMail tasks example or how create an example.

Its very very very very simple. Just try it - witha little perseverance you won't need any documentaiton.

onamika wrote:

Maybe it's because I'm faily new to MSDN.

Not sure what you mean by this. What has MSDN got to do with this? How can you be "new to MDSN"?

-Jamie

|||

I'd like to stick to the point - question about "SendMail" please!

Ok, bonus question for you. This is mostly text based mail I see. How can I make this email HTML based?

|||

onamika wrote:

I'd like to stick to the point - question about "SendMail" please!

Ok, bonus question for you. This is mostly text based mail I see. How can I make this email HTML based?

Drop the Send Mail task in the Control Flow tool box onto the work surface. Double click on it to configure it. From there, I think you should be able to figure out what to put where.

As far as HTML e-mail (WHY? WHY? WHY?), please search this forum for examples. This has been discussed before.

As an aside - HTML is VERY BAD design, in my opinion. HTML should be left to Web servers to display. If I had it my way (and many in the security community) e-mails would be limited to TEXT ONLY. Anyway......... That's my rant for today.