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 have an SSIS solution which has grown over the past few months to the stage where I need to break it out into separate solutions.
A bit of background: The project is migrating data from one database to a new system. Data is extracted from the old database into flat text files which I then import to SQL Server using SSIS Workflows. The data is then transformed and output to Excel templates ready for importing to the new system.

All of this works fine. Now we are coming up to the cut off date for data from the old system. This will be the dataset that forms the Live data in the new system. There is a period of one month between the data cut off and the use of the new system, during which time changes will be made on the old system. So, we have a requirement to produce delta output which generates records that have been Inserted, Deleted, or Updated. All of this is fine and the scripts are ready.

However, I've run into serious issues with memory with having both the main data migration and delta workflows in the same project. Therefore, I want to move the Delta workflow to a new solution. The way I've gone about doing this is to set up a new solution in VS 2017 (15.9.16), and adding an existing package to the solution. When I do this, it loses the Connection to the database. So, I've tried creating the connection prior to adding the existing package, yet I get the same result.

I could go through every data flow task and update the connection, but this also requires …
0
Learn SQL Server Core 2016
LVL 13
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

How to copy large DAT files  1.36 GB from FTP to application server. Coldfusion was used earlier but it hungup at this file. we have SSIS too.

Reason they used CF earlier cause its actually parsing the copied file.
0
Hi,
I have built an SSIS package in VS2017 which runs fine in Debugger mode.
I am trying to call the package from a SQL Server Agent job that sits on a SQL Server 2008R2.
Using SSMS on the 2008R2 Server I am trying to load the Package from the File System into the job but when I try to add the Package , I get the following error
TITLE: SSIS Execution Properties
The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails.
Any guidance appreciated
0
SQL Server 2012 and VS 2013 for SSIS.

I want to set up a connection in my packages to point to a configuration table for the packages other connections (OLE DB, Flat File, and Excel).  I have read you can use a system environment variable to do that so that when you promote the packages to different servers, it will pick up the environment variable (named the same) and point the package to the correct configuration table on that server (for dev, test, and prod).

I have a system environment variable set up (SSIS_Config) with the connection string for the SSIS Configuration table in it on the server and a data source (dsSSISConfig) in each package to connect to the SSIS Configuration table.  There are a couple of other data sources in the packages that I want to config from the SSIS Configuration table and have it set up for them.

So how do I make the dsSSISConfig data source use the system environment variable SSIS_Config?  Can I do that?  I can't seem to find a good example of how to set it up.  Any help would be greatly appreciated.

Thank you!

Jim
0
Hi, I have few SSIS packages. NOw I want to deploy them. However I can see that our DBA has not
installed any Integration services on the server. But he asked me to just create a Job in SQL Server
agent and call the SSIS Package from the path and schedue it.

can anyone pleae let me know this will work and is this a good strategy ? I thought we need to first
import the SSIS package into the SSIS server and then call this from SQL server agent for the job schedue.

Many Thanks
0
First, I'm a novice with SSIS, having mostly just maintained existing packages.  I have a project that I upgraded from SQL Server 2008 R2/Visual Studio 2008 to SQL Server 2016 (deployment model) in VS 2017.  All of the packages work running locally on my machine just as they always have, with the exception of one.  

This loops through oledb source servers  using a connection string built by variables received from a query.  Then it starts the control flows, which each has a series of data flow queries.    The weird thing is sometimes it runs fine.  Most of the time I get errors like this:

[NetComply - CollectionMembers [212]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Protocol error in TDS stream".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Communication link failure".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "TCP Provider: An existing connection was forcibly closed by the remote host.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Communication link failure".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  
0
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
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
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.

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
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
Exploring SharePoint 2016
LVL 13
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

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

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.