22

I have a handful of queries I use. The general concepts are that the table with the information is catalog.operation_messages and you are interested in events with a 120 (error) type. Depending on how robust of a query you want to build out, the following two derived tables might also be of interest. --- http://technet.microsoft.com/en-us/library/...


10

(From the screenshot) I see an unusual configuration where you have SQL Server's Min and Max memory set the same. Leave min memory as default. See my answer to SQL Server Min Server Memory. To determine ideal memory, refer - How to determine ideal memory for instance? and SQL Server Maximum and Minimum memory configuration We have SSIS running packages on ...


8

You can use: SELECT OPR.object_name , MSG.message_time , MSG.message FROM catalog.operation_messages AS MSG INNER JOIN catalog.operations AS OPR ON OPR.operation_id = MSG.operation_id WHERE MSG.message_type = 120 This will give you only the error messages from SSIS package ...


6

Casting a string of 000000000000017943 to decimal(18,2) is going to result in a value of 17943 The source doesn't have a decimal place, so why would the cast know you need a decimal there? Also, given your source data, your substring formula is off. It's resulting in a source string of R00000000000001794 That said, I take one of 2 approaches. Either I use ...


5

How much memory does SSIS need? (The real answer is: It depends.) However, Jonathan Kehayias has a formula that is pretty good, or so I think, since it works great for me. https://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/ The simple formula for reserving space for needs other than SQL Server in Windows is: ...


4

The SSIS intallation is working as a charm, the only problem was that we wanted to connect to SSIS 2014 from SSMS 2012. At the end we agreed to upgrade the developers machines to sql server 2014 developer edition, and migrated the packages to sql 2014, and now everything is working fine. I will leave the question here as a reference, for the community and ...


4

Chances are your drop/create statements are blocking each other on occasion. As already mentioned, you can truncate the tables instead, however a fairly simple test would be to run the drop/create tasks sequentially, and the data loads afterwards in parallel. If you're desperate to know the detailed reasons behind the variable times, then do as suggested ...


4

Here is one query I created to find the job error message from the SSISDB: DECLARE @DATE DATE = GETDATE() - 7 -- This is to restrict the data for last 7 days, used in ON condition SELECT O.Operation_Id -- Not much of use ,E.Folder_Name AS Project_Name ,E.Project_name AS SSIS_Project_Name ,EM.Package_Name ,CONVERT(DATETIME, O.start_time) AS Start_Time ...


4

