I'm still trying to learn the advantage of having stored procedures. I have a DTS that uses a Transform Data Task to append the result of a view into a table. All operations are done locally in the server.
Do I have any advantage if I write a stored procedure to insert the view into the table, and then call the stored procedure in the DTS, in stead of using the Transform Data Task ?
Hello, I am working on a module to extract data from a Teradata server to SQL database. I am using a DTS package to extract the data and need to make the data source name (database name and object name) configurable at runtime and to be read from a Config table in the SQL database. What do you suggest is the simplest and most efficient method to do this?
I am trying to use a dynamic SQL query in the data tranform task with the data source as a query. But its giving me a strange syntax error. Can we use a dynamic SQL in the query option of DTS transform task source?
I am importing data from xls file to a db table with a dts. In the time of the dts creation I am using 'Transform Data Task Properties' GUI window to map incoming xls fields (source) to the table columns (destination). Question: Is there any way to invoke the 'Transform Data Task Property' GUI window in dts runtime and use it to change the mapping dynamically in the run time? Thanks, Vadim.
I am trying to read in a flat file, transform the fields and store into a destination database.
In DTS, this works using Transform Data Task Properties. I define the columns and then have a VB script on the Transformations tab that changes any bad data.
Is there a way to do this in SSIS that I can define the column transformations and re-use my VB scripts?
I need to call a stored procedure to insert data into a table in SQL Server from SSIS data flow task. I am currently trying to use OLe Db Destination, but I am not sure how to map inputs to OLE DB Destination to my stored procedure insert. Thanks
I have an Access 2.0 database that holds call data on a mapped drive. I am running MS SQL Server 2000. I can open it and view the records inside. I can even run the query below and get results, if I removed the CallDate and CallTime parameters.
SELECT CallDate, CallTime, Mid(CallRecordData, 68, 3) AS Extension, 'I' AS Direction, Mid(CallRecordData, 34, 11) AS Called, Val(Mid(CallRecordData, 18, 2)) + Val(Mid(CallRecordData, 21, 2))/ 60 AS Minutes, Val(Mid(CallRecordData, 21, 2)) AS Seconds FROM CallRecords WHERE (CallDate = ?) AND (CallTime >= ?) AND (CallTime < ?) AND (Mid(CallRecordData, 30, 1) <> '9')
When I preview in the Transform Data Task, I get: Package Error Error Source: Microsoft JET Database Engine Error Description: No value given for one or more required parameters.
When I look at the parameters, they are listed. I check their values, and they have the appropriate values (DateCalled, String, 07/14/2005) (StartTime, String, 06:30) (EndTime, String, 07:00)
When I run it in the build query or in Access with a linked table to the source, I can enter the values when asked for them and it works.
i have too many DTS packages to migrate to SSIS, and while examining a DTS package in BIDS (converted with the migration utility) i tried to edit the resulting migrated package, which opened the DTS interface with the two connection icons joined by the big fat arrow with a gear on it...not exactly what i had in mind, iow, it looks like SSIS on the outside, but its still DTS on the inside. So I stripped out a series of components from a more complex package hoping that simplifying it would reveal the contents of old DTS Transformations tab at least partially set up in a Derived Column transformation. Can i get there from here, or must i recreate every stinking definition in a derived column manually from the ground up? thanks very much for your help
Dear All! I use a "Execute SQL Task" to call a Stored Procedure. My Stored Procedure have an input parameter with type: ntext. And in "Execute SQL Task", I set variable in Parameter Mapping as following:
Variable Name: User::xmlDocument (this variable is a String to store xml data) Direction: Input DataType: NVARCHAR ParameterName: 0
When length of "User::xmlDocument" is too large then error is happen but on the contrary, "Execute SQL Task" run successfully. So, Can you show me how to pass large data into stored procedure using "Execute SQL Task"? I am looking forward to hearing from you Thanks
Hi JayH (or anyone). Another week...a new set of problems. I obviously need to learn .net syntax, but because of project deadlines in converting from DTS to SSIS it is hard for me to stop and do that. So, if someone could help me some easy syntax, I would really appreciate it.
In DTS, there was a VBScript that copied a set of flat files from one directory to an archive directory after modifying the file name. In SSIS, the directory and archive directory will be specified in the config file. So, I need a .net script that retrieves a file, renames it and copies it to a different directory.
I am using execute sql task to run a stored procedure in oracle database which returns a resultset. This works. Now I need to send the ouput to a destination table in a sql database. Should I use for each loop to pick the resultset and insert it into the destination one by one (which I dont think is a great idea) or is there a better way to accomplish this task (in data flow task) ?
When I use dataflow task instead of execute sql task, the main issue is I am not able to see the output columns when I execute an oracle stored procedure, but when I see the preview I can see the resultset . But I can see the output columns for a sql server stored procedure.
I'm having a hard time to getting back an xml data back from a stored procedure executed by an Execute SQL task.
I'm passing in an XML data as a parameter and getting back resulting XML data as a parameter. The Execute SQL task is using ADO connection to do this job. The two parameters(in/out) are type of "string" and mapped as string.
When I execute the task, I get the following error message.
[Execute SQL Task] Error: Executing the query "dbo.PromissorPLEDataUpload" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 2 ("@LogXML"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I also tried mapping the parameter as XML type, but that didn't work either.
If anyone knows what's going on or how to fix this problem please let me know. All I want to do is save returning XML data in the parameter to a local package variable.
I'm using SSIS in Visual Studio 2012. My Execute SQL Task calls a Stored Procedure where I have a TRY-CATCH. Last week there was a problem and the CATCH was executed and logged an error to my error table, but for some reason the Execute SQL Task didn't fail. Is there a setting to make the Execute SQL Task fail when an SP encounters a failure?
I have a SQL Task that calls a stored procedure and returns an output parameter. The task fails with error "Value does not fall within the expected range." The Stored Procedure is defined as follows: Create Procedure [dbo].[TestOutputParms] @InParm INT , @OutParm INT OUTPUT as Set @OutParm = @InParm + 5 The task uses an OLEDB connection and has a source type of Direct Input. The SQL Statement is Exec TestOutputParms 7, ? output The parameter mapping is: Variable Name Direction Data Type Parameter Name User::OutParm Output LONG @OutParm
I am trying to use a DTS package to get data from db2 in a s390 environment. I am able to use the Import task and then run a query on db2, save the package and execute the package.But when i try edit the transform task i get a mmc.ese application error...it says that the instruction at addres "" tries referencing memory at address "". The memory could not be read...
I installed a ibm odbc driver on my client...obviously the connection seem to work since the package executes...But then the edit issue...
If any one faced this problem or know what i am doing wrong....appreciate ur time and effort... Thanks
The thing is I am using a DTS package to get data from db2 residing on a s390-mainframe and put it in a sql 2000 db on windows 2000 server..
I have the odbc driver that the server guys gave me..I installed it and then run a batch pgm, which i think does the database set up...in short i set up the DSN's in my local machine..I can use the dsn when i use the sql client...
I can't create a new package and then complete the task..Problem is when i try set up a transform task, i get a memory error...it says that the instruction referenced a memory location that can't be read...
So what i do is i use the import task in sql...which inturn uses the same odbc connection and runs the query on db2..inserts the results into a table in sql..then save the package...Only problem is that i can't edit the transorm task...same memory error...
Any idea why this happens ?? I would like to see how i can get past this using Odbc...
Have you used the stored procedure task? What is the difference in calling a stored procedure using the stored procedure task in SSIS Than calling a stored procedure using T-SQL?
I have a stored procedure thats transferring/processing data from onetable to two different tables. The destination tables have a uniquevalue constraint as the source tables at times has duplicate recordsand this will prevent the duplicates from being reported. When thestored procedure (which includes a cursor) is executed through queryanalyzer, it runs fine, and reports an error everytime it sees aduplicate value (as expected). It moves all the unique values from thesource to the destination tables.However, if the same stored procedure is run as a task/job in SQLServer Agent, the behaviour is different. The job fails when it see'sthe error and ends up skipping records or terminating the procedureall together. Eg. if there are 100 records in the source table with 10duplicates, the stored procedure when run through Query Analyzer willcopy the 90 unique records to the destination tables but when run fromSQL-Agent, it copies just 10-15 records.Any idea why this happens?
I am not sure if this is a correct place to post this question. i am making a simple pay bill system, require people set a schedule that pays bill, then save it into database, when the time come, it auto transfers the money, i am thinking if i can do this in a store procedure. here is the interface:From Account:To Payee:Amount:ScheduleDate: Save the schedule task View scheduled task
I'm attempting to call a storedprocedure from within an ActiveX task ina DTS and am getting a "Command Text was not set for the commandobject" error. I have no problem if I replace the stored procedure callwith the actual SQL.Is it possible to directly call a SP via an ActiveX task? If yes, couldsomebody help me with the syntax please?This is what I have currently haveSet Conn = CreateObject("ADODB.Connection")SQL = "exec (MySP)"set RLoop=conn.Execute(SQL)do while (NOT RLoop.EOF)msgbox RLoop(1)RLoop.MoveNextLoopTIAKarthik
Dear all! I have a SSIS packet. I declare a parameter that have scope of packet. I want to pass this parameter into a stored procedure and call this stored procedure from "Execute SQL Task". Please help me how to call stored procedure with parameter from "Execute SQL Task". Thank you very much
I am trying to exectue a stored procedure through execute sql task which takes two input parameters table name and filegroup to create a table and it also returns a value.
i am using the oledb connection
but i am getting the following error
[Execute SQL Task] Error: Executing the query "declare @rtn_value int exec spm_Utopia_FinanceUsage_Create_MonthlyTable ?,? select @rtn_value as rtn_value " failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
i tried with ado.net connection by directly giving the parameter names.
it will create a table but the the table name is something different from what i specified
In my current project i have a requirement to assign value of an aggregate transform to a variable. But i need to accomplish it without using a script task.
I have One package that it contains one Execute SQL task in that i have placed a Stored procedure . Now i want to pass values to Stored procedure parameters from a databse table by dynamically .For this i am trying to use " Script task " How can i pass that table column values to that stores procedure thru using Script Task?
I had the SP, I want to call in Script Task , had the Result set data value then I need pop up message box. So how can I call stored procedure result in message box in ssis script task using C#.
and I want to use SSIS -OLEDB connection.
ConnectionManager cm; System.Data.SqlClient.SqlConnection sqlConn; System.Data.SqlClient.SqlCommand sqlComm; cm = Dts.Connections["OLE_TEST_"]; sqlConn = (System.Data.SqlClient.SqlConnection)cm.AcquireConnection(Dts.Transaction); sqlComm = new System.Data.SqlClient.SqlCommand("Exec dbo.sOp_xx_XXXe_VXX 280", sqlConn); sqlComm.ExecuteNonQuery();
