PackageID And GUIDs In ExecuteSQL Task

Jan 18, 2006

Am I looking at a potential bug here or do I not understand the feature properly? I have an ExecuteSQL task that inserts into a table for logging and includes the System::PackageID as one of the values. It's stored in my table as a uniqueidentifier. When I set the output variable in Parameter Mappings tab of the Execute SQL task to VarChar, all works great. WHen I set it to GUID as the data type in that tab, it outputs a different GUID than the actual System::PackageID variable.

-- Brian

View 1 Replies


ADVERTISEMENT

Execute DTS 2000 Package Task: Invalid GUID When Setting PackageId Via Expression

May 24, 2007

Hi there,

I'm creating an SSIS package that will execute legacy dts packages. The package to be executed is decided at runtime using a sql query task.

Executing a dts package statically works fine, but when I try to set the details of the dts to be run via expressions, I get the error below.

To make it dynamic, I created a variable of type string, and put the package name in here. I also have a string variable for the packageid. Then I set up an expression on the Execute DTS 2000 Package Task that sets the PackageName & PackageID property to this variable.

The PackageId is a string variable I've retrieved using:

select top 1 name,
cast(id AS varchar(50)) id,
cast(versionid AS varchar(50)) versionid
from sysdtspackages
where name = @PackageName
order by createdate desc

When I use the task to set the package id it works find (by selecting a dts, then changing the name), but when I try to provide the package id I get this message:


Error: 0x0 at Execute DTS 2000 Package Task: System.Runtime.InteropServices.COMException (0x8004040E): Invalid GUID specified.
at DTS.PackageClass.LoadFromSQLServer(String ServerName, String ServerUserName, String ServerPassword, DTSSQLServerStorageFlags Flags, String PackagePassword, String PackageGuid, String PackageVersionGuid, String PackageName, Object& pVarPersistStgOfHost)
at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.PackageUtils.LoadDTS8Package(String pkgSrc, String sourceUser, String sourcePwd, Int32 srcType, Boolean bUseTrustedConnection, String packageName, String packagePassword, String packageID, String packageVersionGUID)
at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.LoadPackage()
at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()
Task failed: Execute DTS 2000 Package Task

but the method signature specifies String PackageGuid, and it is a string..

any ideas??

i tried casting the variable like so:

(DT_GUID) @[User:TSPkgId], as the versionid is a uniqueidentifier on sysdtspackages.. It didn't like that at all (can't cast from type DT_WSTR to DT_GUID error code 0xC00470C2)

View 8 Replies View Related

ExecuteSQL Task With Parameters

Jul 14, 2005

I have an insert statement that i am trying to parameterize based on a variable.  I have tried:

View 23 Replies View Related

ExecuteSQL Task Has Changed

Jun 19, 2005

Since the last IDW.

View 10 Replies View Related

ExecuteSQL Task Fails And I Think It Should Not

Jun 19, 2005

I setup my ExecuteSQL task to have a "Single Row" resultset.  The query returns no rows.  It fails.  I don't think it should but then maybe this is consistent with the lookup transform piping rows down the error output if there is not a lookup value returned.

View 9 Replies View Related

Logging Query Messages From ExecuteSQL Task

Dec 15, 2006

The ExecuteSQL task makes it easy to capture the resultset of a query or stored proc in a variable. But what about the messages that would normally appear in the Messages tab when running queries in Management Studio or Query Analyzer? There must be a way to display and log rowcounts and other messages that a query normally produces.
 
Various package/task logging configrations haven't helped. I've read discussions about logging rowcounts in data flow tasks, but what about capturing messages in ExecuteSQL and other control flow tasks that run T-SQL?
 
I only have enough time to dabble in this technology so I may be missing something obvious or approaching this incorrectly. Any suggestion, reference, constructive criticism or reponse would be appreciated.
 

View 15 Replies View Related

Getting Permission Denied Error In ExecuteSQL Task

Aug 21, 2006

