Insert A Variable Into HTML Body?

Oct 18, 2013

I have created a .bat file for my sqlcmd

my sqlcmd includes a .sql file that builds up my email with a HTML body.

Is it possible to insert a variable (@customername) into the HTML body?

I am already using @maillist and select statement to create my customer email address's.

View 5 Replies


ADVERTISEMENT

SSIS Send Email Html Body

Apr 4, 2008

Hi, I need help please.

I want to send an HTML Email

I have an Htm file that i want to use as my email body on Send Mail Task
MessageSourceType: File Connection
MessageSource: Email.htm

It sends the email with the file in the body but not in HTML format
it outputs the HTML tags + data
eg: <span style="font-size:9.0pt;">Period: 2008.04</span>

I used the same file in a DTS package & that works fine.

Please Assist!

Regards

View 2 Replies View Related

Sending Email From An HTML Template As The Body

Aug 28, 2006

We have a DTS package that sends smtp email from an ActiveX script task. The body of the emails are in template files that we read in and then replace values relating to the customer.

I am looking for suggestions of handling this process from an SSIS package. This is what our existing code does.



'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()
'********************************************************************************************
' Email results
'********************************************************************************************
Dim oRS, objMessage, fTemplate

Set fso = CreateObject("Scripting.FileSystemObject")

Set oRS =DTSGlobalVariables("ToBeNotified").Value
oRS.MoveFirst

Do While NOT oRS.EOF
Set objMessage = CreateObject("CDO.Message")
Set objMessage2 = CreateObject("CDO.Message")

objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objMessage2.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = DTSGlobalVariables("RemoteSMTPServer").Value
objMessage2.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = DTSGlobalVariables("RemoteSMTPServer").Value

'Server port (typically 25)
objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objMessage2.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

'Type of authentication, NONE, Basic (Base64 encoded), NTLM
objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
objMessage2.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1

'Your UserID on the SMTP server
objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusername") = "CustomerService"
objMessage2.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusername") = "CustomerService"

'Your password on the SMTP server
objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "cslogin"
objMessage2.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "cspassword"

objMessage.Configuration.Fields.Update
objMessage2.Configuration.Fields.Update

objMessage.Sender = DTSGlobalVariables("EmailFrom").Value
objMessage2.Sender = DTSGlobalVariables("EmailFrom").Value

objMessage.From = DTSGlobalVariables("EmailFrom").Value
objMessage2.From = DTSGlobalVariables("EmailFrom").Value

objMessage.To = oRS.Fields("ContactEmail").value
' objMessage.To = "me@ourmail.com"

objMessage2.To = DTSGlobalVariables("EmailBCC").Value
' objMessage2.To = "me@ourmail.com"


If oRS.Fields("Rejected").value = 1 Then
'Rejection Message
objMessage.Subject = "electronic request notification"

If oRS.Fields("ProductType").value = "Fixed" Then
Set htmlTextStream = fso.OpenTextFile (DTSGlobalVariables("EmailTemplateRejectedFixed").Value)
Else
Set htmlTextStream = fso.OpenTextFile (DTSGlobalVariables("EmailTemplateRejectedVariable").Value)
End If

Else
'Successful results
objMessage.Subject = "electronic confirmation #" + CSTR(oRS.Fields("ConfirmationId").value)
objMessage2.Subject = "Customer request for hard copy of contract"

'Add Terms and Conditons document
termDocument = DTSGlobalVariables("TermDocumentsRepository").Value + CSTR(oRS.Fields("TermDocument").Value) + ".pdf"

If fso.FileExists(termDocument) Then
objMessage.AddAttachment termDocument
objMessage2.AddAttachment termDocument
End If

If oRS.Fields("ProductType").value = "Fixed" Then
Set htmlTextStream = fso.OpenTextFile (DTSGlobalVariables("EmailTemplateSuccessFixed").Value)
Else
Set htmlTextStream = fso.OpenTextFile (DTSGlobalVariables("EmailTemplateSuccessVariable").Value)
End If

End If

htmlText = htmlTextStream.ReadAll
htmlText = Replace( htmlText , "[OurLogo]" , DTSGlobalVariables("EmailLogo").Value)
htmlText = Replace( htmlText , "[CustomerName]" , oRS.Fields("ContactFirstName").value + " " + oRS.Fields("ContactLastName").value)

