Map Resultset From Executing A Stored Proc Into Input Columns Of A Data Flow Task
I need to loop the recordset returned from a ExecuteSQL task and transform each row using a Data Conversion task (or a Script Task).
I know how to loop the recordset returned by an ExecuteSQL task:
http://www.sqlis.com/59.aspx
I loop the returned recordset (which is mapped to a User variable of type System.Object) and assign the Variable Mappings in the ForEach Loop to different user variables which map to the Exec proc resultset (with names and data types).
I assume to now use these as the Available Input columns for the Data Conversion task, I drag a Data Flow task inside the For Each Loop container and double-click it, then add a Data Conversion task.
But the Input columns (which I entered in the Variable Mappings in the ForEach Loop containers) dont show up in the Available Input columns of the Data Conversion task.
How do I link the Variable Mappings in the ForEach Loop containers from the recordset returned by the Execute SQL Task to the Available Input columns of the Data Conversion task?
.......................
If this is not possible, and the advice is to use the OLEDB data flow as the input for the Data Conversion task (which is something I tried too), then the results from an OLEDB Command (using EXEC sp_myproc) are not mapped to the Available Input columns of the Data Conversion task either (as its not an explicit SQL Statement and the runtime results from a stored proc exection)
I would like to use the ExecuteSQL task to do this as the Package is clean and comprehensible. Which is the easiest best way to map the returned results from a Stored proc execution to the Available Input columns of any Data Flow transformation task for the transform operations I need to execute on each row of data?
[ Could not find any useful advice on this anywhere ]
thanks in advance!
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Data Flow Task Within For Each Loop Not Executing
All: I am sure I am missing something really silly but I am not able to figure out what. The For Each Loop uses an ADO Enumerator and passes variable values to a data flow. In executing the package the loop runs fine but nothing is happening to the data flow. When I move the data flow out of the loop it runs fine. What is going on? Thanks! desibull
View Replies !
View Related
Executing A Stored Proc On Another Server From A Scheduled Task
Ok, I thought this one would be easy.I have a stored proc: master.dbo.restore_database_fooThis is on database server B.Database server A backs up database foo on a daily basis as a scheduledtask.What I wanted to do was, at the end of the scheduled task is then call thestored proc on B and restore the database.If I go into Query Analyzer and log into database A, then execb.master.dbo.restore_database_foo works.But if I take the same command and make it part of the scheduled task itfails.Error is:OLE DB provider 'SQLOLEDB' reported an error. [SQLSTATE 42000] (Error 7399)[SQLSTATE 01000] (Error 7312). The step failed.To me this seems like a permissions issue, but nothing I've tried seems tohave helped.Suggestions?----Greg D. MoorePresident Green Mountain SoftwarePersonal: http://stratton.greenms.com
View Replies !
View Related
DTS Or Import Data Into SQL Server 7/2000 From Oracle Stored Proc Returning Resultset
Dear Brains of the Universe. Here is my challenge or I think it is that I am challenged. The scenario is, after days of searching I have finally managed to find how to return resultsets from an Oracle stored procedure. What I want to do is get this resultset into an SQL Server table using T/SQL or DTS. 1) Question 1. Is this possible? (I bet there is a way to do it, but just dunno how..) 2) Question 2. Hmmm, Is there any other way I can achieve what I want to achieve? Ok here is the SQL Script for the Oracle Package, function and statement to view the resultset in oracle. (remember to use SQL* Plus). /* ||Package Creation */ create or replace package Types as type cursorType is ref cursor; end; / /* ||Function Creation */ create or replace function sp_ListEmp return types.cursorType as l_cursor Types.cursorType; begin open l_cursor for select ename, empno from emp_order by ename; return l_cursor; end; / /* ||View Resultset */ variable c refcursor exec :c := sp_ListEmp print c I hope this enuff to explain my pridacament. Any help would be most gracefully appreciated. Thanks in advance.
View Replies !
View Related
Data Flow Task - Multiple Columns From Different Sources To A Single Table
Hi: I have a data flow task in which there is a OLEDB source, derived column item, and a oledb destination. My source is a SQL command, that returns some values. I have some values, that I define in the derived columns, and set default values under the expression column. My question is, I also have some destination columns which in my OLEDB destination need another SQL command. How would I do that? Can I attach two or more OLEDB sources to one destination? How would I accomplish that? Thanks MA2005
View Replies !
View Related
OLE DB Source Task In The Second Control Flow Data Task Fails After The Same Task Succeeds In The First Control Flow Data Task
I am trying to run a SSIS package as an agent job. The package has several control flow data tasks. Each of the control flow data tasks uses a OLEDB source task to connect to the same SQL 2005 database(defined by a single connection manager) using SQL server authentication and basically pulls some data. The first control flow data task succeeds, but the second one fails and so he package errors out. My question is why would the second OLEDB task be failing when it uses the same connection manager as the frist one? Here are some facts 1) the package runs fine in BIDS from my development wkstation. 2) the package uses an XML configuration file which specifies the sql server password and connectionstring 3) the package runs on a 64 bit sql server box with SP1. 4) the job is running under sql agent service account. 5) I created the package as well as the agent job. 6) the ole db source is a sql 2005 server. 7) the error message generated by the second task(oledb source) is "The AcquireConnection method call to the connection manager "Source" failed with error code 0xC0202009." I have read about some issues running SQL authenticated ssis packages running under job agent but i wonder if this is related. thankyou, js40
View Replies !
View Related
Lookup Task Data Flow Transformation Causes Data Flow Task To Hang?
Hi, I'm trying to implement an incremental data pull (Oracle to SQL) based on Andy's blog: http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspx My development machine is decent: 1.86 GHz, Intel core 2 CPU, 3 GB of RAM. However it seems the data flow task gets hung whenever I test the package against the ~6 million row source, as can be seen from these screenshots. I have no memory limitations on the lookup transformation. After the rows have been cached nothing happens. Memory for the dtsdebug process hovers around 1.8 GB and it uses 1-6 percent of CPU resources continuously. I am not using fast load to insert new records into my sql target table. (I am right clicking Sequence Container 3 and executing this container NOT the entire package in the screenshots) http://i248.photobucket.com/albums/gg168/boston_sql92/1.jpg http://i248.photobucket.com/albums/gg168/boston_sql92/2.jpg http://i248.photobucket.com/albums/gg168/boston_sql92/3.jpg http://i248.photobucket.com/albums/gg168/boston_sql92/4.jpg http://i248.photobucket.com/albums/gg168/boston_sql92/5.jpg http://i248.photobucket.com/albums/gg168/boston_sql92/6.jpg The same package works fine against a similar test table with 150k rows. http://i248.photobucket.com/albums/gg168/boston_sql92/7.jpg http://i248.photobucket.com/albums/gg168/boston_sql92/8.jpg The weird thing is it only takes 24 minutes for a full refresh of the entire source table from Oracle to the SQL target table. Any hints,advice would be appreciated.
View Replies !
View Related
I Exec A Stored Proc From VB... No Resultset
using VB5, SQL 6.5 and ADO to connect how can I get SQL to send me a signal, flag, whatever when a stored procedure is finished running. Let me explain... I exec a stored Proc from VB... no resultset returned since SQL does all the processing (moving data between tables, blah, blah, blah) and when it`s done, that`s it... however I would like SQL to send me something back to the front end app so that I can put a msgbox saying.. the sp has finished running. Thanks
View Replies !
View Related
Need Help Executing A Proc In Execute SQL Task
Hello, I am trying to use an Execute SQL Task to execute a stored procedure with parameters in SSIS but it keeps giving me errors, for example I type in 'exec someproc ?, ?, ?' in SQL Task it wouldnt parse correctly and when I set the bypassPrepare to 'true' the package runs but gives me error at the destination saying couldnt find stored procedure 'someProc'. Does anybody know how I can solve this problem? Also how do I pass the results of the queries in the Execute SQL Task into the Data Flow Task? The proc is supposed to retrieve data and transform them into XML format to be placed at a XML file. I am not sure if I should used an OLEDB source or an XML source since the data has been transformed into XML. Any help will be greatly appreciated. Please note proc runs fine when it does not have any parameters.
View Replies !
View Related
MSMQ Connection Manager And Input Into A Data Flow
Firstly the disclaimer: im a total SSIS bunny. Ok, with that out of the way... What i want to do is have data read from an MSMQ message (which will be a simple XML message) and have it transformed and inserted into a simple table. Sound simple, but i cant work out how to do it. I started a new Integration Services Project and dropped a Data Flow task on the Control flow tab, then double-click it to go to the data flow tab. I have added a MSMQ connection manager which points at my private queue and i can hit the test button and it sees it tests ok. How do i wire that into my data flow? On my dataflow tab i have a SQL Server Destination (set to the local SQl server instance) and ive been trying a DataReader source and try setting the Advanced Editor "Connection Manager" column on IDBConnection row to point to my MSMQ connection manager. I get some error "cannot aquire a managed connection from the run-time connection manager". How do i get a Data Flow source which reads the XML message off my MSMQ connection manager?? On the Control Flow tab you can add a Message Queue Task and i can bind it to my MSMQ connection manager and set it up as a recieve task etc, but how do i use that? Help!
View Replies !
View Related
Problem Executing A Stored Proc
Hi All, I have stored proc that processes about 60,000 rows using a cursor. When I call the SP from Query Analyzer, I get the following error message after processing about 12,000 records : Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (InvalidParam()). Server: Msg 11, Level 16, State 1, Line 0 General network error. Check your network documentation. ODBC: Msg 0, Level 16, State 1 Communication link failure Connection Broken 12614 records What can i do to make this SP run sucessfully ? I even tried using a table variable instead of a cursor, but got the same result. THE output from SP_CONFIG is Option config_value ---------------------------------------------------------------------------------------------------------------------------------- affinity mask 0 allow updates 0 awe enabled 0 c2 audit mode 0 cost threshold for parallelism 5 Cross DB Ownership Chaining 0 cursor threshold -1 default full-text language 1033 default language 0 fill factor (%) 0 index create memory (KB) 0 lightweight pooling 0 locks 0 max degree of parallelism 0 max server memory (MB) 2147483647 max text repl size (B) 65536 max worker threads 255 media retention 0 min memory per query (KB) 1024 min server memory (MB) 0 nested triggers 1 network packet size (B) 4096 open objects 0 priority boost 0 query governor cost limit 0 query wait (s) -1 recovery interval (min) 0 remote access 1 remote login timeout (s) 20 remote proc trans 0 remote query timeout (s) 0 scan for startup procs 0 set working set size 0 show advanced options 1 two digit year cutoff 2049 user connections 0 user options 0
View Replies !
View Related
Executing Stored Proc Across Network
I am trying to execute stored procedures accross a network, which return recordsets. The problem I'm having is as follows: I need to return the recordset with cursor type adOpenStatic and not adOpenForwardOnly, which is the default when using the execute command. So I decided to use the open cammand instead which seemed to work. the probelm is that it takes a really long time to process the open command, but the execute is very fast. Does anyone know why this might be? Here is the code which I am using: bool SOMEFUNCTION( ..... ) _CommandPtr objCmd = NULL; objCmd.CreateInstance(__uuidof(Command)); bool retval = true; try { objCmd->ActiveConnection = connection; objCmd->CommandText = "sp_as_played_list"; objCmd->CommandType = adCmdStoredProc; recordset->CursorLocation = adUseClient; // WORKS VERY SLOWLY - get to have Static cursor recordset->Open( (IDispatch *)objCmd, vtMissing, adOpenStatic, adLockReadOnly, adCmdStoredProc ); // Or - WORKS VERY QUICKLY - adOpenForwardOnly default cursor recordset = objCmd->Execute( NULL, NULL, adCmdStoredProc); if(recordset->State == adStateClosed) { retval = false; } thanks aidan
View Replies !
View Related
Executing A Stored Proc From DTS Package
My current task in my company is... I got a Spreadsheet from which i have to get the data into a "Staging Table" and from there the data has to go into 10 different Tables in my database.. I had created a DTS package using ActiveX script and written a Stored Proc to get the records from Staging table to Database tables...every thing is working fine... but... they asked me to send "TapeType' and "DealName" into two fields and "current datetime" into one more field of the stagingtable fields which are not present inthe Spreadsheet.... so in the Activex script i hard coded... destination"TapeType"= "BidTape" and destination"DealName"= "ABCD" and its working... and for currentdate i had defined a DTS Task and in that i had written a SQL stmts.. update table.... set currentdate = getdate() where TapeType = 'BidTape' ..... which is also working....but the problem is if i got one more spread sheet with same "TapeType" i.e. BidTape but with different "DealName" i.e. "XYZ".... every thing is working out but....the Currentdate field is getting updated every where with as one date..??? can u suggest me a better solution here..... and after this Iam planning to create one more Task to Execute the Proc..... but I have to pass 2parameters i.e. "Dealname" and "TapeType"... they want every thing to be get done with single mouse click... can u suggest me here how to pass these 2parameters in the DTS package,.. I will be waiting for any one your valuable suggestions....
View Replies !
View Related
Compare Performance (Execute SQL Task Insert And Data Flow Task)
I am using SQL 2005 SSIS. I am joining several large tables and then the move result into another table in the same database. I would like know which method is faster: Use Execute SQL Task to insert the result set to the target table Use the Data Flow Task to insert the result set to the target table. (Use OLE DB source to execute SQL command and then use the SQL destination) Could you tell me why then other is slower? Thanks.
View Replies !
View Related
Trigger Not Executing CLR Code/stored Proc
I have a database trigger that is set to call a CLR trigger/stored proc when a certain field in a table is updated. The issue is that if i execute the stored proc manually in enterprise studio, it works perfectly but the same call made through the trigger does not go through. A few more details - I have CLR integration enabled on the sql server. The dbo has UNSAFE ASSEMBLY rights I have the both the assembly and the serialized dll imported in the database. Here's the definition of the stored proc - CREATE PROCEDURE [dbo].[WriteXMLNotification] @TaskID [nvarchar](20) WITH EXECUTE AS CALLER AS EXTERNAL NAME [DataInterfaceWebServices].[TaskUpdateXMLWriter.WriteXMLNotification].[run] and the trigger - CREATE TRIGGER [dbo].[tr_Task_U] ON [dbo].[_Task] FOR UPDATE, INSERT AS IF UPDATE (TaskType_Status) OR UPDATE (TaskType) OR UPDATE (TaskType_SubType1) BEGIN SET NOCOUNT ON; DECLARE @status AS INT DECLARE @taskType AS INT DECLARE @taskSubType AS INT DECLARE @taskID as sysname DECLARE @cmd as sysname DECLARE @parentTask as sysname DECLARE @NotificationXMLTaskID as sysname SELECT @status = [TaskType_Status] FROM inserted SELECT @taskType = [TaskType] FROM inserted SELECT @taskSubType = [TaskType_SubType1] FROM inserted SELECT @taskID = [TaskID] FROM inserted SELECT @parentTask = [Parent_TaskID] FROM inserted SELECT @NotificationXMLTaskID = [MCCTaskID] FROM _TaskNotificationXML WHERE [MCCTaskID] = @parentTask IF (@status = 2602) AND (@taskType = 2282) AND (@taskSubType = 19500) BEGIN exec WriteXMLNotification @taskID; END ELSE IF (@taskType = 2285) AND (@parentTask IS NOT NULL) AND (@NotificationXMLTaskID IS NOT NULL) BEGIN exec WriteXMLNotification @parentTask; END END I stepped into the trigger and it seems to execute the line " exec WriteXMLNotification @taskID;" but nothing happens, but if I run that same line manually, it works. Could it be that the impersonation by the EXECUTE AS clause is causing it to fail? Please advise! Thanks in Advance, -Mihir Sonalkar.
View Replies !
View Related
Error Using Row Count Task In Data Flow Task
Hi, I'm trying to get a record count out of a databse using OLE DB Source and row count tasks but keep getting an error. I set up a variable as int32 and select the variable name in the row count task and when I go to the Input Columns tab to select a field to count, it gives me this error: Error at Data Flow Task[Row Count[505]]: The component "Row Count" (505) has forbidden the requested use of the input column with lineage ID 32. I don't even know what this means? thanks,
View Replies !
View Related
Strange Timeout Problem When Executing Stored Proc
We have a stored procedure that will return at most about 600 records. In almost all cases it works fine, but in a few odd cases the command times out when used from a webform. We changed the CommandTimeout property to 120 seconds... no difference. If we run a query that times out in Query Analyzer, it runs in under 3 seconds (on the SQL Server machine). We also did a lot of profiling, and for any page that runs fine, the query takes about the same time when called from a webform and run in the Query Analyzer. We ran our tests on the pre-production environment with absolutely no one else on the machines. We also created a test page that just fills a DataSet. Straightforward... get connection object, create command, set params, create data adapter, fill dataset. It suffers the same problem. The web server and SQL server are two separate machines. Oh, and before I forget... This is SQL 2000. It used to run SQL 7, and all the queries there were (slightly) slower, but none timed out, not even the ones that do now. Maybe the first question should be: How can it be that a query that runs fine in Query Analyzer times out from a webform?
View Replies !
View Related
Executing Stored Proc On Multiple Rows From Select
I have a couple of stored procs I'd like to execute sequentially building the parameter list from the result of a SELECT statement. Each proc takes a large amount of parameters, and each proc takes several actions (a number of INSERTs and a number of UPDATEs) The procs themselves are pretty bulky and probably somewhat error prone. For that reason, I'd like to avoid modifying them, potentially having to spend alot of time debugging there. I'd like to basically have it work like this, but this doesn't work out syntactically: SELECT exec sp1 f1,f2,f3,f4... exec sp2 f2,f4,f5,f6... FROM SomeTable I first thought the easiest parallel would be to move the sps to UDFs and use the UDFs, but when I tried this it failed due to my trying to INSERT/UPDATE from a UDF. I've read several posts like mine, and the suggestions are usually to convert the proc to a UDF (like I tried, but I need INSERT/UPDATE), or to rework the proc involved. Here I need 2 procs to exec on the SELECT data, and it would probably be much more of a hassle to rework these procs than the performance penalty the cursor use will involve. That said, my thought is to use a cursor. I understand how to use the cursor, and I'm pretty sure this will work just fine. Before implementing that though, I wanted to see if anyone had any alternate suggestions since cursors seem to be the "unnatural" way of doing most things.
View Replies !
View Related
Urgent. Output Columns Are Not Appearing When I Use OLEDB Data Source With An Oracle Stored Procedure In Dataflow Task
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.
View Replies !
View Related
Recompile SQL Task With Data Flow Task
Hi, I created a package with SQL 2005. The package gets the Access DB and then inserts it into SQL Server. If I open the package in .NET, I can see the SQL Task and Data Flow Task. The SQL Task has a property sqlstatementsource, which has the necxessary SQL code to create the tables. How can I tell the SQL Task to recompile the SQL code if I give it another DB name, because the tables differ and don't map in the Data Flow Task Thanks
View Replies !
View Related
Executing A Stored Proc Which Calls SSIS Packages Using A Proxy Accnt
Hi all, I have a problem while I create a proxy account.The situation is like this...There is a user who has an login in to the server.He has a stored procedure which calls some on the SSIS packages and XP_cmdshell...so this stored procedure basically load some data in to the tables .So for the login in order to execute the stored procedure as he is not a Sys admin I have created a proxy account in my account as Iam an SA and then in the proxies and in principals I selected his login name and this way I have created a credential and a proxy account. Now the problem is if he logins with his id and password and try to execute the stored procedure it gives an error message Server: Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1 The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information. ....so this mean the login is not able to see the proxy account.So what I did is I created a job and then in the job owner tab I have selected his login and then created a step with the type operating system (CmdExec) as I need to just execute the stored procedure and used the proxy account that I have created. so I gave the command -- exec <stored procedure> --. But this job fails and gives the error message as [298] SQLServer Error: 536, Invalid length parameter passed to the SUBSTRING function. [SQLSTATE 42000].... So now ....first My question is am I doing in a right way....if its right then why Iam not able to execute the stored procedure. If there is any other way through which I can execute the stored procedure using a proxy account for the logins who are not sys admins....please do let me know..... Thanks Raja.V
View Replies !
View Related
Removing Columns From A Data Flow
Seems obvious but I can't see how. How would I remove columns from a data flow so that columns which have been used earlier but are not needed for insert/update are taken out of the flow. I'm asking because the data ends up in a update statement and the flow has got so big it is unreadable. Cheers, Al
View Replies !
View Related
Data Flow: Converting Data In Multiple Columns
Hi, I'm just wondering what's the best approach in Data Flow to convert the following input file format: Date, Code1, Value1, Code2, Value2 1-Jan-2006, abc1, 20.00, xyz3, 35.00 2-Jan-2006, abc1, 30.00, xyz5, 6.30 into the following output format (to be loaded into a SQL DB): Date, Code, Value 1-Jan-2006, abc1, 20.00 1-Jan-2006, xyz3, 35.00 2-Jan-2006, abc1, 30.00 2-Jan-2006, xyz5, 6.30 I'm quite new to SSIS, so, I would appreciate detailed steps if possible. Thanks.
View Replies !
View Related
Data Flow Task Error To Extract Data From Sql Server To Excel
Hi All, I want to export data from SQL Server2005 to an Excel spreadsheet thru "Data Flow Task". I am using OLE DB for SQL Server for the source connection and a Connection To Excel as my destination source. The Excel spreadsheet (2003) exists and has the first row with column names. I don't have any warnings before trying to execute. The SQL datable fileds are i) ID - Int ii) RefID iii) txtRemarks - nvarchar(MAX) iv) ddlWaterLevel - nvarchar(50) While executing the tasks, I got the error Error: 0xC0202025 at Data Flow Task, Excel Destination [427]: Cannot create an OLE DB accessor. Verify that the column metadata is valid. Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "Excel Destination" (427) failed the pre-execute phase and returned error code 0xC0202025. After analysing I found in the DataFlow --> Excel destination --> Advanced Editor for Excel Destination, the default data type for txtRemarks shows as "Unicode string [DT_WSTR]". But this is supposed to be "Unicode text stream [DT_NTEXT]". Even if I change the data type in the design time, It doesn't accept. Please do help me out. thanks Sanra
View Replies !
View Related
Processing The Resultset Of Another Proc From A Proc
Is it possible to retrieve the resultset of a stored procedure from another procedure in sql server 2000. Basically I am calling proc2 from the inside of proc1. proc2 returns 2 resultsets. I want to process these two resultsets from within proc1. If its possible , please provide sample code. thanks in advance, Alok.
View Replies !
View Related
Which Data Flow Task To Use?
I have a table which has been loaded from various source feeds. The SourceId relates to the source name and the SourceCompanyId is the sources primary key for the company. I am basically trying to create one row with all the SourceCompanyIds in my column headers. What data flow tasks would be necessary in SSIS? The structure of the final table is: CREATE TABLE [dbo].[Company]( [CompanyId] [int] IDENTITY(1,1) NOT NULL, [CompanyName] [varchar](75), [CIK] [varchar](10), [Ticker] [varchar](10), [Source1CompanyId] [int] NULL, [Source2CompanyId] [int] NULL, [Source3CompanyId] [int] NULL, [Source4CompanyId] [int] NULL, [Source5CompanyId] [int] NULL, [Source6CompanyId] [int] NULL, CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED ( [CompanyId] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] ================================= The table in which contains all the company data CREATE TABLE [dbo].[SourceCompany]( [SourceId] [int] NOT NULL, [SourceCompanyId] [varchar](10) , [SourceCompanyName] [varchar](75), [CIK] [varchar](10), [Ticker] [varchar](10), CONSTRAINT [PK_SourceCompany] PRIMARY KEY CLUSTERED ( [SourceId] ASC, [SourceCompanyId] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
View Replies !
View Related
Data Flow Task
Hi I have a data flow task. If it completes I should update a flag in the database. So How I can I know if the data flow task has completed or not. Thanks Sai
View Replies !
View Related
Some Columns Not Populated In Data Flow Destination
I am populating a table using a SQL command. very simple. SELECT RISKID ,RISKIDREN ,RISKIDEND ,PREMIUMSUBTOTAL ,PREMIUMTOTAL ,SURCHARGE1 ,SURCHARGE2 ,SURCHARGE3 ,SURCHARGE4 ,SURCHARGE5 ,COMMPREMIUM1 FROM PREMIUM However, the first three columns are not being populated in the destination table. The other columns come over fine. The SQL stmt. returns data as expected when run against the source database. I deleted the source and destination and recreated the flow to prevent metadata mapping issues. In the source editor preview I see all of the columns and data. In the destination editor preview, the first three columns of data are null ???. It appears that the columns are not mapping properly even though they are in the source and destination of the mapping editor. I have made sure that the destination mapping contains all the columns in the UI. The source and destination have the columns represented in the advanced editor metedata. I also checked the XML to verify that the columns are in the destination. There is a row count between the source and destination. which should have no effect. This is a part of a larger DW load where I have 10 other tables populated within the dataflow. I also do not get any validation, or error messages. So, I have eliminated truncation errors or the like. I am really puzzled. Has anyone run accross anything like this?
View Replies !
View Related
Ordering Columns In Data Flow (simple?)
Hello, I am new to SSIS. I am trying to write a simple package to export data from some SQL 2005 tables and into a flat file. In my data flow, I am using the OLE-DB data source and then the flat file destination. This all works fine except that I cant get the package to write the columns out in the order I want. Even when I drive the OLE-DB source by a query, they columns are getting written to the flat file in a different order than I want. How is SSIS determining what order to write the columns in and, more importantly, how can I change it to do it in the order I want? Please help if you can. As mentioned I am new to SSIS so please give clear+simple answers. Thanks Mgale1
View Replies !
View Related
Paramater Data FLow In Multiple Columns
Hello all you guys! This is a question that, by all rights, should be easy...But again, I can not figure out how to do it...so I ask: I have the following expression: =IIF(Parameters!single.Value="True",nothing,Join(Parameters!groupid.Label,", "+vbCrLf+"")) which lists all the parameters choosen within a Textbox. Problem: I need it to not waste space by only flowing data verticaly in one column. I want it to use up the rest of the landscape page of the report so that the text flows like this: ----------------------------- -------------------------------- ------------------------------ ------------------------------- ----------------------------------- ----------------------------- ---------------------------------- ----------------------------- -------------------------------- ------------------------------ ------------------------------- ----------------------------------- ----------------------------- ---------------------------------- ----------------------------- -------------------------------- ------------------------------ ------------------------------- ----------------------------------- ----------------------------- ---------------------------------- ----------------------------- -------------------------------- ------------------------------ ------------------------------- ----------------------------------- ----------------------------- ---------------------------------- instead of just strait down...only one column... How do I do one of the following options or a better one you can come up with: 1. Some addition code to state "flow data into another table after a certain limit" 2. Or limit data to top 25 then use a table to list the next 25 ...so on and so on 3. or some better idea!
View Replies !
View Related
Row Count Task, What Is &&"Input Columns&&" Tab For?
I'm using a Row Count Task to count the number of records passing thru a particular path in my data flow. I created a package variable and referenced this variable in the Components Properties tab in the Row Count Task. I believe this is the minimum I need to do to get the row count. However, as I explore the other tabs in the editor, I see there is something called Input Columns tab. What is this for? I didn't select anything in there and things are working fine. At first I thought that I had to choose the columns that I want to have available to me for further processing after the Row Count Task, but this isn't the case. I am able to see all my columns coming out of the Row Count Task even tho I didn't do anything in the Input Columns tab. So, what is this setting for?
View Replies !
View Related
Executing Stored Procedure With Parameters Using Execute Sql Task
Hi , 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 Please do help as soon as possible Thanks Manish
View Replies !
View Related
Data Flow Task Question
Hi there. I'm trying to learn SSIS, please, help me. I have 2 questions: 1) There are 2 databases on 2 different servers. I need to get data from Table1(database1) and put it to Table2(database2). But I have to insert rows, which ID is not exists in Table2. How Can I do necessary filter? 2) In the OLE DB DataSource Component I have used SQL Command(it's simplified): declare @TmpTable TABLE (WorkCode int not null); INSERT INTO @TmpTable (WorkCode) select WorkCode from Table1 SELECT WorkCode FROM @TmpTable SSIS Package works without any exception. But there is no any inserted record in destination table. If I try similar query without temporary table - it works good. Why?
View Replies !
View Related
Data Flow Task SQL Strings
Hi, I just wanna ask: I'm creating an SSIS package, a Data Flow Task. I have used OLEDB Source connected to a SQL Server Destination. Now in my OLEDB Source, I have this SQL statement SELECT FirstName, LastName, Age FROM Employees WHERE (Age > 10) AND (Age < 95) But what I want is to have the last name and first name concatenated and in proper case(capitalize first letter of the firstname and surname). I also want to TRIM or remove the blank spaces of the field in my SQL statement. How I be able to do this? I tried using proper(), trim() and ucase() like in MSAccess but no success. Please help. Thanks in advance.
View Replies !
View Related
Data Flow Task Error
Hi, I have SQL Server 2005 Express edition on my machine. On an SSIS project in BIDS, when i drag a "Data Flow Task" to the package it returns the following error: The designer could not be initialized. (Microsoft.DataTransformationServices.Design) Does this has anything to do with the fact that i don't have SSIS installed on my machine? I thought that SSIS was only needed (on my machine) for the runtime, just to run the packages. To create and edit the pachages i need to install SSIS on my machine too? this doesn't makes sense, maybe it's another problem. Can anyone help me on this? Thank you, Rafael Augusto
View Replies !
View Related
Need Help For Design Data Flow Task
Hi frns, I am new to SSIS. I need some help in designing the below dataflow task. -- Teacher creates several tasks and each task is assigned to multiple students -- The teacher table contains contains all the tasks created a every teacher use ods go create table teacher ( yr int, tid int, tname varchar(20), taskid int ) insert into teacher values(2007,101,'suraj','task1') insert into teacher values(2007,101,'suraj','task2') insert into teacher values(2007,102,'bharat','task3') insert into teacher values(2007,103,'paul','task4') insert into teacher values(2007,103,'paul','task5') insert into teacher values(2007,103,'paul','task6') -- Teacher "suraj" has created 2 tasks -- Teacher "bharat" has created 1 task select * from ods..teacher yr tid tname taskid ============================ 2007 101 suraj 1111 2007 101 suraj 1122 2007 102 bharat 2222 -- Students table contains studentid(sid),teacherid(i,e tid ) & taskid drop table students create table students ( yr int, sid varchar(10), tid int, taskid varchar(10) ) truncate table students insert into students values(2007,'stud1',101,'task1') insert into students values(2007,'stud1',101,'task2') insert into students values(2007,'stud2',101,'task1') insert into students values(2007,'stud2',101,'task2') --Note : stud1,stud2 comes under teacher with tid "101" insert into students values(2007,'stud3',102,'task3') -- Note : stud3 and stud4 comes under teacher with tid "102" insert into students values(2007,'stud4',103,'task4') insert into students values(2007,'stud4',103,'task5') insert into students values(2007,'stud4',103,'task6') insert into students values(2007,'stud5',103,'task4') select * from students yr sid tid taskid ---------------------------- 2007 stud1 101 task1 2007 stud1 101 task2 2007 stud2 101 task1 2007 stud2 101 task2 2007 stud3 102 task3 2007 stud4 103 task4 2007 stud4 103 task5 2007 stud4 103 task6 2007 stud5 103 task4 Now in my target table i need to load the data in a such a way that use targetdb go drop table trg go create table trg ( yr int, -- data should load from teacher.yr tid int, taskid int(20), cnt int ) Mapping in target column and value to be loaded ================================================== yr -- teacher.yr tid -- teacher.id taskid -- this need to start a new sequence of numbers starting from 1 for each teacher and dont want the task id to be copied as it is. cntofstudents -- need to count no of students from "students" table for a given teacher and for his assignment For example for teacherid "101" and taskid "task1" there are 2 students again for the same teacher "101" and taskid "task2" there are 2 students For teacher "102" and taskid "task3" there is only 1 student Similary for teacher "103" Relation ======== Teacher table | Students Table yr | yr tid | tid After i run the ETL the data should look as follows : insert into trg values(2007,101,1,2) insert into trg values(2007,101,2,2) insert into trg values(2007,102,1,1) insert into trg values(2007,103,1,2) -- task4 is created by teacher "103" and assigned to 2 students stud4 and stud5 insert into trg values(2007,103,2,1) -- task5 is created by teacher "103" and assigned to 1 student i.e stud4 insert into trg values(2007,103,3,1) -- task6 is created by teacher "103" and assigned to 1 student i.e stud5 Note : If u observer the values in 3rd column of the trg table, instead of directly mapping the taskid we need to generate a separate sequence for every teacher. BottomLine : for each and every task created by each teacher there should be a unique record along with the count of students in "STUDENTS" table Can anyone help me out in designing the Data Flow task for this Functionality. Thanks, Manu
View Replies !
View Related
How To Debug Data Flow Task?
How can I debug data flow task. From this article (http://www.databasejournal.com/features/mssql/article.php/3567961) and this video (http://www.jumpstarttv.com/Media.aspx?vid=3) I found that when we are executing the task, the data count will be shown along with color coded task but when I debug the package, no count is shown. I have SQL and source and flat file as destination. There is nothing stored in the destination file but the sql script returns the data. Thanks.
View Replies !
View Related
SSIS Ate My Data Flow Task!
I was working all day making changes to my 3MB package. I was adding a large number of transforms that were copied-and-pasted from elsewhere in the same data flow task. All was going well. I even took the time to have SSIS lay out the task again (1/2 hour). Suddenly I started receiving some strange errors: After the layout, I noticed two stray components 'way off in the upper right corner. I found that one of them had a duplicate name to a component which had been added hours ago. Even after deleting it, I got "duplicate name" errors. I copied three components in one selection, and when I tried to paste them, got the error "can't initialize component on paste". I tried them one at a time, but got the same error. I got errors about COM failures due to marshalling to another thread I then exited Visual Studio and started it again. To my great surprise, the data flow task I was working on was still there, but was completely empty. Comparing what I'm left with to my last version in source control, I find that the entire pipeline element is missing from the DTS: ObjectData element! I'm developing a real love/hate relationship with SSIS. It varies from one day to the next. Guess what kind of day this is!
View Replies !
View Related
Data Flow Task Stuck
I have a simple data flow task setup... 2 ADO.NET connection managers, each referencing a DSN pointed to a Unidata database. 2 DataReader sources, each using a single ADO.NET connection managers, running a simple SELECT statement from a table. I have a Union All transform setup to merge the data and write to a OLE DB Destination (SQL05 database) When I run the package, each source will validate, but only one will execute. The other source will do nothing. The data source will be colored yellow, and will just sit there. The package will just sit, almost like it is waiting for input. This behavior is not consistent, however. It varies which data source will hang, pretty much 50-50. About 25% of the time, both sources will execute, and all rows will be written to the destination. Any help is appreciated. thanks
View Replies !
View Related
Transaction In A Data Flow Task
Hi All, I used a Transaction required in Dataflow task.where i have a flat file source and oledb destination .when I created a small prototype, it was working. But when I implemented ina program it gave me error like Event Type: Error Event Source: Shell SSIS Exception Event Category: None Event ID: 0 Date: 10/12/2007 Time: 5:11:13 PM User: N/A Computer: AMSDC2-S-6212 Description: Package Name: Shell SSIS DS1 OTC GSAP CustomerDataToMPCT Task Name: Insert Rows in MPCT Execution Instance GUID: {B7110F6A-CAF7-4D5D-82FC-46279DC51854} Error Code: -1071636471 Error Description: An OLE DB error has occurred. Error code: 0x80040E37. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E37 Description: "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.". Start Time: 10/12/2007 5:11:13 PM For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp. Kindly Suggest, Shagun Khare
View Replies !
View Related
Data Flow Task Problem
I have a Windows XP X64 machine with SQL 2005 Developer and VS 2005 Team Edition for Architects on it. For the most part it appears that all normal VS and SQL functions are working properly with the exception of SSIS. If I open the developer studio and drag a Data Flow Task onto the design surface I get the message below. I have tried doing an unistall and reinstall of Integration Services as well as a repair on VS 2005 with no success. I've searched the web and newsgroups and can't find any mention of the problem I'm having. Any help greatly appreciated.
View Replies !
View Related
ERROR AT DATA FLOW TASK
HI, I HAVE A NEW PROBLEM...HOPE SOMEONE KNOWS WHAT THE $#%#$ IS HAPPENING. HERE IT´S THE THING: I´M USING A DATA FLOW TASK TO READ DATA FROM AN ORACLE SERVER AND TRANSFER THE INFO TO MY SQL 2005 SERVER, THE SOURCE IS AVAILABLE AND THE CONNECTION IS WORKING, I'M USING A DATA READER SOURCE TO CONNECT AND EXTRACT. I´VE PUT THIS DTS IN A JOB AND IT WAS OK, IT HAD BEEN RUNNING OK FOR ALMOST A MONTH BUT SUDDENLY HIS MORNING IT FAILED WITH THE FOLLOWING ERROR: SSIS package "SAZSIE_CargaVentasSeguros (1).dtsx" starting. Information: 0x4004300A at Extrae SAZ_GranoO_New, DTS.Pipeline: Validation phase is beginning. Information: 0x4004300A at Extrae SAZ_GranoO_New, DTS.Pipeline: Validation phase is beginning. Information: 0x40043006 at Extrae SAZ_GranoO_New, DTS.Pipeline: Prepare for Execute phase is beginning. Information: 0x40043007 at Extrae SAZ_GranoO_New, DTS.Pipeline: Pre-Execute phase is beginning. Information: 0x4004300C at Extrae SAZ_GranoO_New, DTS.Pipeline: Execute phase is beginning. Error: 0xC0047062 at Extrae SAZ_GranoO_New, SAZ_GranoONew [421]: System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutput(IDTSManagedComponentWrapper90 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer90[] buffers, IntPtr ppBufferWirePacket) Error: 0xC0047038 at Extrae SAZ_GranoO_New, DTS.Pipeline: The PrimeOutput method on component "SAZ_GranoONew" (421) returned error code 0x80004003. 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. Error: 0xC0047021 at Extrae SAZ_GranoO_New, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038. Information: 0x402090DF at Extrae SAZ_GranoO_New, OLE DB Destination [1022]: The final commit for the data insertion has started. Information: 0x402090E0 at Extrae SAZ_GranoO_New, OLE DB Destination [1022]: The final commit for the data insertion has ended. Information: 0x40043008 at Extrae SAZ_GranoO_New, DTS.Pipeline: Post Execute phase is beginning. Information: 0x40043009 at Extrae SAZ_GranoO_New, DTS.Pipeline: Cleanup phase is beginning. Information: 0x4004300B at Extrae SAZ_GranoO_New, DTS.Pipeline: "component "OLE DB Destination" (1022)" wrote 19522 rows. Task failed: Extrae SAZ_GranoO_New Warning: 0x80019002 at SAZSIE_CargaVentasSeguros: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "SAZSIE_CargaVentasSeguros (1).dtsx" finished: Failure. THE DATA HAS BEEN COPIED INTO MY DESTINATION TABLE, BUT THE PACKAGE ENDS IN AN ERROR, WHAT IS THE PROBLEM WITH THIS??? PLASE SOMEONE !!!! THANKS!!!!!!!
View Replies !
View Related
Two Destinations In A Data Flow Task
Hi All, I wanna know if we can have more than one "OLEDB Destination" within a Data Flow Task, I want to use the same data flow and write to two different tables in a database with some changes. If we cannot do this within the same data flow what is the best way to do this. Thanks
View Replies !
View Related
Error With Data Flow Task
I am having problems with the Data Flow task. It does not even show up in the list of items to drop into the SSIS project. If I go to the Data Flow tab and hit create, I get the follow error. I have tried repairing and reinstalling, but nothing seems to clear up the error. Without rebuilding my machine, is there anyone who knows how to get the Data Flow Task reinstalled properly? Thanks Wayne TITLE: Microsoft Visual Studio------------------------------Registration information about the Data Flow task could not be retrieved. Confirm that this task is installed properly on the computer. ------------------------------ADDITIONAL INFORMATION:TaskHost "{C3BF9DC1-4715-4694-936F-D3CFDA9E42C5}"' is not installed correctly on this computer. (Microsoft.DataTransformationServices.Design)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.762&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=TaskHostNotInstalled&LinkId=20476------------------------------BUTTONS:OK------------------------------
View Replies !
View Related
Error At Data Flow Task
Hi all, I am getting the following when trying to import text coloums from execl to SQL server 2005. Any ideas? Error at Data Flow Task [Destination] Coloums "blar" and "Blar_name" cannot convert between unicode and non-unicode sting types. Any help would be great. Thanks Dave Dave Dunckley says there is a law for the rich and a law for the poor and a law for Dirty Davey.
View Replies !
View Related
|
TRACKER
