Data type Object in Send Mail Task?

I have an Execute SQL Task that runs a simple SELECT query. I have the result set = Full Result Set. The variable is of type Object and the value is System.Oject. After successful completion of the Execute SQL Task, I am doing a Send Mail task. For the Message Source, I want to use this Object. The drop down is only listing variables of type String. Can you not use a variable of type Object in a Send Mail Task? If not, what is the easiest workaround? Thanks!

No, you can't use an object for the message source. You could use a column from the resultset, though. If you give more description on what you are trying to do, we'll be able to suggest some work arounds.

If you are trying to send the contents of the recordset in an email, here are a few posts that might help:

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

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

Data Type in Parameter Mapping for an Execute SQL Task

Hi, I am trying to use an integer as input parameter for my task I get suck on the parameter data type.

The input parameter is define as @.Control_ID variable as Int32 in SSIS. When I got into the parameter mapping of Execute SQL Task, I don't find the Int32 data type. I used to try Short, Numeric, Decimal and so on, but all of those data type didn't work. and it returns the following error message:

SSIS package "DCLoading.dtsx" starting.
Error: 0xC002F210 at Update Control_ID, Execute SQL Task: Executing the query "use DCAStaging

update DCA_HFStaging set
[dbo].[Control_ID] = P0 where [Control_ID] is null
" failed with the following error: "The multi-part identifier "dbo.Control_ID" could not be bound.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Update Control_ID
Warning: 0x80019002 at DCLoading: 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.
SSIS package "DCLoading.dtsx" finished: Failure.

Any help?this link might help: http://www.sqlis.com/default.aspx?58|||Thanks for the response. The link you posted is helpful, but does not include the example that I need. I want to set an Int32 variable as intput parameter, but I don't find any integer type in the parameter mapping. And, if I set the SSIS variable as string type, in the parameter mapping, I set the input parameter as varchar, it will return error said the parameter length can't be defined.

data truncated when exported via DTS transform task

I have a DTS transform task where i am exporting data into a tab delimeted
flat file from a table where column is defined as varchar(500)
What i noticed is that data is truncated after 255 characters for this
column when i export this into a tab delimeted file.
Te length of column shows as 500 chars even in Destination tab of DTS
transform task
Pls help
Thanks
Hi
Have you seen http://www.sqldts.com/default.aspx?297
John
"Sanjay" wrote:

> I have a DTS transform task where i am exporting data into a tab delimeted
> flat file from a table where column is defined as varchar(500)
> What i noticed is that data is truncated after 255 characters for this
> column when i export this into a tab delimeted file.
> Te length of column shows as 500 chars even in Destination tab of DTS
> transform task
> Pls help
> Thanks
>
>

data truncated when exported via DTS transform task

I have a DTS transform task where i am exporting data into a tab delimeted
flat file from a table where column is defined as varchar(500)
What i noticed is that data is truncated after 255 characters for this
column when i export this into a tab delimeted file.
Te length of column shows as 500 chars even in Destination tab of DTS
transform task
Pls help
ThanksHi
Have you seen http://www.sqldts.com/default.aspx?297
John
"Sanjay" wrote:

> I have a DTS transform task where i am exporting data into a tab delimeted
> flat file from a table where column is defined as varchar(500)
> What i noticed is that data is truncated after 255 characters for this
> column when i export this into a tab delimeted file.
> Te length of column shows as 500 chars even in Destination tab of DTS
> transform task
> Pls help
> Thanks
>
>

Data Transformation Services (DTS)(Bulk Insert)

Hi All,

I'm using DTS package, a tool to transfer data from a txt file to database(Bulk Insert).

The Bulk Insert task provides an efficient way to copy large amounts of data into a SQL Server table or view.It seems that the Bulk Insert task supports only OLE DB connections for the destination database. But I want to use sql server authentication as OLEDB connection requires windows authentication.

So can the bulk insert be done using SQLServer authentication ? if yes then please help me.

I have given the code snippet below.

Code Sample:

Dim oPackage As New DTS.Package2()
Dim oConnection As DTS.Connection
Dim oStep As DTS.Step2
Dim oTask As DTS.Task
Dim oCustomTask As DTS.BulkInsertTask
Try
oConnection = oPackage.Connections.New("SQLOLEDB")
oStep = oPackage.Steps.New
oTask = oPackage.Tasks.New("DTSBulkInsertTask")
oCustomTask = oTask.CustomTask
With oConnection
oConnection.Catalog = "pubs"
oConnection.DataSource = "(local)"
oConnection.ID = 1
oConnection.UseTrustedConnection = True
oConnection.UserID = "Tony Patton"
oConnection.Password = "Builder"
End With
oPackage.Connections.Add(oConnection)
oConnection = Nothing
With oStep
.Name = "GenericPkgStep"
.ExecuteInMainThread = True
End With
With oCustomTask
.Name = "GenericPkgTask"
.DataFile = "c:\dts\authors.txt"
.ConnectionID = 1
.DestinationTableName = "pubs..authors"
.FieldTerminator = "|"
.RowTerminator = "\r\n"
End With
oStep.TaskName = oCustomTask.Name
With oPackage
.Steps.Add(oStep)
.Tasks.Add(oTask)
.FailOnError = True
End With
oPackage.Execute()
Catch ex As Exception
MsgBox("Error: " & CStr(Err.Number) & vbCrLf_
& Err.Description, vbExclamation, oPackage.Name)
Finally
oConnection = Nothing
oCustomTask = Nothing
oTask = Nothing
oStep = Nothing
If Not (oPackage Is Nothing) Then
oPackage.UnInitialize()
End If
End Try

This is really a SSIS forum not DTS. There is a newsgroup for DTS, with a web/forum style interface.

Corrected Snippet

oConnection.UseTrustedConnection = False ' Must be false to use SQL Security
oConnection.UserID = "Tony Patton"
oConnection.Password = "Builder"

Data Transformation Question

I have a student table that needs some clean up. My first task is to remove all the periods (.) from the middle name column. Some people have two char middle initials with two periods. Can this be done via a SSIS package (I sure it can, but not how)? I can't think of a simple update statement that would accomplish the same thing.

Any direction would be appreciated.

Isn't it easier removing the periods by running an update query

UPDATE Students SET MiddleName = Replace(MiddleName , N'.', N'')

Eralper

http://www.kodyaz.com

|||Yes, it is :-)

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Data Transformation Question

I have a student table that needs some clean up. My first task is to remove all the periods (.) from the middle name column. Some people have two char middle initials with two periods. Can this be done via a SSIS package (I sure it can, but not how)? I can't think of a simple update statement that would accomplish the same thing.

Any direction would be appreciated.

Isn't it easier removing the periods by running an update query

UPDATE Students SET MiddleName = Replace(MiddleName , N'.', N'')

Eralper

http://www.kodyaz.com

|||Yes, it is :-)

Jens K. Suessmeyer.

http://www.sqlserver2005.de