SQL 2012 :: Lookup Task In SSIS - Using A View Rather Than A Table?

Aug 6, 2014

We have a LKP table that we will use to decode incoming codes from multiple sales feeds to link to the relevant surrogate key for the ultimate dimension table.

The LKP table has a start date, end date and active row flag to track history of the codes.

This table in its entirety is required for the FULL history load but we will need a CURRENT version of the table containing only the ACTIVE rows for the DAILY incremental loading.

I am thinking of putting a standard view over the top of the LKP table to present only the ACTIVE rows rather than creating a 2nd table and reloading it each night. We don't have enterprise edition to use materialised views.

My question is, will the LKP cache populate as the SSIS packages begins, loading it with all the rows from the view, as it would if I used the 2nd physical table with only ACTIVE rows?

View 3 Replies


SSIS Lookup Task - Best Practice Concerning Cachetype Property

Oct 26, 2006

SSIS data flow transformation - Lookup task - best practice concerning Cachetype

I would like to know if there's any best practice concerning the CacheType property for the the Lookup task. Default value is "Full", but if the SSIS package is working with at lot of data, i.e. +10 mill. records from the OLE DB source to be handled through a variated numbers of data flow transformation tasks, it must have an impact memory usage if the lookup table also is a large table, i.e. +8 mill. records? When should I consider turning the property value to "none"

View 1 Replies View Related

Using A View As A Lookup Table

Jan 29, 2004


I'm designing a database for my final year project at University, and have run into a bit of a dilemma with one of the tables. Basically, it's an equipment loan database where both students and lecturers need to be able to book and borrow equipment. To avoid having two separate bookings tables (i.e. StudentBookings and LecturerBookings) I've got students and lecturers combined into one table called 'Borrowers'. The trouble comes because the clients want the lecturer storing in the booking information table, which can get quite messy because lecturer information is stored in the same table as borrower information meaning that I basically need to relate both the Booking.BorrowerID and Booking.LecturerID to Borrowers.BorrowerID.

In theory, I think this could be solved by creating a View called Lecturers that pulls the required information for Lecturers out of the Borrowers table and then link Booking.LecturerID to the ID of the view. In practice, I've got a couple of queries:

1) Is it possible to perform a JOIN on a view in the way that I would need to here?

2) To keep things nice and clear, is it possible to 'rename' the BorrowerID to LecturerID whilst creating the View?

Sorry if some of this isn't too clear. Please ask any questions to clarify what I mean if you need to.


View 2 Replies View Related

Integration Services :: SSIS 2012 Lookup Transform Converts Date Field To String

Jul 30, 2015

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....

View 2 Replies View Related

Is It Possible To Lookup Value Based On Two Tables Using Lookup Task

Jun 27, 2007

Hi All,

Actually this is in regard to SCD Type 2 Dimension, Scenario is like that I am moving Fact table from some old source and I have dimensionA description value in fact which I want to replace with appropriate id from Dimension Table and that Dimension table is SCD Type 2 based on StartDate and EndDate and Fact Table doesn't contains direct date value rather there is timeId in Fact so to update the value in Fact table I have to Join Time Dimension table and other Dimension Table to replace fact Description with proper Id.

Lets assume DimensionA Structure





Fact Table






Time Dimension




Hour ...

View 1 Replies View Related

SQL Server 2012 :: Get ID From Lookup Table Based On Value Passed To Function

Dec 19, 2014

I have a requirement regarding a color combination data. I have a lookup table that holds a colorid, p1, p2, p3, p4 to p8 which will be having colors Red, Green and Amber. P1 to P8 columns holds these three colors based on their combinations.

I have attached the look up table data for reference.I need to pass the color values to p1 to p8 and need to retrieve the color id based on the passed color. If we pass values for all p1 to p8 then it is easy to get the color code, however it will not happen. The passed values may be dynamic. ie we will not have all 8 values all the times. sometimes we will have 2 colors passed, sometimes 5 colors will be passed.

If i pass only two colors say red and red, i need the color id of only the row that has red and red for p1 and p2 alone. i dont want want all the colorid's that has red and red in p1 and p2 and some other colors in p3 to p4.

The exact colorid of the combination must be returned on passing the values to p1 and p2.I am passing Red and Red as values to P1 and P2. In the look up table we can have 10 rows that has red and red i p1 and p2 like

colorid p1p2p3p4p5p6p7p8
1 redred
10 redredred
20 redredred
30 redredredred
40 redredredredred
50 redredredredredred
60 redredredredredredred
70 redredredredredredredred

