SSIS

SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks. SSIS is a platform for data integration and workflow applications. It features a fast and flexible data warehousing tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data. SSIS replaced Data Transformation Services, which had been a feature of SQL Server since Version 7.0.

Share tech news, updates, or what's on your mind.

Sign up to Post

I want my business analysts to have access to the nice dashboard reports in SSIS Catalog. But i dont want to give them access to SQL.

Where are the canned SSIS Catalog reports located? Im interest in " All Executions" and "Overview"  per execution.

So I can place this RDL's on the Report Server and put them on a subscription so i can send them to the business team.

Im not finding them anywhere. And nothing im finding on the web arent quite what i need.

All-Executions---9102019-752-PM---DB.jpg
0
Exploring ASP.NET Core: Fundamentals
LVL 13
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

When I try to import csv file from Microsoft SSMS by using "ImportFromExcel", it says that "ImportFromExcel" does not exist.  Please see the attachment. How can I enable this option?
0
In SSIS 2017, I have a task DATA FLOW that gets data from SQL SERVER 2016 then sends it to the task FLAT FILE DESTINATION.

PROBLEM: if the connection manager for  FLAT FILE DESTINATION does see the existence of the text file, then I get the error 'Either the file does not exist or you do not have permissions to access the file'

How can I create a task that creates a blank text file so that the file would exist?
0
I have an SSIS that is suddenly failing. This project reads a csv file and inserts the data into MSSQL.The message I get is:

[Read CFS Flat File [2]] Error: Data conversion failed. The data conversion for column "Cancel Reason" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

[Read CFS Flat File [2]] Error: The "Read CFS Flat File.Outputs[Flat File Source Output].Columns[Cancel Reason]" failed because truncation occurred, and the truncation row disposition on "Read CFS Flat File.Outputs[Flat File Source Output].Columns[Cancel Reason]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

[Read CFS Flat File [2]] Error: An error occurred while processing file "D:\MyFolder\MyFile-Input\AUTO-CFS.CSV" on data row 977131.


[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on Read CFS Flat File returned error code 0xC0202092.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

The actual record count I see while the project is running is 964,492 just before the failure. There over 1,000,000 records in this file.. I have checked both of these and the value …
0
Hi, below is my SQL which I am using to export to a | demited text file using SSIS. However I now
need to add a Header and Footer to the file preferably in my below SQL itself, if possible ?

Header (both the below values are fixed and never change)
HD,1.0

MY existing SQL for the export file

SELECT  [Record Type],[Provider CIS No]
FROM (
  SELECT DISTINCT 'CI' AS 'Record Type'

             ,CUSTNO AS 'Provider CIS No'
             ,c.cd
  FROM customer c
  UNION
  SELECT DISTINCT 'BD'
             ,CUSTNO AS 'Provider CIS No'
             ,c.cd
  FROM customer c
  WHERE YEAR(c.cd) = YEAR(getdate()) AND MONTH(c.cd) = MONTH(getdate())
) t
ORDER BY cd,[Provider CIS No]

Footer
FT,(count of records from my SQL above)



can anyone please give me that SQL with the header and footer ?

Many Thanks
0
I am trying to create an SSIS catalog but it fails on every attempt with the following
message in ssms 2017 and 2018.

TITLE: Microsoft SQL Server Management Studio
------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
ADDITIONAL INFORMATION:

Database 'SSISDB' does not exist. Make sure that the name is entered correctly. (Microsoft SQL Server, Error: 911)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.00.2027&EvtSrc=MSSQLServer&EvtID=911&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

I've tried many supposed solution but nothing has worked to date.

I need a solution.
0
I think the title says it all. If I don't have a package variable named user::MyVar defined in the package, can it be created at run-time?

I want to be able to populate it with a value in a subsequent T-SQL Script Task.

Just for clarification, I only want to create it (as a string variable) if it does not exist. Its value will be set elsewhere.
0
Hi,

We are using SQL SERVER 2016 in the Microsoft Azure cloud.  I have an existing ssis package that gets data from a sharepoint 2016 list.

I’m using vs2017.  The package runs fine on my workstation but not in the Microsoft Azure cloud.
Cloud error log says:  version 14 script is not supported.
So I changed the ssis targetServerVersion from 2017 to 2014, and that error went away.

But a new error appeared: odata source could not be upgraded to the newer version of the component.  The performance upgrade failed.

Note: the cloud does not show the OData source driver, the Azure cloud admin said use ‘sharepoint list adapter 2014’ driver.

Question:  should i get odata source working or replace how it gets data from a sharepoint list by using ‘sharepoint list adapter 2014’?
Please provide an example how to deploy ‘sharepoint list adapter 2014’ in SSIS (Visual Studio 2017).

thank you
0
I am executing SSIS package and getting access denied error:
Description: Cannot open the datafile to D:\Customer\payment.txt.
I am able to open the file from windows under the same login credentials

Any inputs appreciated.
0
I have a package that loads 300 tables from DB2 to SQL Server.  We added a column to all the DB2 tables to keep track of changes named DW_LASTTIME.  
My question is, each table has a data flow task with the source table having a variable like this to pull the data.

"SELECT * FROM ABC WHERE LASTTIME >= " +  @[User::strAdjLastRunTime] +" WITH ur"

Open in new window


So I need to update all 300 to make it say

"SELECT * FROM ABC WHERE DW_LASTTIME >= " +  @[User::strAdjLastRunTime] +" WITH ur"

Open in new window

Is there any way to do this without having to manually open and update each dataflow?  Like in a text editor or something?  I know SSIS packages are just XML files but I was told if you mess with the actually file, it can corrupt it.

Thanks!

Jim
0
OWASP: Avoiding Hacker Tricks
LVL 13
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

hi,

I have a study on ETL tools and recently heard a lot of voice on just need to do ETL programming using script/coding, e.g. R and Qview, so is this means now ETL tools like MS SSIS is useless ?

what is the pros and cons on doing ETL logic by coding and ETL tools?

it seems now doing ETL code in container level with RESTFUL API already make ETL process can do load balancing, parallel execution and scale out (by container), is that correct ? so no need ETL tools any more ???

the new MariaDB X3 platform seems can even ignore ETL process as it can stream data directly form OLTP to OLAP, so not need ETL anymore?
0
SSDT 2017 on SQL Server 2014.  When running a SSIS package with a script component that has a stop point inside on one of the lines of code, the warning is listed in the package progress tab and the stop point is ignored.
We have tried the following:

-Rebuild all
-Reconstructing the SSIS package from scratch
-Rebooting the computer

There doesn't seem to be much on this error on the Internet.  Is there a way to fix this so the debugger comes up showing the script task's code for debugging as it should?
James
ssiserror.PNG
0
hi, we are migrating our SSIS packages from 2014 - 2016.

can some please suggest me the safest and the most easy way to migrate them ? what kind of
testing do i need to do ?

Many Thanks
0
I have the following code in an SSIS C# Script task. It hangs on me and I've tried several ways to get it to run with no success. The object is, if a parameter named paramclientlist is empty, read the value from a configuration table, otherwise use the current value of the parameter. So, if the package is called with a value for parameterclientlist, that value will be retained.

In the code below, the configuration table has three rows of data, one of them having a value of paramclientlist for the column ConfigurationName. It is the last of the three values that is processed. When the code gets to the line
Dts.VariableDispenser.LockOneForWrite(parmName, ref variables);

Open in new window

for the parmName paramclientlist, it 'hangs' like it is locked trying to get the lock. I don't know if it will eventually time out with an exception but after a couple of minutes I got tired of waiting and killed it.

I've tried several things, including defining the parameter as a ReadOnlyVariable, a ReadWriteVariable, not defining it as either (which throws an error, I assume because I am setting the variable cientList to its value in the script) but nothing works. Interestingly, the other two parameter values I set work fine, and neither is set as a read only or read-write variable in the script task properties window. Does anyone have any clue how I can get this to work (VS 2017).

            try
            {
                //get package name from variable passed in via dtexec
  

Open in new window

0
KingswaySoft, CRM 2011, Visual Studio 2012, SQL Server 2012

I am working to install the KingswaySoft SSIS Productivity Pack and Integration Toolkit on Visual Studio:
https://www.kingswaysoft.com/products/ssis-productivity-pack/download
https://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-dynamics-365/download

I am following the steps below but not seeing the SSIS Data Flow items for KingswaySoft:
Adding SSIS Data Flow Components to Business Intelligence Development Studio's Toolbox
SSIS Integration Toolkit - Productivity Pack includes two data flow components. These need to be added to the SSIS toolbox before you can use them in a SSIS data flow task.

To add the data flow components, create a new data flow task if you do not have one yet and switch to the SSIS data flow page. Right-click on the toolbox area to bring up the context menu, where you can select the "Choose Items..." option as shown below.

Choose items
You will be presented with a window called "Choose Toolbox Items". Switch to "SSIS Data Flow Items" tab, and select the components from the list.
Add SSIS data flow components
Add Data Flow Items
I have installed the KingswaySoft packages but they do not appear.

Thanks
0
I recently discovered that the input csv file I was being given for an SSIS project had two of the column names changed.  I have gone into the project and made these corrections and I also remapped all the fields. The problem I have now is that the project fails with the message "[Read CASES Flat File [2]] Error: The column delimiter for column "Incident No" was not found." This column is the last field of the row. I can view this file in Notepad and the Header Row delimiter is set to CRLF. If I preview the data all looks well. What can do to correct this issue?
0
I have a series of SSIS projects that have been running happily for months. Suddenly a few are failing. The error message is column delimiter for column 'XYZ' is missing. The input file is a csv file. I checked the structure of the input file and it is fine. I am suspecting that bad characters are in the input file. All I have in the Data flow section of this project is Read CSV file(flat file source) and write to a SQL table(OLE DB Destination). What should I add to this project to be able to find the bad data or at least have it skip the bad records and write the good records?
0
Experts,
I have an SSIS package that has an Execute SQL task that runs a stored procedure. The package was hanging. I figured out the proc has an error. If I execute the task manually in SSIS it does in fact fail the task. But if I execute the entire package it will just hang on that task. Just for speed I created a proc that simply divides by zero so it will fail instantly.  I've tried it without a Try/Catch. I've tried with a Try/Catch with a "THROW" and a Try/Catch with a RAISEERROR but the package will not fail.  

Any insight is appreciated. We are using VS2010.
0
I have an SSIS design question. (SQL SERVER 2014 or SQL SERVER 2016)   I'm using am SSIS task that i would would to multi-thread in some way to make it more efficient and faster.

Basically I'm looping through 100 companies and pulling their emails from a specified email account and storing each email as its own record in its table.

I have this in one script task in one package.

So my question is.

A. Should I Multi-thread the loop inside the script task for each company?

OR

B. Design the package to take 1 company only and setup a separate SQL Agent Job for each company? What settings on the package would i have to set for that?

Advice and help is appreciated.
1
Microsoft Azure 2017
LVL 13
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

What are the top 10 most common package failures? Thanks
0
I have a background in computer programming including sql, access, cobol, vb for apps. I worked for a fortune 500 company generating reports with access at the time.  I have been a stay at home mom for the last 5 years.
I had minimal training 10 years ago in data warehouse. I took a couple of courses online and understand the fundamentals of using visual studio - creating a package with a data flow, defining the source and target, configuring the connection manager, linking table lookups to the reference tables in the warehouse. I brushed up as  i had an interview for a data analyst job that required ssis and ssrs ( I am familiar with reporting tools) - I did not put on my resume but it did state on the requirements. They want to interview me to trouble shoot failed packages. I can figure things out very quickly. However, I am not sure what type of questions they might ask that are more basic relating to ssis and ssrs. I was wondering if you might please be able to provide some basic questions regarding package failures or any questions that might help me get through this. Maybe I am over my head. Thanks again for your help
0
I need to copy 24 tables from one database to another to keep both databases in sync. I currently have 24 SQL scripts accomplishing this but I would prefer to convert these to an SSIS project. My first question is do I create 24 separate Data Flow tasks to accomplish this? My second question is that all tables have a unique identifier column. What is the best way to copy over only the new records that have been added? I was initially thinking of starting off the SSIS project with 24 truncate table commands and then copying all records. This will work but that is a lot of records. If it can be setup to bring over only the new records that would be a lot less work it will be doing.
0
Hi EE,

I have this irritating issue on a server again of not being able to connect to the SSIS service when I expand the MSDB box error is slightly different this time error code xFFFFFFF. See pic 1

The SSIS is SQL 2016 named instance DC1PRDRPT01 it installed along with SSAS and SSRS and SQL server browser and SQL DB Engine on the same server.

The service runs under a local service account I have set up which has admin privileges.

Troubleshooting steps;

- Put the server name DC1PRDRPT01\DC1PRDRPT01 in the MsDtsSrvr.ini.xml file.
- Configured the service account username in DCOM services.
- Made sure the Named Pipes and TCP protocols are enabled.
- Windows Firewall is disabled.
- Checked the registry HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\120\SSIS\ServiceConfigFile. All good.

IPv6 is enabled on this box if that helps.

Any assistance is welcome.

Thank you.
pic1.png
0
I am having issues with my SSIS pkgs using Visual Studio 2015 on SQL Server 2016.  
We currently use SQL 2008 R2 and the Package Deployment model so we can update/debug a single package at a time, make necessary updates and then copy modified file to the \packages directory to be called by the SQL Server Agent job.

We are now moving to SQL Server 2016 and Visual Studio 2015.  We’ve updated to SQL Server 2016 SP2 w/ Cumulative Update 6.   I have created new script tasks with VS2015, created a project to then change the TargetServerVersion to SQL Server 2016.  It works either in project or pkg deployment mode until opened and saved outside of the project.  I can run the package successfully from the project and also from a job via SQL Server Agent as long as I do not open & save it outside of the project.  As soon as I have opened the .dtsx file with Visual Studio 2015 and saved it, then the job will fail on my script tasks with: See Attached file


The package doesn’t identify any errors when just opening it and viewing the tasks/scripts.

I have also tested on a different SQL 2016 server with Visual Studio 2017.  Almost same results from VS2017 project, change the TargetServerVersion to SQL Server 2016, create new script tasks, convert to Pkg Deployment Model.  This runs successfully within the project.  When run from the job it is successful until you open/save it as it requires migration.  Then when run from job, same error as :  See Attached File.
 
With VS2017 …
0
I'm trying to export a View as a Flat text File. The view has 6 months of transactions and executes in 5 seconds.
I created an export data DTS package to export this data to text and it is taking an age, the data file is stored on the same server.

Anyone know why this is taking so long?
0

SSIS

SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks. SSIS is a platform for data integration and workflow applications. It features a fast and flexible data warehousing tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data. SSIS replaced Data Transformation Services, which had been a feature of SQL Server since Version 7.0.