The purpose of the Package is to load an SQL table with data from text files in a subdirectory. The parameter is parsed from the text filenames. It is a zipcode.

I have an ExecuteSQL task inside a ForEach container. The SQL in the task is:

IF EXISTS (SELECT * FROM dbo.ZipCodeRadius
WHERE CenterPostalCode = ?1)
BEGIN
DELETE FROM dbo.ZipCodeRadius
WHERE CenterPostalCode = ?1
END

I also have a Data Flow task in this container which populates the ZipCodeRadius table. The same parameter is also passed to the Data Flow task which specifies a zipcode to delete and afterward load.

I am using Windows authetication for the package.
What am I doing wrong?

Fred

View 21 Replies View Related

Error Occurring In ExecuteSQL Task In OnPreExecute (and In OnPostExecute) But Package Not Failing

Jun 5, 2006

When any of
my tasks or script tasks in my control flow, or data flow, have an
error, the entire package, and then the calling package fail, as far as
I've seen, through some CTP versions, beta versions, and the release
version of SQL Server 2005.

But, I've just made a change, in an
sproc called from an ExecuteSQL Task in a PreExecute event attached to
a DataFlow, and that ExecuteSQL Task is reporting an error in the
output window, but the package execution is not being stopped. Why not?

I see something like so

Error: 0xC002F309 at MyExecuteSqlTaskName, Execute SQL Task: ....
Task failed: MyExecuteSqlTaskName
Warning:
0x80019002 at OnPreExecute: The Execution method succeeded, but the
number of errors raised (1) reached the maximum allowed (1); resulting
in failure. This occurs when the number of errors reaches the number
specified in MaximumErrorCount. Change the MaximumErrorCount or fix the
errors.

repeated many times in the output stream, and the same thing for the PostExecute event attached to the same object.

I'm disappointed, because I want errors propagated upwards, as I'm used to.

I
looked, and as far as I can tell, all my ExecuteSQL tasks (in events
and in the regular control flow) have the default settings of

FailPackageOnFailure: False
FailParentOnFailure: False
MaxErrorCount: 1


Do
I have to go revise these settings on every ExecuteSQL Task in every
event handler in every SSIS package? (That will be exceedingly
tedious.) Do I set these on the ExecuteSQL task inside the event? Do I
set these on the event handler itself?

View 3 Replies View Related

PackageID In Logging Provider

Jan 11, 2006

Is there a technical reason (I imagine the real reason is time) that PackageID wasn't included as a Logging Provider column to log? While you can still track this by doing some fun things like event handlers or self-joining, I would think from a usability perspective, you should avoid that altogether by just adding the column.

I added a suggestion here to vote on if anyone else agrees:

http://lab.msdn.microsoft.com/ProductFeedback/viewfeedback.aspx?feedbackid=e58141d5-859f-4941-a675-cb9352d85575

-- Brian

View 1 Replies View Related

Sequential Guids

Oct 31, 2007

 Hello,I am creating a new database and I was advised to use Sequential Guids.I was reading some information and, as far as I understood, I can use NEWSEQUENTIALID. This can be used when I have a uniqueidentifier column as the key of a clustered index to avoid fragmentation during insert. Ok, so I use NEWSEQUENTIALID instead of NEWID.But I will use LINQ most of the time instead of Stored Procedures.So can I specify in my tables scripts to use Sequential Guids when, for example, a record is created? And am I right when using Sequential Guids?Here is a part of my code:-- Blogs ...
create table dbo.Blogs
(
BlogID uniqueidentifier not null
constraint PK_Blog primary key clustered,
Title nvarchar(400) null,
Description nvarchar(2000) null,
CreatedDate datetime null
)