So the result must have only the colorid 1 and not all the colorid's listed above. when I pass 3 red as values for p1, p2, p3 then the result must be 10. Colorid 1, 20, 30, 40, 50, 60 and 70 must not come in the result.I need a function or procedure that will accept the arguments and provide me the result based on the values.

View 2 Replies View Related

SQL 2012 :: SSIS Package - FTP Task Fails When Run From A Job

Apr 3, 2014

We are running SQL Server 2012 SP1 64-Bit EE on Windows Server 2008 R2 SP1. I have a SSIS Package which connects to a FTP Site and downloads a file. Then it truncates a table and loads the file data into a table. This package works okay when executed from within VSS and SSMS (In SSISDB, right click on the package and execute). However, when I execute it as a Job it does not run and appears to be failing on the first task which is the FTP Task. SQL job step - Type: SSIS Package; Run as: SQL Server Agent Service Account (domain account called playuser); Authentication: Windows Authentication.In the All Executions Standard Report for the SSISDB Catalog, it only says: FTP Download File: Errors: There were errors during task validation.

Is this because my domain account does not have access to the FTP Site?Is this where I need to come up with a Proxy Account with Credentials?Do I need to set up a SQL Server Login (Proxy Account) with the same username being used in the FTP Batch file?

FTP Commands in a batch file:
cd omb

View 3 Replies View Related

SQL 2012 :: SSIS FTP Task - Can't Move From Child Folder To Parent

Jan 13, 2015

I have set up an FTP connection that tests successfully. I can log on to the FTP site with the same credentials and see my root folder, and within that, two more folders. In my FTP task, I want to receive files to my local machine. The problem is that the only remote path available is not at the root level, and the only thing I can see are files from one of the child folders, but not the child folder I want.

Is it possible in the remote path to change folders? The remote path just shows a "/", which I thought would be the root level, but it is somehow linked to a child folder.I've tried various combinations to get to the folder I want, /root folder/child folder, but that gives the error that the folder does not exist.

If I open the FTP Task Editor, click on File Transfer, and click the ellipses for the Remote Path, the Browse For File box opens, with a "/" in the Location section, and a list of files in the child folder that I do not want to be in. If I click the "Up Directory" button, I get the message "Already at top level directory".

how I can get the files from one particular folder on the FTP site?

View 0 Replies View Related

SQL 2012 :: SSIS FTP Task Error Handling - Files Not Found

Nov 2, 2015

When my ForEach Loop runs, when a file does not exist on the server, I am getting a File does not exist error.I would prefer to write a mesaage to my log and then move on to the next step successfully.When I got to Event Handlers and select OnTaskFailed, what do I want to do from here?

View 0 Replies View Related

SQL Server 2012 :: SSIS - Stop DataFlow Task If Result Set Is Empty?

Oct 7, 2014

I am trying to create a SSIS package that will create a csv of a dataset for daily events in the database. However there will be days that there was no activity and thus an empty dataset. The package still runs fine but I want to stop the package if the dataset is empty.


DATA FLOW task: get daily data and put in CSV file

FTP TASK: upload the file to FTP server

MAIL/Copy file task: Move the file and then send a confirmation mail on task completion status.

Pretty simple and it all works great, I do have a few complexities in there. What I would like to add and I am at a loss is at the beginning, if the OLE DB Task resultset is empty then move to Mail Task otherwise process normally. I have tried conditional split, derived columns, the only thing I haven't tried in Script task and am not sure about that yet.

View 4 Replies View Related

SQL 2012 :: SSIS Script Task Hangs When Trying To Download Missing File From A URL?

Oct 12, 2014

I have an SSIS package that inserts website URLs from a SQLServer table into a variable used by an HTTP Connection Manager, then downloads the data files from those URLs using a ForEach Loop and a Script Task. Works beautifully when a data file is found at the URL, but hangs if no data file is found. I've set the Timeout property on the HTTP Connection Manager to 30 seconds, but doesn't work. how to first check if a data file exists, of if the request returns nothing, or how to trap this situation in a try-catch?

Here is the VB code I'm using in the Script Task:

Public Sub Main()
' Connect to website using HTTP connection manager
Dim nativeObject As Object = Dts.Connections("HTTP Connection Manager").AcquireConnection(Nothing)
' Create a new HTTP client connection
Dim connection As New HttpClientConnection(nativeObject)


View 0 Replies View Related

Performance Expectations For Fuzzy Lookup Against 25mill Row Lookup Table

Oct 31, 2007

We did some "at scale" fuzzy lookup tests today and were rather disappointed with the performance. I'm wanting to know your experience so I can set my performance expectations appropriately.

