I have a for-each loop that goes through a file directory. These files are transaction information from different clients. When there is an error with a file (i.e. can't be processed), the Event Handler is to send an email to the client notifying them. There is a SQL task that uses an existing variable (Client ID from the file) that queries a table to get the Client's email address. I created a variable for the Client Email. I want to make this update each time the for-each loop is run, since each file will have a different value for the client email variable. How can I ensure this? Inside the for-each loop, there are three data flow tasks that could conceivably "error" and that's when I will be sending these notification emails. Will I need to scope a variable for each of the three executables within the loop for the Client Email? Or can I scope a single variable in the for-each loop? I understand this may be a bit confusing so please let me know if you have any questions.

I'm trying to determine the most effect way to do an upsert in SSIS with CSV source 100+ columns 1 x CSV Source (loop through multiple CSVs same structure 100 + columns) 1 x Staging Table Destination I have come up with 3 approaches: 1. Using the SSIS lookup component, if record doesn't exist insert otherwise call some SQL script to do the update (row by row) so could be slow. 2. Calling a stored procedure with a merge statement, so CSV to stored procedure (will be row by row) so slow. Also there will 100+ params 3. Using another staging table that gets cleaned out before insert..Then calling a stored procedure to merge the 2 staging tables I'm leaning towards approach #3 as it's the simplest and cleanest. Thoughts?

I have installed 2 instances of sql server plus SSIS on the following server. Note the amount of RAM is nearly 384 GB And this is the max and min memory settings that I have applied to my 2 instances. I assume that both instances will use equal amount of resources, if that would really be the case, 184,320 MB which is 180 GB be a good starting number to set up my memory? How much memory would I allocate to SSIS?

I have a simple data flow. I'm trying to join two sets of data from two different databases. The TXT database is collation SQL_Latin1_General_CP1_CI_AS. The other dataset (Cost) is coming from Dynamics AX which is Latin1_General_CI_AS. AX columns are Unicode. So I have put data conversion on TXT columns to Unicode However no matter what I do I cannot get rid of the MERGE JOIN ERROR and it's driving me insane. > TITLE: Microsoft Visual Studio > > Both inputs of the transformation must contain at least one sorted column, and those columns must have matching metadata. Both datasets are set to sort by ITEMID (and I have set sort keys in both). Any ideas???

I am installing SQL Server 2014, including SSIS, on a Windows Server 2012 R2 machine. From this link: https://msdn.microsoft.com/en-us/library/ms137861(v=sql.120).aspx ssis uses port 135 which should be opened on the firewall(s). I Create a domain account to run SSIS Install SQL - features can be seen on the picture below All services are running fine see a special ad account to run the ssis services Grant the logon as a service and lock pages in memory On control panel -> administrative tools-> Computer management Add the users and the ssis account to “distributed com users” > On control panel -> Administrative Tools > Component Services > > Then go to computer-> my computer -> DCOM Config -> and look for Microsoft > SQL Server Integration Services 12.0 > > As you can see on the picture below You right click on Microsoft SQL Server Integration Services 12.0 I have left the Authentication Level as “call”. This is more detailed explained if you follow this link. These 3 above have to be set up, I have done, and the results are below: Doing all this above, When I run SSMS “As Administrator” on the Server itself, then it works fine. **AFTER I REBOOTED THE SERVER I CAN CONNECT SO THE MESSAGE BELOW IS FIXED** > But on the Server itself if I run SSMS as myself, I get the following > error message: > > Connecting to the Integration Services service on the computer > "sqldw01" failed with the following error: "Access is denied." > > By default, only administrators have access to the Integration > Services service. On Windows Vista and later, the process must be > running with administrative privileges in order to connect to the > Integration Services service. See the help topic for information on > how to configure access to the service. I am administrator on the box. and when I try to connect from my local machine, then I get the following error: **I CANNOT CONNECT USING SQL SERVER 2012 OR LOWER, BUT I CAN CONNECT FINE USING SQL SERVER 2014** Connecting to the Integration Services service on the computer "sqldw01" failed with the following error: > "Class not registered". This error can occur when you try to connect > to a SQL Server 2005 Integration Services service from the current > version of the SQL Server tools. Instead, add folders to the service > configuration file to let the local Integration Services service > manage packages on the SQL Server 2005 instance. What am I missing? The problem that remains solving is How to connect to SSIS 2014 from a client using SQL Server 2012?

I have a task where I am trying to assign a random PIN to an account. I have SQL code that will generate a random integer between 1000 and 9999. I was wondering if I could put that in a Derived Column task in SSIS and not do it in the source data? Also, I can probably write a Script Task to accomplish this. At this point it's more an exercise to see if it's possible since I have alternatives.

I have a requirement for a third party vendor application which requires SSIS and SSRS. The vendor had suggested this 3 server model, and my management is going ahead with the same. Three VMs are being provisioned, one each for SQL Server, SSIS, and SSRS. I want to know in what ways I can configure SSIS and SSRS to work with the SQL Server. Also, what would be the best way to create SSIS packages? Because, if the packages have to be created on the dedicated instance, I still need to create a local instance of the database engine on the SSIS server. I need some guidance as to how I can configure SSIS and SSRS in conjunction with the SQL Server instance in the third VM. I am fairly new to these two areas. I am going through these two links: 1. Configuring SSIS 2. Configuring SSRS Please let me know if another URL would provide detailed step by step information. Also, would the three servers have to be in the same cluster? The system is spread across continents hence we expect a lot of users in the long term. The way it was planned was to have separate instances of SSIS and SSRS, and they point to a database in an Always On setup which stores the reporting information.

I have created an SSIS package to run DBCC CHECKDB on all the databases on the server and send error messages by email. But for several of the databases, I get an exception running my script: > Timeout expired. > The timeout period elapsed prior to completion of the operation or the server is not responding. The only timeout I can find in the project/package is set to 0, which I presume means no timeout. DBCC CHECKDB runs from SSMS just fine.

We are trying to setup a centralized SSIS server (SQL Server 2014). I want to know the following: 1. Any procedure which is utilizing Linked Server and if that procedure gets called from SSIS SQL Task, is it better to put that logic inside SSIS itself without utilizing Linked Server? 2. If we ask end user to put logic inside the SSIS package (i.e. JOIN...) and not use Linked Server, is it better performance wise? Example: I have Table A from Server_1 and Table B from Server_2. If I am creating a stored procedure on Server_1, normally I will be joining Table A to Table B using Linked Server to Server_2. If I put this same logic inside the SSIS package and use [JOIN] task instead, would that be better?

I am trying to enable SSIS package logging to event logs. When an attempt is made to write to the event log the following error appears in the system event logs: > The application-specific permission settings do not grant Local > Activation permission for the COM Server application with CLSID > {806835AE-FD04-4870-A1E8-D65535358293} and APPID > {EE4171E6-C37E-4D04-AF4C-8617BC7D4914} to the user DOMAIN\machine$ > SID (S-1-5-21-16020293-282541685-632688529-241633) from address > LocalHost (Using LRPC). This security permission can be modified using > the Component Services administrative tool. I've dealt with this error before but the fact the error relates to a machine account has thrown me. Does this mean I have to specifically grant permissions to the machine account in component services? The app pool account running under the NETWORK SERVICE account.

I have a stand alone instance of SSIS and SSRS running on 2 seperate VM's in production. And I want to know if the Servers can be protected with some kind of Snapshot method like Veeam to restore them back in case they blow up. Please let me know if something like this is possible at all.

On SQL Server 2014 Microsoft (12.0.2430) SQL Server Enterprise: Core-based Licensing (64-bit) after changing the retention period from 365 to 90 since last 2 days job clean up about 2 GB of data only. I have rebuild & update all stats on SSISDB database still its slow. Current Size of database is 344GB approximately. what is the best way to clean old records Quickly, To do that do i need down time ? I have followed below article so far :-

I'm trying to upload data from a CSV file into a SQL server table through SSIS package (Flat File source and OLEDB destination). This CSV has comma as column delimiter and {CR}{LF} as row delimiter. This csv file has one column which holds XML data in it. In this XML, they again used {CR}{LF} to make it as a multi-line XML and this XML is placed within a double quotes (""). Ideally, this whole XML needs to be placed in one column however {CR}{LF} makes it as a new row in my table. Is there any method to have escape sequence and place the whole XML data into a single column? I'm using Visual studio 2013 and connected to SQL server 2014 database. Thanks.

I have 43 tables on 2 different servers which I have to sync regularly. Because of our network policy and IG reasons, we can't have link server object between these two servers. Standard procedure so far is to create a SSIS package that will do the transfer however, I have come across situation where I need 43 of those. As much as I would love to get on with the task, there has to be a smarter way of doing this. I have seen some articles about generating packages dynamically using C# but C# ain't my strongest suit. Is there a way from within SSIS to perform this task dynamically? I was thinking like maintaining mapping tables, one with source and destination tables and another with source and destination column mappings and looping through it but obviously metadata needs to be refreshed to achieve the successful execution. Any thoughts or ideas will be highly appreciated.

I am completely new to SSIS.I am using SQL Server 2014. I am not able to understand what is wrong with code as it results good in SSMS. I use OLEDB connection as source assistant and input the below code in SQL Command option. I am using the below query as part of exporting source T-SQL output to Flat file. I am getting below issue while creating a SSIS package. can anyone help me on this. Below is the code I use (I modified for finding complete backup Info purpose) : if object_id ('tempdb..#temp1') is not null drop table tempdb..#temp1 create table #temp1 ([server] nvarchar(200), [name] nvarchar(200), [last full backup] datetime , [last differential backup] datetime, [last log backup] datetime ) insert into #temp1 SELECT  (select @@SERVERNAME) as Server,name , d AS 'Last Full Backup' ,             i AS 'Last Differential Backup' ,             l AS 'Last log Backup'     FROM    ( SELECT    db.name ,                         db.state_desc ,                         db.recovery_model_desc ,                         type ,                         backup_finish_date FROM      master.sys.databases db                         LEFT OUTER JOIN msdb.dbo.backupset a ON a.database_name = db.name             ) AS Sourcetable          PIVOT              ( MAX(backup_finish_date) FOR type IN ( D, I, L ) ) AS MostRecentBackup if object_id ('tempdb..#temp2') is not null drop table tempdb..#temp2 create table #temp2 ([server] nvarchar(200), [name] nvarchar(200), Backupsize nvarchar(max)) insert into #temp2 ------------------------------------------------------------------------------------------- --Most Recent Full Database Backup for Each Database - Detailed ------------------------------------------------------------------------------------------- SELECT A.[Server] As SQLServerName, A.database_name As DatabaseName, --A.last_db_backup_date As DatabaseLastBackupDate, -- B.backup_start_date As DatabaseBackupStartDate, CAST(B.backup_size/1024.0/1024/1024 as decimal(10,2)) --B.backupset_name FROM ( SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, msdb.dbo.backupset.database_name, MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE msdb..backupset.type = 'D' and server_name = (select @@servername) AND name = 'CommVault Galaxy Backup' GROUP BY msdb.dbo.backupset.database_name ) AS A LEFT JOIN ( SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date, msdb.dbo.backupset.expiration_date, msdb.dbo.backupset.backup_size, msdb.dbo.backupset.name AS backupset_name, msdb.dbo.backupset.description FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE msdb..backupset.type = 'D' ) AS B ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date] ORDER BY A.database_name if object_id ('tempdb..#tempdiff') is not null drop table tempdb..#tempdiff create table #tempdiff ([server] nvarchar(200), [name] nvarchar(200), DiffBkpGB nvarchar(MAX), backupset_name nvarchar(max)) insert into #tempdiff SELECT A.[Server] As SQLServerName, A.database_name As DatabaseName, --A.last_db_backup_date As DatabaseLastBackupDate, --B.backup_start_date As DatabaseBackupStartDate, --B.backup_size/1024/1024 as DifferentialBackupSizeGB, CAST(B.backup_size/1024.0/1024/1024 as decimal(10,3)) as DiffBkpGB , B.backupset_name FROM ( SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, msdb.dbo.backupset.database_name, MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE msdb..backupset.type = 'I' and server_name = (select @@servername) AND name = 'CommVault Galaxy Backup' GROUP BY msdb.dbo.backupset.database_name ) AS A LEFT JOIN ( SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date, msdb.dbo.backupset.expiration_date, msdb.dbo.backupset.backup_size, msdb.dbo.backupset.name AS backupset_name, msdb.dbo.backupset.description FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE msdb..backupset.type = 'I' ) AS B ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date] ORDER BY A.database_name if object_id ('tempdb..#templog') is not null drop table tempdb..#templog create table #templog ([server] nvarchar(200), [name] nvarchar(200), LogBkpGB nvarchar(MAX) ) insert into #templog ------------------------------------------------------------------------------------------- --Most Recent Log Database Backup for Each Database - Detailed ------------------------------------------------------------------------------------------- SELECT A.[Server] As SQLServerName, A.database_name As DatabaseName, --A.last_db_backup_date As DatabaseLastBackupDate, --B.backup_start_date As DatabaseBackupStartDate, --B.backup_size/1024/1024 as LogBackupSizeGB, CAST(B.backup_size/1024.0/1024/1024 as decimal(10,3)) AS LogBkpGB --B.backupset_name FROM ( SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, msdb.dbo.backupset.database_name, MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE msdb..backupset.type = 'L' and server_name = (select @@servername) AND name = 'CommVault Galaxy Backup' GROUP BY msdb.dbo.backupset.database_name ) AS A LEFT JOIN ( SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date, msdb.dbo.backupset.expiration_date, msdb.dbo.backupset.backup_size, msdb.dbo.backupset.name AS backupset_name, msdb.dbo.backupset.description FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE msdb..backupset.type = 'L' ) AS B ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date] ORDER BY A.database_name select (select getdate()) as CurrentDateTime,#temp1.server, #temp1.name as Database_Name, #temp1.[last full backup] as Last_Full_Backup, #temp2.Backupsize as FullBkpGB, #temp1.[last differential backup] as Last_Differential_Backup,#tempdiff.DiffBkpGB, #temp1.[last log backup] as Last_Log_Backup, #templog.LogBkpGB, #tempdiff.backupset_name from #temp1 left join #temp2 on #temp1.server = #temp2.server and #temp1.name = #temp2.name left join #tempdiff on #temp1.server = #tempdiff.server and #temp1.name = #tempdiff.name left join #templog on #temp1.server = #templog.server and #temp1.name = #templog.name When I am working on creating a new SSIS package I get the following issue. Please help.

This is the first time I have come across this type of error. I am running a process task and it runs fine until I add it to the scheduler (sql agent). >Date 3/2/2017 4:13:05 PM >Log Windows NT (System) > >Source Microsoft-Windows-DistributedCOM Category (0) Event 10016 User NT SERVICE\SQLSERVERAGENT Computer CFBF-SQL2 Message >The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID {806835AE-FD04-4870-A1E8-D65535358293} and APPID {EE4171E6-C37E-4D04-AF4C-8617BC7D4914} to the user NT SERVICE\SQLSERVERAGENT SID (S-1-5-80-344959196-2060754871-2302487193-2804545603-1466107430) from address LocalHost (Using LRPC) running in the application container Unavailable SID (Unavailable). This security permission can be modified using the Component Services administrative tool.

We've been moving application packages from the older methods of storage and execution into the SSIS Catalog. We're required to use an enterprise scheduling system instead of the SQL Server Agent to execute batch jobs and SSIS packages. Basically, the scheduler opens a command line on the server and executes Windows commands. In our case, we'd be using SQLCMD to execute a stored procedure like the one below. Here's what I need to be able to do: CREATE PROCEDURE usp_Execute_Packages @Package_name NVARCHAR(260) WITH EXECUTE AS 'Domain\User' AS DECLARE @execution_id bigint EXEC [SSISDB].[catalog].[create_execution] @package_name=@Package_name, @execution_id=@execution_id OUTPUT, @folder_name=N'A Folder Name', @project_name=N'Test Deployment' EXEC [SSISDB].[catalog].[start_execution] @execution_id GO But, this doesn't seem to work; I get an error telling me that I can't use a SQL Server account to start an execution. I assume it's having an issue with the EXECUTE AS statement. Any ideas how to start the package execution as a different user without using SQL Server Agent and proxy accounts?

My packages have been running on our production environment for a couple of weeks until they started failing with very little information apart from packages having a pending execution status. All packages fail regardless of the user executing them e.g. myself or the SQL Agent service account. There is a cryptic error message in the windows log about activation permissions for the SQL Integration Services. Setting permissions for the accounts calling the package makes no difference, I managed to get them working by repairing the SQL Server installation I couldn't see what repairs it had made. Now following a reboot of the server all packages refuse to run. I have managed to get them to work by repairing the SQL Server installation again. This is obviously not a scalable solution. I suspect that some group policy setting is overriding a local security setting and this is why it happens after some time or after a reboot. Has anybody come across this issue before or have any ideas where to look.

I was doing an audit of certificates in our environments to find those that have either expired or are expiring in 30 days. I used the following query: SELECT * FROM sys.certificates WHERE expiry_date < DATEADD(DAY,30,GETDATE()); I noticed in SSISDB that there are a large number of expired certificates: What are these certificates used for, is it an issue that they have expired? I have not heard any complaints from our development team that SSIS isn't working as expected.

At my work we have three different environments: dev, test, and prod. We also quite a few different SSIS packages. Currently, when we need to deploy to a particular environment, we update the connection for the package in Visual Studios and deploy it to the correct environment. I'd like to make this easier. I'd like to pass in the environment name, the server name, and the database name and have a parameterized connection string in order to run the package correctly. I'm currently not allowed to set up an Integration Services Catalog for these environments and variables. The only options I have is to somehow pass in the parameters when I set up the SQL Server Agent Job that will execute these packages on a schedule. I previously attempted using a parameter in the SSIS package, but when I attempted to pass in a value I got an error stating that parameters are read only. So, how can I set up these packages to send the pertinent data they need for the server they're running on? Clarification: We are currently deploying our packages by uploading them to MSDB.