-- Posts ...
create table dbo.Posts
(
PostID uniqueidentifier not null
constraint PK_Post primary key clustered,
BlogID uniqueidentifier not null,
AuthorID uniqueidentifier not null,
Title nchar(1000) null,
Body nvarchar(max) null,
UpdatedDate datetime not null,
IsPublished bit not null,
constraint FK_Posts_Blogs
foreign key(BlogID)
references dbo.Blogs(BlogID)
on delete cascade,
constraint FK_Posts_Users
foreign key(AuthorID)
references dbo.Users(UserID)
on delete cascade Thanks,Miguel

View 2 Replies View Related

Using Guids Instead Of Identities

Sep 21, 2005

I'm really sorry if this is too OT but it is related to replication...

View 1 Replies View Related

Frustration With UniqueIdentifiers (GUIDs)

Feb 26, 2004

We're trying to set a new standard at my office, of using GUIDs as database ID fields - all new tables in all databases will have a GUID as the ID field, using the UniqueIdentifier data type.

The problem that we are running into, is that different applications interpret the UniqueIdentifier data type differently - ADO 2.x interprets it with the opening and closing { } braces. ADO.NET does not include the { and } in the GUID structure, SQL Server Query Analyzer does not include the { } braces, and SQL Server Enterprise Manage does include the { }

so we end up with results like this:

ADO 2.x: {012345678-abcd-ef01-2345-6789abcd}
ADO.NET: 012345678-abcd-ef01-2345-6789abcd
Query Analyzer: 012345678-abcd-ef01-2345-6789abcd
Enterprise Manager: {012345678-abcd-ef01-2345-6789abcd}

The problem with this is in two parts:
1) VB6 does not have a GUID structure or data type, so we are treating GUIDs as strings... but VB6 doesn't recognize "{012345678-abcd-ef01-2345-6789abcd}" and "012345678-abcd-ef01-2345-6789abcd" as the same string
2) when sending a URL link through an email, the { } braces break the link - in all email applications that we have tested. This include Groupwise 6, Groupwise 6.5, IPSwitch Web IMail, Outlook 2000, and Outlook Express (IE 6).

We're becoming very frustrated with the problems at hand, and need to know how others have worked around these problems. Please respond with any kind of advice or any real life situations where you have encountered this and found a solution, etc.

Thanks.

View 3 Replies View Related

Character Len Probelm With Executesql

Jun 15, 2007

I need to create some tables dynamically using sp_executesql. But the problem is the length of sql string is more the 4000 characters. How can I solve this problem?
Thanks

View 3 Replies View Related

SSIS ExecuteSQL Wierd Error

Feb 13, 2007

hi all!

i have encountered an inconsistent error while creating packages programatically. i am trying to create an ExecuteSQL Task like this:

ExecuteSQLTask sqlTask = (ExecuteSQLTask)sqlTaskHost.InnerObject;

and somtimes it failes on this command with this error:

<Message>Error in generating package C:PROJECTSPOSTALBuildSSIS packagesMappingsMappingsSBSCR_52_OUT.dtsx</Message>

<InnerException>Type 'Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask' in Assembly 'Microsoft.SqlServer.SQLTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' is not marked as serializable.</InnerException>

<Data />

<Source>mscorlib</Source>

<HelpLink />

<StackTrace> at System.Runtime.Serialization.FormatterServices.InternalGetSerializableMembers(RuntimeType type)

at System.Runtime.Serialization.FormatterServices.GetSerializableMembers(Type type, StreamingContext context)

at System.Runtime.Serialization.Formatters.Binary.WriteObjectInfo.InitMemberInfo()

at System.Runtime.Serialization.Formatters.Binary.WriteObjectInfo.InitSerialize(Object obj, ISurrogateSelector surrogateSelector, StreamingContext context, SerObjectInfoInit serObjectInfoInit, IFormatterConverter converter, ObjectWriter objectWriter)

at System.Runtime.Serialization.Formatters.Binary.WriteObjectInfo.Serialize(Object obj, ISurrogateSelector surrogateSelector, StreamingContext context, SerObjectInfoInit serObjectInfoInit, IFormatterConverter converter, ObjectWriter objectWriter)

at System.Runtime.Serialization.Formatters.Binary.ObjectWriter.Serialize(Object graph, Header[] inHeaders, __BinaryWriter serWriter, Boolean fCheck)

