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 Complete Forum Thread with Replies
Related Forum Messages:
Data Flow Multiple Sources To Populate A Destination
I have 5 or more tables to join to get a particular output which has to be sent to a destination table. In the 5 tables some are inner joins and some are left outer join. I am opting for stored procedure at this point. But I would like to know how can this be done in data flow transformations having multiple souce and merge joins or any other alternates. I tried using merge join, but this does not accept more than two tables. I saw this simple post which kick started me to use ssis transformations to stored procedures. But I encounter issue. http://www.mssqltips.com/tip.asp?tip=1322 error "The destination component does not have any available inputs for use in creating a path". Please advice alternates
View Replies !
Using Multiple Data Sources For Single..
Hi, I am new to Reporting Servies. I need to create a report that is shared by different offices, each office has it own database. So I will need to include multiple data sources. I want users to select their office first (parameter) and then based on their selection they can see the report with the data source from their office. Can I do it in one report. Or do I have to create multiple version of same report for each office. I would appreciate any help. Regards, Hasn
View Replies !
Single Report Multiple Data Sources
I have multiple data sources that I would like to use for a single report. The data sources are separate due to security requirements. I have a table that I can get the Initial Catalog from. How do I get that field from the database into the data source connection string at run time? I saw this link here but the light bulb didn't go off: http://technet.microsoft.com/en-us/library/ms156450.aspx "define a query that retrieves a list of data sources at run time" Can somebody help me out here? TIA, Darren
View Replies !
Using Multiple Data Sources In Single Report.
I am using SQL RS 2005. I have a report that is using multiple data sources from different offices. I can add these data sources in my report but is it possible that I can select a specific data source based on a selected parameter value. In this case user from each office, select their office location and be able to see the data from their office. I would appreciate any suggestions. Sal
View Replies !
Selecting A Single Data Source From Multiple Data Sources
Hi, I am pretty new to SSIS. I am trying to create a package which can accept data in any of several formats. i.e. CSV, Excel, a SQL Server database/table and import the data into my destination database. So far i've managed to get this working OK. However I am now TOTALLY stuck. I'm currently trying to just concentrate on the data sources being a CSV (using a Flat File Data Source) and/or an Excel Spreadsheet. I can get the data in and to my destination using a UNION ALL component and mapping the data sources to it so long as both the CSV file and the Excel spreadsheet exist. My problem is that I need my package to handle the possibility that only the CSV file might exist and there is no Excel spreadsheet. In which case i'd like the package to ignore the Excel datasource completely. Currently either of my data sources do not exist I get errors and the package terminates. Is there any way in SSIS that I can check all my data sources to see which ones exist (i.e. are valid). If they exist I want to use them. If it doesn't exist i'd like to disgard it (without error - as long as there is a single datasource the package should run) I've tried using the AcquireConnection method in a script task on each of my connections, hoping that it would error if the file/datasource did not exist. It doesn't though (in the case of an Excel datasource it just creates a empty excel file for me). The only other option I can come up with are to have seperate packages depending on the type of data we want to import and then run a particular package depending on the format of the source data. This seems a bit long winded. I am pretty sure I must be able to do what I want to achieve but I can't work out how. I'll be grateful to anyone who can send me any tips/hints/links on how I can achieve this. Many thanks Rob Gibson
View Replies !
How To Merge Multiple Rows One Column Data Into A Single Row With Multiple Columns
Please can anyone help me for the following? I want to merge multiple rows (eg. 3rows) into a single row with multip columns. for eg: data Date Shift Reading 01-MAR-08 1 879.880 01-MAR-08 2 854.858 01-MAR-08 3 833.836 02-MAR-08 1 809.810 02-MAR-08 2 785.784 02-MAR-08 3 761.760 i want output for the above as: Date Shift1 Shift2 Shift3 01-MAR-08 879.880 854.858 833.836 02-MAR-08 809.810 785.784 761.760 Please help me.
View Replies !
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 !
Single Report With The Data From &&"Multiple Data Sources&&"
Hi, In my project i want a report. In that report data is getting from more than one data sources(systems). While creating data source view i used named query for both primary and secondary data source. But at the time of crating "Report Model" i am getting below error. An error occurred while executing a command. Message: Invalid object name 'Table2'. Command: SELECT COUNT(*) FROM (SELECT SerialNum, ModelNum AS com_model FROM Table2) t Is there any way to create a report with multiple data sources?
View Replies !
Data Flow Task Multiple Destinations
Hi, The further i get with doing my current SSIS package the more i am starting to wonder about best practices and performance. My current package loops through CSV files in a specified location and extracts events from these files. Each file contains multiple events which are a mixture of different types. Depending on the event there are a different number of comma seperated values. In the package i firstly set each event to one column seperated by a comma delimeter. I then create an array for the event which is split by the delimeter. In a script i weed out all elements of the array that are common to all events and set the remaining events to another array. After some processing i come to my conditional split transformation which splits the processing of each event based on the EventID. This is where i'm having doubts on whether i have approched the package correctly. There are approximately 60 different events so each one of these has a seperate pipeline to process the remaining parameters in the array and output them to the destination table. The destination table is differnet for each ID. Is it viable to have this amount conditions and paths when creating the pacakge and is this likely to have any detrimental effect on performance. Is there possibly another way that i could approach this problem? Many thanks, i hope that made sense. Grant
View Replies !
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 !
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 Replies !
Multiple Sources - Single Destination
There are two EXCEL sources one destination table. Each record in the destination should be populated with 2 columns from one source and 2 from other source. Source 1: ID Name 1 abc Source 2: Address Location 232/2 xyz Destination: ID Name Address Location 1 abc 232/2 xyz I tried using UNION ALL transformation but it fetches 2 seperate records (assuming that one record in each source). How to achieve this without using SCRIPT component? TIA
View Replies !
MULTIPLE DATA SOURCES WITH SAME TABLE SCHEMAS IN SSIS
Hi, I have the following scenario: N identical Databases (corresponding to different Fiscal Years, with names <Company Name>.<YEAR>). We want to consolidate the N DBs to a New Datawarehouse. In SSIS we have designed a Dataflow that reads through a OLE DB Source (Connected to one of the N Databases) and maps to a OLE DB Destination (Connected to the NEW DB). The question is, how we loop in SSIS through the N identical Connections, so to repeatedly execute the designed Dataflow, each time with a different Connection? Thanks in Advance, --Dimitris Doukas
View Replies !
Running Multiple Select Statements With One Data Flow Task
My goal is to run a bunch of select statements from different tables in one database and have them all insert to the same columns/table in the new database. Do I need a new data source for each statement, or is there a way to run all the statements in one set seeing as they all have the same destination. I keep receiving the SQL statement improperly ended error when trying.
View Replies !
One Data Flow Task And Multiple Data Flows
I have a data flow task which has around 5 data flows (like the 2nd diagram shown here). These 5 simple flows with just a row count transformation in between. Now, I want to fail the entire task immediately even if one of the data flows failed. Right now if one flow fails the remaining flows fails after a long time, not immediately. How can I make it fails immediately. The other I would like to do is Can I place these 5 data flows in a transaction, so that if one data flow fails, others data flows also roll backs? ( I assume its not possible) Thanks
View Replies !
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 !
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 !
Use Newly Created Table In Data Flow Task
Hello dear Forum, I guess this should be rather simple to answer but i just don't get how to do this. I just want to import some data from an Access mdb file into a SQL Server 2005 Database. As I dont want to create all tables in Sql Server 2005 beforehand i'd like to create them out of my SSIS Package. Therefore i use the 'Execute SQL Task' in the control flow before stepping into the dataflow where only a oledb-source and an oledb-destination exist... VERY simple... My problem is, that i can't select the table from the selectionlist in the oledb-destination because it does not exist before executing the package... If i create the table by hand just to be able to select it, that does not help. because if everything is set up and then i delete the table (because it will be created by the package anyway) an error occurs before executing the package - in the validation phase: Package Validation Error: "Invalid object name 'myToBeCreatedTable'". Can't i create tables on the fly which i use then in my dataflow tasks? Kind regards, Wolfgang Hello dear Forum, I guess this should be rather simple to answer but i just don't get how to do this. I just want to import some data from an Access mdb file into a SQL Server 2005 Database. As I dont want to create all tables in Sql Server 2005 beforehand i'd like to create them out of my SSIS Package. Therefore i use the 'Execute SQL Task' in the control flow before stepping into the dataflow where only a oledb-source and an oledb-destination exist... VERY simple... My problem is, that i can't select the table from the selectionlist in the oledb-destination because it does not exist before executing the package... If i create the table by hand just to be able to select it, that does not help. because if everything is set up and then i delete the table (because it will be created by the package anyway) an error occurs before executing the package - in the validation phase: Package Validation Error: "Invalid object name 'myToBeCreatedTable'". Can't i create tables on the fly which i use then in my dataflow tasks? Kind regards, Wolfgang
View Replies !
Using Newly Created Table In Data Flow Task
Hello dear Forum, I guess this should be rather simple to answer but i just don't get how to do this. I just want to import some data from an Access mdb file into a SQL Server 2005 Database. As I dont want to create all tables in Sql Server 2005 beforehand i'd like to create them out of my SSIS Package. Therefore i use the 'Execute SQL Task' in the control flow before stepping into the dataflow where only a oledb-source and an oledb-destination exist... VERY simple... My problem is, that i can't select the table from the selectionlist in the oledb-destination because it does not exist before executing the package... If i create the table by hand just to be able to select it, that does not help. because if everything is set up and then i delete the table (because it will be created by the package anyway) an error occurs before executing the package - in the validation phase: Package Validation Error: "Invalid object name 'myToBeCreatedTable'". Can't i create tables on the fly which i use then in my dataflow tasks? Kind regards, Wolfgang
View Replies !
Data Flow Task - Have Null Values Take Table Default
Hi, I have a data transform from a flat-file to a SQL server database. Some of the flat-file fields have NULL values. The SQL table I'm importing into does not allow NULL values in any field, but each field has a Default value specified. I need to have it so that if a null value comes across in a field using the data transform, it takes the table default on import. I could of sworn I had this working a few days ago, but I get errors now that state I'm violating table constraints. Has anyone done this before? Thanks Jeff
View Replies !
Iplementing Multiple Lookups In A &&"Data Flow Task&&"
I have implemented a single lookup and would like to know the optimal approach to implement multiple lookups within a single €śdata flow task€? i.e. my question was if I had to look up multiple reference tables to obtain surrogate keys. I am oversimplifying for illustration purposes€¦ Source Table Destination Table Customer Customer Customer Name City State Name Key City Key State Key ABC Los Angeles CA 1 2 1 MNO Boston MA 2 1 2 Reference Tables Customer Name Customer City Customer State PK Key Customer Name PK Key City PK Key State 1 ABC 1 Boston 1 CA 2 MNO 2 Los Angeles 2 MA
View Replies !
Multiple Columns With Different Values OR Single Column With Multiple Criteria?
Hi, I have multiple columns in a Single Table and i want to search values in different columns. My table structure is col1 (identity PK) col2 (varchar(max)) col3 (varchar(max)) I have created a single FULLTEXT on col2 & col3. suppose i want to search col2='engine' and col3='toyota' i write query as SELECT TBL.col2,TBL.col3 FROM TBL INNER JOIN CONTAINSTABLE(TBL,col2,'engine') TBL1 ON TBL.col1=TBL1.[key] INNER JOIN CONTAINSTABLE(TBL,col3,'toyota') TBL2 ON TBL.col1=TBL2.[key] Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record. I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information. for e.g.; i was thinking to concatinate both fields like col4= ABengineBA + ABBToyotaBBA and in search i use SELECT TBL.col4 FROM TBL INNER JOIN CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1 ON TBL.col1=TBL1.[key] Result = 1 row But it don't work in following scenario col4= ABengineBA + ABBCorola ToyotaBBA SELECT TBL.col4 FROM TBL INNER JOIN CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1 ON TBL.col1=TBL1.[key] Result=0 Row Any idea how i can write second query to get result?
View Replies !
Built In Limit Or Setting That Limits The Number Of Rows From An OLE DB Source Table In A Data Flow Task?
I have a table that I'm loading as part of a control flow that in turn is copied to a target table by using a data flow task. I am doing this because a different set of fields may be used from the source entry to create the target entry based on a field in the source table. That same field may indicate that multiple entries need to be created in the target table from one source table entry for which I use a multi-cast transformation. The problem I'm having is that no matter how many entries there are in the source table, the OLE DB Source during execution only shows 7,532 entries being taken from the source table. If there are less than 7,532 entries in the source table, everything processes fine. More than 7,532 and the data flow task only takes 7,532 and then seems to hang. It also seems as though only one path of the multi-cast transformation is taken when the conditional split directs a source entry down that path. Seems like a strange problem I know, but any insight anyone could provide is appreciated. Thanks.
View Replies !
Using Multiple Data Sources
Not sure if this is possible, here is the scope: I have a vb program that calls a sp. My vb program connects to DataSource1, Catalog1 and to DataSource2, Catalog2. My sp resides in Catalog1 using a table from catalog1 and a table from catalog2. Is this possible to do?
View Replies !
Multiple Columns Into Single Row -- Very Urgent
Hi. I want to return multiple rows into a single row in different columns. For example my query returns something like thisThe query looks like thisSelect ID, TYPE, VALUE From myTable Where filtercondition = 1ID TYPE VALUE1 type1 121 type2 152 type1 16 2 type2 19Each ID will have the same number of types and each type for each ID might have a different value. So if there are only two types then each ID will have two types. Now I want to write the query in such a way that it returnsID TYPE1 TYPE2 VALUE1 VALUE21 type1 type2 12 152 type1 type2 16 19Type1, Type2, Value1, and Value2 are all dynamic. Can someone help me please. Thank you.
View Replies !
Multiple Columns From Single Cell
Running SQL2000 I have a one to many relationship between Flag and StudentFlag. I'm wanting to select each Flag.FlagID that exist as a column that contains the StudentFlag.Value per StudentFlag.StudentID (or null for that column if that StudentFlag row doesn't exist). This single column appearing multple times in a single dataset has seemed difficult in a select statement. Is it posible in T-SQL? Tables, Keys, and Relationships CREATE TABLE [dbo].[Flag] ( [FlagID] [int] NOT NULL , [FlagName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[StudentFlag] ( [StudentFlagID] [int] NOT NULL , [FlagID] [int] NULL , [StudentID] [int] NOT NULL , [FlagValue] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[Flag] WITH NOCHECK ADD CONSTRAINT [PK_Flag] PRIMARY KEY CLUSTERED ( [FlagID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[StudentFlag] WITH NOCHECK ADD CONSTRAINT [PK_StudentFlag] PRIMARY KEY CLUSTERED ( [StudentFlagID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[StudentFlag] ADD CONSTRAINT [FK_StudentFlag_Flag] FOREIGN KEY ( [FlagID] ) REFERENCES [dbo].[Flag] ( [FlagID] ) GO Sample Output StudentID, FlagType1, FlagType2, FlagType3 1, 'yes', '2', null 2, null, null, 'X' 3, null, '7', 'X'
View Replies !
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 !
Multiple Data Sources For A Dataset
Hey everyone, I am trying to combine like data from two different data sources into a single data set. Is there anyway I can do this? It seems like I can only add one data set, but is there some sort of workaround I could use? thanks, Keith
View Replies !
Making Single Column Value To Multiple Columns
I have a table which has single column like this. REP5426 02-28-08 0592 00100028 CAFE 00205415 23.77 A O INPUT REP5426 02-28-08 0592 00100028 CAFE 00580910 475.58 A O INPUT REP5426 02-28-08 0592 00100028 CAFE 00800840 -13.32 A O INPUT REP5426 02-28-08 0592 00100028 CAFE 00205416 23.77 A O INPUT I want to put this in a new table in individual columns Col1 col2 col3 col4 col5 col6 col7 col8 col9 REP5426 02-28-08 0592 00100028 CAFE 00205415 23.77 A O INPUT REP5426 02-28-08 0592 00100028 CAFE 00580910 475.58 A O INPUT REP5426 02-28-08 0592 00100028 CAFE 00800840 -13.32 A O INPUT REP5426 02-28-08 0592 00100028 CAFE 00205416 23.77 A O INPUT How to do this. Thanks.
View Replies !
Error Writing Data To Same Destination In Single Data Flow
I am getting the following error running a data flow that splits the input data into multiple streams and writes the results of each stream to the same destination table: "This operation conflicts with another pending operation on this transaction. The operation failed." The flow starts with a single source table with one row per student and multiple scores for that student. It does a few lookups and then splits the stream (using Multicast) in several layers, ultimately generating 25 destinations (one for each score to be recorded), all going to the same table (like a fact table). This all is running under a transaction at the package level, which is distributed to a separate machine. Apparently, I cannot have all of these streams inserting data into the same table at one time. I don't understand why not. In an OLTP system, many transactions are inserting records into the same table at once. Why can't I do that within the same transaction? I suppose I can use a UnionAll to join them back together before writing to a single destination, but that seems like an unnecessary waste and clutters the flow. Can anyone offer a different solution or a reason why this fails in the first place? Thanks in advance.
View Replies !
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 !
Stored Procedure For Multiple Data Sources
Is there a best way of writing stored procedure to access data from multiple databases. we have an archive database and a live database. I need to retrieve the data from both and merge together, perform calculations and then display the data. Thanks for any inputs.
View Replies !
Building An Application On Multiple Data Sources
ASP.NET on SQL ServerI've been asked to quote for developing a system to expose data on aweb application. Most of the data will come from SQL Server DBslocated on a single box. However, some of the data will be sourcedfrom ORACLE which is located on a different box. It may be necessaryto create VIEWS and Stored Procedures joining these DBsDoes anyone have any pointers, clues, hints, tips or pitfalls that Imight consider while making my proposal? What sort of extracontingency should I allow for the connection to ORACLE? Should I doall the data retrieval on the DB server, or should I do it on the Webserver?Any thoughts would be appreciated.ThanksWilliam Balmer
View Replies !
Report Parameters And Multiple Data Sources
Hi, Am having difficulty with report/query parameters, where the report now regularly tells me that I must declare the scalar variable @Site. I want to use 3 data sources : 1. to select a site from a list of sites - works fine and I can select at runtime 2. Once this is selected - I need to present a pair of dates to the user - min and max for data for the site - needs selection 1 to have been performed. Now I HAVE seen this work - once, the date selectors were greyed out until the site had been chosen, then they became available. Now I get the scalar variable error. 3. Finally I will pull the data with 3 parameters (site, startdate, enddate) This was almost working, and the detail was produced for item 3, until I introduced the date selection option. Now neither item 2 or 3 will accept the users selected site - its from a drop-down. All 3 queries are being performed by SQL SP's : 1. exec getsitelist - used to populate the dropdown to select @Site 2. exec getdates @Site - used to preset the start/end dates (NB I would really like calendar control here to select the date, with the value pulled from 1 to set the start point - but hey lets walk first ;-). 3. exec GetData @Site, @Start, @End SOooo - can SSRS2005 even support dependent parameters of this type ??. If so - whats the best way to create the parameters etc. ??. NB I can see all 3 parameter defs in 'Report Parameters'. Many thanks - hopefully - for my sanity :-O. Regards Graham
View Replies !
How Can I Combine Values Of Multiple Columns Into A Single Column?
Suppose that I have a table with following values Table1 Col1 Col2 Col3 ----------------------------------------------------------- P3456 C935876 T675 P5555 C678909 T8888 And the outcome that I want is: CombinedValues(ColumnName) ---------------------------------------------- P3456 - C935876 - T675 P5555 - C678909 - T8888 where CombinedValues column contains values of coulmn 1,2 & 3 seperated by '-' So is there any way to achieve this?
View Replies !
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 !
Multiple Columns Index/Key (does It Free Me From Creating A Single Column Indexes???)
I hope i'm in the right place, but thanks anyway.... Actually i have 2 questions (regarding sql-server Indices/Keys): 1) I have an index, which is consisted of 4 columns. I've read elsewhere that this index functions (as well) as an index (single column index) on the first column of this multi-column index. Does this mean that if i'd like to have (in addition) Indices on all of the 4 columns seperately i need to define only 3??? 2) I have a unique key consisted of multiple columns. I'd like to save an index to this combination of columns as well (to speed up things in DB...). Does the definition of a multiple-columns key free me from defining the multiple- columns index??? can anyone explain the main diference between Keys and Indices???
View Replies !
Multiple Columns Index/Key (does It Free Me From Creating A Single Column Indexes???)
I hope i'm in the right place, but thanks anyway.... Actually i have 2 questions (regarding sql-server Indices/Keys): 1) I have an index, which is consisted of 4 columns. I've read elsewhere that this index functions (as well) as an index (single column index) on the first column of this multi-column index. Does this mean that if i'd like to have (in addition) Indices on all of the 4 columns seperately i need to define only 3??? 2) I have a unique key consisted of multiple columns. I'd like to save an index to this combination of columns as well (to speed up things in DB...). Does the definition of a multiple-columns key free me from defining the multiple- columns index??? can anyone explain the main diference between Keys and Indices??? thanks, Ran Kizi
View Replies !
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 !
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 !
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 !
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 !
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 !
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 !
|
TRACKER