If oRS.Fields("Rejected").value = 0 Then
htmlText = Replace( htmlText , "[ConfirmNumber]" , oRS.Fields("ConfirmationId").value)
htmlText = Replace( htmlText , "[OrderDate]" , oRS.Fields("SalesDate").value)
htmlText = Replace( htmlText , "[ProductName]" , oRS.Fields("ProductType").value)
htmlText = Replace( htmlText , "[Months]" , oRS.Fields("TermMonths").value)
htmlText = Replace( htmlText , "[Price]" , oRS.Fields("SalesPrice").value)
htmlText = Replace( htmlText , "[Units]" , oRS.Fields("SalesPriceUnit").value)
htmlText = Replace( htmlText , "[StartDate]" , oRS.Fields("FlowStartDate").value)
htmlText = Replace( htmlText , "[AccountNumber]" , oRS.Fields("AccountNumber").value)
End If

objMessage.HTMLBody = htmlText
objMessage2.HTMLBody = htmlText


objMessage.Send


If oRS.Fields("SendHardCopy").value = True AND oRS.Fields("Rejected").value = 0 Then

' Attach Instructions for Hard Copy delivery
instructionDocument = DTSGlobalVariables("TermDocumentsRepository").Value + "Instructions.txt"

set file = fso.CreateTextFile(instructionDocument, true)
file.WriteLine("The customer has requested a hard copy of their contract to be sent to them.")
file.WriteLine("")
file.WriteLine("Instructions:")
file.WriteLine("1) Print the email")
file.WriteLine("2) Print the attached Terms & Conditions document")
file.WriteLine("3) Mail both items to the address below")
file.WriteLine("")
file.WriteLine("Customer Address:")
file.WriteLine(oRS.Fields("ContactFirstName").value + " " + oRS.Fields("ContactLastName").value)
file.WriteLine(oRS.Fields("BillingAddress1").value)
If oRS.Fields("BillingAddress2").value <> "" Then
file.WriteLine(oRS.Fields("BillingAddress2").value)
End If
file.WriteLine(oRS.Fields("BillingCity").value + ", " + oRS.Fields("BillingState").value + " " +oRS.Fields("BillingZip").value)
file.Close
set file=nothing

objMessage2.AddAttachment instructionDocument

fso.DeleteFile(instructionDocument)

objMessage2.Send
End If

set objMessage = nothing
set objMessage2 = nothing

oRS.MoveNext
Loop

Main = DTSTaskExecResult_Success
End Function

All suggestions are appreciated.

SK

View 1 Replies View Related

SQL Server 2008 :: DBmail HTML Body Size Limit

Nov 4, 2015

When sending an email in HTML format, shouldn't this allow for 2gb of data? Mine is getting truncated after 4000 characters.

@body NVARCHAR(MAX) = NULL,

EXEC msdb.dbo.sp_send_dbmail
@recipients='someone@some.com',
@reply_to='someone'
@from_address='someone@here.com>',
@profile_name = 'profilename',
@body_format = 'HTML',
@body = 'lots of data'

View 2 Replies View Related

Insert Trigger Not Getting Row Data For Email Body

Oct 18, 2007

hello,
need help with a simple trigger i have been working on. the trigger automatically sends me an email out when a record is inserted, how ever i can't seem to get the row column data into the email. The part i do not understand is that I get the row column data  information in the email if I update the row.
This is for 2005 SQL
Any direction would be greatly appreaciated
OneIDesigned
 

View 5 Replies View Related

Variable Insert To SQL Server Insert Satement Setting Values For The @variable INSIDE Sql

Apr 29, 2007

ok, I am on Day 2 of being brain dead.I have a database with a table with 2 varchar(25) columns I have a btton click event that gets the value of the userName,  and a text box.I NEED to insert a new row in a sql database, with the 2 variables.Ive used a sqldatasource object, and tried to midify the insert parameters, tried to set it at the button click event, and NOTHING is working. Anyone have a good source for sql 101/ASP.Net/Braindead where I can find this out, or better yet, give me an example.  this is what I got <%@ Page Language="C#" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server">     protected void runit_Click(object sender, EventArgs e)    {       //SqlDataSource ID = "InsertExtraInfo".Insert();      //SqlDataSource1.Insert();    }      protected void Button1_Click1(object sender, EventArgs e)    {        SqlDataSource newsql;                newsql.InsertParameters.Add("@name", "Dan");        newsql.InsertParameters.Add("@color", "rose");        String t_c = "purple";        string tempname = Page.User.Identity.Name;        Label1.Text = tempname;        Label2.Text = t_c;        newsql.Insert();    }</script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server">    <title>mini update</title></head><body>    <form id="form1" runat="server">        &nbsp;name<asp:TextBox ID="name" runat="server" OnTextChanged="TextBox2_TextChanged"></asp:TextBox><br />        color        <asp:TextBox ID="color" runat="server"></asp:TextBox><br />        <br />        <asp:Button ID="Button1" runat="server" OnClick="Button1_Click1" Text="Button" />        &nbsp;<br />        set lable =&gt;<asp:Label ID="Label1" runat="server" Text="Label" Width="135px" Visible="False"></asp:Label><br />        Lable 2 =&gt;        <asp:Label ID="Label2" runat="server" Text="Label"></asp:Label><br />        Usernmae=&gt;<asp:LoginName ID="LoginName1" runat="server" />        <br />        <br />        <br />        <br />        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"            ConnectionString="<%$ ConnectionStrings:newstring %>" DeleteCommand="DELETE FROM [favcolor] WHERE [name] = @original_name AND [color] = @original_color"            InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, @color)"            OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT [name], [color] FROM [favcolor]"            UpdateCommand="UPDATE [favcolor] SET [color] = @color WHERE [name] = @original_name AND [color] = @original_color">            <DeleteParameters>                <asp:Parameter Name="original_name" Type="String" />                <asp:Parameter Name="original_color" Type="String" />            </DeleteParameters>            <UpdateParameters>                <asp:Parameter Name="color" Type="String" />                <asp:Parameter Name="original_name" Type="String" />                <asp:Parameter Name="original_color" Type="String" />            </UpdateParameters>            <InsertParameters>        <asp:InsertParameter("@name", "Dan", Type="String" />        <asp:InsertParameter("@color", "rose") Type="String"/>                                       </InsertParameters>        </asp:SqlDataSource>        &nbsp;        <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"            AutoGenerateColumns="False" DataKeyNames="name" DataSourceID="SqlDataSource1">            <Columns>                <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowSelectButton="True" />                                <asp:BoundField DataField="color" HeaderText="color" SortExpression="color" />                <asp:BoundField DataField="name" HeaderText="name" ReadOnly="True" SortExpression="name" />            </Columns>        </asp:GridView>           </form></body></html>  

View 1 Replies View Related

String Variable From CodeBehind To Html

Dec 3, 2007

Hi Folks,
How can i pass a string variable from code behind page to html page on event fire. i want to pass sql qerry to SelectCommand (SELECT * FROM [Tickets] WHERE ([TicketNo] = @TicketNo)) in some variable from codebehind on some event fire.
<asp:SqlDataSource ID="sqldsTickets" runat="server" ConnectionString="<%$ ConnectionStrings:IMTicketingConnectionString %>" SelectCommand="" >
<SelectParameters>
<asp:SessionParameter Name="TicketNo" SessionField="test" Type="Int64" />
</SelectParameters>
</asp:SqlDataSource>
 Thanks in advance

View 4 Replies View Related

T-SQL (SS2K8) :: SSIS - Extract Data From Table And Insert In Message Body And Email To User

Jun 25, 2014

What I am trying to do, Extract the data from SQL table and Insert in Email Body and email to user. I got good article on Internet, I follow all steps as it is, but still I am getting error.

Here is the link : [URL] ....

But I am getting Error:

Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument list.
at System.Text.StringBuilder.AppendFormat(IFormatProvider provider, String format, Object[] args)
at System.String.Format(IFormatProvider provider, String format, Object[] args)
at ST_7f59d09774914001b60a99a90809d5c5.csproj.ScriptMain.Main()

[Code] ....

View 4 Replies View Related

Passing Session Variable To Html Page.

Apr 17, 2008