at System.Runtime.Serialization.Formatters.Binary.BinaryFormatter.Serialize(Stream serializationStream, Object graph, Header[] headers, Boolean fCheck)

at System.Runtime.Remoting.RemotingServices.MarshalToBuffer(Object o)

at Microsoft.SqlServer.Dts.Runtime.ObjectHostImpl.get_InnerObject()

at Microsoft.SqlServer.Dts.Runtime.TaskHost.get_InnerObject()

at InterfaceEngine.OutputInterface.CreateExcel() in C:PROJECTSPOSTALBuildInterfaceInterfaceEngineInterfaceEngineOutputInterface.cs:line 842

at InterfaceEngine.OutputInterface.GenerateOutputPackage() in C:PROJECTSPOSTALBuildInterfaceInterfaceEngineInterfaceEngineOutputInterface.cs:line 322</StackTrace>

<TargetSite />



what is this about? why does it happen , and why not always? the package generation is generated by a web service that is called from an asp.net 2.0 page .



thanks a lot!!!

View 4 Replies View Related

GUIDs Using NEWSEQUENTIALID() On SQL Server Mobile

Feb 28, 2006

Hello,

I've been thinking about using GUIDs as primary keys within my application. One problem I have with this approach is that GUIDs generated with NEWID() are not sequential, and this can have a huge impact on insertion performance due to fragmented indexes. It would seem that NEWSEQUENTIALID() solves this issue, however it doesn't seem very practical to use this on SQL Server Mobile. With SQL Server, you can specify an OUTPUT clause for an INSERT statement, and using this, determine what the last inserted GUID was. There doesn't seem to be such an option in SQL Server Mobile, so how can I get the last generated GUID so that I can attach children records to my parent record? It's been suggested that you could use the MAX() function to get the latest GUID, but there is a problem with this approach - NEWSEQUENTIALID() guarantees that the GUIDs generated will be sequential for the machine generating them, but makes no guarantees about how this will fit in with GUIDs generated by other machines. It is entirely possible that GUIDs generated by a different machine will be larger than the latest one generated on the current machine, so using MAX() to determine the latest value doesn't seem like a valid option to me.

Does anybody have any ideas on tackling this problem? Any thoughts on the suitability of using GUIDs as the primary key for SQL Server Mobile devices in general?

Thanks,
Adrien.

View 1 Replies View Related

Use Of Guids As Unique Identifiers - Good Or Bad?

Jan 30, 2008

Are Guids good or bad? I've spoken to people who hate them in databases, but the db's I'm using right now all use them, so there must be *some* people out there who like them. I'm interested to know what other people think.

thanks!

View 6 Replies View Related

ExecuteSQL Using Variable Expression And Comments

Nov 16, 2007

I have been successful using comments (to help remind me that the SQL is coming from a varaible which is set via an expresion) within the variable expression passed to a Source or Destination. I have been unable to get this to work in an ExecuteSQL control flow task when parameters are used within the query. I am not sure what is causing this not to work the fact that it is an ExecuteSQL control flow task or that the SQL has parameters (or perhaps that it also uses an OUTPUT parameter).

Any thoughts to share? Am I missing something?

WORKS (In Data Flow SQL):
"--NOTE: This is defined by an expression on Source_AdventureWorks_Customers_SQL variable
SELECT
[CustomerID],
[TerritoryID],
[AccountNumber],
[CustomerType],
[rowguid],
[ModifiedDate]
FROM
[AdventureWorks].[Sales].[Customer]"

FAILS (in Control Flow ExecuteSQL with parms including Output parm):
"--NOTE: This is defined by an expression on Source_AdventureWorks_Customers_SQL variable
[audit].[up_LogEtlPackageStart]
@ETLAuditParentKey = ?,
@Description = ?,
@PackageName = ?,
@PackageGuid = ?,
@PackageVersionMajor = ?,
@PackageVersionMinor = ?,
@PackageVersionBuild = ?,
@MachineName = ?,
@ExecutionGuid = ?,
@LogicalDate = ?,
@StartTime = ?,
@Operator = ?,
@ETLAuditKey = ? OUTPUT"