We were doing a fuzzy lookup against a lookup table with 25 million rows. Each row has 11 columns used in the fuzzy lookup, each between 10-100 chars. We set CopyReferenceTable=0 and MatchIndexOptions=GenerateAndPersistNewIndex and WarmCaches=true. It took about 60 minutes to build that index table, during which, dtexec got up to 4.5GB memory usage. (Is there a way to tell what % of the index table got cached in memory? Memory kept rising as each "Finished building X% of fuzzy index" progress event scrolled by all the way up to 100% progress when it peaked at 4.5GB.) The MaxMemoryUsage setting we left blank so it would use as much as possible on this 64-bit box with 16GB of memory (but only about 4GB was available for SSIS).

After it got done building the index table, it started flowing data through the pipeline. We saw the first buffer of ~9,000 rows get passed from the source to the fuzzy lookup transform. Six hours later it had not finished doing the fuzzy lookup on that first buffer!!! Running profiler showed us it was firing off lots of singelton SQL queries doing lookups as expected. So it was making progress, just very, very slowly.

We had set MinSimilarity=0.45 and Exhaustive=False. Those seemed to be reasonable settings for smaller datasets.

Does that performance seem inline with expectations? Any thoughts to improve performance?

View 4 Replies View Related

SQL 2012 :: Passing Record Counts From Execute Task In SSIS To Package Variables

Mar 4, 2014

I've got a package in SSIS 2012 that has an Execute SQL task in the control flow level.

The SQL in question does an Upsert via the SQL merge statement. What I want to do, is return the count of records inserted and records updated (No deletes going on here to worry about). I'm using the output option to output the changed recs to a table variable.

I've tried returning the values as:

Select Count(*) as UpdateCount from @mergeOutput where Action = 'Update'
Select Count(*) as InsertCount from @mergeOutput where Action = 'Insert'

I've tried setting the resultset to both Single rowset and Full rowset, but i'm not seeing anything returned to the package variables I've set for them (intInsertcount and intUpdatecount).

View 2 Replies View Related

Integration Services :: How To Implement Data Driven Query Task In SSIS 2012

Jun 15, 2015

I have a requirement of migrating DTS package which is done in Sql Server 2000 to SSIS 2012.

I started with one package having data driven query task and done with source for which i chose OLE DB Source and given the required select query in ssis 2012

I'm stuck now and i'm unable to choose the relevant tools in ssis 2012 for binding, transformation,queries and lookup tabs used in dts 2000 for this DDQT.

View 4 Replies View Related

SQL Server 2012 :: Bold Last Row From Table Coming From View?

May 31, 2015

I have a scenario to bold the total row coming from view.

SalesDate BrandName LeadTypeLeadsSalesCR%
01-Jun-2015XYZ ABC 81 1012.35 %
01-Jun-2015DEF ZXXYY 412 61.46 %
[code]<b>01-Jun-2015 XYZDEF Total 49316 3.25 %</b>

I have a SQL and I want to bold the Total row. I have been unable to do so. I have written a Stored proc to get the HTML table emailed.

View 1 Replies View Related

Integration Services :: How To Process SSAS 2012 Cube With SSIS 2014 And Script Task

Nov 11, 2015

Now I have a different constellation: Integration Services run on one server, in version 2014, the Analysis Services instance to process the cube database on runs on another server, version 2012.I tried several different combinations of SSIS version and Analysis Management Objects version, and got several errors while running the process package (e.g. object reference not set to an instance of an object, cannot find AnalyisServices.dll..)

Is this combination 2014/2012 possible at all?I assume the BIDS version has to be for SQL Server 2014, as I want to run SSIS packages on a 2014 server, is that correct? Does it matter at all, can I also deploy 2012 packages?Which version of Analysis Management Objects do I have to use? I assumed I have to use version 11.0 here, because I want to process a 2012 cube?If it is possible to use the "old" 11.0 version of AMO, do I have to do anything so that it can be found by the SSIS package running on the server (it was built on my local computer, there I have all SQL Server versions from 2005 to 2014 installed in parallel), or do I just have to copy it to the appropriate SQL Server folder?

View 3 Replies View Related

SQL Server 2012 :: Filtering Common Table Expression Within View

Sep 1, 2015

I have a multi-tenant database where each row and each table has a 'TenantId' column. I have created a view which has joins on a CTE. The issue I'm having is that entity framework will do a SELECT * FROM MyView WHERE TenantId = 50 to limit the result set to the correct tenant. However it does not limit the CTE to the same TenantId so that result set is massive and makes my view extremely slow. In the included example you can see with the commented line what I need to filter on in the CTE but I am not sure how to get the sql plan executor to understand this or weather it's even possible.I have included a simplified view definition to demonstrate the issue...