Hi Folks,
i am trying to add a connection string value to my client side as follows. i am getting error. any ideas??
<asp:SqlDataSource ID="SqldsComments2" runat="server" ConnectionString='<% session("sqlConn") %>'
SelectCommand="SELECT * FROM TicketComments Where TicketNo=@TicketNo ">
<SelectParameters>
<asp:Parameter Name="TicketNo" DefaultValue="0" Type="string" />
</SelectParameters>
</asp:SqlDataSource>

View 6 Replies View Related

Insert Html In Mssql

Oct 30, 2007

 Hello Everybody,In my web application I have to insert html from editor. I have assigned the filed as "nvarchar". But after running the insert query it throws an error "A potentially dangerous Request.Form value was detected from the client
(oEdit1="<P><SPAN style="FONT...").  " I'm using WYSWYG editor. I'm now quite bit confused about the the field and the query. If you have any idea about the problem please help me out of this problem.Thanks in option.Regards--Arindam 

View 3 Replies View Related

Insert HTMl Code In Database

Feb 23, 2008

Hello,

I am creating an ASP.NET web site which inserts data in a SQL database using LINQ.

One table column will hold HTML code. I am using a simple TextBox to input the data.

I would like to create my text in my computer and then copy/paste the HTML code to my TextBox and insert in the database.

What software should I use to create my text? Microsoft Office 2007, ... ? Any suggestion would be great.

Can I do it this way?

Thanks,

Miguel

View 9 Replies View Related

Retreive Html Code From Xmlfile, Insert Into Table

Nov 15, 2007

I use the following query to shred an xml and insert it into a table, but I want to have the whole html structure into the column to be able to present the formatted text in Cognos 8 BI.

WITH xmlnamespaces('http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-01-15T13:29:33' AS my)
INSERT INTO TMP_ATGFORSLAG (AtgForslagDesc)
SELECT
AtgForslagRad.value('(/my:AtgForslagRad/my:AtgForslagDesc)[1]', 'varchar(2000)') AS AtgForslagDesc
FROM dbo.DeklAtgForslagXml

The structure of the source in the xmlfile I want to import is:
<my:AtgForslagDesc>
<html xmlns="http://www.w3.org/1999/xhtml">
<ol>
<li>Text1...</li>
<li>Text2...</li>
<li>Text3...</li>
</ol>
</html>
</my:AtgForslagDesc>

How do I shred not only the text but the whole starting <html> to finishing </html> and insert it into a table I would be very happy.

View 1 Replies View Related

Transact SQL :: Insert Values From Variable Into Table Variable

Nov 4, 2015

CREATE TABLE #T(branchnumber VARCHAR(4000))

insert into #t(branchnumber) values (005)
insert into #t(branchnumber) values (090)
insert into #t(branchnumber) values (115)
insert into #t(branchnumber) values (210)
insert into #t(branchnumber) values (216)

[code]....

I have a parameter which should take multiple values into it and pass that to the code that i use. For, this i created a parameter and temporarily for testing i am passing some values into it.Using a dynamic SQL i am converting multiple values into multiple records as rows into another variable (called @QUERY). My question is, how to insert the values from variable into a table (table variable or temp table or CTE).OR Is there any way to parse the multiple values into a table. like if we pass multiple values into a parameter. those should go into a table as rows.

View 6 Replies View Related

Display HTML Codes As HTML And Not Text

Jan 15, 2008

I am retrieving a field from SQL and displaying that data on a web page.
The data contains a mixture of text and html codes, like this "<b>test</b>".
But rather than displaying the word test in bold, it is displaying the entire sting as text.
How do I get it to treat the HTML as HTML?

View 6 Replies View Related

SQL Server 2008 :: Insert Data Into Table Variable But Need To Insert 1 Or 2 Rows Depending On Data

Feb 26, 2015

I am writing a query to return some production data. Basically i need to insert either 1 or 2 rows into a Table variable based on a decision as to does the production part make 1 or 2 items ( The Raw data does not allow for this it comes from a look up in my database)

I can retrieve all the source data i need easily but when i come to insert it into the table variable i need to insert 1 record if its a single part or 2 records if its a twin part. I know could use a cursor but im sure there has to be an easier way !

Below is the code i have at the moment

declare @startdate as datetime
declare @enddate as datetime
declare @Line as Integer
DECLARE @count INT

set @startdate = '2015-01-01'
set @enddate = '2015-01-31'

[Code] .....

View 1 Replies View Related

How To Insert Int Variable...

Sep 14, 2006

hi,

I have two question

string a;

int i;

insert into sss (id, name) values(.....)

as id is integer and name is nvarchar in the database.

how can I write this variable within the values paranthesis



2) how to adjust a column in sql server 2005 to auto number