WORKS (in Control Flow ExecuteSQL with parms including Output parm):
"[audit].[up_LogEtlPackageStart]
@ETLAuditParentKey = ?,
@Description = ?,
@PackageName = ?,
@PackageGuid = ?,
@PackageVersionMajor = ?,
@PackageVersionMinor = ?,
@PackageVersionBuild = ?,
@MachineName = ?,
@ExecutionGuid = ?,
@LogicalDate = ?,
@StartTime = ?,
@Operator = ?,
@ETLAuditKey = ? OUTPUT"


View 7 Replies View Related

ExecuteSQL Using SQLSource Variable And Output

Nov 16, 2007



Really stuck trying different ways to get a string variable (expression based) to parse and run correctly in an ExecuteSQL task. Would someone be able to advise why one of the following methods works and the other does not?
I'd really prefer to have the second currently not working method be used if possible.

WORKS:
"[audit].[up_LogEtlPackageStart]
@ETLAuditParentKey = ?,
@Description = ?,
@PackageName = ?,
@PackageGuid = ?,
@PackageVersionMajor = ?,
@PackageVersionMinor = ?,
@PackageVersionBuild = ?,
@MachineName = ?,
@ExecutionGuid = ?,
@LogicalDate = ?,
@StartTime = ?,
@Operator = ?,
@ETLAuditKey = ? OUTPUT"

FAILS:
When the variable expression is set as follows I :



Code Block

"[audit].[up_LogEtlPackageStart]
@ETLAuditParentKey = " + (DT_WSTR, 100)@[User::ETLAuditParentKey] + ",
@Description = " + (LEN( @[User::PackageDescription]) == 0 ? "' '" : @[User::PackageDescription]) + ",
@PackageName = " + @[System::PackageName] + ",
@PackageGuid = " + @[System::PackageID] + ",
@PackageVersionMajor = " + (DT_WSTR, 100)@[System::VersionMajor] + ",
@PackageVersionMinor = " + (DT_WSTR, 100)@[System::VersionMinor] + ",
@PackageVersionBuild = " + (DT_WSTR, 100)@[System::VersionBuild] + ",
@MachineName = " + @[System::MachineName] + ",
@ExecutionGuid = " + @[System::ExecutionInstanceGUID] + ",
@LogicalDate = " + (DT_WSTR, 100)@[User::LogicalDate] + ",
@StartTime = " + (DT_WSTR, 100)@[System::StartTime] + ",
@Operator = " + @[System::UserName] + ",
@ETLAuditKey = " + (DT_WSTR, 100)@[User::ETLAuditKey] + " OUTPUT"




This method generates a "Syntax error, permission violation, or other nonspecific error. Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly"

Any ways to get the second failing expression to work?
Is this a problem due to the OUTPUT parameter?
THANKS!

View 7 Replies View Related

Seeded(int) Identity Columns Vs. UniqueIdentifiers(GUIDs)

Jul 1, 2003

I need gurus input on the pros and cons of Seeded(int) Identity colums vs. UniqueIdentifier(GUIDs) columns for my db design.

As I understand it, merging the data of 2 independent db's both using IDENTITY columns would be very hairy because of the possibility of overlap. GUIDs are much more likely to be unique across different servers.

What I'd like to hear from others are the other pros and cons of the situation. I of course understand the performance hit suffered at the hands of the GUID.

Thanks for your help!

View 29 Replies View Related

Can I Update GUIDS In Sysmergepubliction And SysmergeAricles In Sqlserver 2005 ?

Dec 14, 2006

Hi fellows ,

there is a senerio where i want to  update GUIDS in columns "pubid" and "artid" generate in sysmergepubliction and sysmergeAricles respectivly  in sqlserver 2005 ?

