Showing posts with label ssis. Show all posts
Showing posts with label ssis. Show all posts

Friday, March 23, 2012

MSMQ Connection Manager and input into a data flow

Firstly the disclaimer: im a total SSIS bunny.
Ok, with that out of the way...
What i want to do is have data read from an MSMQ message (which will be a simple XML message) and have it transformed and inserted into a simple table. Sound simple, but i cant work out how to do it.

I started a new Integration Services Project and dropped a Data Flow task on the Control flow tab, then double-click it to go to the data flow tab. I have added a MSMQ connection manager which points at my private queue and i can hit the test button and it sees it tests ok. How do i wire that into my data flow?

On my dataflow tab i have a SQL Server Destination (set to the local SQl server instance) and ive been trying a DataReader source and try setting the Advanced Editor "Connection Manager" column on IDBConnection row to point to my MSMQ connection manager. I get some error "cannot aquire a managed connection from the run-time connection manager". How do i get a Data Flow source which reads the XML message off my MSMQ connection manager?

On the Control Flow tab you can add a Message Queue Task and i can bind it to my MSMQ connection manager and set it up as a recieve task etc, but how do i use that?

Help!

Well, like yourself I am a complete MSMQ bunny but nevertheless....

You are right that the MSMQ Task will be of help here. I don't know much about it but I am guessing that it allows you to save the message to a Variable. Am I correct? If so you can do that and then manipulate the data that is in the variable.

That is as much as I know. Like I say I don't have any experience of using MSMQ with SSIS.

-Jamie

|||Hi, yer, i think i can save the message data to a variable, but im a bit lost on which component ill need to transform that message - the XML one would seem the logical choice but i cant seem to bind it to that variable.
|||

Hi

im new in msmQ TASK

I already configured the msmque task for sending. - > connected to dataflowtask -> msmque task for recieving is this the proper way to implement?

MSMQ Connection Manager and input into a data flow

Firstly the disclaimer: im a total SSIS bunny.
Ok, with that out of the way...
What i want to do is have data read from an MSMQ message (which will be a simple XML message) and have it transformed and inserted into a simple table. Sound simple, but i cant work out how to do it.

I started a new Integration Services Project and dropped a Data Flow task on the Control flow tab, then double-click it to go to the data flow tab. I have added a MSMQ connection manager which points at my private queue and i can hit the test button and it sees it tests ok. How do i wire that into my data flow?

On my dataflow tab i have a SQL Server Destination (set to the local SQl server instance) and ive been trying a DataReader source and try setting the Advanced Editor "Connection Manager" column on IDBConnection row to point to my MSMQ connection manager. I get some error "cannot aquire a managed connection from the run-time connection manager". How do i get a Data Flow source which reads the XML message off my MSMQ connection manager?

On the Control Flow tab you can add a Message Queue Task and i can bind it to my MSMQ connection manager and set it up as a recieve task etc, but how do i use that?

Help!

Well, like yourself I am a complete MSMQ bunny but nevertheless....

You are right that the MSMQ Task will be of help here. I don't know much about it but I am guessing that it allows you to save the message to a Variable. Am I correct? If so you can do that and then manipulate the data that is in the variable.

That is as much as I know. Like I say I don't have any experience of using MSMQ with SSIS.

-Jamie

|||Hi, yer, i think i can save the message data to a variable, but im a bit lost on which component ill need to transform that message - the XML one would seem the logical choice but i cant seem to bind it to that variable.
|||

Hi

im new in msmQ TASK

I already configured the msmque task for sending. - > connected to dataflowtask -> msmque task for recieving is this the proper way to implement?

MSMQ & Integration Services

I have a SSIS package in SQL Server 2005 that attempts to publish some data into a MSMQ Private queue located on another machine.

While I was creating the package the connection string was pointing to the local server something like: localhost\private$\csin

Now I'm trying to get to the remote machine but I'm not sure what type of connection string I need to enter:

I tried: mw59\private$\csin where mw59 is the name of the remote machine I need to publish to but that result into a "Invalid Queue Path Name."

I tried: formatname:DIRECT=OS:mw59\private$\csin but that results in "The specified format name does not support the requested operation...."

Any idea?

Thanks,

Thierry

I haven't tried MSMQ in SSIS yet but I know that in SQL 2000 DTS the standard MSMQ task could not access remote private queues. I got around this with a couple of fairly simple/generic VBS tasks.

One task reads a text file into the message body and sends the message to a remote private queue. The second taks reads messages from the queue and merges them into a single text file. The next step in this tasks uses the text file source connection to read the data from the text file into a SQL table.