thanks

View 3 Replies View Related

Variable To Insert Parameter

Nov 7, 2007

Hi,
I'm trying to put two textbox values together and make an insert parameter out of them. It doesn't give me any errors but the database value is null. Here's the code:
This is the function at the top of the page: (the lbl1.text is to control that it gets the value at that point, which it does...)Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)


Dim useAU As String = tbUseAmount.Text & " " & ddUnit1.Text

Dim maxSU As String = tbMaxStored.Text & " " & ddUnit2.Text


Dim amountP As New Parameter(useAU)

Dim storedP As New Parameter(maxSU)


lbl1.Text = amountP.ToString



amountP.Name = "useAmountUnit"

storedP.Name = "maxStoredunit"


SqlDataSource1.Insert()

End Sub


 
The parameters: (with a bunch of other parameters which it takes straight from textboxes and works great)
<InsertParameters>
<asp:ControlParameter Name="name" Type="String" ControlID="tbName" />
<asp:ControlParameter Name="RSmarkings" Type="String" ControlID="tbRSmarkings" />
<asp:ControlParameter Name="CAS" Type="String" ControlID="tbCAS" />
<asp:ControlParameter Name="precautions" Type="String" ControlID="tbprecautions" />
<asp:Parameter Name="useAmountUnit" Type="String" />
<asp:Parameter Name="maxStoredUnit" Type="String" />
<asp:ControlParameter Name="KTTdate" Type="String" ControlID="tbDate" />
<asp:ControlParameter Name="supplier" Type="String" ControlID="tbsupplier" />
</InsertParameters>
 
Any clues?

View 2 Replies View Related

How To Insert Variable In One Column

Jan 9, 2008

Hi there

I am migrating all my DTSs to SSISs. One of them is a very basic DTS, that copies data from a text file and places it in a table with one extra column. That extra column is populated with the value of a global variable.

In DTS mode I was creating a custom ActiveX transformation stating
DTSDestination("FieldName")=DTSGlobalVariables("VariableName").Value

Can any one advise me on the best way to accomplish this in the SSIS?
I tried several approaches but all failing.

Many thanks
ds9

View 3 Replies View Related

Insert SP Results Into Variable?

Jul 23, 2005

Is it possible to take the result from an SP that returns 1 value andinsert it into a variable?

View 2 Replies View Related

INSERT INTO Table(@variable) V...

Mar 28, 2008

I have table with Rows
Row1 Row2 ............. Row(n)

I want write query which will change the number on the end of Row(number) AS

DECLARE @I int
SET @I = 0
INSERT INTO table(Row(I)) VALUES ('blablabla')

A then
SET i = i + 1 (Increment variable I)

View 5 Replies View Related

Insert Increment Variable

Oct 31, 2007



This is on SQL 2000 and 2005. I need to increment a variable on insert, but am having a hard time getting a workable solution:

declare @var int
set @var = (select max(id) from table1)

insert into table1

select val1,

@var +1 as val2
from table2
where blah and blah and blah

If I were trying to insert val1's of (5, 10, 20) with @var having an initial value of 100, the added rows should be:

val1 val2
5 101
10 102
20 103

Does anyone know how to do this without temp tables (or table variables)?



View 5 Replies View Related

Insert Local Variable Value

Dec 15, 2006

Is there an expression syntax for putting a local variable value into a text box like there is for putting a parameter value? I'm using the report builder via VS

View 4 Replies View Related

Accessing The Return Variable Of An Insert

May 4, 2008

When I create a tableadapter to do an insert it added
SELECT EventDate, EventID, org_id, ROMEventID FROM ROMEvents WHERE (ROMEventID = SCOPE_IDENTITY())
at the end. This looks useful to me since it implies that I can access ROMEventID which is the auto-incremented identity field.
 How would I reference the variable in my .cs file?