2nd is there any easy way to generate scripts of  triggers of all replicated tables in subscriber at one .

View 1 Replies View Related

Complex Copy Routine, Mulitple Tables And Changing GUIDs

Dec 11, 2007

hello,
I have several tables that have guids as their primary keys and the tables are related as follows:
Table1 - primary key = ServiceNo (Guid), Filter Key = CampaignNo
Table2 - primary key = CostBasisNo (Guid), Foreign Key = ServiceNo (from Table1)
Table3 - primary key = UserId, Foreign Key = ServiceNo (from table1)
Table4 - primary key = SourceServiceNo (Foreign Key from Table1), MemberServiceNo(Foreign Key from Table1)
what I need to do is copy all records from Table1 where CampaignNo = @CampaignNo and insert them into table1, this I can do easily but I will generate a new ServiceNo for each one and associated a new CampaigNo which is fine.
The problem comes in that I need to also copy the contents of Table2 = Table3 for all ServiceNos that have been copied from Table1 but insert the new Guid that will have been created when copying the rows in Table1
This is further compounded when I need to do the same to Table4 but this time I need to insert the newid's for SourceServiceNo and the related MemberServiceNo which all would have changed.
I haven't the first clue where to start with this task, do I need to use temporary tables, cursors? any help gratefully received, even if it's a pointer to the most efficient approach.
 regards
 
 
 

View 4 Replies View Related

Guids Are Not Very Guid! Updating In DetailsView,FormView (Object Must Implement IConvertible)

Dec 1, 2005

I've been playing around with the new data controls (DetailsView,FormView) and have been having problems when attempting to update a record that has a uniqueidentifier as its primary key.I get the error message:
Object must implement IConvertible. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidCastException: Object must implement IConvertible.I gather this is because there is a bug that has propagated from the beta version.One suggested work around is from http://64.233.183.104/search?q=cache:GDjA62POtgcJ:scottonwriting.net/sowBlog/archive/11162005.aspx+Implicit+conversion+from+data+type+sql_variant+to+uniqueidentifier+is+not+allowed.+Use+the+CONVERT+function+to+run+this+query.&hl=en
The crux of the problem, it appears, is that the <asp:Parameter> value for the uniqueidentifier field is, by default, set to Type=�Object�. To fix this, simply remove the Type property altogether. That is, change the SqlDataSource parameter setting from something like:
<asp:SqlDataSource ...>  <InsertParameters>    <asp:Parameter Name=â€?UserIdâ€? Type=â€?Objectâ€? />    ...  </InsertParameters></asp:SqlDataSource>
to:
<asp:SqlDataSource ...>  <InsertParameters>    <asp:Parameter Name=â€?UserIdâ€?  />    ...  </InsertParameters></asp:SqlDataSource>
This change worked for me; once the Type was removed the exception ceased and the updates/inserts worked as expected.Unfortunately this only partially worked for me as while it is fine for deletes it won't work for updates.If anyone can help shed any light on this I would greatly appreciate it.CheersMark

View 28 Replies View Related

Send Mail Task Problem Using A Combination Of ForEach Loop, Recordset Destination, Execute SQL Task And Script Task

Jun 21, 2007

OK. I give up and need help. Hopefully it's something minor ...



I have a dataflow which returns email addresses to a recordset.

I pass this recordset into a ForEachLoop configuring the enumerator as (Foreach ADO Enumerator). I also map the email address as a variable with index 0.



I then have a Execute SQL task which receives this email address as a varchar variable (parameter 0) which I then use in my SQL command to limit the rows returned. I have commented out the where clause and returned all rows regardless of email address to try to troubleshoot this problem. In either event, I then use a resultset to store the query result of type object and result name 0.



I then pass this resultset into a script variable to start parsing the sql rows returned as type object. ( I assume this is the correct way to do this from other prior posts ...).



The script appears to throw an exception at the following line. I assume it's because I'm either not passing in the values properly or the query doesn't return anything. However, I am certain the query works as it executes just fine at the command prompt.



Try

ds = CType(Dts.Variables("VP_EMAIL_RESULTS_RS").Value, DataSet)



My intent is to email the query results to each email address with the following type of data by passing the parsed data from the script to a send mail task. Email works fine and sends out messages but the content is empty. I pass the parsed data as string values to the messagesource and define the messagesourcetype as a variable in the mail task.



part number leadtime

x 5

y 9

....



Does anyone have any idea what I might be doing wrong?

thanks

John

View 5 Replies View Related

Using Membership UserId Guids With SqlParameters (Membership UserId Guid)

May 7, 2008

In my site, when a user registers, I need to create rows in additional tables besides aspnet_Users. So, I need to be able to pass the generated userId guid to subsequent SqlCommands. I'm having a terrible time with this. What's the correct way to set up a SqlParameter so that it will accept a guid? I keep getting this error: "Conversion failed when converting from a character string to uniqueidentifier."
I've tried creating the parameter both with and without a SqlDbType.
cmd.Parameters.AddWithValue(paramName, guid);
and
SqlParameter p = new SqlParameter(paramName);p.SqlDbType = SqlDbType.Guid;cmd.Parameters.Add(p);
and I get the same error either way.
Driving me nuts! Any help appreciated.

View 1 Replies View Related

Integration Services :: Stored Procedure In Execute Task Fails But Task Does Not Fail

Jul 1, 2015

I'm using SSIS in Visual Studio 2012. My Execute SQL Task calls a Stored Procedure where I have a TRY-CATCH. Last week there was a problem and the CATCH was executed and logged an error to my error table, but for some reason the Execute SQL Task didn't fail. Is there a setting to make the Execute SQL Task fail when an SP encounters a failure?

View 3 Replies View Related

Error: The Task With The Name Data Flow Task And The Creation Name DTS.Pipeline.1 Is Not Registered For Use On This Computer

May 4, 2006



Hi,

I am trying to create a simple BI Application for SSIS. In Visual Studio 2005 I just get a Data Flow Task from the toolbar and add it to the project. When I double click it I get the following error:

The task with the name "Data Flow Task" and the creation name "DTS.Pipeline.1" is not registered for use on this computer.

Then when I try to delete it it gives this other error:

Cannot remove the specified item because it was not found in the specified Collection.

I am creating this application in an administrator account in this computer, so I doubt the problem is related to permissions. I am running SQL Server 2005 and Visual Studio 2005 in WinXP Tablet PC Edition.

Any suggestions why this is happening and how to fix it?

View 17 Replies View Related

SSIS Task Transfer SQL Server Objects Task And Default Constraints On Tables

Feb 21, 2008



I am using the "Transfer SQL Server Objects Task" to copy some tables from database A to database B including data.

The tables, primary key constraints, Foreign key, data and all transfers nicely except for "DEFAULT CONSTRAINTS" on the tables.

I have failed to find any option in the "Transfer SQL Server Objects Task" task to explicitly say "copy default constraints". So I guess logically it should happen automatically but it doesn't. I hope it is not a bug :-)

Any option anyone knows will help.

Thanks.

View 17 Replies View Related

Compare Performance (Execute SQL Task Insert And Data Flow Task)

Mar 12, 2008



I am using SQL 2005 SSIS. I am joining several large tables and then the move result into another table in the same database.

I would like know which method is faster:


Use Execute SQL Task to insert the result set to the target table

Use the Data Flow Task to insert the result set to the target table. (Use OLE DB source to execute SQL command and then use the SQL destination)
Could you tell me why then other is slower?

Thanks.

View 7 Replies View Related

Execute SQL Task – Output Parameter On Stored Procedure Causes Task To Fail.

Dec 2, 2005