Dick Campbell

|||

The next Web update of Books Online, scheduled to coincide with Service Pack 1, contains an already-written new topic titled "Sending to a Remote Private Message Queue with the Script Task." Sadly, as we all know, it is not a pretty sight when one attempts to copy and paste the HTML of a BOL topic. Here is my best effort after converting it to plain text:

Sending to a Remote Private Message Queue with the Script Task

Microsoft Windows Message Queuing makes it easy for developers to communicate with application programs quickly and reliably by sending and receiving messages. A message queue may be located on the local computer or a remote computer, and may be public or private. The Integration Services MSMQ Connection Manager and Message Queue Task do not support sending to a private queue on a remote computer. However, by using the Script task, it is easy to send a message to a remote private queue.

Description

The following example uses an existing MSMQ connection manager, together with objects and methods from the System.Messaging namespace, to send the text contained in a package variable to a remote private message queue. The call to the AcquireConnection method of the MSMQ connection manager returns a MessageQueueTask object whose Send method accomplishes this task.

To configure this Script Task example

Create an MSMQ Connection Manager with the default name. Set its path to a valid remote private queue, in the following format:

FORMATNAME:DIRECT=OS:<computername>\private$\<queuename>

Create an Integration Services variable named MessageText of type String to pass the message text into the script. Enter a default message as the value of the variable.

Add a Script Task to the design surface and edit it. On the Script tab of the Script Task Editor, add the MessageText variable to the ReadOnlyVariables property to make the variable available inside the script.

Click Design Script to open the Visual Studio for Applications script editor.

Add a reference in the script project to the System.Messaging namespace.

Replace the contents of the script window with the code in the following section.

Imports System
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Messaging

Public Class ScriptMain

Public Sub Main()

Dim remotePrivateQueue As MessageQueue
Dim messageText As String

remotePrivateQueue = _
DirectCast(Dts.Connections("Message Queue Connection Manager").AcquireConnection(Dts.Transaction), _
MessageQueue)
messageText = DirectCast(Dts.Variables("MessageText").Value, String)
remotePrivateQueue.Send(messageText)

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

-Doug

MSMQ

Does anyone know or have any idea what's the use of msmq or how can this apply in ssis.

Thanks

Find out what MQ is-

Message Queuing (MSMQ)
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/msmq/html/ff917e87-05d5-478f-9430-0f560675ece1.asp)

Find out about the MQ task you have in SSIS-