VS 2010/VS 2012 => SQL Server 2012. VS 2013 => SQL Server 2014. VS 2015 => SQL Server 2016 (for now, soon they'll have the ability to target SQL Server 2012+) So, you either need to redo your imports using SSDT for VS 2013/SQL Server 2014 or wait until they deliver the next release candidate for SQL Server which will have the ability to target specific ...


4

This kind of situation is exactly what BIML (Business Intelligence Markup Language) is made for. From Introduction to Business Intelligence Markup Language (BIML) for SSIS, this solves exactly the problem you are facing: With Microsoft SQL Server Integration Services (SSIS), you can build powerful and flexible packages to manage your enterprise-wide ...


3

SSIS is, indeed, clunky when it comes to importing data for all the processing prowess that it seems to tout. One of the commenters, Kin I believe, had it right with creating a temporary table. Here's the code we use to import data directly from Excel into a temporary table to be parsed for compliance. As you can see in the example below, using T-SQL, ...


3

What you are asking about is broad architecture rather than specific issues. I dont believe there will be a single document that gives you a step by step guide. If your vendor is making this reccomendation you could ask them to explain thier design with at least some basic documentation and diagrams that help you understand the layout. If you are ...


3

Load the data into a Staging Table and then use a MERGE statement within SQL to perform the actual merging of the data. To answer your 2nd question: If you use the Merge Transformation within the SSIS package, the operation will utilize memory on the server running the SSIS package. If SSIS saturates the memory and either needs more or is asked by the OS ...


3

As you are seeing, one of the benefits of Kimball Dimensional Modeling is that the data warehouse design is essentially your SSAS design. While there are always exceptions, you can typically select a table in the DSV and immediately move to hierarchy design, cube relationships etc. I'd recommend moving to the DDS with the caveat that you phase out the NDS. ...


3

Don't grant ssis_admin if the dev shouldn't be sysadmins: Members of the db_ssisadmin role and the dc_admin role may be able to elevate their privileges to sysadmin. Source: https://msdn.microsoft.com/en-us/library/ms141053.aspx Personally I would never let the devs create jobs with their accounts. As soon as they leave the company and someone disables ...


3

SSIS doesn't support nullable types for it's built in variables. So - as you've experienced - a null integer in SQL will become zero when assigned as an SSIS variable. Those are the breaks, but there ARE workarounds. In order to keep our source table NULLs, we will need to skip over the whole "assigning them to SSIS variables" completely. Have a look at ...


2

apparently this is because I need to download and install Microsoft SQL Server Data Tools - Business Intelligence for Visual Studio 2013 http://www.microsoft.com/en-us/download/confirmation.aspx?id=42313 I will get this done and if it all works I will accept this answer.


2

Here is a link on SQL Server 2014 for you to Grant Permissions to Integration Services Service https://msdn.microsoft.com/en-us/library/Hh213130(v=sql.120).aspx The first step is: Run Dcomcnfg.exe. Dcomcnfg.exe provides a user interface for modifying certain settings in the registry. The following link for DCOM settings were last reviewed in 2006, but ...


2

You can use below steps as mentioned on MSDN : Component Services -> Computers -> My Computer -> DCOM Config -> MSDTSServer Right click on MSDTSServer Click properties Click on the Security tab Select Customize and add the users/groups to the Launch/Activation and Access tabs accordingly Add the user to the local Distributed COM Users group Restart the ...


2

(longer than comment... so posting as answer) I would suggest you to not create table every time. You can just truncate the table. That will be more efficient - unless I am missing some dependency that you have not described in your question. Set DelayValidation property to True for Data flow task. Set ValidateExternalMetadata to False for individual data ...


2

In order to do it quickly, you will need to delete the SSISDB and recreate it. But I don't recommend doing that. The cleanup job is interfering with your execution of SSIS Packages (or vice-versa). My approach when I'm trimming back the retention period is to walk backwards a day at a time. The script below will do that, just change the @...


2

If you trace the query the report is generating you'll see that the executions are stored in the [catalog].[executions] of the database behind the Integration Services Catalog. You can just modify your [end_time] there and you should be able to 'fix' the report. I haven't managed to reproduce your exact scenario (i.e. hard resetting a VM to get a package ...


2

That occurred because VS 2015 was built to support only SQL server 2016 and you probably deployed the package under SQL Server 2014 or lower(unsupported versions). With the latest version of Data tools in VS 2015, you can chose the targeted SQL server version in the package to allow SSDT to chose the correct .NET libraries used in the script component. ...


2

You'll have to test to find which works best for your scenario. By "join task" I assume you mean a Merge Join transformation which requires the input to be sorted. Sorting can be expensive especially if using a Sort transformation. Even sorting at source will take time. You could try a Lookup transformation which works best using full cache, but if the ...


2

For posterity I got this working via the following: Altering the stored procedure called by the users (Admin.RunImport) to 'Execute as' the account used by the SQL Service The SQL Server service account (an AD Managed Service account) is modified to have permissions to execute the Admin sproc allowing the use of execute as above The SQL Server service ...


2

What the vendor is suggesting is a common practice for large systems/applications that require database engine, ETL, and reporting. Each of those components will suck up resources based on their need, so trying to have those run on one system is not a good practice (but it does depend on workload). Each component can run on a dedicatd server, but do require ...


2

You don't actually have to alter your source data, but you can use a customised SQL command in your OLE DB Datasource. Basically change the Data access mode from Table or view to SQL command or even SQL command from variable and create your statement to include your PIN code, something like this: SELECT *, 1 + ABS(CHECKSUM(NEWID())) % 9999 PIN FROM dbo....


2

A common approach is to use a different SSIS configuration file for each environment. Give it the same name, but different contents (server name, local paths, any other variables) in each environment.


2

2) Time/Date datatypes are often problematic choices for storing durations. Instead use an integer or decimal for whatever resolution is appropriate (seconds or milliseconds are popular, but minutes may work for you in this case). 1) In this instance, the simplest option may be: (DT_I4)TOKEN(Column,":",1) * 60 + (DT_I4)TOKEN(Column,":",2). Other methods: ...


2

"EXECUTE AS" works by default only within one database. You can overcome this by enabling cross-database ownership chaining. But this implies security issues! Please have a look at books online: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/enabling-cross-database-access-in-sql-server An alternative solution is to move the calling ...


Only top voted, non community-wiki answers of a minimum length are eligible