I have a SQL Task that calls a stored procedure and returns an output parameter.  The task fails with error "Value does not fall within the expected range."   The Stored Procedure is defined as follows: Create Procedure [dbo].[TestOutputParms]             @InParm INT ,             @OutParm INT OUTPUT as Set @OutParm  = @InParm + 5   The task uses an OLEDB connection and has a source type of Direct Input.  The SQL Statement is Exec TestOutputParms 7, ? output    The parameter mapping is: Variable Name Direction Data Type Parameter Name User::OutParm Output LONG @OutParm  

View 7 Replies View Related

Writing Full Result Set From Execute SQL Task Into A File Using Script Task

Mar 28, 2007

In the Control flow tab, I have an Execute SQL Task that outputs full Result set into a variable of an object type. Now how can I write the contents of the Full Result Set into a text file using Script Task. I also want to format the following way while I output into a file:

Column Name 1 : Column Value

Column Name 2: Column Value and so on

I tried writing the contents of the Object Variable into a file, but the file had an output of single word: System.__ComObject.




Code for Writing the Full Result Set into a Text File

Dim RSsqloutput as String = Dts.Variables("objVariable").Value.ToString

Dim strVal as String = "File completed on " & Now() & vbCrLf & "------------------------------------------------------" & vbCrLf

oLogFile.WriteAllText("C:MyFile.txt", strValue)

oLogFile.WriteAllText("C:MyFile.txt", rsSQLOutput)



I went through this link that explains how to write XML Result Set into a File, But this doesn't help as it writes in XML format.

Would you please give me a hint of code how I can go upon.





View 7 Replies View Related

SSIS (Integration Services) Transfer SQL Server Objects Task: This Task Can Not Participate In A Transaction

Feb 1, 2007

In short, does the €œTransfer SQL Server Objects Task€? support distributed transactions?

In trying to use a €œTransfer SQL Server Objects Task€? in a container using a transaction on the container. The task is set to support the transaction. It is setup to copy table data from several tables from a non-domain server (sql server 2000) to a domain-based server (sql server 2005). I get an error stating, €œThis task can not participate in a transaction€?.

I am wondering if it means exactly what it says €“ this task in SSIS can€™t participate at all. Or does it mean that it won€™t in this scenario for some reason. I attempted a simple copy of data from mssql 2005 to mssql 2005 (same server) and the task still failed). MSDTC appears to be running properly on my machine and such (I can do a simple distributed transaction across linked server to the 2000 server in Query Analyzer (QA)). Also, MSDTC appears to be working on both servers with distributed transaction query tests in QA.

Here€™s the error info€¦

SSIS package "Development BusinessContacts and Products Migration.dtsx" starting.
Information: 0x4001100A at Copy BusinessContacts Data: Starting distributed transaction for this container.
Error: 0xC002F319 at Copy BusinessContacts database table data 1, Transfer SQL Server Objects Task: This task can not participate in a transaction.
Task failed: Copy BusinessContacts database table data 1
Information: 0x4001100C at Copy BusinessContacts database table data 1: Aborting the current distributed transaction.
Information: 0x4001100C at Copy BusinessContacts Data: Aborting the current distributed transaction.
SSIS package "Development BusinessContacts and Products Migration.dtsx" finished: Failure.
The program '[4700] Development BusinessContacts and Products Migration.dtsx: DTS' has exited with code 0 (0x0).

View 9 Replies View Related

How To Fetch The Recrods From MS Access And Using It In Script Task Using Control Flow Tools(Execute SQL Task)

Jun 14, 2006

Hi

I have an application like fetching records from the DataBase(MS Access 2000) and results i have to use in Script Task. At present i have used the record fetching query,connection string in Script itself. I would like to use in Independently. Is there any Tools like (Control Flow Tools like Execute SQL Task) are there to fetch the result set from Acccess and can use the fetching results in Script Task....

Thanks & Regards

Deepu M.I

View 5 Replies View Related

Can A Result Set From SQL Script Task Be Used As A Source For Data Flow Task?

Oct 2, 2007

I have a stored procedure that is executed via a sql script task that returns a full result set. I map this result set to a variable or object type. Is there a way to use this variable as a data source in a subsequent data flow task?

A.

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved