SQL 2012 :: SSIS Script As Transform Not Producing Output
Jan 30, 2015
I have an SSIS with several data flows I need to do some complex data evaluations so I have used a script as transform in two of the DFT's. If I run these separately everything works great and there are no problems what so ever. If I run them together I notices I was getting an error on the second one. I discovered that this seems to be some kind of namespace problem since both Scripts were using Input_0 buffer. So I changed the name of the second one and retested.
Well I no longer get the error and in fact it seems to run through the entire SSIS just fine. However when I look closer I notice that the second Script task just does not seem to do anything at all. The script task does a lot of evaluation of the incoming data and then does some calculations depending on the value in the service code. however when it runs through this in the second script task all of the define output rows are just empty.
I have gone through and made sure that all input and output buffers are unique names thinking this was a similar problem but no luck. I even changes all column and variable names to unique with no luck. Again If I run them separately everything work fine it is only when I run the entire package that this problem occurs.
I have a Pivot Transform in SSIS (2005) working perfectly, EXCEPT for that the first column of the output (the date) repeats for each of the following columns, which are themselves falling into the correct column, but not on the same line for a particular date as the others. Snipet of result from Data Viewer is:
We are using lookup transformation in SSIS 2012. The lookup transformation queries a table with two date columns. When we hover the mouse over the two columns in the 'columns' tab of the lookup transformation editor, the two columns show as DT_WSTR instead of DT_DBDATE. This causes the SSIS package to fail due to data type mismatch.A similar abandoned thread is available at: URL....
I am generating some dynamic sql which I would like to run and return the data from, however when I run EXEC(@TEMP_TABLE_STRING) it cannot return the table I think because it is out of scope.
Is there a way around this.
declare@Tablename VARCHAR(150) = 'TEMP_LOCATIONS' declare@FilterClause VARCHAR(512)=NULL declare@Classes VARCHAR(100)=NULL declare@IsExcel BIT = 1 DECLARE @SQL Nvarchar(MAX) = '' SET NOCOUNT ON;
I want to create the following scenario. I have a table that stores employees working on projects and their project hours by week, but now I also need a running total per week for each of those projects. For example take a look below:
EmployeeID, Project, Sunday, Monday, Tuesday,....Saturday, ProjectHours, TotalProjectHoursPerWeek(this is the column I am trying to derive), FiscalWeek
Correlated Subquery: SELECT EmployeeID,Project, Sunday, Monday,....Saturday, ProjectHours, SELECT(SUM(ProjectHours) FROM dbo.TableABC ap GROUP BY FiscalWeek), FROM dbo.TableABC a
I got this to work one time before, but now I am getting the following error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I map the columns, refresh & OK out of the component without trouble, but on executing the package it fails during validation on this component. I'm utterly stumped. Any light shed would be greatly appreciated. Many thanks in advance, Tamim.
Would anyone happen to have any pointers or know of any good code examples to either programmatically change the type of an input column when it is passed through the component, or add a new column to the output? I am extracting data from an Oracle database which is in Julian date format (represented within SSIS as a DT_NUMERIC column) and I need to to either transform the input column holding it into a date column, or to dynamically add a new output column holding the transformed data.
Requirement: I have a simple package with one dataflow task. In that I need to read from a sql table and for every row in that table loop through n times and generate new output rows based on certain conditions (which are best evaluated in custom script as they are rather complex). Hence, if I have 100 rows in the table as my input, I may end up with 100*n rows as output.
My Design: To implement this I have used an OLE DB Source which outputs to a Script Transform (ST). In the ST I intend to loop through in custom code and generate new rows using the .AddRow feature when I need new rows. This ST then feeds into another OLE DB Destination which writes the data to the table. Simple! I am using the default buffer settings. All I have tweaked is the Synchronous... property on the script transform (otherwise I do not get to the Output0Buffer within the script!).
Problem: I wish to do as much as possible in parallel. So I would expect the OLE DB Source to provide more than one row at a time to the script transform and that should process more than one input row simultaneously. It seems the script componenet is serializing input, so it seems to take one row at a time from the OLE DB source, loop through and process in the script transform).
AM I RIGHT IN THINKING THAT THE SCRIPT TRANSFORM IS EXECUTING THE INPUT IN A SEQUENTIAL MANNER? CAN I PARALLELISE THIS? If so, how?
I have a very simple SSIS package that is moving data from a DB2 database to a Teradata box. I've run it around 10 times, twice it pushed data over, the balance of the time, it executes with no error, but moves nothing over. In the "incomplete" runs, a command line box pops up for half a second, then the package ends.
Does anyone have ideas as to why this behavior is occurring?
I am trying to write a ssis surrogate key data transform, my problem is I can't find an example how to add a column to the incoming columns and add some data to it. If anyone has a sample, can you please post it. I found a script option that works but I would like an actual transform.
I have an OLE DB Source and i want to transform the data type fields of the table before i export the table in an OLE DB Destination. Is there a way to transform numeric value to float, and numeric to nvchar?
I am trying to read in a flat file, transform the fields and store into a destination database.
In DTS, this works using Transform Data Task Properties. I define the columns and then have a VB script on the Transformations tab that changes any bad data.
Is there a way to do this in SSIS that I can define the column transformations and re-use my VB scripts?
i have too many DTS packages to migrate to SSIS, and while examining a DTS package in BIDS (converted with the migration utility) i tried to edit the resulting migrated package, which opened the DTS interface with the two connection icons joined by the big fat arrow with a gear on it...not exactly what i had in mind, iow, it looks like SSIS on the outside, but its still DTS on the inside. So I stripped out a series of components from a more complex package hoping that simplifying it would reveal the contents of old DTS Transformations tab at least partially set up in a Derived Column transformation. Can i get there from here, or must i recreate every stinking definition in a derived column manually from the ground up? thanks very much for your help
I can't figure out how to put nested tables into the Data Mining Model Training Transform (SSIS). I can do a simple case table, but how do you get those nested tables with DM Training Transformation? Any ideas? Samples?
I work in the healthcare area, and am handling the survey data ETL's. There are around 8 different survey areas and based on information received from them for the visit they reference, I want to pull in more info from our invoicing database. My idea is this:
1.) Pull in the flat file to an ODBC staging table 2.) Cache all invoice records that fall between the MIN(Date of Service) and MAX(Date of Service) from the staging table. 3.) First lookup the information needed on patientID, providerID, date of service, and billing location. 4.) For the surveys that didn't match on those 4 columns, try looking up based on patientID, date of service, and billing location (since I could be 99% sure this would still return the record I need). 5.) For the remaining surveys, lookup based just on patientID and date of service. These records will be flagged for manual review because clearly, if a patient has multiple appointments in the same day, this will be prone to error.
However, in trying to use only 3 of the columns in the lookup, I get the error saying basically that I need to utilize all 4. Is there a way around this, or is there an entirely different way I should be approaching this? The reason I thought cache transform was the answer is because I will need to run a different package for each lookup, as the data and logic between each survey will vary, but the invoice data "pool" will stay the same regardless.
I am trying to create an ssis package with dynamic csv file as output. and out format contains query output.
sample file name:
Unique identifier + query output + systemdate();
The expression is looking like this.
@[User::FilePath] + @[User::FileName] + ".CSV"
-- user filepath is a variable from ssis package. File name is the output from SQL query. using script task i have assigned the values to @[User::FileName] .
When I debugged the script task the value getting properly but same variable am using for Flafile destination. but its not working.
In my current project i have a requirement to assign value of an aggregate transform to a variable. But i need to accomplish it without using a script task.
I would like to know what happens when a very large reference data set for a lookup transform with full caching enabled is getting loaded during package execution and the computer memory runs out or is very low. Does SSIS a) give an out of memory error of some sort b) resort to a no caching or partial caching mode c) maintain the full caching mode but will switch to using the paging file(virtual memory).
I think it will resort to using the page file in which case the benefits of in memory lookups are lost and performance would suffer. If I cannot upgrade the memory or shrink the reference set somehow, i should switch that lookup task to use partial caching or no caching with an indexed lookup table. Would this make sense?
Hi JayH (or anyone). Another week...a new set of problems. I obviously need to learn .net syntax, but because of project deadlines in converting from DTS to SSIS it is hard for me to stop and do that. So, if someone could help me some easy syntax, I would really appreciate it.
In DTS, there was a VBScript that copied a set of flat files from one directory to an archive directory after modifying the file name. In SSIS, the directory and archive directory will be specified in the config file. So, I need a .net script that retrieves a file, renames it and copies it to a different directory.
I am not sure the SQL code to use...I am to produce a list that shows total sales by customer for the first two months of the year. I am to show the customer name, customer number, and total sales in that order. The current format I have it in list every invoice separately and what I need to do is find a way (the code) to combine the invoice totals into one total amount and only have the company name listed once. I also do not know how to set a date range so it includes invoices only from 1/1/2005 to 2/28/2005 Right now this is the current list and SQL code I have:
NameCustomer NumberDateSales Ace Construction Co.100023/18/2005$62.25 Ace Construction Co.100022/13/2005$62.25 Ace Construction Co.100022/13/2005$97.50 Ace Construction Co.100022/13/2005$89.00 Ace Construction Co.100022/13/2005$194.25 Ace Construction Co.100021/29/2005$24.90 Ace Construction Co.100023/18/2005$67.80 Ace Construction Co.100023/18/2005$304.15 Ace Construction Co.100021/29/2005$695.00 Ace Construction Co.100021/29/2005$19.50 Ace Construction Co.100023/18/2005$299.00 Aho Electrical Services100233/20/2005$449.55 Aho Electrical Services100233/20/2005$149.55 Aho Electrical Services100233/20/2005$994.70 Aho Electrical Services100233/20/2005$276.45 Barren Construction100501/30/2005$298.41 Barren Construction100502/25/2005$62.85
SELECT DISTINCTROW tblCustomer.CustomerName, tblSales.CustomerNumber, tblSales.InvoiceDate, [SInvPrice]*[SInvQuantity] AS Sales FROM (tblCustomer INNER JOIN tblSales ON tblCustomer.CustomerNumber = tblSales.CustomerNumber) INNER JOIN (tblInventory INNER JOIN [tblSales-Inventory] ON tblInventory.InventoryItemCode = [tblSales-Inventory].InventoryItemCode) ON tblSales.InvoiceNumber = [tblSales-Inventory].InvoiceNumber ORDER BY tblSales.InvoiceNumber, [tblSales-Inventory].InventoryItemCode;
trying to make a script to view data in a excell spreadsheet.I can get all the data I need except one.select ordernumber, itemcode, quantity, rate, totalfrom dbo.chargeswhere ordernumber = '45676'the problem is there are more than one itemcodeexample: run resultsordernumber, itemcode, quantity, rate, total45676 fuel 123 .10 12.3045676 stops 3 50 150how do I get this data on one line?to look more like this: run resultsordernumber, itemcode, quantity, rate, total itemcode, quantity, rate, total45676 fuel 123 .10 12.30 stops 3 50 150now just one ordernumber appears and its all on one line.hope you can help, thanks for your time...Mike
select distinct pd.productcategory,pd.imagepath,pd.[description],p.productid,p.[name] ,p.designer,p.weight,p.price from productdescription pd inner join products p on pd.productcategory=p.productcategory where @prodcat=p.productcategory order by p.productid
return
My Results are:
ProductCategory ProductID (Rest of the columns) 22 47 22 47 22 58 22 58
In my productdescription table there are 2 rows in the productcategory column which has number 22. In the products table there are 2 rows(productid 47&58) in the productcategory column which has number 22. I believe this is many to many relationship problem but I do not know how to correct it. My results need to show only 2 records and not 4.
I ran my SP in QA and it brought back some data from my live database but I had my test DB selected from the DDL at the top of the screen. So i ran a Select statement on the whole table in a different query window (with the same parameters)and it gave me the correct info. Any idea y? They both should be bringing back information from the same table not one from my live DB and one from my TEST DB. Please help me--I'm so lost!min max rate <----This is what my stored procedure brought back(data from my live DB) ---it has parameters 90 100 .4080 89 .3070 79 .2559 69 .1549 58 .10The stored procedure is as follows: CREATE PROCEDURE dbo.sp_WageMatrix_GetRate( @CompanyID nvarchar(2), @FacilityID nvarchar(2))AS SET NOCOUNT ON;SELECT [Min], [Max], Rate FROM Performance.dbo.WageMatrix WHERE (CompanyID = @CompanyID) AND (FacilityID = @FacilityID) AND PeriodID = dbo.fn_GetCurrentPeriod(CompanyID, FacilityID)ORDER BY RangeIDmy select statement brought back the following:min max rate 90 100 .4080 89 .3070 79 .2560 69 .15and I ran the following select statement: SELECT [Min], [Max], [Rate] FROM [PerformanceDEV].[dbo].[WageMatrix] where companyid = '21' and facilityid = '01' and periodid = 2order by rangeid
I am going crazy. I have a typical date field in tbl_Feedback(smallDateTime) that is storing just the date portion. I have a stored proc that I want to dynamically retrieve records within a certain date range (typical!). I am using ASP/ADO, etc. When I run the following Stored Proc in SQL Analyzer, it works (if I replace the variable "@sDate" with '10/01/2004' --There ARE records within this date range.) Further more, If I run a simple Query on the Feedback table and do the same, it produces results, but when I call the stored Proc from the web page, I get 0 results when attempting to PASS date values dynamically. I tried to pass them as actual date types, still 0. I'm now attempting to pass it as a 10 length string ("10/01/2004") and converting it to a date, still 0. What am I MISSING???
SELECT * FROMtbl_Feedback WHERE tbl_Feedback.DateIn >= @startDate GO
--- ADO Web page
' Stored Procedure Name: sp_009_SiteLead ' Site Lead Default Query
set cmd=server.CreateObject("ADODB.command") set psDate = cmd.CreateParameter("sDate", adDBTimeStamp, adParamInput) set cmd.ActiveConnection=DBConnection cmd.CommandText="sp_009_SiteLead" cmd.Parameters.Append peDate cmd.Parameters.Append psdate psDate.Value = "10/01/2004" Set rsObj = CreateObject("ADODB.Recordset") rsObj.ActiveConnection = DBConnection rsObj.CursorLocation = adUseClient rsObj.PageSize= 20 rsObj.Open cmd
Again, If I replace the @startDate variable in the stored proc with a date surrounded in single quotes, I get the expected results, but it just does not understand my date if I pass it. Can anyone help?
I'm having difficulty coming up with the right syntax for a query. Suppose I have a database containing a Stores table, an ProductInventory table, and a Customers table. The Stores table has an ID field that serves as a foreign key in both the ProductInventory table and in the Customers table. I'm trying to write a query that, for each Store record, will return the total number of records in the ProductInventory table and the total number of records in the Customers table.
The following query returns, for each store, the total number of records in the ProductInventory table:
SELECT Stores.Name, COUNT(ProductInventory.ID) AS ProductInventoryItemCount FROM Stores LEFT JOIN ProductInventory ON Stores.ID = ProductInventory.StoreID GROUP BY Stores.Name
The following query returns, for each store, the total number of records in the Customers table:
SELECT Stores.Name, COUNT(Customers.ID) AS CustomerCount FROM Stores LEFT JOIN ProductInventory ON Stores.ID = Customers.StoreID GROUP BY Stores.Name
I combined the two queries:
SELECT Stores.Name, COUNT(ProductInventory.ID) AS ProductInventoryItemCount, COUNT(Customers.ID) AS CustomerCount FROM Stores LEFT JOIN ProductInventory ON Stores.ID = ProductInventory.StoreID LEFT JOIN Customers ON Stores.ID = Customers.StoreID GROUP BY Stores.Name
When I run this last query, however, I get an "Arithmetic overflow error converting expression to data type int" error. Using COUNT_BIG instead of COUNT eliminates the error, but the numbers that are generated are astronomical in size. This indicates to me that there is a *lot* more table joining going on than I expected
What is the correct syntax to produce the desired results? I have a few other tables similar to ProductInventory and Customers; I'm hoping to extend the correct syntax so as to be able to get a comprehensive record count list for each store. Thanks for your help!
I have a decimal value, 10,55,263.55 . This value should be converted into lakhs and the output should show as 10.55. I need to display in lakhs. What is the query do I need to write to get this output.
We have two queries that run nightly and we'd like to combine them and only have one result set instead of two. What's the best way to combine these? The only difference is the Table the information is being pulled from.
Query 1:
set nocount on select case when datalength(MICRACCTNUMBER) = 4 then convert(char(20),('001 000000000000'+MICRACCTNUMBER)) when datalength(MICRACCTNUMBER) = 5 then convert(char(20),('001 00000000000'+MICRACCTNUMBER)) when datalength(MICRACCTNUMBER) = 6 then convert(char(20),('001 0000000000'+MICRACCTNUMBER))
[code].....
Again, the only difference is the Table the info is coming from...
I want to produce a summary table of amounts per status per user.
I have 2 tables:
Invoices:
Code: user_id, amount, status 1, £10, S 2, £20, P 3, £30, P 3, £40, E
Users:
Code: user_id, name 1, user A 2, user B 3, user C
And I want to produce a summary table like this:
Code: S P E Total user A £10 £10 user B £20 £20 user C £30 £40 £70
What I have is:
Code: SELECT Users.name, (SELECT SUM(amount) FROM Invoices AS t1 WHERE t1.user_id = Invoices.user_id AND (t1.status = 'S')), (SELECT SUM(amount) FROM Invoices AS t1 WHERE t1.user_id = Invoices.user_id AND (t1.status = 'P')), (SELECT SUM(amount) FROM Invoices AS t1 WHERE t1.user_id = Invoices.user_id AND (t1.status = 'E')), (SELECT SUM(amount) FROM Invoices AS t1 WHERE t1.user_id = Invoices .user_id AND (t1.status IN ('S','P','E'))) FROM Invoices LEFT JOIN Users ON Users.user_id = Invoices .user_id GROUP BY Invoices.user_id, Users.name ORDER BY Users.name
This does give me what I want, however the real situation has lots of status codes, many more fields in the Invoices table, hundreds of users and hundred of thousands of records in the Invoice table and I have run out of system memory.
