Using A Sproc As A Source Within Dataflow
Dec 8, 2005
I was wondering what has worked for all of you in regards to using a sproc as a source within a dataflow. I have had limited success doing this, but maybe there is a workaround I'm unaware of. Basically, using a SQL command in an OLE DB Source, I run an EXEC statement that returns a resultset from a stored procedure. I've noticed that depending on how the sproc is structured, I will either get metadata info in the columns tab of the OLE DB Source or not. Without this metadata of course I can not link it with a destination, since the destination believes that no data is being returned, even if this is not the case. This all seems to depend on the "final" select statement in the sproc being at the very top of the sproc. If it is not at the top, the columns tab will not be populated. Has anyone else had similar issues? Is there a workaround other than populating a temp table outside of the dataflow?
View 14 Replies
ADVERTISEMENT
Feb 27, 2008
Has anyone in here had luck using AD as a datasource? Big picture, we have an input file with unique identifiers (employee clock numbers) and I need to associate a clock number with an email address. The only place we have those two together is in Active Directory.
Currently, we have a lookup task with an OLE DB connection to our AD and a query that looks like
Code Snippet
SELECT mail, clockNumber
FROM 'LDAP://DC=company,DC=com'
I've mapped my input to the clockNumber in AD and all is well and good. I then go to thinking, perhaps it'd be better if I used that query as a source in an OLE DB component and used a Merge Join to make the link between the two. I paste that same query into a source component and boom
Code Snippet
TITLE: Microsoft Visual Studio
------------------------------
Error at Create extracts [OLE DB Source [1226]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x00000000.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)
Is this just not something that a person can do or is there something I've set up incorrectly? I'd be lucky to be considered a novice with AD so I'm fine if someone has a better suggestion for retrieving this data, this was just something I'd cobbled together out of some forum postings.
View 4 Replies
View Related
Oct 14, 2007
Hi,
I want to create pckg with two data source,
the pckg will know to take data from the right DS after IF will check if getdate() returned endweekday it will take from DS1 else DS2.
any suggestion how to do it??
10X.
View 1 Replies
View Related
Aug 25, 2007
Dear Friends,
I have to import dBASE files to SQL Server. For this I have created one ODBC connection manager for those dBASE files. This I have to set for DataReader Source. But I am unable to configure the DataReader Source.
So, Please tell me how to configure DataReader Source for ODBC connection Manager.
Eagerly waiting for your valuable reply............
Santosh
INDIA
View 2 Replies
View Related
Oct 10, 2006
I am trying to use an XML Source on xml data from an XML webservice, I am putting the document into a variable the trying to import the data from there with the XML Source, but I am getting an error telling me that truncation occured
The Error is "[XML Source [1]] Error: The "component "XML Source" (1)" failed because truncation occurred, and the truncation row disposition on "output column "linking" (1579)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component."
The linking column mensioned in the error is sometime quite a long string but there is nowhere in the XML Source editor to change the size.
HELP!
View 3 Replies
View Related
Feb 27, 2007
I need to extract data from tables in a database that I can only access via ODBC.
I have successfully created a connection in Connection Manager (ConnectionManagerType = ODBC) for this database.
However I€™m unable to add this connection as a Data Flow Source. There is no ODBC Source option in the Toolbox.
This is a major because we have been using the system dsn Microsoft Visual Foxpro Driver to access free table directory .dbf files under ODBC with DTS for years. To install a new Microsoft OLEDB driver for foxpro is out of the question on a production system as it would cost many thousands of dollars to go through our BAT testing process
How do I extract data from tables in a database via ODBC?
Thanks in advance
Dave
View 5 Replies
View Related
Mar 29, 2007
Hello All,
I am trying to convert an application created using DTS classes to SSIS object model. I have found following code in the application.
For x As Integer = 1 To mTask.Properties.Count
If mTask.Properties.Item(x).Name = "SourceConnectionID" Then
CnID = mTask.Properties.Item(x).Value
Exit For
End If
Next
For x As Integer = 1 To mPkg.DTSPackage.Connections.Count
If mPkg.DTSPackage.Connections.Item(x).ID = CnID Then
Return mPkg.DTSPackage.Connections.Item(x).Name
End If
Next
Return mTask.Properties.Item("SourceObjectName").Value
How can I retrieve or Set "SourceConnectinId", "SourceObjectName" of a data flow task in SSIS
Please help me to solve this problem.
Thanks in advance
Subin
View 5 Replies
View Related
Jun 23, 2006
Hi,
I have problem to extract data from DB2 table using SSIS dataflow task with OLEDB source selecting the data access mode with command. Here is the scenario.(Note:If I select the data access mode as a table then it works fine.)
If I use the data access mode as SQL Command then it is not working. i.e. the flow stays yellow in controlflow nothing happens in dataflow and I noticed the SQL dumper and proc completes. I couldn't find any log to verify the issue including the package execution results.Any of your thoughts will help me to resolve this issue.
Thanks..Mako
View 1 Replies
View Related
Apr 4, 2008
Hi,
I am using SQL Server2005 for SSIS. I want to change the source connection dynamicaly evertime.
Let me clear, I have to extract some column from excel to MS-Access. I am using Data Flow Task and able to successfully complete the job. But problem is that, whenever a new file comes , i must have to reconfigure my Excel Source.
All the time column in file are same, so no need to worry about mapping but how can my package select a file automatically.
I have a directory, suppose "C:dpak". I should able to pick the filename and sheet name from this directory every time when my package will execute.
View 10 Replies
View Related
Mar 13, 2007
Hi,
I have implemented a custom source component that can be used as the data source in the Data Flow task.
I have also created a custom UI for this component by using the IDtsComponentUI .
But my component does not have the capability of setting the custom properties via the DTS Variables using the Expression Builder.
I have looked around for samples on how to do this, but I can only find samples of how to do this for custom Control Tasks, i.e. IDtsTaskUI.
My question is, How can implement the Expression Builder in my custom Source component + custom Source UI. Or do you know of any samples which I can look at.
Thank you,
Jameel.
View 1 Replies
View Related
May 6, 2015
I have implemented a package to load multiple files to a destination. Since the source was a txt file, i have created as flat file source. However now we are getting files in excel format as well.
Is there anyway the source gets changed dynamically based on the file extension, output of the foreach file enumerator? I can think one solution to have 2 dataflow tasks based on precedence constraining and expression one is for .txt and other one is for .xls.
View 6 Replies
View Related
Nov 12, 2007
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 9 Replies
View Related
Apr 17, 2007
Dear Friends,
I need to execute a SQL query, inside a dataflow (not in controlFlow) and need the records returned to continue the dataflow... In my case I cant use lookup and OLE DB COmmand and nothing else...
I need to execute a query and need the records for dataflow... with OLE DB command I cant see the fields returned... :-(
How can I do it? Using a script? Can I use a Script Component? That receive 2 parameters for input and give me the fields returned from query as output?
Thanks!!
View 38 Replies
View Related
Apr 18, 2008
Hi,
I am having a Data flow task in For each loop which will gets 100 sourcetable names and 100 target table names...
am having a simpleData flow task which trasferes from OLEDBSource to OLEDBDestination.
I am repeating the Dataflow task which transfers from sourcetablename extracted from for loop to a destination table var.
The problem am gettting is for the first table it is able to transfer correcly because I did mapping for those tables at design time...but for the next coming sourcetable-desttable (which r having different no of cols,datatypes) its giving Validation failed...and...needs to refresh metadata....
is there any way to refresh the metadata of Data flow task (I set the property of OLEDBSource validate external meta to false then also same error is coming)
Thanks
Radhika
View 4 Replies
View Related
Feb 13, 2007
I have attached the results of checking an Update sproc in the Sql database, within VSS, for a misbehaving SqlDataSource control in an asp.net web application, that keeps telling me that I have too many aurguments in my sproc compared to what's defined for parameters in my SQLdatasource control.....
No rows affected.
(0 row(s) returned)
No rows affected.
(0 row(s) returned)
Running [dbo].[sp_UPD_MESample_ACT_Formdata]
( @ME_Rev_Nbr = 570858
, @A1 = No
, @A2 = No
, @A5 = NA
, @A6 = NA
, @A7 = NA
, @SectionA_Comments = none
, @B1 = No
, @B2 = Yes
, @B3 = NA
, @B4 = NA
, @B5 = Yes
, @B6 = No
, @B7 = Yes
, @SectionB_Comments = none
, @EI_1 = N/A
, @EI_2 = N/A
, @UI_1 = N/A
, @UI_2 = N/A
, @HH_1 = N/A
, @HH_2 = N/A
, @SHEL_1 = 363-030
, @SHEL_2 = N/A
, @SUA_1 = N/A, @SUA_2 = N/A
, @Cert_Period = 10/1/06 - 12/31/06
, @CR_Rev_Completed = Y ).
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[sp_UPD_MESample_ACT_Formdata].
The program 'SQL Debugger: T-SQL' has exited with code 0 (0x0).
And yet every time I try to update the record in the formview online... I get
Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
I have gone through the page code with a fine tooth comb as well as the sproc itself. I have tried everything I can think of, including creating a new page and resetting the fields, in case something got broken that I can't see.
Does anyone have any tips or tricks or info that might help me?
Thanks,
SMA49
View 3 Replies
View Related
Apr 23, 2004
I'm sorta new with using stored procedures and I'm at a loss of how to achieve my desired result.
What I am trying to do is retrieve a value from a table before it is updated and then use this original value to update another table. If I execute the first called sproc in query analyzer it does return the value I'm looking for, but I'm not really sure how to capture the returned value. Also, is there a more direct way to do this?
Thanks,
Peggy
Sproc that is called from ASP.NET:
ALTER PROCEDURE BP_UpdateLedgerEntry
(
@EntryLogID int,
@ProjectID int,
@NewCategoryID int,
@Expended decimal(10,2)
)
AS
DECLARE@OldCategoryID int
EXEC @OldCategoryID = BP_GetLedgerCategory @EntryLogID
UPDATE
BP_EntryLog
SET
ProjectID = @ProjectID,
CategoryID = @NewCategoryID,
Expended = @Expended
WHERE
EntryLogID = @EntryLogID
EXEC BP_UpdateCategories @ProjectID, @NewCategoryID, @Expended, @OldCategoryID
Called Sprocs:
*********************************************
BP_GetLedgerCategory
*********************************************
ALTER PROCEDURE BP_GetLedgerCategory
(
@EntryLogID int
)
AS
SELECT CategoryID
FROM BP_EntryLog
WHERE EntryLogID = @EntryLogID
RETURN
*********************************************
BP_UpdateCategories
*********************************************
ALTER PROCEDURE BP_UpdateCategories
(
@ProjectID int,
@NewCategoryID int,
@Expended decimal(10,2),
@OldCategoryID int
)
AS
UPDATE
BP_Categories
SET CatExpended = CatExpended + @Expended
WHERE
ProjectID = @ProjectID
AND
CategoryID = @NewCategoryID
UPDATE
BP_Categories
SET CatExpended = CatExpended - @Expended
WHERE
ProjectID = @ProjectID
AND
CategoryID = @OldCategoryID
View 2 Replies
View Related
Jan 20, 2004
create procedure dbo.GetZipID( @City varchar(30), @State char(2), @Zip5 char(6))
as
DECLARE @CityID integer
declare @StateID integer
declare @ZipID integer
set @ZipID=2
set @Zip5=lTrim(@Zip5)
if @Zip5<>''
SET @ZIPID = (select Min(lngZipCodeID) AS ZipID from ZipCodes where strZipCode=@Zip5)
if @ZipID is null
set @CityID= EXEC GetCityID(@City);
set @StateID= EXEC GetStateID(@State);
insert into ZipCodes(strZipCode,lngStateID,lngCityID) values(@Zip5,@StateID,@CityID)
if @@ERROR = 0
SET @ZIPID = @@Identity
select @ZIPID
GetCityID and GetStateID are two stored procs, how do I execute those two stored procs
in the above stored proc? I mean what is the syntax??
Tks
View 2 Replies
View Related
Jun 18, 2015
I have a got a package with source as sql table which has got 50 columns. We are using only 10 columns out of this. Recently one column name has changed and thus throws error invalid mapping. When I open the source to do the changes noticed that all the colums are prselected now and also the datatypes got changed to default ( I had changed the datatypes as per my requirement while i developed). So now I had to select required columns from source and redo the datatype changes in advanced editor.Is there any option which doesnt disturb this settings and we just need to correct the mapping alone.Â
View 4 Replies
View Related
Dec 20, 2005
Hi,
I want to transform an xml flow to an html flow. For this, I create an XmlDataDocument, I add on it, all that I want, and I store it in a file (in a dataflow task).
After that, in an xml task, I set the input properties like that:
Operation type: xslt
SourceType: File connection
Source: ras.xml
All works fine. But now, I want to use a variable to store my xml data. So, instead of storing my data in ras.xml, I store it in a variable like that:
Dim v As Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariables90
Me.VariableDispenser.LockOneForWrite("RAS", v)
v("RAS").Value = doc.OuterXml
v.Unlock()
and in the xml task, I set configuration like that:
Operation type: xslt
SourceType: Variable
Source: user::RAS
But when I execute, I got this following exception:
Error: 0xC002F304 at Format HTML Mail, XML Task: An error occurred with the following error message: "Root element is missing.".
Error: 0xC002928F at Format HTML Mail, XML Task: Property "New Source" has no source Xml text; Xml Text is either invalid, null or empty string.
Furthermore, when I want to debug it, in replacing the xml task, by a script task, I can see that in my RAS variable, I get the xml data which is well formed.
<?xml version="1.0" encoding="UTF-8"?>
<root> €¦
</root>
(When I store it in a file, I can see it in IE)
Have you got an idea ?
View 3 Replies
View Related
Dec 13, 2007
I was trying to load data using SSIS, Data Flow Task, OLE DB Source, source was a view to a OLE DB Destination (SQL Server). This view returns 420,591 rows from Query Analyzer in 21 seconds. Row length is 925. When I try to executed the Data Flow Task from SSIS, I had to stop the process after 30 minutes, because only 2,000 rows had been retrieved. I modified the view to retun top 440, 000 and reran. This time all 420, 591 rows were retrieved and written in 22 seconds. Next, I tried to use a TOP 100 Percent. Again, only 2,000 rows were return after 30 minutes. TempDB is on a separate SAN Raid group with 200 gig free, Databases on a separate drive with 200 gig free. Server has 13 gig of memory and no other processes were executing.
The only way I could populate the table was by using an Execute SQL Task and hard code an Insert into table selecting data from the view (35 seconds) from SSIS.
Have anyone else experience this or a similar issue? Anyone have a solutionexplanation?
View 13 Replies
View Related
Sep 14, 2007
Hi
I am having a huge xml file with nested section.
i also have a xsd file for that xml.
i have a destination table where the data from the xml should be loaded into.
i am using the xml source transformation. But o get all the data i need to use multiple merje joins to get the data in a single row which i can insert into the destination.i was not quiet convinced with using so many joins.
so i tried using the script source transformation where i am using xml objects to get the node and dynamically construction the data row. and the output is then inserted into the destination.
on comparing the two approach the one using the script source is working much faster than the xml source transformation.
i wanted to know is there any limitaion using the script source to parse through xml files.
also i would like to know any other better way of getting the data from xml source without using the joins.
Hari
View 7 Replies
View Related
Nov 7, 2007
Hi,
I'm wondering if it is possible to make an update of a specifics rows in a database table using dataflow tasks
thanks
View 9 Replies
View Related
Dec 11, 2007
When i use tablename or viewname variable in datasource component and data determination component ,
how can i manage the output columns and the input columns?
Yes,i can use default value init the columns ,but when variable value changed,error occurs.
Thanks a lot!
View 3 Replies
View Related
Dec 24, 2007
Hi Pals,
Here is my scenario in my ETL process, I have one DataFlow task.
Assuming that i have 10 clean records in my source database and i need to load all the 10 recs into my target table.
IS there any means of cross checking the no of rows from source table and number of rows loaded into my target table.
Any suggestions are greatly appreciated.
Thanks & Regards.
View 6 Replies
View Related
May 23, 2007
Hi all,
In my DataFlow i set the "OLEDB Source" which is a table in my Extract Server and need to do some transformations and stage the table which will be a Dimension in the staging DB,
Q1-Now i need only 3 columns from the Source table, which transformation do i need to use to just extract the the 3 columns?
Q2- Two Columns of 3,which i will need to transform as it is-no changes at all and One of the column which has values like "BOSTON...."
(I have a vague idea of what i need to do,need something solid suggestions/advices to kickoff,plan is to use this city column with a Replace function (as one of the forum member's Spirit1 adviced..thanks..!!))to take out the dots and then need to write a condition if BOSTON then Assign Code "BOS" which will be City_Code and this "City_Code" will have to be looked in City_Dimension to get the "City_Key_Number" for "Boston" and lastly the City_Code and City Key Number both have to be transformed to the destination Dimension.
Any ideas /suggestions will be appreciated.
Thanks in advance...!!
ravi
View 5 Replies
View Related
May 23, 2006
Greetings!
I am attempting to implement the following case statement BEFORE getting the data in to my destination table but I don't know how to create an expression for it.
In the mapping section of my OLE DB destination component I can only do mapping but I can't actually manipulate the data before it gets to the destination table.
What do I have to do to implement :
case
when SOPD.PRICE_TOP_NUMBER is NULL then -8
else SOPD.PRICE_TOP_NUMBER
end AS price_top_number,
before it goes to the destination column?!
Thanks for your help in advance.
View 11 Replies
View Related
Nov 7, 2007
Hello every one,
I have a simple dataflow from source to target.
My source is raw file and target is oracle table.
The problem I€™m facing in SSIS is its talking more than 10 minutes to load almost 30-40k records, I do not have any transformation activity this is just a simple dump from source to target.
Please do inform me of any setting to be talk care for fasten the process.
Thank you
View 5 Replies
View Related
Nov 13, 2006
I know the idea was to seperate workflow and dataflow, but I have come across a scenario where it would be useful for a branch of a dataflow to wait until another branch has finished.
I have some transactional data which records events for the start and end of a session. I want to build a list of unique sessions with the start and end date. I currently have the list of events sorted by time, followed by a conditional split for the start and end events. I can then insert all of the start events and would like to wait until all of the starts are inserted before updating them with their relevant end times.
Is this achievable?
Does anyone else think it would be a good idea to be able to set precendence across multiple branches of a data flow?
Does anyone have a better solution?
I know this is the wrong forum, but is there a way to model this against the transactional data in SSAS, I will move this question to the SSAS forum if anyone can think this would work!
Philip Coupar
View 7 Replies
View Related
Apr 25, 2007
This error seem to be very silly.did anyone come across this error.
I have been transferring data from textfile to a table using oledb destination.
The number of records in the text file are 2,091,650
Its was running just fine couple of days ago when the incoming data was little small then this...(arround 300,000).Now it seem to have a problem.
Here is the flow
1.File System task ->I copy the file to different location
2.Execute sql task->truncate tables
3.DataFlow task->I check for only the error files in this data flow.and all valid rows i transfer to a different text file.
4.Dataflow->filesource i connect to new text file created earlier.Here i convert fields to repective datatype and i insert if new or update record.
I dont know whats going on...
When i run my package it runs through the first three perfectly fine.When it comes to fourth step it sits there.....it dosent go to the tasks within this dataflow at all...and begining i have flat files source...
What could be the reason...
when i look at progress tab...i was able to look at the progress of other tasks but when it comes to this task it shows start>>>>>time and it sits there...
View 4 Replies
View Related
Dec 12, 2006
I am transfering data from a textfile to sql server.I use a data flow task for trasfering my text files.
Here is what i do.
1.Add text file source
What i want to achieve here is if the text file countains the column name in the first row i should delete them and if it does not contain column name in the first row just transfer it.
how can this be achieved???
2.add one more column to my text file which should contain the status(insert or update).
how can this be done??
3.before transfering data ot destination i want to know if the record exists if exists i just want to update it instead of insert.and if new record i want to insert it .and the status in the above new column need to change.
please help...
View 5 Replies
View Related
Jul 16, 2007
Hi all,
I have encountered a SQL 2005 deadlock issue while executing dataflow in a SSIS package. The deadlock happens when I have indexed two columns. If I don't have index, deadlock does not happen.
Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Transaction (Process ID 67) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".
The above causes the rest of the dataflow execution to be terminated.
What my dataflow does is to extract data from 14 flat files and then insert the records into a single table (no primary key, but with two columns indexed).
Can anyone please advise how I can avoid deadlock with indexes in a table?
Thank you and much appreciated!
View 6 Replies
View Related
May 2, 2008
Greetings!
I am painfully learning SSIS and just when I think I have a better understanding of how things work and go down the path that I should take, I hit a wall. Frankly, discouraging and demoralizing.
I am performing a Select of rows that are then passed on to a ForEachLoop with an ADO Enumerator with a FullResultSet.
Variable mappings for the columns used are:
Variable IndexAccountNumber 0
AccountName 1
AccountAddress 2
CountryCode 3
A ScriptTask in the foreach loop allows me to confirm that I am processing rows.
A DataFlow in the ForEachLoop has in it a DERIVED COLUMN item that allows me to assign the four variable fields above to their respective DERIVED COLUMN.
An attempt to use a Flat File Destination to process the derived columns is being made. As I monitor the execution of the package I notice the following message.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "Flat File Destination" (382)" wrote 0 rows.
Can anyone PLEASE tell me why is it that I am able to see the data being processed through the script task? And yet not see any rows being written to the Flat file. Why?
Any information you may provide would be immensely appreciated.
Thank you and God Bless.
View 6 Replies
View Related
May 23, 2006
HI, I have a lookup that find a specific row. If that row does not exist, I need to create a new one. If it exists, I need to raise an error and trap it with the "on_error" event of the dataflow in order to log it. Is there a way to raise an error and specify the error message from the dataflow? I was thinking using the conditional split and verify if the value returned from the lookup (by set the error config to ignore the error) is not or not. But how can I raise an error when the value is not null?
Thank you,
Ccote
View 1 Replies
View Related