View 4 Replies View Related

SQL Server 2012 :: Creating A View Or Procedure From Dynamic Pivot Table

May 29, 2015

I have written a script to pivot a table into multiple columns.

The script works when run on its own but gives an error when i try to create a view or aprocedure from the same script. The temporary table #.... does not work so i have converted it to a cte.

Here is a copy of the script below

-- Dynamic PIVOT
IF OBJECT_ID('#External_Referrals') IS NULL
DROP TABLE #External_Referrals;

[Code] ....

View 7 Replies View Related

SQL 2012 :: Allow User To Select Data Through A View But Not Restrict Access To Underlying Table?

Sep 29, 2015

I have two databases DB1 and DB2 DB1 has a source table named 'Source' I have created a login 'Test_user' in DB2 with Public access. I have also created a view named 'Test_view' in DB2 which references data from DB1.dbo.Source

How can I do the following: AS A Test_user

SELECT * FROM DB2.dbo.Test_view --Should work

SELECT * FROM DB1.dbo.Source --Should Not work

View 2 Replies View Related

Lookup Task

Feb 22, 2008


I've looked at so many examples on the web but I can't get my dataflow with lookup to work correctly. It just keeps inserting the records instead of updating them because they already exist. I know it's something simple but I've been trying to figure it out for the last 4 hours and am over my head. In the lookup I have 5 columns as available input and of course the matching lookup columns and selected 4 with lookup operation replace and tried add as new. It just keeps adding them to my destination table when they already exists. I can't figure out what i am doing wrong?


View 8 Replies View Related

Using Lookup Task

Jun 6, 2006

Is there a way to set the Lookup task to be not case sensitive? For example, the lookup table has id 1, value 'ABCD'. The value that I'm using to lookup is 'abcd'. I cannot make it to return the id 1 unless I convert my lookup value to be all upper case.

And I don't want to use Fuzzy Lookup.



View 4 Replies View Related

SQL 2012 :: Catalog Doesn't Have Option To Give Read Access To SSIS Catalog To View Package Run Reports

Oct 23, 2014

"SSIS 2012 Catalog doesn't have option to give read access to SSIS Catalog to view package run reports" ... Any luck allowing power developers / operators access to READ the SQL 2012 SSIS Execution Reports without granting them SSIS_Admin or Sysadmin?

