How To Invoke C# Code From SSIS? Can We Script In C# For Script Task?
Aug 5, 2005How to invoke C# code from SSIS?
View 22 Replies
ADVERTISEMENT
Is It Possible To Ftp Files Using Code In A SSIS Script Task?
Jun 12, 2006Is it possible to ftp files using code in a Script Task? I need to read the contents of an xml file and if it has a a specific file name in there then I ftp the corresponding pdf file which is at the same location as the xml file. However I cannot do this using the provided FTP Task in SSIS, I would need to use code to do this as there are close to 50 xml files which I need to read and upload the corresponding pdf file file it meets a certain criteria.
I do not see a way of looping thru all the files in a folder unless I do this in a Script task. Any inputs or alternative comments on doing this will be appreciated.
Thanks,
MShah
How To Invoke SSIS Package As A Webservice
May 31, 2007Hi Guys,
Anyone can tell me, How to invoke ssis package as webservice ?
Is it possible ? If yes How ?
Yogesh V. Desai. | SQLDBA|
SSIS - Process Task Exit Code 4 - Via SQL Agent Scheduled Job Only - Works If Manually Started....
Oct 10, 2007We have a Process Task component setup in a couple SSIS jobs to call a command batch file to support transfering a file via Secure FTP to other servers and the process works fine if we start the SQL Agent job manually, however when the job is started via the scheduler, it fails with an exit code of 4. Even though there is a proxy setup on the agent job, is there a different user account being invoked by the scheduler??? We're on 2005 SP1 Hotfix 1 (2153). Thanks
Some more info...have found that if we leave a login session open on the server (login is the proxy account) the process works. It appears the issue is associated with a need to render/create a command window for the command line/batch process to run in and without an active windows session it fails....would seem to be that a product setup to run on a server in a batch mode would be able to work without this...is this the case? if so, how? Thanks.
Invoke SSIS From Client-side Batchfile
Oct 9, 2007Until now we worked with SQL Server 7.0, now we're migrating to SQL Server 2005.
With SQL Server 7.0 we had an MS Access application (located on a file server) used by several users, from which's form a batchfile (located on the same server) can be started. The Batchfile deletes an existing TXT-file called import_result.txt, then invokes a DTS-Package (located on a SQL Database server) which imports data to the Access-DB and creates a new file with the result of the DTS-execution (again called import_result.txt). Like that the user gets some information about success (or failure) of the data import.
How could this be implemented with SQL Server 2005 without having to install (and to licencse) SQL Express Engine on the clients? I found some threads about how to invoke an SQL Agent Job from the client to execute the SSIS-Package on the server, but I think that's not exactly what we need. Besides I'm not quite sure how exactly the SQL Agent Job could be invoked from Access.
Do you have any proposals?
Meicee
SSIS Invoke Procedure In Oracle Package
Feb 6, 2008Hello there,
I'am asking you people if it's possible to invoke procedure, functions in package in Oracle database > 9.X with SSIS package...
Thx for your incoming answers
Regards,
KiK2k1
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.
SSIS (Integration Services) Transfer SQL Server Objects Task: This Task Can Not Participate In A Transaction
Feb 1, 2007In 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).
New 2005 SSIS Task: File Properties Task
Nov 7, 2007A common issue that I run across with clients is they want only want to process a file if it's finished transmitting to the server. This SQL Server 2005 task reads the properties of a file and writes the values to a series of variables. For example, you can use this task to determine if the file is in use (still be uploaded or written to) and then conditionally run the Data Flow task to load the file if it's not being used. You can also use it to determine when the file was created in order to determine if it must be archived.
http://www.pragmaticworks.com/filepropertiestask.htm
Source Code For The Standard SSIS Components That Come With SSIS?
Jun 2, 2006Hi,
I'm finding that the standard components often just don't quite meet my
needs, but would only need some fairly minor changes to save me and my team a lot of
work (and produce more elegant solutions). So I was just wondering whether the source code was available for the standard components that come with SSIS, or if there is anyway to extend their functionality?
Or do you just have to start form scratch?
Thanks,
Lawrie
How Do You Get A Reference To The Task Host In An SSIS Task
Feb 26, 2008I've created my own posting for this. The original post was here, I apologize: http://forums.microsoft.com/forums/ShowPost.aspx?PostID=2906512&SiteID=1
According to the poster it's not possible. But there has to be some way to do it? Reflection (don't know how)?
I need to get a reference to the task host in an SSIS Task component.
Basically the scenario is this:
I have a custom task I have created. However I would like to validate that the ExecValueVariable is infact a string variable during the validate event of the task. I know how to verify its a string variable. But I can't figure out how to read what the user selected (such as User::Myvariable). The only way I've been able to figure out how to do it, but it only works if you open my custom task UI.
What I did is this:
I've implemented IDtsTaskUI and during the initialize section I wrote:
Sub Initialize(ByVal taskHost As TaskHost, ByVal serviceProvider As IServiceProvider) Implements IDtsTaskUI.Initialize
' Store the TaskHost of the task.
Me.taskHostValue = taskHost
Dim myTask As CustomTask= CType(taskHost.InnerObject, CustomTask)
myTask.myTaskHost = taskHost
End Sub
My Task is named: CustomTask. I have a public variable in my task as follows:
Public NotInheritable Class CustomTask
Inherits Task
Implements IDTSComponentPersist
Public myTaskHost As TaskHost = Nothing
Therefore I pass back the taskhost value to the CustomTask class, and voila I have it.
Problem is, this only works if the custom task calls the initialize method, and this only happens when you open the custom editor.
I then do the validation in my CustomTask class and it works fine, but myTaskHost is null/nothing until you actually open the custom task UI
How do I solve this?
Boilerplate Code/template For Custom Task UI?
Aug 2, 2006does anyone know of any boilerplate code or templates (codesmith or other code-generation software) that can be used to produce a UI for a custom task?
View 1 Replies View RelatedHow To Capture The Return Code Of An Execute Process Task
Jan 18, 2008Can anyone tell me how to capture the return code of a process launched by an Execute Process Task? I am able to capture the output by using the StandardOutputVariable but can't seem to capture the actual code.
View 4 Replies View RelatedExecute Process Task: Unexpected Exit Code
Jul 5, 2006In Executing "E:EmailDelivery.exe" "EP12A 4" at "", The process exit code was "-532459699" while the expected was "0".
View 4 Replies View RelatedExec SQL Task: Capture Return Code Of Stored Proc Not Working
May 19, 2006I am just trying to capture the return code from a stored proc as follows and if I get a 1 I want the SQL Task to follow a failure(red) constrainst workflow and send a SMTP mail task warning the customer. How do I achieve the Exec SQL Task portion of this, i get a strange error message [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".
Using OLEDB connection, I utilize SQL: EXEC ? = dbo.CheckCatLog
EXEC SQL Task Editer settings:
RESULTSET: Single Row
PARAMETER MAPPING: User::giBatchID
DIRECTION: OUTPUT
DATATYPE: LONG
PARAMETER NAME: 0
PS-Not sure if I need my variable giBatchID which is an INT32 but I thought it is a good idea to feed the output into here just in case there is no way that the EXEC SQL TASK can chose the failure constrainst workflow if I get a 1 returned or success constraint workflow if I get a 0 returned from stored proceedure
CREATE PROCEDURE CheckCatLog
@OutSuccess INT
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
DECLARE @RowCountCAT INT
DECLARE @RowCountLOG INT
---these totals should match
SELECT @RowCountCAT = (SELECT Count(*) FROM mydb_Staging.dbo.S_CAT)
SELECT @RowCountLOG = (SELECT Count(*) FROM mydb_Staging.dbo.S_LOG)
--PRINT @RowCountCAT
--PRINT @RowCountLOG
BEGIN
IF @RowCountCAT <> @RowCountLOG
--PRINT 'Volume of jobs from the CAT file does not match volume of jobs from the LOG file'
--RETURN 1
SET @OutSuccess = 1
END
GO
Thanks in advance
Dave
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB Error Has Occurred. Error Code: 0x8000FFFF.
Jan 28, 2008Hi All,
Recently in an SSIS package I am getting the following error for a particular Data flow task.
Error: 2008-01-25 12:01:48.58
Code: 0xC0202009
Source: Import Datasynapse Data User Events Source [3017]
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF.
End Error
Error: 2008-01-25 12:01:48.73
Code: 0xC004701A
Source: Import Datasynapse Data DTS.Pipeline
Description: component "User Events Source" (3017) failed the pre-execute phase and returned error code 0xC0202009.
End Error
Our guess is when the data size of User Events table is more it throws this error. If we try to transfer small subset of data it succeeds. What could be reason for this error?
Since this is very urgent, immediate response would be very much appreciated.
Thanks & Regards,
Prakash Srinivasan
The Attempt To Add A Row To The Data Flow Task Buffer Failed With Error Code 0x8007000E
Mar 5, 2008
Hi Everyone-
i am facing a memery problem error while i am running the SSIS package
while i am running the package it show the following Error
In Spend Dataload package: A buffer failed while allocating 70485760 bytes.
--------------------------------------------------------------------------------
In Spend Dataload package: The system reports 54 percent memory load. There are 3747647488 bytes of physical memory with 1694883840 bytes free. There are 2147352576 bytes of virtual memory with 1061253120 bytes free. The paging file has 7328251904 bytes with 5083856896 bytes free.
--------------------------------------------------------------------------------
In Spend Dataload package: The attempt to add a row to the Data Flow task buffer failed with error code 0x8007000E.
--------------------------------------------------------------------------------
In Spend Dataload package: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Flat File Source" (2718) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
--------------------------------------------------------------------------------
In Spend Dataload package: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
--------------------------------------------------------------------------------
In Spend Dataload package: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
--------------------------------------------------------------------------------
In Spend Dataload package: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
is there anyone know the solution of that problem and please dont tell me to use extra memery or a hardware solution as this option is not available.
thanx
Maylo
Execute Process Task - Error/Return Code Listing With Descriptive Messages?
May 28, 2008
I'm having a hard time locating a listing of potential error codes and the meanings for the Execute Process Task component...can someone assist?
Thanks
Out Of Process SP Invoke
Dec 25, 2007Team:
How to invoke a stored procedure "out of process", meaning i want to add it to a queue.
Reason: we have a proc that is fired real-time each time an order comes into the system and it take a few seconds and when there is plenty of order activity in the system, the process times out. I would like to essentially add this to a queue and move on and some other process will process from the queue. Is CLR call the way to do? I know that the easiest way can be to add to a table and process from the table every 5 mins or something like that. Thoughts?
Thx
Sri
DTS: Did Not Invoke SQL Mail When DTS Fails
Dec 6, 2000Hi!
I have developed DTS package for various tasks and scheduled to run some time. I have included SQL Mail task to notify me for each process when it fails. The questions is, When I open the package and execute from the menu, it works and it notifies me through SQL Mail task when it fails but when DTS package invokes at scheduled time and it got failed but it did not notify me that process in between has failed. It seems like SQL Mail task does not work when i schedule the package. Is there any one did come across this situation? or is there any way to resolve this issue? Any help would be appreciated!
Thanks,
Mohana
Custom Alerts To Invoke A Job
Aug 19, 2004I want to creat an alert that will back up my database when the log files get to 90% full. When i try to create a new alert and select performance condition and select sqlserver:databases as the object then log file(s) Used size (kb) as the counter but when i go and select the instance i dont see my database in the drop down list but if I change to sql server event alert I see the database i want to use for this alert. I dont want to wait until the log is already full to back it up i want to do it when it hits a percent full. Is there a way to do this as a performance condition? thanks for your help!
View 7 Replies View RelatedHow To Invoke Insert Stored Procedure
Apr 3, 2007Feeling really dumb tonight - below is my stored procedure and code behind which completes (it puts "completed" in TextBox4) but does not insert anything into database.
Questions:1) do in need to include the primary key field in the insert stored procedure?2) do I need a DataAdapter to actually get it running and open and close the connection? STORED PROCEDURE running on SQL 2000 server: ______________________________________
CREATE PROCEDURE newuser003.InsertCompanyInfo
@CS_CompanyName nchar(100),@CS_City nchar(500),@CS_Phone varchar(20)
AS
INSERT into tblCompanyInfo_Submit(CS_CompanyName, CS_City, CS_Phone)VALUES ('@CS_CompanyName', '@CS_City', '@CS_Phone')RETURN
C# CODE BEHIND: ______________________________________________________
public partial class ContractorSubmision : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { } protected void Button1_Click(object sender, EventArgs e) { SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["localhomeexpoConnectionString2"].ConnectionString); SqlCommand cmd = new SqlCommand("CompanyInfoSubmit", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@CS_CompanyName", TextBox1.Text); cmd.Parameters.AddWithValue("@CS_City", TextBox2.Text); cmd.Parameters.AddWithValue("@CS_Phone", TextBox3.Text); TextBox4.Text = "Completed"; }}
[ask] How To Invoke Sqldatasource Command In Method??
Apr 24, 2007hello everybody, i have a question to ask, suppose i have a sqldatasource, can i use it in a method??this is my case, i need to make a new method to count the rows in a datagrid, so i will have to read the sqldatasource. the problem is, how to retrieve it?? usually i use the built in sqldatasource_selected to count the rows.... is there any other way??
View 6 Replies View RelatedHow To Invoke DTS Package From A Stored Procedure
Jan 15, 2001I have a DTS Package to populate data from excel sheet to direct simple table. I like to call this DTS from User interface. thanks in advance
View 2 Replies View RelatedWhy Does SqlCeConnection.Dispose() Invoke GC.WaitForPendingFinalizers()?
Jan 30, 2008I've got an application uses a WPF GUI, is built in VS2008 final, targets .Net Framework 3.5, and uses a Sql Ce 3.5 database for local storage. After a specific WPF window is rebound the application hangs. Hitting pause on the debugger reveals the offending method is a call to SqlCeConnection.Dispose(). Via Reflector I can see this method calls SqlCeConnection+ObjectLifeTimeTracker.Close() which in turn calls GC.WaitForPendingFinalizers(). Within WPF there are some objects (specifically TextBox) which need to have their resources freed on the main application thread. The finalizer thread is waiting on the main application thread, which is in turn waiting on the finalizer thread, resulting in a deadlock.
You can see some additional discussion of the topic including our temporary resolution in this thread.
My question is why is there a call to GC.WaitForPendingFinalizers() buried within the SqlCeConnection.Dispose() call tree?
Can’t Find P/Invoke DLL Sqlceme30.dll
May 14, 2008Hi,
i have Smart Device with following Configuration :
Processor: ARMv4i
OS: Window CE 5.0
I have installed SQL CE 3.0 on my Device.
When I try to make new database on my devcie using Qurey Analyser 3.0 , it gives "ISQLW Provider Failed... ".
When I try Deploye SQL CE 3.0 Database Application using F5 in VS2005 , it give an error "Can€™t find P/Invoke DLL sqlceme30.dll".Plz tell me How can i resolve that problem.
thanks.
VIJAY KUMAR
DTS To SSIS And VB Code
Apr 22, 2008Folks:
After migrating our SQL 2000 DTS packages to SSIS I wanted to know what changes I will have to make to my VB code. Here is the VB code I use to execute 2000 DTS package. Anybody with VB or similar situation.... what changes I will have to make so that I can execute my SSIS (sql 2005) package.
Dim BalancesPackage As New DTS.Package
BalancesPackage.LoadFromSQLServer("SERVER NAME", "", "", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, "", "", "", "DTS PACKAGE NAME")
BalancesPackage.Execute()
Vb Code In Ssis
Feb 21, 2007HI,
I have a function written in vb and want to know if there is a way to use it in an ssis package? Is there a task that I can paste this function in and execute it? Like the vbscript task or T-sql task? The function opens up 2 tables and updates table1 recordset with data from table2 if the (if statement) is correct and It uses 2 do loops. Any ideas?
Thanks
LINQ Layer - How To Invoke A Sort Function
Jun 5, 2007
Dear all,
I'm trying to create a BLL for an objectdatasource to used for a sorted DataGrid. I am trying to learn about how to use LINQ and I'm sure this is a simple question.
var query = (from p in db.MP3Collections select p).OrderBy(?????????);
Now I want to invoke the following method List<MP3Collection> GetAllMP3s(string sortColumns), which can contain a string name for the field in MP3Collection that is to be sorted (and optionally the "desc" tag).
Now I'm resorting tocoding the query on a case by case basis, which is very tiresome (see the partial code below). This works fine, but is very long and tedious coding. I'm not sure how to go about neatening the code, though am sure it involves the ?????? space above.
Any ideas?
public static List<MP3Collection> GetAllMP3s(string sortColumns)
{System.Diagnostics.Debug.WriteLine("Pure sort expression: " + sortColumns);
bool descending = false;if (sortColumns.ToLowerInvariant().EndsWith(" desc"))
{
sortColumns = sortColumns.Substring(0, sortColumns.Length - 5);descending = true;
}string[] columnNames = sortColumns.Split(',');
MP3DataClassesDataContext db = new MP3DataClassesDataContext();
List<MP3Collection> query = (from p in db.MP3Collections select p).OrderByDescending(p => p.fileName).ToList<MP3Collection>();foreach (string columnName in columnNames)
{switch (columnName.Trim().ToLowerInvariant())
{case "filename":if (descending)
{
query = (from p in db.MP3Collections select p).OrderByDescending(p => p.fileName).ToList<MP3Collection>();System.Diagnostics.Debug.WriteLine("Sorting filename descending");
}
else
{query = (from p in db.MP3Collections select p).OrderBy(p => p.fileName).ToList<MP3Collection>();System.Diagnostics.Debug.WriteLine("Sorting filename ascending");
}
break;case "filepath":if (descending)
{
query = (from p in db.MP3Collections select p).OrderByDescending(p => p.filePath).ToList<MP3Collection>();System.Diagnostics.Debug.WriteLine("Sorting filepath descending");
}
else
{query = (from p in db.MP3Collections select p).OrderBy(p => p.filePath).ToList<MP3Collection>();System.Diagnostics.Debug.WriteLine("Sorting filepath ascending");
}
break;
//OTHER CASES HEREdefault:
query = (from p in db.MP3Collections select p).OrderBy(p => p.filePath).ToList<MP3Collection>();
//throw new ArgumentException("SortColumns contains an invalid column name.");break;
}
}
//var query = (from p in db.MP3Collections select p).OrderBy(p=>p.fileName).ToList<MP3Collection>();return query;
}
IS IT Possbile To Invoke DATASTAGE (oracle Package) Job In Dot Net .
Sep 21, 2006Hi ,
Anyone help me. Now we can do Invoke SSIS Package into ASp.NET.
Vice versa
Is there Possbile to invoke Datastage (oracle Job) into ASp.net? . Is already created in Oracle DataStage Server. it call or access through Dot net. Is it possble? Please any one help me.
Thanks & Regards,
Jeyakumar.M
chennai
Can't P/Invoke Sqlceme35.dll With Headless Compact Framework
May 19, 2008I am aware of the many posts that have addressed this issue in the past - I resolved this issue many months ago when it first happened to me. However, I have recently created a headless version of my Windows CE 6.0 based project that uses the Headless versions of the Compact Framework and this problem has suddenly re-surfaced. As in my UI equipped versions, the SQL CE cab files are copied to the device and have been installed. I have also tried copying the sqlceme35.dll to the application folder. The only real difference I can see is that the device is using the headless version so I suspect that there may be some incompatibility.
Has anyone successfully deployed a SCQL CE aplication to a headless device and if so were there any special steps that had to be taken to make it work?
Thanks
Invoke VS20005 Debugger From Report Preview?
Jan 22, 2008Is it possible to initiate debugging a stored procedure in VS2005 by viewing the report (that calls the stored procedure) in preview mode?
Thanks,
-cs
C# Code Convert To SSIS
Jul 5, 2007Hi, all
I've seach the threads before. a lot of discussion about C# to VSA.
So could anyone with experience on conversion from C# to SSIS/VSA give me a suggestion?
I 've C# code application which complete some ETL process. Now I need to convert all the ETL process to SSIS pacakge. the function of part of C# code is to get the result from stored procedure and then write the result to excel file, not row by row.
the C# code call Microsoft.Office.Interop.Excel as a object to write excel file.
But in VSA, I can't add Microsoft.Office.Interop.Excel as a reference so I can convert C# to VB.net regarding writing excel file.
So any solution about that?
any help will be appreciated.........