HOW? Access Results Of Computed SQL Statement In A Data Flow
Apr 24, 2007
The SQL computed is complex enough that I can't see a way to make it a parameterized query. The obvious approach seems to be to compute the SQL in a CONTROL FLOW SCRIPT TASK and then use it to load a variable to set the VARIABLE SOURCE of a CONTROL FLOW EXECUTE SQL TASK.
I see that I can return a resultset to a variable.
But getting the rows of the results into a dataflow is not obvious. I have heard mentione that a Derived Column can do this. I can see using a dummy SCRIPT COMPONENT as DATA SOURCE with nothing in it to then drop into DERIVED COLUMN. But when setting up DERIVED COLUMN I don't see how to pull the columns out of the RESULTSET variable.
If it makes a difference I think the columns of the resultset will always be the same in this scenario.
Maybe this is totally the wrong approach? Any clues would be appreciated.
I'm currently setting variables at the package level with an ExecuteSQL task. This works fine. However, I'm now starting to think about restartability midway through a package. It would be nice to have the variable(s) needed in a data flow set within the data flow so that I only have to restart that task.
Is there a way to do that using an SQL statement as the source of the value in a data flow?
OR, when using checkpoints will it save variable settings so that they are available when the package is restarted? This would make my issue a moot point.
I need some help determining the best way to accomplish my task. The workflow starts by generating a list of unique ID's from a local table. Then take that list of unique ID's and query an Oracle table for all matching records.
My thought was to first use an Execute SQL task with the following SQL:
select projectid from projectlist group by projectid
with Result Set configured as follows:
Result Name = projectid Variable Name = varProjectIDList
Then in the Data Flow Task add a DataReader Source to pull the matching data. Here's where I'm getting hung up. I'd like to pass the result set from the Execute SQL task. I tried the following SQL but it doesn't work.
select * from masterlist where projectid = @[User::varProjectIDList]
I'm open to any suggestion on the best way to take my unique list and use it as input for a query against my Oracle DB.
Hi, Quick question on how SSIS handles queries from Data Source in a Data Flow. I noticed that when I run a particular query from Query Analyzer it takes forever. But, when I run the same query in SSIS data source in a data flow. The query results are immediate.
The query plan is already cached in SQL.
Is this just something which I am seeing incorrect or is there some bit of optimization in there in SSIS. As per my understanding SSIS does not optimize the source query.
I'm just starting off in SSIS and have a question that I can't find an answer to...
I'm loading in a number of files (in separate Data Flows) and performing some transformations on them before merging them back together. What I'm not sure about is what I should be doing with the data at the end of each of my "Import Data From XXXX Flat File" Data Flows. Am I better off using OLE DB Destinations (or SQL Server Destinations) and saving this intermediate data to temporary tables, or am I better off using a Raw File Destinations and saving this intermediate data to files? Or is there, perhaps, a better option that I'm currently unaware of?
If the Raw File Destination is the way to go, then isn't there a maintenance issue with cleaning up all the files created? And will there not be a management issue to ensure that there is sufficient disc space available on the drive you are saving to?
I'm a bit confused and overwhelmed by SSIS at the moment, so any help would be much appreciated!
I'm importing a large csv file two different ways - one with Bulk Import Task and the other way with the Data Flow Task (flat file source -> OLE DB destination).
With the Bulk Import Task I'm putting all the csv rows in one column. With the Data Flow Task I'm mapping each csv value to it's own column in the SQL table.
I used two different flat file sources and got the following:
I can't seem to find a way to make the Data Flow Destination in a Business Intelligence Visual Studion Project output an MDB file for Microsoft Access.
In Excel they are showing Sale as -ve quantity and purchase as +ve quantity.
The database has quantity always as +ve figure and a separate column "isPurchase" set to true or false depending on whether purchase or sale.
So I need Derived Column to return a bolean (or int) depending whether quantity is positive or negative. I tried each of the following in the Expression but all of them were invalid expressions.
CASE WHEN [TotalQuantity] > 0 THEN 0 ELSE 1 END
If [TotalQuantity] > 0 THEN 1 ELSE 0 END
IIF ([TotalQuantity] > 0, 1,0)
Can anyone help me with correct syntax, or correct Data Flow Transformation if Derived column is wrong.
I have following requirement. From OLE-DB source I am getting IDS. Then lookup with some master data. Now I have only matching IDs. Now I need find some filed(say Frequency from some table for each above id). I already write stored procedure for same where I am passing ID as parameter.Which is working fine when I run it SQL server management studio.
Query is sort of
Select field1,fiel2... from table 1 where id = @id
@id is each ID from lookup
Now I want to call this stored procedure in Data flow. I tried it using OLE DB command but it did not return output of stored procudre. I am getting output same what ever I am passing input.
Is there way to do this? In short my requirement is execute parametrized select statement using data flow trasformation component.
I'm creating a SSIS in the designer view of SQL Server BI Dev. Studio (SQL Server 2005)
I need to import a whole table from MS Access into my local SQL Server.(this task will be performed weekly, so once working I'll schedule a job for it)
I've created a 'FILE' connection to MS Access in the 'Connection Managers'.
When I'm on the 'Data Flow' tab I can't find a Data Flow Item to use as a MS Access connection. (available on the 'Data Flow Sources' are only: DataReader, Excel, Flat File, OLE DB, Raw File and XML Sources)
hi in my package, some sql operations need the special user name and admin privilage. so how do i create my ssis package so that when it executes it takes the given username and password from the table in some database.
I am using Remote Data service to Query an Sql Server Database using MDAC. The Os in which server is loaded in Window 2003 and the MDAC 2.8 version is installed. Now I create a table X with identity column. Then when I try to insert records in that table using Insert into X select * from Y statement. The statement gets executed by when i check the X table I find that the duplicate records are present with different identity no's.
Even when i truncate and retry the same thing occurs.
I want to use an if statement to compute the value of a column in SQL Database using other columns. I am supposed to check if a column is null or not and do the computations accordingly. Can anyone help?
I have a query that returns results based on information in several tables. The problem I am having is that is there are no records in the one table it doesn't return any information at all. This table may not have any information initially for the employees so I need to show results whether or not there is anything in this one table. Here is my select statement: SELECT employee.emp_id, DATEDIFF(mm, employee.emp_begin_accrual, GETDATE()) * employee.emp_accrual_rate - (SELECT SUM(request_duration) AS daystaken FROM request) AS daysleft, employee.emp_lname + ', ' + employee.emp_fname + ' ' + employee.emp_minitial + '.' AS emp_name, department.department_name, location.location_name FROM employee INNER JOIN request AS request_1 ON employee.emp_id = request_1.emp_id INNER JOIN department ON employee.emp_department = department.department_id INNER JOIN location ON department.department_location = location.location_id GROUP BY employee.emp_id, employee.emp_begin_accrual, employee.emp_accrual_rate, employee.emp_fname, employee.emp_minitial, employee.emp_lname, department.department_name, location.location_name ORDER BY location.location_name, department.department_name, employee.emp_lname
The section below is the part that may or may not contain information: SELECT (SELECT SUM(request_duration) AS daystaken FROM request) AS daysleft
So I need it to return results whether this sub query has results or not. Any help would be greatly appreciated!!! TIA
I am receiving error msg on the below query line as "incorrect syntax near MSF" , "Incoorect syntanx near ExtCost", "incorrect syntax near From on line 28"
SELECT xxxcolumns, (rj.RECEIVEDLINEAL * ((r.WIDTH / 12.0)) / 1000.0 MSF, Case when rv.PricePerCode = 'MSF' Then ((rj.RECEIVEDLINEAL * (r.WIDTH / 12.0)) / 1000.0) * rv.price
I am getting inconsistent results when BULK INSERTING data from a tab-delimited text file. As part of my testing, I run the same code on the same file again and again, and I get different results every time! I get this on SQL 2005 and SQL 2012 R2.We have an application that imports data from a spreadsheet. The sheet contains section headers with account numbers and detail rows with transactions by date:
AAAA.1234 /* (account number)*/ 1/1/2015Â Â Â Â Â $150Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â First Transaction 1/3/2015Â Â Â Â Â $24.233Â Â Â Â Â Â Â Â Â Â Â Â Â Second Transaction
BBBB.5678 1/1/2015Â Â Â Â Â $350Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Third Transaction 1/3/2015Â Â Â Â Â $24.233Â Â Â Â Â Â Â Â Â Â Â Â Â Fourth Transaction
My Import program saves this spreadsheet at tab-delimited text, then I use BULK INSERT to bring the data into a generic table full of varchar(255) fields. There are about 90,000 rows in each day's data; after the BULK INSERT about half of them are removed for various reasons. Next I add a RowID column to the table with the IDENTITY (1,1) property. This gives my raw data unique row numbers.
I then run a routine that converts and copies those records into another holding table that's a copy of the final destination table. That routine parses though the data, assigning the account number in the section header to each detail row. It ends up looking like this:
AAAA.1234Â Â Â Â 1/1/2015Â Â Â Â Â $150Â Â Â Â Â Â Â Â Â First Purchase AAAA.1234Â Â Â Â 1/3/2015Â Â Â Â Â $24.233Â Â Â Â Â Second Purchase BBBB.5678Â Â Â Â 1/1/2015Â Â Â Â Â $350Â Â Â Â Â Â Â Â Â Â Â Â Third Purchase BBBB.5678Â Â Â Â 1/3/2015Â Â Â Â Â $24.233Â Â Â Â Â Â Â Fourth Purchase
My technique: I use a cursor to get the starting RowID for each Account Number: I then use the upper and lower RowIDs to do an INSERT into the final table. The query looks like this:
SELECT RowID, SUBSTRING(RowHeader, 6,4) + '.UBC1' AS AccountNumber FROM Â GenericTable WHERE RowHeader LIKE '____.____%'
Results look like this:
But every time I run the routine, I get different numbers! my results are not accurate. I get inconsistent results EVERY TIME.Here is my code, with table, field and account names changed for business confidentiality.This is a high profile project at my company;
TRUNCATE TABLE GenericImportTable; ALTER TABLE GenericImportTable DROP COLUMN RowID; BULK INSERT GenericImportTable FROM 'SERVERGeneralAppnameDataFile.2015.05.04.tab.txt' WITH (FIELDTERMINATOR = ' ', ROWTERMINATOR = ' ', FIRSTROW = 6)
I need to pass a parameter from control flow to data flow. The data flow will use this parameter to get data from a Oracle source.
I have an Execute SQL task in control flow to assign value to the Parameter, next step is a data flow which will need take a parameter in the SQL statement to query the Oracle source,
The SQL Looks like this:
select * from ccst_acctsys_account
where to_char(LAST_MODIFIED_DATE, 'YYYYMMDD') >?
THe problem is the OLE DB source Edit doesn€™t have anything for mapping parameter.
I have an Execute SQL Task that returns a Full Rowset from a SQL Server table and assigns it to a variable objRecs. I connect that to a foreach container with an ADO enumerator using objRecs variable and Rows in first table mode. I defined variables and mapped them to the columns.
I tested this by placing a Script task inside the foreach container and displaying the variables in a messagebox.
Now, for each row, I want to write a record to an MS Access table and then update a column back in the original SQL Server table where I retreived data in the Execute SQL task (i have the primary key). If I drop a Data Flow Task inside my foreach container, how do I pass the variables as input to an OLE DB Destination on the Data Flow?
Also, how would I update the original source table where source.id = objRects.id?
Thank you for your assistance. I have spent the day trying to figure this out (and thought it would be simple), but I am just not getting SSIS. Sorry if this has been covered.
Dear All! My package has a Data Flow Task. In Data Flow Task, I use a Script Component and a OLE BD Destination to transform data from txt file to database. Within Data Flow Task, I want to call File System Task to move file to a folder or any Task of "Control Flow" Tab. So, Does SSIS support this task? Please show me if any Thanks
Hi all! I recently started working with SSIS and one of the things that is puzzling me the most is what's the best way to go:
A small control flow, with large data flow tasks A control flow with more, but smaller, data flow tasksAny help will be greatly appreciated. Thanks, Ricardo
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)
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.
We are using MS SQL Server 2008. I am running a batch job which deletes 21 days older records(6-7 million records). But daily we have transaction is going on in the database. When the delete occurs, all the insert statements got blocked and waits till the delete statement to complete. May I know why the blocking occurs?Â
I get the below error on the event log of my application server which uses SQL database.
Details: RuleId:a811dcbc-4c5b-d9de-592b-f01e17fc0e9a. HealthServiceId:a5f70248-b545-4d35-7c84-e7aa87610ee4. The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Alert_BaseManagedEntity". The conflict occurred in database "OperationsManager", table "dbo.BaseManagedEntity", column 'BaseManagedEntityId'.
The statement has been terminated.RuleId:a811dcbc-4c5b-d9de-592b-f01e17fc0e9a. HealthServiceId:a5f70248-b545-4d35-7c84-e7aa87610ee4. The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Alert_BaseManagedEntity". The conflict occurred in database "OperationsManager", table "dbo.BaseManagedEntity", column 'BaseManagedEntityId'.The statement has been terminated..
Details: RuleId:a811dcbc-4c5b-d9de-592b-f01e17fc0e9a. HealthServiceId:a5f70248-b545-4d35-7c84-e7aa87610ee4. The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Alert_BaseManagedEntity". The conflict occurred in database "OperationsManager", table "dbo. BaseManaged Entity", column 'BaseManagedEntityId'.The statement has been terminated..
I am wondering if it is possible to use SSIS to sample data set to training set and test set directly to my data mining models without saving them somewhere as occupying too much space? Really need guidance for that.
I am working on importing an Excel workbook, saved as multiple CSV flat files, that has both group level data and related detail row on the same sheet. I have been able to import the group data into a table. As part of the Data Flow task, I want to be able to save the key value for the group, which I will use when I insert the detail rows.
My Data Flow has the following components: The flat file with the data, which goes to a derived column transformation to strip out extraneous dashes, which leads to the OLEDB Destination component.
I want to save the value as a package level variable, so that I can reference it in another dataflow.
Is this possible, and if so, at what point do I save the value?
I have run into a problem while creating a simple UDF on SQL Server 2000.
Code Snippet
CREATE FUNCTION [dbo].[GetSectionNum] (@section varchar(4)) RETURNS varchar(2) AS BEGIN DECLARE @sTemp varchar(2),@s char DECLARE @count int DECLARE @length int
set @length = LEN(@section) set @count = 1
WHILE (@count <= @length) BEGIN SET @s = SUBSTRING(@section,@count,1) IF(ISNUMERIC(@s)) BEGIN SET @sTemp = @sTemp + @s END SET @count = @count + 1 END
IF(LEN(@sTemp) = 1) BEGIN SET @sTemp = '0' + @sTemp END
RETURN @sTemp ENDWhen I perform a syntax check I receive and error about "Error 156: incorrect syntax near keyword 'BEGIN'. I have narrowed the problem to the IF statement inside the While block. If I remove the IF statement the syntax check is successful. This is the first UDF I have written so I'm swimming in uncharted water. Thanks ahead of time for your help.
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