My insert is working as ROMEventsTableAdapter ROMEventInsert = new ROMEventsTableAdapter();
ROMEventInsert.InsertIntoROMEvents(theDate, EventID, org_id);
Thanks for any help

View 2 Replies View Related

Insert Values Of All The Fields Into Variable?

Aug 7, 2014

I need to create a stored procedures, this procedure will receive two parameters:

@TableName

@KeyField

I need to storage into a variable the values of ALL THE FIELDS separeted by |

For example if my table is Customers

CustomerID CustomerName City

111 Adventure Boston

222 Pubs NY

And I execute EXEC mysp 'Customers',111

I need a return value like this

@ReturnValue = 111|Adventure|Boston

I create something like this

CREATE PROCEDURE my sp
@table varchar
@key numeric
AS
@field varchar(40),
@object int
Select @object = object_id from systables where name =@table

--The instructions to create a cursor

WHILE (@@FETCH_STATUS = 0 )
Select @object = name from sys.columns where object_id = @object

--The instructions to close and deallocate the cursor

I have alredy to save the value using the EXEC command but is not working; something like

@String = Select @object from @table where CustomerID = @keyValue

View 1 Replies View Related

Help On Global Variable For Insert Statement

Jan 9, 2007

 

I am new to DTS, but really enjoy it and was wondering if someone could help me with the following small vb app.

I am using the following DTS insert statement to insert records into my table. I have multiple textboxes that needs to be filled and then inserted, none of them exept Nulls. How can I modify my code to insert those textboxes as well as run through the boxes and then check if they have nulls and NOT insert the ones that has nulls?

My form has 9 textboxes. Textbox1, 2, 3  Needs to insert values into the first column. Textbox4, 5, 6 into the second and then Texrbox7, 8, 9 into the last column.

My question is how do I format the following line of code to do what I need?

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('1rowst', '2rowst', '3rowst')"

I think it is something like this, but I am Really not sure and some help would be greatly appretiated:

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('Textbox1', 'Textbox4', 'Textbox7')"

I am really not sure.

Here is all the code:

Public Sub Task_Sub1(ByVal goPackage As Object)

        Dim oTask As DTS.Task
        'Dim oLookup As DTS.Lookup

        Dim oCustomTask1 As DTS.ExecuteSQLTask2
        oTask = CType(goPackage, DTS.Package).Tasks.New("DTSExecuteSQLTask")
        oTask.Name = "DTSTask_DTSExecuteSQLTask_1"
        oCustomTask1 = oTask.CustomTask

        oCustomTask1.Name = "DTSTask_DTSExecuteSQLTask_1"
        oCustomTask1.Description = "Execute SQL Task: undefined"
        oCustomTask1.SQLStatement = "Insert into TestTable (Test1, Test2, Test3) " & vbCrLf
        oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('1rowst', '2rowst', '3rowst')"
        oCustomTask1.ConnectionID = 1
        oCustomTask1.CommandTimeout = 0
        oCustomTask1.OutputAsRecordset = False

        goPackage.Tasks.Add(oTask)
        oCustomTask1 = Nothing
        oTask = Nothing

    End Sub

View 10 Replies View Related

How To Insert SSIS Variable To OLE DB Destination

Nov 29, 2007

I am trying to migrate database from old structure to new structure usign SSIS.

The table in new db have extra field that i need to assign it using variable. this is because i have few customer that having different variable value. (meaning for 1 customer, the variable will be fix for all the tables in the database)

my question, without using the Execute Sql Task, can i assign the variable into the the old db destination?

eg my data flow Task is : OLE Db Source - Derived Column - OLE DB Destination.

Example data

Old structure (key = txnID)
---------------------
TxnID
ChequeNo
Bank (chq Bank - Bank in Bank)
Amount



New Structure (key = TxnID & CoCode)
-------------------------
TxnID
ChequeNo
ChqBank
BankInBank
Amount
CoCode


TQ.



View 6 Replies View Related

Auto Insert A Variable Number Of Records?

Sep 20, 2006

I have the following situation; I have one table (tblA) in which a new record just has been inserted. Once this insert is completed successfully, I want to insert a variable number of records into another table (tblB). The primary key of tblA is being used inside tblB as one of the columns in each insert. I’ve already been able to transfer the primary key, generated by the insert for tblA, pretty easy. But to make things a bit more complicated, the variable number of records to add is being decided by the outcome of a query based on an entry inside tblA (after the insert) and this is then being run on another table (tblC). The SELECT statement from tblC  combined with the Select parameter from tblA will then decide how many records I have to insert. Sorry for the (perhaps) confusing way of writing this down, but I’ve been struggling with this for a couple of days now and I really need to get it working. Anybody who can help?Thanks in advance,Sunny Guam

View 3 Replies View Related

Howto Pass Variable Into Bulk Insert In SP?

Aug 7, 2006

hi, good day,

if i would like to pass a parameter into store procedure for bulk insert command as below



BULK INSERT [mytable] FROM
@FILE_PATH
WITH (FIELDTERMINATOR = ' ', ROWTERMINATOR = '')


howto i put @FILEPATH in above query inside store procedure ?

thanks for guidance :)

View 2 Replies View Related

Variable On Open Query - Insert Statement

Nov 6, 2014

i have a linked server that i use a variable to get the last invoice date and only pull in the latest records that are after the last invoice date but i dont know how to insert that into a table?

DECLARE @TSQL varchar(MAX), @LastDate As Datetime, @LastRunDate As Varchar(6)
SET @LastDate = (SELECT MAX(OrderDate) FROM [SYNC_REPORT].[DPY_SyncReport_Prod].[Stage].[Sales])
Set @LastRunDate = ( select right(convert(char(4), year(@LastDate)), 2) + right('000' + convert(varchar(3), datediff(dd, convert(datetime, '01/01/' + convert(char(4), year(@LastDate))), @LastDate+1)), 3) +100000 )

[code]....

but i get msdtc on server xxxxx is unavaiable?how to get my linked server query into my table

View 3 Replies View Related

Cannot Insert A Variable In SSIS Package Designer

Feb 21, 2007

Hello,
This morning I got an odd problem, I just added a new package to my dtproj and I cannot add a variable. Am I missing anything. ?

here is the image

View 6 Replies View Related

Need Help Using Bulk Insert From A CSV With A Variable Number Of Fields

Dec 6, 2007

Hi folks....

I am able to import a CSV file into a temporary table as long as I know the number of fields in the CSV file. Here is what I would like to do:

I would like to have a CSV file which has UP to 6 entries per row. I would like to insert each row into a table; if the there three fields, then I want to insert them into the first three columns to the temporary table. If there are four, then insert into the first four fields. Is this possible?

Does anyone have any suggestions?

Thanks!

Forch

View 4 Replies View Related

How Can You Use A Variable Tablename And Retrieve The Output From The Insert?

Aug 28, 2007

We are trying to create a unique key from a table with indentity set in the table. We will have a number of these tables. Therefore, we will be creating a stored procedure and passing the table as a parameter. In this example we are setting the table.

When we run the the script, the output clause from the insert should give us a unique number from the given table in the temporary table. This example stores the output in a temporary table @tTemp.

How can you use a variable table name and retrieve the output from the Insert?


declare @tTestTable varchar (20)

set @tTestTable = 'mis.test_sequence'


--DECLARE @tTestTable TABLE ( sqVal [int] IDENTITY(1,1) NOT NULL, add_date datetime)
declare @testsql varchar (4000), @testseq int

DECLARE @tTemp table (mainpk int)

set @testsql = 'DECLARE @tTemp table (mainpk int) INSERT ' + @tTestTable + ' OUTPUT INSERTED.sqVal into @tTemp VALUES (getdate() ) SELECT @testseq=mainpk FROM @tTemp'

select @testsql

EXECUTE sp_executesql @testsql, N'@testseq int output,@tTemp table (mainpk int),@tTemp table (mainpk int) ',@tTemp,@tTemp,@testseq output,@tTemp

SELECT * FROM @tTemp



Please help
Thanks Tim.

View 3 Replies View Related

Anyone Else Get No Body To Msgs After 10/28?

Oct 28, 1998

Anyone else get no body to msgs after 10/28?
Why admin peoples?

View 1 Replies View Related







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