Can someone help me create SSIS expressions for variables to generate QuarterStart(DateTime) and QuarterEnd(DateTime) as a variable with year(int32) and quarter(int32) passed on as user variables. The equivalent T-SQL script would be

@FISCAL_YEAR INT = 2023,
    @QUATER INT = 1
SELECT 
    DATEFROMPARTS(@fiscal_year,(@Quater * 3)-2,1) AS QuaterStart,
    EOMONTH(DATEFROMPARTS(@fiscal_year,@Quater * 3,1)) AS QuaterEnd

I have a new SSIS project developed in VS and intended to run on a SQL Server 2016 instance. The project has the ProtectionLevel of EncryptSensitiveWithPassword set. A package has a connection to a .Net Provider and this works in the package (both connection test and package run) after the SQL Server Authentication password is entered. However, if I later review the connection properties the password is missing. Why is the password being removed when the ProtectionLevel is set as it is?

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.

Our SSIs package running via SQL Agent Job on a dedicated Reporting server had been running fine since deployed more than 3 weeks ago. Due to performance issue we had to fail over the database to Secondary. Since we failed over the SQL Agent Job is failing with error in package indicating that the login failed for domain user (DOMAIN\etl.user. We also noticed this error: The login is from an untrusted domain and cannot be used wit Integrated authentication. Below is the visual of our Architecture and explanation: More details: 1. Connection string is set at the SQL Agent job and the connection is made using Listener. 2. The SQL Agent job is ran as Proxy user as we do not want the user to have SysAdmin privilege. 3. I have ensured that the domain login on secondary server exist. Anyone has any idea why the package would fail?

I am trying to use Visual Studio 2022 to successfully load and build an SSIS package that targets SQL Server 2016. Documentation suggests that this is indeed supported. Per the online docs (https://learn.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-ver16) , I have used the visual studio installer to add in the SQL Server Data Tools. Then I have installed the SSIS extension. I am getting an error about OLEDB. More research suggests that I need the msoledbsql driver installed. However, the installer for that complains that I need a visual c++ redistributable. I downloaded that from here and installed it without issue: https://learn.microsoft.com/en-us/cpp/windows/latest-supported-vc-redist?view=msvc-170 I have in fact installed that as shown in my add/remove programs list. However, the msoledbsql installer refuses to install. It still insists that I need to install the visual c++. Can anyone advise a way forward?

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?

Built a simple SSIS package that pulls data from six SQL tables and dumps out to flat files. Package works fine within Visual Studio but keep getting the error code 0xC0048021 when deploying within SSMS.. This is the main error > Data Flow Task:Error: The component is missing, not registered, not > upgradeable, or missing required interfaces. The contact > information for this component is "Flat File Destination;Microsoft > Corporation; Microsoft SQL Server; (C) Microsoft Corporation; All > Rights Reserved; http://www.microsoft.com/sql/support;1 ". I tried both CSV and TXT and both fail, RAW file destination **does** work. SQL 2016 and the package has been built in Visual studio 2019. I have changed the config properties for the target server version to SQL 2016 as per image SSMS is version 20.2.30 SSIS Service is running as the local NT Service Any advice or suggestions to try would be appreciated. I am playing around with error capture now see if that helps.

I am having an issue in executing a conditional split for upserting records into my production table, using SSIS 2016 and MSSQL 2016 (Standard Ed.) I am trying to load two separate files (produced from an OpenVMS database) that contain similarly-formatted content, however they are from two different companies: AB_CustomerData.txt and CD_CustomerData.txt. Customer Format files RecordType: CU01 ---------------- RecordType 2 characters Company 2 characters CustomerNumber 7 characters CustomerName 50 characters RecordType: CU02 ---------------- RecordType 2 characters Company 2 characters CustomerNumber 7 characters City 9 characters State 8 characters RecordType: CU03 ---------------- RecordType 2 characters Company 2 characters CustomerNumber 7 characters Phone 10 characters AB_CustomerData.txt ------------------- CU01AB0001234ABC Company CU02AB0001234SmalltownAnywhere CU03AB00012342135551212 CU01AB0002345Unbrella Corp CU02AB0002345SmalltownAnywhere CU03AB00023452135551213 CU01AB0003456MegaCorp CU02AB0003456SmalltownAnywhere CU03AB00034562135551214 CD_CustomerData.txt ------------------- CU01CD0001234Jake's Widgets CU02CD0001234SmalltownAnywhere CU03CD00012342134441313 CU01CD0005678Jane's Doohickies CU02CD0005678SmalltownAnywhere CU03CD00056782135551314 CU01CD0006789Frank's Thingamabobs CU02CD0006789SmalltownAnywhere CU03CD00067892135551315 My end result is to have this in my production table: | Company | CustomerNumber | CustomerName | City | State | Phone| |-|-|-|-|-|-| | AB | 0001234 | ABC Company | Smalltown | Anywhere | 2135551212| | AB | 0002345 | Umbrella Corp | Smalltown | Anywhere | 2135551213| | AB | 0003456 | MegaCorp | Smalltown | Anywhere | 2135551214| | CD | 0001234 | Jake's Widgets | Smalltown | Anywhere | 2135551313| | CD | 0005678 | Jane's Doohickies | Smalltown | Anywhere | 2135551314| | CD | 0006789 | Frank's Thingamabobs | Smalltown | Anywhere | 2135551315| I have a ForEach container to loop through these files in my directory, and do the following: - load the file into a pre-staging table - process the customer record type (CU01, CU02, CU03 for each customer) into record-type specific staging tables (ie: record-type CU01 goes to a CU01 staging table, etc) - merge the record types into one larger staging table, containing all records - merge join the staging table and the production table, to prepare for upserting - upsert the production table My conditional splits are defined as follows:

INSERT: (ISNULL(Production_CustomerNumber) && 
!ISNULL(Staging_CustomerNumber)) && (ISNULL(Production_Company) && 
!ISNULL(Staging_Company))

UPDATE: (!ISNULL(Production_CustomerNumber) && 
!ISNULL(Staging_CustomerNumber)) && (!ISNULL(Production_Company) && 
!ISNULL(Staging_Company))

DELETE: (!ISNULL(Production_CustomerNumber) && 
ISNULL(Staging_CustomerNumber)) && (!ISNULL(Production_Company) && 
ISNULL(Staging_Company))

On the first pass of the ForEach container, the data from the first company file loads correctly all the way through to production. However, on the second pass of the ForEach container, any data pre-existing in the production table gets deleted. I am almost positive it is because of my conditional split definitions, but I can't seem to figure out where.

How to create Destination table name with Getdate in ssis destination task. I have one table like Test..but i want to create new table like Test_09Feb2023 in OLEDB destination.

I have an SSIS project consisting of many packages, including a Main_Flow package which executes the other packages in the desired order. The first package to be called from Main_Flow is Extract_Archive with a ForEach container that looks for zipped files. If one or more zip file exists, it extracts them in a location and marks a variable ArchiveFileFound to TRUE. Now back in the Main-Flow package, I would like the next step to be executed ONLY if the ArchiveFileFound from the Extract_Archive is set to TRUE. However that variable only exists in Extract_Archive. I've been looking for information on how to pass variables from one SSIS package to another but I cannot find a good practical and recommended example. Any thoughts?

I have an intermittent fault in my SSIS ETL process when I try and process my OLAP cube. There are no error messages in the SQL log, and there's nothing else significant running at the time. The only error message I can find in the event log is: > An error was encountered in the transport layer. SSIS and SSAS are SQL are all running locally on the same server, so I don't believe it's network related at all. The same package has been deployed and has run successfully for weeks. If we manually connect to the cube it processes without any problem. Windows Server 2012 R2 6.3 (Build 9600: ) on VMWare 64gb of RAM. Microsoft SQL Server 2016 Enterprise (SP1-CU1) - 13.0.4411.0 SQL Max mem = 48gb My gut feel is that this is more of an SSIS issue than a SSAS issue. Has anyone else encountered this? Can anyone reccomend a solution or workaround? Is it possible to get additional debugging information?

I want to trace the execution and see the changes in variables in the package during the runtime. the Microsoft report tool for the SSIS package is not sufficient. I want more details and don't have access to its code in the visual studio. Is there a plugin for reporting or debugging for SSIS packages in SQL Server Management Studio?

Consider the following scenario for an SSIS package: A Sequence Container contains two jobs for loading data from an Excel file: - Job A: Attempts to load data from an Excel file following a specific file definition. - Job B: In case JOB A fails, Job B will be executed instead. Outside the container, a final task is executed to reset data. The issue I have, however, is that if JOB A fails, even though JOB B takes over and manages to process the file correctly, the container is marked as a failure. Although I can circumvent this issue with the use of some variables and "Precedent Constraint" (this can be seen in the second screenshot above), I wonder if there is a more elegant way, in which I simply ensure that the Sequence Container is not marked as failed is the first task of loading data fails. Now, the first thing that comes to mind for me is the use of MaximumErrorCount property on the container. However I have other tasks within the container and if they fail it could mess with the logic. Perhaps nesting yet another container for just JOBS A and B within the existing container would be the way forward? But really (I'm a hopeless idealist) what I was hoping to find is some sort of property for JOB A that says *if job A fails, don't mark the container as a failure* in the simplest way possible. Thoughts?

We are planning on installing SSIS 2016 on a new dedicated ETL server. The packages will be called from SQL Server Agent (on a separate server), or from an application (again on a separate server). From reading the Microsoft docs , it seems that we need to install **Database Engine** along with Integration Services on the dedicated SSIS server. Is Database Engine required on the SSIS server for our needs? I'm confused by this: "*If the ETL server does not have an instance of the Database Engine, you have to schedule or run packages from a server that does have an instance of the Database Engine*". Does this also mean that the SSIS server is then redundant if run from a server that does have DB Engine?

Setting up SSISDB with the new AlwaysOn support in SQL 2016 Enterprise. This creates two jobs, one of which is the SSIS Failover Monitor Job. The secondary is not readable. The code for this job is: DECLARE @role int DECLARE @status tinyint SET @role = (SELECT [role] FROM [sys].[dm_hadr_availability_replica_states] hars INNER JOIN [sys].[availability_databases_cluster] adc ON hars.[group_id] = adc.[group_id] WHERE hars.[is_local] = 1 AND adc.[database_name] ='SSISDB') IF @role = 1 BEGIN EXEC [SSISDB].[internal].[refresh_replica_status] @server_name = N'SQL2', @status = @status OUTPUT IF @status = 1 EXEC [SSISDB].[catalog].[startup] END Every time this job runs, I get the below error due to SSISDB not being online on this secondary instance. This is because SQL Server must parse all parts of the query, regardless if conditional code paths are currently valid or not. Since SSISDB is offline, it cannot be parsed. > Executed as user: ##MS_SSISServerCleanupJobLogin##. The target database, 'SSISDB', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online. [SQLSTATE 42000] (Error 976). NOTE: The step was retried the requested number of times (3) without succeeding. The step failed. This code is generated by SQL Server, and since it is such a simple setup, I have a hard time believing it is a bug. But I don't know 1) what else it could be and 2) how one would work around it and provide the designed functionality without the secondary being readable.

We need to grant a couple of users access to run only specific SQL jobs that runs SSIS packages. Is there a solution that can be used to accomplish this?

We have 4 virtual machines that we used to test our BI platform (PoC), the final dimensions are: - SSIS (16 vcpu), SSAS (8 vCPU), SSRS (4 VCPU) et Data Engine (24 vCPU) What are the recommendations to install these components to a physical server? For SQL Server performance, [VMware recommends](https://www.vmware.com/content/dam/digitalmarketing/vmware/en/pdf/solutions/sql-server-on-vmware-best-practices-guide.pdf#page=23&zoom=100,0,0) (chap. 3.5.2) setting the number of cores per socket to one when allocating virtual CPUs to VMs on the vSphere platform". That means that a VM with 16 CPUs will have 16 vCPUs with 1 core each. Can we calculate physical server dimensions using reverse engineering? I mean: 16 vCPU equals 16 physical core? The dimensions CPU and RAM of these VM are big so we are asking if it's not more interesting to deploy on physical machines. We need 280 GB of RAM. The same thing that VMware said here the ratio 1:1 or 1:2 is considered for more performance.

A few colleagues & I have installed the latest Visual Studio (SSDT) - BIDS. We are unable to create packages, in Solution Explorer right clicking on SSIS Packages > New SSIS Package results in this infamous error: > TITLE: Microsoft Visual Studio > > Error creating package Failed to save package file "C:\Users\userid\AppData\Local\Temp\tmp9ABC.tmp" with error 0x80040154 "Class not registered". Googling this error brings up results from 2008 and it pertains to MSXML. I've tried all the solutions, install MSXML6.0, install OWC, regsvr32 the DLLs, I ran procmon and there is no hint of the problem in the trace. I am very confident this is a different bug to the 2008 one as none of the solutions work. One of my colleagues installed BIDS at home and it works perfectly. The work network we are in is locked down, however we all ran the install as Admins and the ISO file was > 2gig so we don't believe a component was missed during the install, but this could be wrong. Has anyone encountered this error? Anyone got any ideas of how to troubleshoot this?

We have an SSIS package that is going to generate files to be consumed by Google Big Query. The files will be gzipped .tsv files. One of the requirements is that the file be UTF-8. We have set this in the flat file destination so that it's 65001 - UTF-8. After this, the generated gzip files are consumed to Big Query correctly. The problem now is that some fields have character lengths of up to 21,000 characters. The DT_WSTR doesn't allow for this size. Changing the flat file destination field to DT_NTEXT yields the following error message > Error: 0xC020802E at 14_3 Data Flow into MyTSV, Flat File Destination MyDestination : The data type for "Flat File Destination MyDestination.Inputs[Flat File Destination Input].Columns[Value]" is DT_NTEXT, which is not supported with ANSI files. Use DT_TEXT instead and convert the data to DT_NTEXT using the data conversion component. All solutions I have read involve converting back to DT_WSTR or changing the code page back to 1252 which is not an option due to the Big Query code page requirement and the length of the data. Is there another solution for this problem?

I am having an issue with SSIS packages where scripts included in package parts ar not executed when the target server version is SQL Server 2016. 1. Create an SSIS project, set target server version to SQL Server 2016 (very important!) 2. Go ahead and add a simple script in the default Package. This should work fine. - In my case I have a script that sends an email using SendGrid service via SMTP. You can see the code here: https://pastebin.com/DW3hfBP3 - However I tested this issue with other scripts Something like: MessageBox.Show("Great. Another bug. Thanks a lot guys..."); would do. 3. Create a package part. 4. Now just try re-creating the simple script from step 2 into the package part. Then add the package part onto the default Package or another package and execute the task. Even though the execution is successful, the script is not executed. **NOTE:** If you try to copy the script from the default package into the package part, you should also notice a moronic error saying that the version is wrong. This is why I say to re-create the script in the package part. I wonder if anyone could reproduce this issue and if there is a way around it? Worth noticing that this issue doesn't seem to happen when the target server is SQL 2017.