Message Queue Task
(http://msdn2.microsoft.com/en-us/library/ae1d8fad-6649-4e93-b589-14a32d07da33.aspx)

|||

Do you have a requirement to use MSMQ, or are you just curious about it? If you're just curious about it then google both terms and do some research.

If you have specific questions about either MSMQ or the MSMQ task in SSIS ask them here, and I will try my best to answer them for you.

|||

can you help me how am i going to use this the "MSMQ task in SSIS"? thanks

Sean Schade wrote:

Do you have a requirement to use MSMQ, or are you just curious about it? If you're just curious about it then google both terms and do some research.

If you have specific questions about either MSMQ or the MSMQ task in SSIS ask them here, and I will try my best to answer them for you.

|||

Can you help us and explain what you are trying to achieve? The task is there, documented, and does what it says on the tin. Do you want to send a message? Do you want to recieve a message?

Have you read any of the references or do you have any specific questions?

Do you know how to create a SSIS package?

Can you add a task to a package? (Hint - Drag and drop from the toolbox)

Now open the the task, and fill in the details required. What could be simpler?

How you use it in the context of a wider application or process is an impossible question on its own as we have no idea what you need to do!

|||

Hi All,

I am going to build an application that is transaction sensitive with asp.net, C# and SQL Server. There will be a folder which will have the text files and I am writing a windows service that picks up these text files and inserts them into SQL Server database. In case, if the SQL Server is down I should not lose any transactions and based on what I read I am thinking that MSMQ is the option wherein I can queue the transactions and insert them into the database once the SQL server is up and running.

Please suggest if this is correct approach or if there is any other alternatvie.

Thanks,

Thanvi

Monday, March 19, 2012

Msg 601 - bizarre

Hi - we are on SQL 2005 and I jsut recently applied SP1. We have a reportin
g
database that I load each night with a SSIS package - PK's are dropped then
readded at the end - I am getting the strangest error rebuilding one PK on
one table - I have tested adding the PK thru the GUI with the same error -
all the doc on this error suggests it comes from using hints to change
isolation levels running queries, but I am simply trying to add a Primary
Key. Anyone seen this or ahve any ideas? Thanks so much, I've commented ou
t
the offending line for now but would like to get the PK back on this table -
I also ran a clean DBCC checktable. here is the statement and the error -
(Select a.name from sysobjects a where a.xtype = 'PK'
and a.name = 'PK_drug_test') Alter Table [dbo].[drug_test] ADD
Constraint PK_drug_test Primary Key Clustered
(
[test_id],
[lcd_id]
) ON [PRIMARY]
Msg 601, Level 12, State 3, Line 2
Could not continue scan with NOLOCK due to data movement.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
The statement has been terminated.
John F.Hi
Did you change the Isolation level in the Tools/Options/Query Execution/SQL
Server
or Query/Query Options/Advanced settings in Managerment Studio? If not then
you may want to check what is being set by using SQL Profiler.
John
"John F." wrote:

> Hi - we are on SQL 2005 and I jsut recently applied SP1. We have a report
ing
> database that I load each night with a SSIS package - PK's are dropped the
n
> readded at the end - I am getting the strangest error rebuilding one PK on
> one table - I have tested adding the PK thru the GUI with the same error -
> all the doc on this error suggests it comes from using hints to change
> isolation levels running queries, but I am simply trying to add a Primary
> Key. Anyone seen this or ahve any ideas? Thanks so much, I've commented
out
> the offending line for now but would like to get the PK back on this table
-
> I also ran a clean DBCC checktable. here is the statement and the error -
> (Select a.name from sysobjects a where a.xtype = 'PK'
> and a.name = 'PK_drug_test') Alter Table [dbo].[drug_test] ADD
> Constraint PK_drug_test Primary Key Clustered
> (
> [test_id],
> [lcd_id]
> ) ON [PRIMARY]
> Msg 601, Level 12, State 3, Line 2
> Could not continue scan with NOLOCK due to data movement.
> Msg 1750, Level 16, State 0, Line 2
> Could not create constraint. See previous errors.
> The statement has been terminated.
> --
> John F.|||"John F." <juantana@.newsgroups.microsoft.com> wrote in message
news:EF5E716D-5896-4207-96F6-F9928EDCA403@.microsoft.com...
> Hi - we are on SQL 2005 and I jsut recently applied SP1. We have a
> reporting
> database that I load each night with a SSIS package - PK's are dropped
> then
> readded at the end -
You shouldn't be dropping and readding clustered PK's. You _might_ want to
drop non-clustered indexes and rebuild them at the end. But loading the
data into a page heap and then moving all the data pages into a clustered
index is unlikely to be faster than loading the data into the clustered
index to begin with. And since it completely recreates the table, you might
as well load into a staging table.
Also in SQL 2005 instead of dropping secondary indexes, you can just disable
them and rebuild them later. This is much easier to script and manage you
don't have to hard-code all the index definitions in the load script.
David|||we did extensive testing and leaving the PK's (clustered or non) made the
SSIS load come to a complete crawl as it tried to maintain/update them - we
do a complete refresh every time so we are a bit of a different scenario.
Back to the original issue - the isolation level is not being changed at all
during this and the error doesn't seem to match the activity at all.
--
John F.
"David Browne" wrote:

> "John F." <juantana@.newsgroups.microsoft.com> wrote in message
> news:EF5E716D-5896-4207-96F6-F9928EDCA403@.microsoft.com...
> You shouldn't be dropping and readding clustered PK's. You _might_ want t
o
> drop non-clustered indexes and rebuild them at the end. But loading the
> data into a page heap and then moving all the data pages into a clustered
> index is unlikely to be faster than loading the data into the clustered
> index to begin with. And since it completely recreates the table, you mig
ht
> as well load into a staging table.
> Also in SQL 2005 instead of dropping secondary indexes, you can just disab
le
> them and rebuild them later. This is much easier to script and manage you
> don't have to hard-code all the index definitions in the load script.
> David
>
>|||Hi John
You may want to try explicitly setting the ONLINE option to OFF and the
MAXDOP option to 1.
John
"John F." wrote:
[vbcol=seagreen]
> we did extensive testing and leaving the PK's (clustered or non) made the
> SSIS load come to a complete crawl as it tried to maintain/update them - w
e
> do a complete refresh every time so we are a bit of a different scenario.
> Back to the original issue - the isolation level is not being changed at a
ll
> during this and the error doesn't seem to match the activity at all.
> --
> John F.
>
> "David Browne" wrote:
>|||hey, thanks to everyone for the help - I will try explicitly setting the
ONLINE to off and the MAXDOP to 1. FYI if it makes any diff this is a 64 bi
t
server jsut recently updated to SP1. David in regards to your comment, I am
changing the previous developer's code to build the PK's as non-clustered as
well. Is there a better way to disable them to load the tons of data we loa
d
every night and then re-enable? we reaplce all data for any given district
when we receive their files, but the other data in the database stays the
same until those corresponding districts are rec'd.
We are getting a few strange erros on this server suddenly - may not be
related but last night my maint plan ran out of stack space on and update
statistics as well.
Thanks again for the help, guys.
--
John F.
"David Browne" wrote:

> "John F." <juantana@.newsgroups.microsoft.com> wrote in message
> news:EF5E716D-5896-4207-96F6-F9928EDCA403@.microsoft.com...
> You shouldn't be dropping and readding clustered PK's. You _might_ want t
o
> drop non-clustered indexes and rebuild them at the end. But loading the
> data into a page heap and then moving all the data pages into a clustered
> index is unlikely to be faster than loading the data into the clustered
> index to begin with. And since it completely recreates the table, you mig
ht
> as well load into a staging table.
> Also in SQL 2005 instead of dropping secondary indexes, you can just disab
le
> them and rebuild them later. This is much easier to script and manage you
> don't have to hard-code all the index definitions in the load script.
> David
>
>|||John, that worked! Thanks so much - I would've never figured out to
exlpicitly set MAXDOP on a PK rebuild, wonder why it works for awhile withou
t
those options set and then starts throwing the error though? So weird.
Thanks again!
--
John F.
"John Bell" wrote:
[vbcol=seagreen]
> Hi John
> You may want to try explicitly setting the ONLINE option to OFF and the
> MAXDOP option to 1.
> John
> "John F." wrote:
>|||John - that worked! Thanks so much for your help, the PK rebuilt just fine.
We started getting the error on another table too so I guess I can change
them to nonclustered and then add those two options for all - wouldve never
thought to explicitly set the degree of parallelism - wonder why it works w/
o
those options for awhile and then starts failing throwing the error all of a
sudden? anyway thanks again!
--
John F.
"John Bell" wrote:
[vbcol=seagreen]
> Hi John
> You may want to try explicitly setting the ONLINE option to OFF and the
> MAXDOP option to 1.
> John
> "John F." wrote:
>|||Hi
I guess it depends on how much work the rebuild is estimated to take and/or
if it thinks it can do parts in parallel. I guess it thought wrong!! You may
want to log this at [url]http://lab.msdn.microsoft.com/productfeedback/default.aspx[/ur
l]
John
"John F." wrote:
[vbcol=seagreen]
> John, that worked! Thanks so much - I would've never figured out to
> exlpicitly set MAXDOP on a PK rebuild, wonder why it works for awhile with
out
> those options set and then starts throwing the error though? So weird.
> Thanks again!
> --
> John F.
>
> "John Bell" wrote:
>|||I will go log it - FYI I can take off the options you suggest and run the PK
nonclustered just fine as well, so it may be more of an issue of shuffling a
huge amount of data around on disk after the fact - this is running late at
night so I dont think its fighting with other processes. So the answer is
either build them nonclustered or if building clustered use the MAXDOP=1 and
ONLINE=NO options - thanks again.
--
John F.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> I guess it depends on how much work the rebuild is estimated to take and/o
r
> if it thinks it can do parts in parallel. I guess it thought wrong!! You m
ay
> want to log this at [url]http://lab.msdn.microsoft.com/productfeedback/default.aspx[/
url]
> John
> "John F." wrote:
>

Msg 601 - bizarre

Hi - we are on SQL 2005 and I jsut recently applied SP1. We have a reporting
database that I load each night with a SSIS package - PK's are dropped then
readded at the end - I am getting the strangest error rebuilding one PK on
one table - I have tested adding the PK thru the GUI with the same error -
all the doc on this error suggests it comes from using hints to change
isolation levels running queries, but I am simply trying to add a Primary
Key. Anyone seen this or ahve any ideas? Thanks so much, I've commented out
the offending line for now but would like to get the PK back on this table -
I also ran a clean DBCC checktable. here is the statement and the error -
(Select a.name from sysobjects a where a.xtype = 'PK'
and a.name = 'PK_drug_test') Alter Table [dbo].[drug_test] ADD
Constraint PK_drug_test Primary Key Clustered
(
[test_id],
[lcd_id]
) ON [PRIMARY]
Msg 601, Level 12, State 3, Line 2
Could not continue scan with NOLOCK due to data movement.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
The statement has been terminated.
--
John F.Hi
Did you change the Isolation level in the Tools/Options/Query Execution/SQL
Server
or Query/Query Options/Advanced settings in Managerment Studio? If not then
you may want to check what is being set by using SQL Profiler.
John
"John F." wrote:
> Hi - we are on SQL 2005 and I jsut recently applied SP1. We have a reporting
> database that I load each night with a SSIS package - PK's are dropped then
> readded at the end - I am getting the strangest error rebuilding one PK on
> one table - I have tested adding the PK thru the GUI with the same error -
> all the doc on this error suggests it comes from using hints to change
> isolation levels running queries, but I am simply trying to add a Primary
> Key. Anyone seen this or ahve any ideas? Thanks so much, I've commented out
> the offending line for now but would like to get the PK back on this table -
> I also ran a clean DBCC checktable. here is the statement and the error -
> (Select a.name from sysobjects a where a.xtype = 'PK'
> and a.name = 'PK_drug_test') Alter Table [dbo].[drug_test] ADD
> Constraint PK_drug_test Primary Key Clustered
> (
> [test_id],
> [lcd_id]
> ) ON [PRIMARY]
> Msg 601, Level 12, State 3, Line 2
> Could not continue scan with NOLOCK due to data movement.
> Msg 1750, Level 16, State 0, Line 2
> Could not create constraint. See previous errors.
> The statement has been terminated.
> --
> John F.|||"John F." <juantana@.newsgroups.microsoft.com> wrote in message
news:EF5E716D-5896-4207-96F6-F9928EDCA403@.microsoft.com...
> Hi - we are on SQL 2005 and I jsut recently applied SP1. We have a
> reporting
> database that I load each night with a SSIS package - PK's are dropped
> then
> readded at the end -
You shouldn't be dropping and readding clustered PK's. You _might_ want to
drop non-clustered indexes and rebuild them at the end. But loading the
data into a page heap and then moving all the data pages into a clustered
index is unlikely to be faster than loading the data into the clustered
index to begin with. And since it completely recreates the table, you might
as well load into a staging table.
Also in SQL 2005 instead of dropping secondary indexes, you can just disable
them and rebuild them later. This is much easier to script and manage you
don't have to hard-code all the index definitions in the load script.
David|||we did extensive testing and leaving the PK's (clustered or non) made the
SSIS load come to a complete crawl as it tried to maintain/update them - we
do a complete refresh every time so we are a bit of a different scenario.
Back to the original issue - the isolation level is not being changed at all
during this and the error doesn't seem to match the activity at all.
--
John F.
"David Browne" wrote:
> "John F." <juantana@.newsgroups.microsoft.com> wrote in message
> news:EF5E716D-5896-4207-96F6-F9928EDCA403@.microsoft.com...
> > Hi - we are on SQL 2005 and I jsut recently applied SP1. We have a
> > reporting
> > database that I load each night with a SSIS package - PK's are dropped
> > then
> > readded at the end -
> You shouldn't be dropping and readding clustered PK's. You _might_ want to
> drop non-clustered indexes and rebuild them at the end. But loading the
> data into a page heap and then moving all the data pages into a clustered
> index is unlikely to be faster than loading the data into the clustered
> index to begin with. And since it completely recreates the table, you might
> as well load into a staging table.
> Also in SQL 2005 instead of dropping secondary indexes, you can just disable
> them and rebuild them later. This is much easier to script and manage you
> don't have to hard-code all the index definitions in the load script.
> David
>
>|||Hi John
You may want to try explicitly setting the ONLINE option to OFF and the
MAXDOP option to 1.
John
"John F." wrote:
> we did extensive testing and leaving the PK's (clustered or non) made the
> SSIS load come to a complete crawl as it tried to maintain/update them - we
> do a complete refresh every time so we are a bit of a different scenario.
> Back to the original issue - the isolation level is not being changed at all
> during this and the error doesn't seem to match the activity at all.
> --
> John F.
>
> "David Browne" wrote:
> >
> > "John F." <juantana@.newsgroups.microsoft.com> wrote in message
> > news:EF5E716D-5896-4207-96F6-F9928EDCA403@.microsoft.com...
> > > Hi - we are on SQL 2005 and I jsut recently applied SP1. We have a
> > > reporting
> > > database that I load each night with a SSIS package - PK's are dropped
> > > then
> > > readded at the end -
> >
> > You shouldn't be dropping and readding clustered PK's. You _might_ want to
> > drop non-clustered indexes and rebuild them at the end. But loading the
> > data into a page heap and then moving all the data pages into a clustered
> > index is unlikely to be faster than loading the data into the clustered
> > index to begin with. And since it completely recreates the table, you might
> > as well load into a staging table.
> >
> > Also in SQL 2005 instead of dropping secondary indexes, you can just disable
> > them and rebuild them later. This is much easier to script and manage you
> > don't have to hard-code all the index definitions in the load script.
> >
> > David
> >
> >
> >
> >|||hey, thanks to everyone for the help - I will try explicitly setting the
ONLINE to off and the MAXDOP to 1. FYI if it makes any diff this is a 64 bit
server jsut recently updated to SP1. David in regards to your comment, I am
changing the previous developer's code to build the PK's as non-clustered as
well. Is there a better way to disable them to load the tons of data we load
every night and then re-enable? we reaplce all data for any given district
when we receive their files, but the other data in the database stays the
same until those corresponding districts are rec'd.
We are getting a few strange erros on this server suddenly - may not be
related but last night my maint plan ran out of stack space on and update
statistics as well.
Thanks again for the help, guys.
--
John F.
"David Browne" wrote:
> "John F." <juantana@.newsgroups.microsoft.com> wrote in message
> news:EF5E716D-5896-4207-96F6-F9928EDCA403@.microsoft.com...
> > Hi - we are on SQL 2005 and I jsut recently applied SP1. We have a
> > reporting
> > database that I load each night with a SSIS package - PK's are dropped
> > then
> > readded at the end -
> You shouldn't be dropping and readding clustered PK's. You _might_ want to
> drop non-clustered indexes and rebuild them at the end. But loading the
> data into a page heap and then moving all the data pages into a clustered
> index is unlikely to be faster than loading the data into the clustered
> index to begin with. And since it completely recreates the table, you might
> as well load into a staging table.
> Also in SQL 2005 instead of dropping secondary indexes, you can just disable
> them and rebuild them later. This is much easier to script and manage you
> don't have to hard-code all the index definitions in the load script.
> David
>
>|||John, that worked! Thanks so much - I would've never figured out to
exlpicitly set MAXDOP on a PK rebuild, wonder why it works for awhile without
those options set and then starts throwing the error though? So weird.
Thanks again!
--
John F.
"John Bell" wrote:
> Hi John
> You may want to try explicitly setting the ONLINE option to OFF and the
> MAXDOP option to 1.
> John
> "John F." wrote:
> > we did extensive testing and leaving the PK's (clustered or non) made the
> > SSIS load come to a complete crawl as it tried to maintain/update them - we
> > do a complete refresh every time so we are a bit of a different scenario.
> > Back to the original issue - the isolation level is not being changed at all
> > during this and the error doesn't seem to match the activity at all.
> > --
> > John F.
> >
> >
> > "David Browne" wrote:
> >
> > >
> > > "John F." <juantana@.newsgroups.microsoft.com> wrote in message
> > > news:EF5E716D-5896-4207-96F6-F9928EDCA403@.microsoft.com...
> > > > Hi - we are on SQL 2005 and I jsut recently applied SP1. We have a
> > > > reporting
> > > > database that I load each night with a SSIS package - PK's are dropped
> > > > then
> > > > readded at the end -
> > >
> > > You shouldn't be dropping and readding clustered PK's. You _might_ want to
> > > drop non-clustered indexes and rebuild them at the end. But loading the
> > > data into a page heap and then moving all the data pages into a clustered
> > > index is unlikely to be faster than loading the data into the clustered
> > > index to begin with. And since it completely recreates the table, you might
> > > as well load into a staging table.
> > >
> > > Also in SQL 2005 instead of dropping secondary indexes, you can just disable
> > > them and rebuild them later. This is much easier to script and manage you
> > > don't have to hard-code all the index definitions in the load script.
> > >
> > > David
> > >
> > >
> > >
> > >|||John - that worked! Thanks so much for your help, the PK rebuilt just fine.
We started getting the error on another table too so I guess I can change
them to nonclustered and then add those two options for all - wouldve never
thought to explicitly set the degree of parallelism - wonder why it works w/o
those options for awhile and then starts failing throwing the error all of a
sudden? anyway thanks again!
--
John F.
"John Bell" wrote:
> Hi John
> You may want to try explicitly setting the ONLINE option to OFF and the
> MAXDOP option to 1.
> John
> "John F." wrote:
> > we did extensive testing and leaving the PK's (clustered or non) made the
> > SSIS load come to a complete crawl as it tried to maintain/update them - we
> > do a complete refresh every time so we are a bit of a different scenario.
> > Back to the original issue - the isolation level is not being changed at all
> > during this and the error doesn't seem to match the activity at all.
> > --
> > John F.
> >
> >
> > "David Browne" wrote:
> >
> > >
> > > "John F." <juantana@.newsgroups.microsoft.com> wrote in message
> > > news:EF5E716D-5896-4207-96F6-F9928EDCA403@.microsoft.com...
> > > > Hi - we are on SQL 2005 and I jsut recently applied SP1. We have a
> > > > reporting
> > > > database that I load each night with a SSIS package - PK's are dropped
> > > > then
> > > > readded at the end -
> > >
> > > You shouldn't be dropping and readding clustered PK's. You _might_ want to
> > > drop non-clustered indexes and rebuild them at the end. But loading the
> > > data into a page heap and then moving all the data pages into a clustered
> > > index is unlikely to be faster than loading the data into the clustered
> > > index to begin with. And since it completely recreates the table, you might
> > > as well load into a staging table.
> > >
> > > Also in SQL 2005 instead of dropping secondary indexes, you can just disable
> > > them and rebuild them later. This is much easier to script and manage you
> > > don't have to hard-code all the index definitions in the load script.
> > >
> > > David
> > >
> > >
> > >
> > >|||Hi
I guess it depends on how much work the rebuild is estimated to take and/or
if it thinks it can do parts in parallel. I guess it thought wrong!! You may
want to log this at http://lab.msdn.microsoft.com/productfeedback/default.aspx
John
"John F." wrote:
> John, that worked! Thanks so much - I would've never figured out to
> exlpicitly set MAXDOP on a PK rebuild, wonder why it works for awhile without
> those options set and then starts throwing the error though? So weird.
> Thanks again!
> --
> John F.
>
> "John Bell" wrote:
> > Hi John
> >
> > You may want to try explicitly setting the ONLINE option to OFF and the
> > MAXDOP option to 1.
> >
> > John
> >
> > "John F." wrote:
> >
> > > we did extensive testing and leaving the PK's (clustered or non) made the
> > > SSIS load come to a complete crawl as it tried to maintain/update them - we
> > > do a complete refresh every time so we are a bit of a different scenario.
> > > Back to the original issue - the isolation level is not being changed at all
> > > during this and the error doesn't seem to match the activity at all.
> > > --
> > > John F.
> > >
> > >
> > > "David Browne" wrote:
> > >
> > > >
> > > > "John F." <juantana@.newsgroups.microsoft.com> wrote in message
> > > > news:EF5E716D-5896-4207-96F6-F9928EDCA403@.microsoft.com...
> > > > > Hi - we are on SQL 2005 and I jsut recently applied SP1. We have a
> > > > > reporting
> > > > > database that I load each night with a SSIS package - PK's are dropped
> > > > > then
> > > > > readded at the end -
> > > >
> > > > You shouldn't be dropping and readding clustered PK's. You _might_ want to
> > > > drop non-clustered indexes and rebuild them at the end. But loading the
> > > > data into a page heap and then moving all the data pages into a clustered
> > > > index is unlikely to be faster than loading the data into the clustered
> > > > index to begin with. And since it completely recreates the table, you might
> > > > as well load into a staging table.
> > > >
> > > > Also in SQL 2005 instead of dropping secondary indexes, you can just disable
> > > > them and rebuild them later. This is much easier to script and manage you
> > > > don't have to hard-code all the index definitions in the load script.
> > > >
> > > > David
> > > >
> > > >
> > > >
> > > >|||I will go log it - FYI I can take off the options you suggest and run the PK
nonclustered just fine as well, so it may be more of an issue of shuffling a
huge amount of data around on disk after the fact - this is running late at
night so I dont think its fighting with other processes. So the answer is
either build them nonclustered or if building clustered use the MAXDOP=1 and
ONLINE=NO options - thanks again.
--
John F.
"John Bell" wrote:
> Hi
> I guess it depends on how much work the rebuild is estimated to take and/or
> if it thinks it can do parts in parallel. I guess it thought wrong!! You may
> want to log this at http://lab.msdn.microsoft.com/productfeedback/default.aspx
> John
> "John F." wrote:
> > John, that worked! Thanks so much - I would've never figured out to
> > exlpicitly set MAXDOP on a PK rebuild, wonder why it works for awhile without
> > those options set and then starts throwing the error though? So weird.
> > Thanks again!
> > --
> > John F.
> >
> >
> > "John Bell" wrote:
> >
> > > Hi John
> > >
> > > You may want to try explicitly setting the ONLINE option to OFF and the
> > > MAXDOP option to 1.
> > >
> > > John
> > >
> > > "John F." wrote:
> > >
> > > > we did extensive testing and leaving the PK's (clustered or non) made the
> > > > SSIS load come to a complete crawl as it tried to maintain/update them - we
> > > > do a complete refresh every time so we are a bit of a different scenario.
> > > > Back to the original issue - the isolation level is not being changed at all
> > > > during this and the error doesn't seem to match the activity at all.
> > > > --
> > > > John F.
> > > >
> > > >
> > > > "David Browne" wrote:
> > > >
> > > > >
> > > > > "John F." <juantana@.newsgroups.microsoft.com> wrote in message
> > > > > news:EF5E716D-5896-4207-96F6-F9928EDCA403@.microsoft.com...
> > > > > > Hi - we are on SQL 2005 and I jsut recently applied SP1. We have a
> > > > > > reporting
> > > > > > database that I load each night with a SSIS package - PK's are dropped
> > > > > > then
> > > > > > readded at the end -
> > > > >
> > > > > You shouldn't be dropping and readding clustered PK's. You _might_ want to
> > > > > drop non-clustered indexes and rebuild them at the end. But loading the
> > > > > data into a page heap and then moving all the data pages into a clustered
> > > > > index is unlikely to be faster than loading the data into the clustered
> > > > > index to begin with. And since it completely recreates the table, you might
> > > > > as well load into a staging table.
> > > > >
> > > > > Also in SQL 2005 instead of dropping secondary indexes, you can just disable
> > > > > them and rebuild them later. This is much easier to script and manage you
> > > > > don't have to hard-code all the index definitions in the load script.
> > > > >
> > > > > David
> > > > >
> > > > >
> > > > >
> > > > >