According to this link posted back in 2011 (w/ Microsoft's feedback in Nov 2011: "We’re closing this issue as “Won’t Fix.” At this point the bug does not meet our bar for resolving prior to SQL Server 2012 RTM. As we approach the SQL Server 2012 release the bar for making code changes gets progressively higher." URL....Regarding Permissions to SSIS Catalog, here are the findings. We can give access in three ways:

1. READ Access – We can provide a user db_datareader access. With this the user can see the objects within the SSIS catalog database, but cannot see the reports.

2. SSIS_ADMIN – Add the user to this database role in SSISDB. With this the user can view the reports. But it also provides them privileges to modify catalog information which is not expected. We can add it using below script EXEC sp_addrolemember 'ssis_admin' , 'REDMONDPAIntelAnalyst'

3. SYSADMIN - Add the user to this server role. This will make the user an admin on the SQL server. This is not intended. Is there any method available which will have provision to give read only access to see SSIS Catalog package execution reports and not having modify Catalog access.

View 1 Replies View Related

SQL Server 2012 :: Inserting Row From A Table Into Another Table From SSIS

Sep 18, 2014

I have a 2010 SSIS package where I am reading csv files with different fields and formatted data, I have created separate packages for each file and I am formatting the data to fit the final destination tables data elements, I've been instructed to create 7 separate packages to read each file and the format the data from the 7 csv files, and insert into their table tbl1, tbl2, tbl3...etc then, I'm taking a execute sql task and wanting to insert the tbl1, tbl2, tbl3...etc into destination table that will be the final table for all reports and other uses.

1- should I create a ID?
2- these files will be read once a month
3- I want to append the data, not drop and recreate each run,
4- It's 2012 SQL and 2010 SSIS

Each csv file is in a different format, some have 15 columns, other have 8 I have to parse the data, in SP to align with the fields in the destination table.

5- Can I force RowID to be the next auto gen number from tbl1,for the start of insert for tbl2, then last row of tbl2 for insert of tbl3???

View 0 Replies View Related

Lookup Task With NOLOCK

Mar 2, 2006

I have an SSIS package that unpivots data - each row from the source becomes 20-24 rows in the destination db. In the dataflow it looks up some type-data IDs before inserting into the destination table. The whole process flies through the data at an incredible rate, however I find that the final commit on data insertion takes a very long time, and two of the lookup tasks remain yellow throughout. It appears that there may be a contention issue because the final table has FKs to those lookup tables.

In T-SQL it is possible to do SELECT..... WITH (NOLOCK), but when I modified the SQL statement of the lookup task by adding this clause, it caused an error "Incorrect syntax near the keywork 'with'."

Is there any way around this problem? THNX!


View 2 Replies View Related

Execute SSIS Task By Table Column Trigger

Feb 22, 2008


I have a situation below

Table : tblStatus

Column1 : Event_Desc

Column2 : Flag

Once the flag is set to "T", I want to trigger a SSIS package named "PerformAction.dtsx"

How to write a table trigger for this.

With Regards,

View 7 Replies View Related

SSIS OLEDB Source Data Access Mode (Table Name Or View Name Variable)

Apr 3, 2007

Thanks for any one can give me a help.

I am try to transfer some tables data from one database server into another database server. I create a package in SSIS, and I use a variable to pass each table name. In Data flow, I use a OLEDB Source, but I cannot set the Data access mode to Table name or view name variable. Ever time, I will get this following error info "===================================

Error at Data Flow Task [OLE DB Source [31]]: A destination table name has not been provided.

(Microsoft Visual Studio)


Exception from HRESULT: 0xC0202042 (Microsoft.SqlServer.DTSPipelineWrap)

Program Location:

at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ReinitializeMetaData()
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowComponentUI.ReinitializeMetadata()
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowAdapterUI.connectionPage_SaveConnectionAttributes(Object sender, ConnectionAttributesEventArgs args)".

Some one can tell me what is the reason, or give me some examples.

Thanks in advance.

View 7 Replies View Related

Lookup Task Not Performing Correctly!

Apr 8, 2008

Hi, I'm using the lookup task to perform updates on some records and it seems to be working correctly but isn't making all updates. I have a case where there are 543 rows in my source connection, there are 436 of them that do not match the date column of the 543 in my destination table. It only updated 224 of them when it should have updated all 436? Does anyone know why it would only update this many instead of all? I also had another that only updated 18 out of 19 instead of all 19.


View 4 Replies View Related

Lookup Task Based On A Variable

Jul 17, 2006

This doesn't seem possible but I'll ask anyway...

Can I build a lookup task where the lookup query is based on a variable, rather than hardcoding the SQL staement?

View 3 Replies View Related

Problems With Lookup Task And Dates

Aug 2, 2005

Im trying to make a Lookup transformation with a relation on two string columns and a between dates condition set by parameter:

View 7 Replies View Related

Handling Errors When Using A Lookup Task

Jul 13, 2006


I am trying to use this painful new SSIS process. I basically need to use a lookup task to check to see whether a record exists or not. If not, then I need to insert the record. However, because this is treated as an error situation (which is stupid in itself), I get a problem when the number of records not found reach the MaximumErrorCount, and the rest of the package fails. Is there any other method of doing this type of thing, without simply increasing the MaximumErrorCounty to some ludicrous value. I could do this type of thing very very very easily when using DTS packages using the Data Driven Task, it seems so stupid that I can't perform the same kind of task using SSIS.

Any help would be appreciated



View 6 Replies View Related

Lookup Task Datatype Issue

Jul 13, 2006


I'm attempting to use the lookup task and i'm running into an issue about incompatible datatypes.

The table that i'm referencing in the lookup has a field named DateCreated, and it's DataType is DateTime.

But when I hook it up to the Lookup Task, the lookup task thinks it's a DB_Timestamp, so i get an incompatible data type error.

What can i do?

View 4 Replies View Related

Case Sensitive In Lookup Task

Jan 25, 2008

My SQL Server database is case in-sensitive.
I have a lookup task to join on varhar column. If the data is only case different such as 'DataModel' and 'Datamodle', lookup task consdiers them as different. Is there a way to set up lookup task as case insentive? I know I can work around it by converted them to upper case and compare them.

View 5 Replies View Related

Use Partial Or No_Cache Mode In Lookup Task

Jun 21, 2006


Can anyone explain to me (or show me where to get info) in what scenario should I use Full Cache, Partial, or No_Cache mode in the Lookup task? I got below warning message during execute my package. I understand that my lookup data have duplicate reference keys. I want to change the mode to partial or no cache to see how it works, but don't know what to change for the parameter map.

Warning: 0x802090E4 at Data Flow Task, Lookup [37]: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.

Any info is appreciated.


View 9 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved