I have to execute this command on my vs.net code:
Code Snippet
SET IDENTITY_INSERT table name ON
I use this to run it:
Code Snippet
.
.
.
Dim command As SqlCommand = New SqlCommand("SET IDENTITY_INSERT table name ON", msSqlConexion)
command.ExecuteNonQuery()
It doesn't throw any kind of errors, but it actually don't execute it, and If I run it on console it works ok.
any help?
Can anybody help me with this command code that stops at the execute and eventually gives timeout.
Dim MM_Cmd, strSQL, strSQL2, strSQL3, strSQL4
Set MM_Cmd = Server.CreateObject("ADODB.Command")
MM_Cmd.ActiveConnection = MM_connAdmin_STRING
strSQL = "update Products_Categories set Depth=NULL, Lineage=''"
MM_Cmd.CommandText = strSQL
MM_Cmd.CommandType = 1
'MM_Cmd.CommandTimeout = 0
MM_Cmd.Prepared = True
MM_Cmd.Execute strSQL
Set MM_Cmd = Nothing
Regards
Amazing
I have a little application that I have designed where I need to be able to execute create table and create function comands against the database.
It seems that it does not like my sql file. Does anyone know of a different method of doing this?
Error message
Line 2: Incorrect syntax near 'GO'.
Line 4: Incorrect syntax near 'GO'.
Line 8: Incorrect syntax near 'GO'.
'CREATE FUNCTION' must be the first statement in a query batch.
Must declare the variable '@usb'.
Must declare the variable '@usb'.
Must declare the variable '@i'.
A RETURN statement with a return value cannot be used in this context.
Line 89: Incorrect syntax near 'GO'.
Line 91: Incorrect syntax near 'GO'.
Line 94: Incorrect syntax near 'GO'.
Protected Sub Install()
Dim err As String = ""
While err.Length < 1
' Dim your StreamReader
Dim TextFileStream As System.IO.TextReader
'Load the textfile into the stream
TextFileStream = System.IO.File.OpenText(Request.PhysicalApplicationPath & "Scripts .sql")
'Read to the end of the file into a String variable.
executesql(TextFileStream.ReadToEnd, err)
err = "Susscessful"
End While
If err = "Susscessful" Then
Response.Redirect("Default.aspx")
Else
Me.lblError.Text = err
End If
End Sub
Private Function executesql(ByVal s As String, ByRef err As String) As Boolean
Try
Dim conn As New Data.SqlClient.SqlConnection(GenConString())
Dim cmd As New Data.SqlClient.SqlCommand(s, conn)
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
Return True
Catch ex As Exception
err = ex.Message.ToString
Return False
End Try
End Function
Example sql file
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MyFunc]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MyFunc]
GO
CREATE FUNCTION [dbo].[MyFunc]
(
-- Add the parameters for the function here
)
RETURNS varchar(1000)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result varchar(1000)
-- Add the T-SQL statements to compute the return value here
-- Do something here
-- Return the result of the function
RETURN @Result
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
hello to all, and I hope you can help.
this is the code from BOL
dtsrun /Sserver_name /Uuser_nrame /Ppassword /Npackage_name /Mpackage_password /Rrepository_name
I have followed it and come up with ...
dtsrun /SPC-409 /Usa /Pmypassword /Nemployee_export /M? /R?
where can I know the repository_name, is it one of the following:
DTS package
Meta data service package
Meta data
all under Data Transformation Service folder in SQL Server
Thanks
Al
Is it possible to create a stored procedure to run a custom dos command (eg. c:ProgramName param1 param2)?
Thanks,
Ben
Hi all,
In SQL Server using xp_cmdshell we can excute any of the command or executable files which can be executed in command prompt. Here my problem is that .. I am trying to execute OSQL from the MSSQL(Query Analyser) using xp_cmdshell.. but its give error saying "'osql' is not recognized as an internal or external command,
operable program or batch file."
This error occours when it is not able to find the executable file... but same thing I am able to execute from the command prompt. So I feel this problem is some where related to the path setting of windows. If some one can solve this problem or sugesst the how to set the path for window it will be help full..
waiting for reply
Hi,
I wanted to know if there is a way to execute sql commands on the operating system's command line. If it is possible, then how do we do it ? For example to execute a SELECT * from Table statement what are we supposed to do ?
Thanks
S
Hi,
I need to execute some store procedures I have in the SQL editor but I seem to been having problems with the formatting for datetime variables needed for the execution of my code. can anyone please help?
thanks in Advance
exec [usp_CMSTemplateCreateNewTemplate] 0, 0, 'My First Page', '', 0, CAST(10/16/2007,DATETIME), CAST(10/25/2007, DATETIME), 0, @PageTemplateID=0
THE STORED PROCEDURE SAMPLE
ALTER PROCEDURE [dbo].[usp_CMSTemplateCreateNewTemplate]
@SiteID AS INT,
@PageID AS INT,
@Title AS NVARCHAR(50),
@EditHREF AS NVARCHAR(512),
@CreatorID AS INT,
@StartDate AS DATETIME,
@EndDate AS DATETIME,
@Child AS INT,@PageTemplateID AS INT OUTPUT
AS
Hi,
How do I execute all the .sql scripts in a folder with OSQL command?
Thanks in advance,
Hari Haran Arulmozhi
I'm wondering if there is any way for me to execute any type of command (delete, insert, create, alter, etc) on management studio without having to wait the server answer.
On Oracle, I use DBMS_JOB. On SQL Server, do I have to create a SQL SERVER Agent Job? What if I don't have permission to create that kind of jobs?
Hello, the following code works perfectly in SQL Server 2000 and SQL Server 2005 Express over WinXP but when run against an instance of SL Server2005 Express over Win2003Server, the first time Command.Execute is invoked returns no error (even though no action seems to be take by the server), subsequent calls return the error -2147217900 couldn't find prepared instruction with identifer -1 (message may vary, it is a translation from may locale)
Any ideas?
Thanks
Code Block
Public Sub Insert_Alarm(sIP As String, nAlarm As Long)
Static cmdInsert As ADODB.Command
Static Initialized As Boolean
On Error GoTo ErrorHndl
If Not Initialized Then
Set cmdInsert = New ADODB.Command
Set cmdInsert.ActiveConnection = db
cmdInsert.Parameters.Append cmdInsert.CreateParameter("IP", adVarChar, adParamInput, Len(sIP), sIP)
cmdInsert.Parameters.Append cmdInsert.CreateParameter("Alarm", adInteger, adParamInput, , nAlarm)
cmdInsert.CommandText = "insert into ALARMS(date_time,ip,alarm,status) values (getdate(),?,?,1)"
cmdInsert.CommandType = adCmdText
cmdInsert.Prepared = True
Initialized = True
End If
cmdInsert.Parameters(0).value = sIP
cmdInsert.Parameters(1).value = nAlarm
cmdInsert.Execute
Exit Sub
ErrorHndl:
...
End Sub
Hi!
I try to find out how to write an tsql program that sends dmx-ddl to SSAS.
this works:
select * from openquery(bdjOLAP, 'select <col> from <modelname>.content')
because it returns an resultset.
but how to something similar this (would fail, because no result set is returned):
select * from openquery(bdjOLAP, 'create mining structure...')
Best regards
Bjorn
Hello.
We have an ASP 3.0 application that currently works "correctly" on one server, Server A, and we€™re testing it on another server, Server B, which is 64 bit.
The connection string for Server A is:
DRIVER={SQL Server};SERVER=...;DATABASE=...;UID=...;PWD=...
The connection string for Server B is:
PROVIDER=sqloledb;SERVER=...;DATABASE=...;UID=...;PWD=...
(Note: Both servers point to the same database on the same server)
The unexpected behavior occurs after calling .Execute on a command. Here is some sample code:
Dim DBConn
Set DBConn = CreateObject("ADODB.Connection")
DBConn.Open strDBConnection '(the ones shown above)
Dim objCmd
Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection = DBConn
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "sp_TestProcedure"
objCmd.Parameters.Append objCmd.CreateParameter("@Name", adVarChar, adParamInput, 50, "Test")
Dim rs
Set rs = objCmd.Execute
For this example, the stored procedure sp_TestProcedure is:
CREATE PROCEDURE sp_TestProcedure
@Name varchar(50)
AS
INSERT INTO tblTest ([Name], Date) VALUES (@Name, getDate())
SELECT COUNT(*) AS 'Count' FROM tblTest
The basic point is the stored procedure does an INSERT and then a SELECT.
Now... to the issue. On Server A, the variable rs above ends up with a single open Recordset which is the results of the SELECT statement.
However, on Server B, rs is set to a closed recordset, and rs.NextRecordset() gets a second recordset of the results of the SELECT statement.
I understand what's going on. Server B is first returning the number of rows affected by the INSERT which translates to a closed recordset. But Server A does not do this.
I would like to know why the default behavior of the command's .Execute is different on the different servers. Does it relate to the Provider/Driver settings in the connection string? Does it have anything to do with 64 bit VS. 32 bit servers?
I know that one way to address this issue to add SET NOCOUNT ON to the start of the stored procedure. But we have many stored procedures, and if the solution is a change in the connection string, that would be preferred. Also, whatever the possible solution is, I also looking to discover *why* it's happening.
Any help would be greatly appreciated.
Hi everybody, I would like to know if it's possible to execute a stored procedure, passing it parameters, using not CommandType.StoredProcedure value of sqlcommand, but CommandType.Text.
I tried to use this:
sqlCmd.CommandType = CommandType.Text
sqlCmd.Parameters.Add(sqlPar)
sqlCmd.ExecuteNonQuery()
With this sql command:
"exec sp ..."
I wasn't able to make it to work, and I don't know if it's possible.
Another question:
if it's not possible, how can I pass a Null value to stored procedure?
This code:
sqlPar = new SqlParameter("@id", SqlDbType.Int)
sqlPar.Direction = ParameterDirection.Output
cmd.Parameters.Add(sqlPar)
sqlPar = new SqlParameter("@parent_id", DBNull)
cmd.Parameters.Add(sqlPar)
doesn't work, 'cause I get this error:
BC30684: 'DBNull' is a type and cannot be used as an expression.
How can I solve this?
Bye and thanks in advance.
P.S. I would prefer first method to call a stored procedure ('cause I could call it with 'exec sp null' sql command, solving the other problem), but obviusly if it's possible...=)
Sorry for grammatical mistakes.
Hi, I need to send a table data into flat and then ftp into different location.
I was using xp_cmdshell via sql task but my network engineer is saying that this xp_cmdshell will break the security and recomond to use "Execute Process Task". If i'm using this task getting the below error.
Could you advice me regrding network engineer thought and any solution for avoiding this error.
---------------------------
Execute Process Task: C:WINDOWSsystem32ftp.exe
---------------------------
CreateProcessTask 'DTSTask_DTSCreateProcessTask_1': Process returned code 2, which does not match the specified SuccessReturnCode of 0.
---------------------------
Thanks,
Hi!
Thanks For your reply!
but this is very urgent please help!!!!!!!
I need one more help in this issue. what if i do not need any "
for e.g: i am trying to set conn string and varaible value
jobCommand = new SqlCommand("xp_cmdshell 'dtexec /f "" + path + "" /Conn "" + Packconn + "" "" + connect + "" '",cconn);
i am getting some value like this :
CommandText "xp_cmdshell 'dtexec /f "D:\SSISProject\Integration Services Project1\ArchiveMainMultiTables.dtsx" /Conn "SE413695\AASQL2005.TestDB;" "Provider=SQLNCLI.1;Data Source=SE413695\AASQL2005;Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;" '" string
I do not need the highlighted escape characters in it. what should i do??????
i need some thing like this :
xp_cmdshell 'dtexec /f "D:SSISProjectIntegration Services Project1ArchiveMainMultiTables.dtsx" /Conn SE413695AASQL2005.TestDB;"Provider=SQLNCLI.1;Data Source=SE413695AASQL2005;Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;"'
Thanks,
Jas
Hi,
I'm looking into the idea of building an enhanced version of dtexec.exe that builds in some extra logging features. My utility will execute packages using the Package.Execute() method.
Thing is, I'd still want to support all of the command-line options that dtexec supports. For example, my utility should accept "/set package.variables[myvariable].Value;myvalue" and pass it through to the executing package but I can't find a way of doing it using Package.Execute().
Am I missing something or is this just not possible?
Thanks
Jamie
[Microsoft follow-up]
Hi all,
Criteria:
We connect to the remote database servers through the network from loca by using Query Analyzer.
Previously we were able to execute the xp_cmdshell command from local Query Analyzer to fetch the remote databases data.
But now we are unable to execute the xp_cmdshell command on remote databases from local Query Analyzer
We do not know what happened but i think due to network updates this command is not able to execute...
For ex:
Previously i was able to execute master..xp_cmdshell 'net start' from local Query Analyzer.But now not able to execute
Now my question is, is there any other way(Directly or indirectly) to execute the xp_cmdshell command on remote databases from local?
Note : we are able to execute this command on remote Query Analyzer but not from local QA
Any suggestions would be very very helpful to me?
Thanks in advance,
Good afternoon-
Can a batch file that resides on another server be executed from a different machine? I have a batch file that resides on a server that I would like to run using SQL 2005 Integration Services. Is there anything I can do that would allow me to remotely execute this batch file and have it run in that environment.
BATCH FILE:
cd C:Trandev
otrun -at OTRecogn.att -DINPUT_FILE=%1 -tl 1 -cs dv -lg mylog -I
C:Trandev represents the remote environment
I have tried mapping the remote machine to a network drive on my local machine and using that drive to execute the batch file in an Execute Process Task, but it does not work.
SSIS:
I have a FOR EACH loop grabbing files and writing fileName to a variable that is passed to the Process Task as an argument through an expression(%1 in the batch file above). The Working Directory is a mapped network drive. The Executable is also a network drive plus batch file name.
Any help would be appreciated.
My computer is a HP Compaq dc7100, 512mb RAM, WindowsXP
Hi,
I'm very new to SSIS and I€™m trying to do the following in a SQL task
RESTORE DATABASE @DatabaseName FROM DISK = @Backup WITH FILE = 1, MOVE @OldMDFName TO @NewMDFPath, MOVE @OldLDFName TO @NewLDFPath, NOUNLOAD, REPLACE, STATS = 10
I'm using an OLE DB connection and I have mapped user variables to the various parameter names. Unfortunately when i test the above command it fails on must declare the scalar variable "@DatabaseName". How can i get my values to be substituted into the command?
Many thanks
Martin
How to execute ssis package from command prompt and also pass configuration file to it and set logging to ssis log provider for sql server. Writing all those options with cmd.
View 3 Replies View RelatedI've a S.P. that need to execute in local PC, how can I execute ?
View 1 Replies View RelatedI have a ForEachLoop parent pkg that reads thru the file system of dtsx to be executed.
I would like to pass CLI parms to the command itself (dtexec). I'm not looking at passing values to the child pkgs but rather additional command line values such as:
/CONFIGFILE "C:myconfig.dtsConfig.
In my expression builder for the PackageName I added the additional string but during the execution, the reference to my dtsconfig is ignored without any errors and the child pkg runs using the default values provided at design time.
@[User::PackageToRun] + " /CONFIGFILE C:\myconfig.dtsConfig ".
How does one add additional CL values during the runtime for Execute Package Task?
Thanks,
Anatole
I have created a job to execute a SSIS package located in the SSIS Package Store. When starting the job I receive an error. The history log reports:
View 12 Replies View RelatedI'm trying to execute a simple VBS file from the Executable command line in the Execute Process Task Editor.
My line is this : cscript.exe "c:convertcsvssisXlsToCsv.vbs"
SSIS keeps saying there are illegal characters here. Â I've Googled and looked about 20 articles and I can't resolve it.
I have a ForEach that loops through Excel files and changes them to CSV files using code i found. This script takes an original Excel file and transfers it to a new CSV file in a new directory.
So in DOS at the CMD line I would type : XlsTocsv.vbs originalfile.xls newfile.csv
I have the original file and new file in the Arguments line so I'm assuming that after the script executes it will look at the filepaths in the loop and loop through them so I want it do to this when it runs:
XlsTocsv.vbs [User::@ExcelFile] [User::@CSVFile]
I just can't get it to execute and I keep getting illegal characters. Â
Hi there
I have small problem with the ADO 2.6 - 2.8 Command object.
I created a Stored procedure preforming some tasks within a tran.
whitin the tran i'm collecting the @@ERROR values and in the end (after tran closing )generating the correct error string - which will be send back to the caller
in the VB6 app, i'm using a command object with the next line:
Set MyRecordset= MyCommand.Execute
In all the cases and option, when i run the SP - the command preform the tasks and return closed recordset (with the currect answer) and i can't read it.
the only solution that worked until now: i need to call MyRecordset.Open which will requery the SP again and generate an error (User defined error).
It happens only when i run command.execute with SP that preforms multitasks and return result recordset.
CAN ANY ONE HELP ME
i am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString() test.InsertCommandType = SqlDataSourceCommandType.Text test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) " test.InsertParameters.Add("roll", TextBox1.Text) test.InsertParameters.Add("name", TextBox2.Text) test.InsertParameters.Add("age", TextBox3.Text) test.InsertParameters.Add("email", TextBox4.Text) test.Insert() i am using UPDATE command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString() test.UpdateCommandType = SqlDataSourceCommandType.Text test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll 123 " test.Update()but i have to use the SELECT command like this which is completely different from INSERT and UPDATE commands Dim tblData As New Data.DataTable() Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True") Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn) Dim da As New Data.SqlClient.SqlDataAdapter(Command) da.Fill(tblData) conn.Close() TextBox4.Text = tblData.Rows(1).Item("name").ToString() TextBox5.Text = tblData.Rows(1).Item("age").ToString() TextBox6.Text = tblData.Rows(1).Item("email").ToString() for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me
View 2 Replies View RelatedHi All,
i am using a OLE DB Source in my dataflow component and want to select rows from the source based on the Name I enter during execution time. I have created two variables,
enterName - String packageLevel (will store the name I enter)
myVar - String packageLevel. (to store the query)
I am assigning this query to the myVar variable, "Select * from db.Users where (UsrName = " + @[User::enterName] + " )"
Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, enterName,. I select that and when I click on OK am getting this error.
Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E0C.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object.".
Can Someone guide me whr am going wrong?
myVar variable, i have set the ExecuteAsExpression Property to true too.
Please let me know where am going wrong?
Thanks in advance.
Do somebody know how long (in chars) script(command) can be solved by SQL Command?
Thanks
reply.
View 1 Replies View RelatedHi. I am writing a program in C# to migrate data from a Foxpro database to an SQL Server 2005 Express database. The package is being created programmatically. I am creating a separate data flow for each Foxpro table. It seems to be doing it ok but I am getting the following error message at the package validation stage:
Description: An OLE DB Error has occured. Error code: 0x80040E0C.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object".
.........
Description: "component "OLE DB Destination" (22)" failed validation and returned validation status "VS_ISBROKEN".
This is the first time I am writing such code and I there must be something I am not doing correct but can't seem to figure it out. Any help will be highly appreciated. My code is as below:
private bool BuildPackage()
{
// Create the package object
oPackage = new Package();
// Create connections for the Foxpro and SQL Server data
Connections oPkgConns = oPackage.Connections;
// Foxpro Connection
ConnectionManager oFoxConn = oPkgConns.Add("OLEDB");
oFoxConn.ConnectionString = sSourceConnString; // Created elsewhere
oFoxConn.Name = "SourceConnectionOLEDB";
oFoxConn.Description = "OLEDB Connection For Foxpro Database";
// SQL Server Connection
ConnectionManager oSQLConn = oPkgConns.Add("OLEDB");
oSQLConn.ConnectionString = sTargetConnString; // Created elsewhere
oSQLConn.Name = "DestinationConnectionOLEDB";
oSQLConn.Description = "OLEDB Connection For SQL Server Database";
// Add Prepare SQL Task
Executable exSQLTask = oPackage.Executables.Add("STOCK:SQLTask");
TaskHost thSQLTask = exSQLTask as TaskHost;
thSQLTask.Properties["Connection"].SetValue(thSQLTask, "oSQLConn");
thSQLTask.Properties["DelayValidation"].SetValue(thSQLTask, true);
thSQLTask.Properties["ResultSetType"].SetValue(thSQLTask, ResultSetType.ResultSetType_None);
thSQLTask.Properties["SqlStatementSource"].SetValue(thSQLTask, @"C:LPFMigrateLPF_Script.sql");
thSQLTask.Properties["SqlStatementSourceType"].SetValue(thSQLTask, SqlStatementSourceType.FileConnection);
thSQLTask.FailPackageOnFailure = true;
// Add Data Flow Tasks. Create a separate task for each table.
// Get a list of tables from the source folder
arFiles = Directory.GetFileSystemEntries(sLPFDataFolder, "*.DBF");
for (iCount = 0; iCount <= arFiles.GetUpperBound(0); iCount++)
{
// Get the name of the file from the array
sDataFile = Path.GetFileName(arFiles[iCount].ToString());
sDataFile = sDataFile.Substring(0, sDataFile.Length - 4);
oDataFlow = ((TaskHost)oPackage.Executables.Add("DTS.Pipeline.1")).InnerObject as MainPipe;
oDataFlow.AutoGenerateIDForNewObjects = true;
// Create the source component
IDTSComponentMetaData90 oSource = oDataFlow.ComponentMetaDataCollection.New();
oSource.Name = (sDataFile + "Src");
oSource.ComponentClassID = "DTSAdapter.OLEDBSource.1";
// Get the design time instance of the component and initialize the component
CManagedComponentWrapper srcDesignTime = oSource.Instantiate();
srcDesignTime.ProvideComponentProperties();
// Add the connection manager
if (oSource.RuntimeConnectionCollection.Count > 0)
{
oSource.RuntimeConnectionCollection[0].ConnectionManagerID = oFoxConn.ID;
oSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(oFoxConn);
}
// Set Custom Properties
srcDesignTime.SetComponentProperty("AccessMode", 0);
srcDesignTime.SetComponentProperty("AlwaysUseDefaultCodePage", true);
srcDesignTime.SetComponentProperty("OpenRowset", sDataFile);
// Re-initialize metadata
srcDesignTime.AcquireConnections(null);
srcDesignTime.ReinitializeMetaData();
srcDesignTime.ReleaseConnections();
// Create Destination component
IDTSComponentMetaData90 oDestination = oDataFlow.ComponentMetaDataCollection.New();
oDestination.Name = (sDataFile + "Dest");
oDestination.ComponentClassID = "DTSAdapter.OLEDBDestination.1";
// Get the design time instance of the component and initialize the component
CManagedComponentWrapper destDesignTime = oDestination.Instantiate();
destDesignTime.ProvideComponentProperties();
// Add the connection manager
if (oDestination.RuntimeConnectionCollection.Count > 0)
{
oDestination.RuntimeConnectionCollection[0].ConnectionManagerID = oSQLConn.ID;
oDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(oSQLConn);
}
// Set custom properties
destDesignTime.SetComponentProperty("AccessMode", 2);
destDesignTime.SetComponentProperty("AlwaysUseDefaultCodePage", false);
destDesignTime.SetComponentProperty("OpenRowset", "[dbo].[" + sDataFile + "]");
// Create the path to link the source and destination components of the dataflow
IDTSPath90 dfPath = oDataFlow.PathCollection.New();
dfPath.AttachPathAndPropagateNotifications(oSource.OutputCollection[0], oDestination.InputCollection[0]);
// Iterate through the inputs of the component.
foreach (IDTSInput90 input in oDestination.InputCollection)
{
// Get the virtual input column collection
IDTSVirtualInput90 vInput = input.GetVirtualInput();
// Iterate through the column collection
foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
// Call the SetUsageType method of the design time instance of the component.
destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READWRITE);
}
//Map external metadata to the inputcolumn
foreach (IDTSInputColumn90 inputColumn in input.InputColumnCollection)
{
IDTSExternalMetadataColumn90 externalColumn = input.ExternalMetadataColumnCollection.New();
externalColumn.Name = inputColumn.Name;
externalColumn.Precision = inputColumn.Precision;
externalColumn.Length = inputColumn.Length;
externalColumn.DataType = inputColumn.DataType;
externalColumn.Scale = inputColumn.Scale;
// Map the external column to the input column.
inputColumn.ExternalMetadataColumnID = externalColumn.ID;
}
}
}
// Add precedence constraints to the package executables
PrecedenceConstraint pcTasks = oPackage.PrecedenceConstraints.Add((Executable)thSQLTask, oPackage.Executables[0]);
pcTasks.Value = DTSExecResult.Success;
for (iCount = 1; iCount <= (oPackage.Executables.Count - 1); iCount++)
{
pcTasks = oPackage.PrecedenceConstraints.Add(oPackage.Executables[iCount - 1], oPackage.Executables[iCount]);
pcTasks.Value = DTSExecResult.Success;
}
// Validate the package
DTSExecResult eResult = oPackage.Validate(oPkgConns, null, null, null);
// Check if the package was successfully executed
if (eResult.Equals(DTSExecResult.Canceled) || eResult.Equals(DTSExecResult.Failure))
{
string sErrorMessage = "";
foreach (DtsError pkgError in oPackage.Errors)
{
sErrorMessage = sErrorMessage + "Description: " + pkgError.Description + "";
sErrorMessage = sErrorMessage + "HelpContext: " + pkgError.HelpContext + "";
sErrorMessage = sErrorMessage + "HelpFile: " + pkgError.HelpFile + "";
sErrorMessage = sErrorMessage + "IDOfInterfaceWithError: " + pkgError.IDOfInterfaceWithError + "";
sErrorMessage = sErrorMessage + "Source: " + pkgError.Source + "";
sErrorMessage = sErrorMessage + "Subcomponent: " + pkgError.SubComponent + "";
sErrorMessage = sErrorMessage + "Timestamp: " + pkgError.TimeStamp + "";
sErrorMessage = sErrorMessage + "ErrorCode: " + pkgError.ErrorCode;
}
MessageBox.Show("The DTS package was not built successfully because of the following error(s):" + sErrorMessage, "Package Builder", MessageBoxButtons.OK, MessageBoxIcon.Information);
return false;
}
// return a successful result
return true;
}
Dear all:
I had got the below error when I execute a DELETE SQL query in SSIS Execute SQL Task :
Error: 0xC002F210 at DelAFKO, Execute SQL Task: Executing the query "DELETE FROM [CQMS_SAP].[dbo].[AFKO]" failed with the following error: "The transaction log for database 'CQMS_SAP' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
But my disk has large as more than 6 GB space, and I query the log_reuse_wait_desc column in sys.databases which return value as "NOTHING".
So this confused me, any one has any experience on this?
Many thanks,
Tomorrow
I'm looking for a way to refer to a package variable within any
Transact-SQL code included in either an Execute SQL or Execute T-SQL
task. If this can be done, I need to know the technique to use -
whether it's something similar to a parameter placeholder question
mark or something else.
FYI - I've been able to successfully execute Transact-SQL statements
within the Execute SQL task, so I don't think the Execute T-SQL task
is even necessary for this purpose.