Friday, March 9, 2012

MsDtsSrvr.ini.xml

Does anyone have a copy of this file from a clustered SQL2005 box with SSIS running? I would to see what you have. I cannot seem to get mine to work.

Thanks David

Hi, you probably already have read this, but just to make sure, the following article on clustering, steps 9-14 deal with the config file.

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

If that does not help the some more info is likely needed such as, are you getting any errors? If so, then what? If not then perhaps posting the acutal contents might be good.

Hope that helps.

|||Thanks to all that replied. I have resolved this issue. I had set the registry entry (that tells SSIS where config file resides) one of my nodes on the cluster and not the other..

MsDtsSrvr.exe memory leak?

In our current environment, we are running about 10,000 packages per day on a given 64 bit SSIS server.

The MSDtsSrvr.exe (SSIS service) process' memory working set size continues to grow until it consumes the memory on the machine.

Some of the packages do use a custom component. Could this be responsible for leaking memory in the MSDtsSrvr.exe process? Has a memory leak been confirmed in this process for the "first-party" components anyway?

More information about the possible memory leak.

The packages are run in in a CLR process with calls Package.Execute(), rather than being executed via dtexec or dtexecui or the designer. The reason for this approach is the ability to write a custom event handler with access to the package pointer, which other log handlers cannot do. The process runs Package.Execute() and exits, so there are 10,000 short-lived processes, each of which call Package.Execute() once.

To be slightly more specific about what the packages are doing, each of the 10,000 packages generally correlates to 1 source file, loading in dummy (aka inferred) dimension records, and then populating a fact table via a surrogate key pipeline.

Wednesday, March 7, 2012

MSDTC problem ... I think ..

I have a SSIS package in which there are lot of Sequence Containers. Each sequence container has two Execute Sql tasks which brings 2 values, and if the comparison of the value is true, it runs a data flow task. Comparison is done using Expression.

If any of the sequence container fails the comparison, I wanted whole package to rollback so I had to use Transactions. I set "Required Transaction" on the package and "Supports Transaction" on each Sequence Container and Tasks respectively.

Now that I am trying to run the package, it runs few sequence containers fine and on few it gives this error.

[Execute SQL Task] Error: Executing the query "SELECT ISNULL(MAX(ClaimBillTypeKey), -1) ClaimBillTypeKey FROM dbo.Dim_ClaimBillType" failed with the following error: "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Also, this is not consistent, one time one Seq. Cont will fail and next time it will run fine but something else will fail. What is going on?

Hello, please anyone? Help?|||

I encountered this issue on the RTM version of SSIS. It seem to occur primarily when we were accessing a SQL Server that was behind a firewall. We never did find a solution - we ended up implementing our own transaction and recovery logic.

I haven't tried it in SP1 or SP2, so I'm not sure if might have been fixed by one of those releases.