[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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 project that I created in Visual Studio.  It includes two packages.  One package exports data from SQL tables into an Excel spreadsheet.  The other iimports data from an Excel spreadsheet into a SQL table.  When I run it in Visual Studio on my development machine, both packages work properly.  But when I deploy it to another server as an Integration Services Catalog SSISDB package, the export from SQL to Excel works, but the import from Excel to SQL fails with the following message:

"SSIS Error Code. DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager 'SQLSERVER' failed with error code 0xC0202009."

Then a second error pops up:

"An OLE DB record is available. Source: 'Microsoft SQL Server Native Client 11.0'. Hresult: 0x80004005. Description: 'Named Pipes Provider. Could not open a connection to SQL Server [53]."

Both packages use the same connection manager (called SQLSERVER).  I made sure that the Run64BitRuntime setting is set to False and DelayValidation is set to True.

Again, the export from SQL to Excel runs without error.  It is the import from Excel to SQL that is throwing the errors.

T
0
CompTIA Security+
LVL 12
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Hi,

I have found the following code to render an SSRS via a SSIS script. This script works as it is intended, e.g. saves the rendered report to disk.

But I would like to send the rendered report as an email attachment, without saving it to disk. Please could someone help? I have tried to render it to a memory stream but I can't quite get it to work.

Thanks, Greg

 Protected Sub SaveFile(ByVal url As String, ByVal localpath As String)
        Dim loRequest As System.Net.HttpWebRequest
        Dim loResponse As System.Net.HttpWebResponse
        Dim loResponseStream As System.IO.Stream
        Dim loFileStream As New System.IO.FileStream(localpath, System.IO.FileMode.Create, System.IO.FileAccess.Write)
        Dim laBytes(256) As Byte
        Dim liCount As Integer = 1
        Try
            loRequest = CType(System.Net.WebRequest.Create(url), System.Net.HttpWebRequest)
            loRequest.Credentials = System.Net.CredentialCache.DefaultCredentials
            loRequest.Timeout = 600000
            loRequest.Method = "GET"
            loResponse = CType(loRequest.GetResponse, System.Net.HttpWebResponse)
            loResponseStream = loResponse.GetResponseStream
            Do While liCount > 0
                liCount = loResponseStream.Read(laBytes, 0, 256)
                loFileStream.Write(laBytes, 0, liCount)
            Loop
            loFileStream.Flush()
            loFileStream.Close()
        Catch ex As Exception
        End Try
    End Sub
    

Open in new window

0
Hi,
I am using the following to populate an SSIS variable
SELECT ? = CAST(MIN_ACTIVE_ROWVERSION() AS BINARY(8))

Open in new window


When I run this in BIDS it returns a non specific error.
When I run the code in SQL 2005 management studio (minus ? =) it returns the value as expected.

Anyone any ideas on why this is not working?

Any help would be appreciated.

Thanks
0
What do i need to do to download a free version of SSIS?
0
i am trying below kind of query which should give count less than 30 minutes of difference of time. not able to execute below. How to modify to make it work

select count(order_nbr) from order INNER JOIN timings ON order.order_id=timings.order_id WHERE LEN(order_nbr) = 11 AND order_cd=02  AND order.time_ins- timings.time_upd <30 minutes
please advise
0
I hope someone can provide me some guidenace...

We have a lot of InfoPath forms. We're going to PowerApps. My teammate has been tasked with finding out if we could convert the InfoPath forms into PowerApps without rebuilding all the forms from scratch . He said he's read something about "auto converters" but he hasn't found any.

I don't know anything about InfoPath or PowerApps. My teammate  has hit a wall in his research. I hope someone can give me some input.
0
Hi EE,

I am getting an error connecting to integration services 'Access is denied' when a particular package is run. I have checked the following:

The users are part of the Windows Admin group and have sysadmin credentials on the SQL Server.

Running SQL server SSIS 2008 or v10.

Any assistance is welcome.

Thank you.
0
Hi EE,

We have recently upgraded our SSIS Server from 2008 to 2014, SSIS services v10 has been kept for some of our older packages. All the SSIS packages on our server are running except for 1, error message below:

Executed as user: BUCR\svc_0070. Microsoft (R) SQL Server Execute Package Utility  Version 12.0.5000.0 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  2:28:10 PM  Error: 2018-11-27 14:29:21.73     Code: 0xC0202009     Source: Cleanse Aurion employee data for import to Procura view_Aurion [154]     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E09.  End Error  Error: 2018-11-27 14:29:21.73     Code: 0xC02020E8     Source: Cleanse Aurion employee data for import to Procura view_Aurion [154]     Description: Opening a rowset for "[dbo].[View_Aurion]" failed. Check that the object exists in the database.  End Error  Error: 2018-11-27 14:29:21.73     Code: 0xC004706B     Source: Cleanse Aurion employee data for import to Procura SSIS.Pipeline     Description: "view_Aurion" failed validation and returned validation status "VS_ISBROKEN".  End Error  Error: 2018-11-27 14:29:21.73     Code: 0xC004700C     Source: Cleanse Aurion employee data for import to Procura SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2018-11-27 14:29:21.73     Code: 0xC0024107     Source: Cleanse Aurion employee data for import to Procura      Description: There …
0
I am trying to run a DTSX package from a SQL Job scheduler. I have created my job using Visual Studio 2015 and it works if I run it from my machine. I have then copied my Package.dtsx to the SQL Server and it gets error whenever I run from the SQL Server Agent - Job. I can run it locally from my Windows machine but weird error from SQL 2016 Server.

My folder has read/write permission. I do not have the file open.
error.log
0
Hi,

I have a folder with a bunch of files that come in a set of 2 files (e.g., Name.pdf, NameMD.pdf).  I need to loop through this source folder (call it FOLDER1) and move only each set of files to another folder (call it FOLDER2).   (NOTE: Then, I have another process that compares the contents of each set of PDF files.)

I need to search for a file that has a *MD at the end of the filename, then search for the 2nd file that does NOT have 'MD' at the end of the filename, then move both files to FOLDER2.  Then I have another process that processes files in FOLDER2.  Then repeat the loop in FOLDER1.

What is the code to do this as a vb script, a standalone console C#.Net or C#.Net inside of SSIS?


Example:

001.pdf
001MD.pdf

002.pdf
002MD.pdf

003.pdf
003MD.pdf
0
Learn SQL Server Core 2016
LVL 12
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.

Hi EE,

How do I write the SSIS logging output to a DB on my server, there is someone wrong in the connection string I am using:

I am using SQL server 2008 R2 SSIS.

Any assistance is welcome.

Thank you.
Capture.PNG
0
I need an SSIS Example project (VS Shell 2013) That Uses a 2003 version of Excel file(s)

And 1 by 1 pulls in the file... sends the data to SQL
Then renames the file to another folder
0
I have an SSIS package that is giving me an error in our test environment. It runs fine in DEV, but errors in test. It is a data flow task with an OLE DB source that executes a SQL Servr stored procedure. The code is:
EXEC [uspME-GF-30250-HMS_HD] ?,?,?,?
WITH RESULT SETS(
(	
	ORDERS VARCHAR(1),
	RECORDS VARCHAR(1000)
 )
)

Open in new window

The error is:
Error: 2018-11-14 09:24:03.48
   Code: 0xC0202009
   Source: DFT_LOAD_MEMBER_EXTRACT_FOR_HMS OLE_SRC_MEMBER_EXTRACT [9]
   Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80040E14  Description: "EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80040E14  Description: "Invalid column name 'CXCX_RECORD_NO'.".
End Error

I thought of removing the WITH RESULT SETS but I can't even save the changes. SSIS gives me the following error:
The metadata could not be determined because the statement 'INSERT INTO #StaticData...' uses a temp table.
The stored procedure is 'relatively' complex, creating and loading several temp tables, some intermediate staging tables, them finally building the data extract result. I've never run across this issue before and am looking for a solution. Checking Google for…
0
Hi,
am Getting an error when importing a file using SSIS
The "Flat File Source.Outputs[Flat File Source Output].Columns[TEST_ACT_TYPE]" failed because truncation occurred, and the truncation row disposition on "Flat File Source.Outputs[Flat File Source Output].Columns[TEST_ACT_TYPE]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.  
An error occurred while processing file "\\testfolder\\Inbound\test_file_123756.TXT" on data row 1.  
any guidance appreciated
0
Dear Experts,

I am thinking to use SSIS to do the following procedure.

(1) copy many files to Linux machines. then
(2) execute a Linux program (command) to analysis those files. The program will output the result to the local Linux drive
(3) when (2) is done, then pull the result back to Windows machine

For (1) I am thinking of FTP task (by FTP or SFTP)

how about the task of (2) and (3) ?  Could you please help ... Thanks
0
I have SSIS package created which transfers data from local mysql server to azure database.  the package runs fine inside visual studio and data gets transferred.  i created the sql job in local sql 2012 server by importing this package .  when i run the job  it throws the below error and job fails

sqlsvrjob_medicsteam_azure Connection manager "khsql.database.windows.net.Kh-Website.khsql"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80040E4D  Description: "Login failed for user 'khsql'.".
0
Hi All,

I'm new to SSIS and I want to do incremental data load for 80 tables from one database to a other database and  I want to  to do it through CDC of SSIS.
Can anyone help me out abut this?

Or please let me know if any  the way to get only the delta from all 80 tables in one database and load into target tables in different database every day without using CDC of SSIS.

Regards,
NPK
0
Using SSIS, I am exectuing a stored procedure and which returns one column which contains values serperated by the | delimiter so the datatype is nvarchar(max).

I can successfully seperate it into columns using the FINDSTRING function, but when I exporting the data to excel (2007-2010 version as an xslx), everything is in the text format.
Excel warns that the cell is a 'number stored as text' on numeric fields.

So I require help on how to ensure numeric fields come out as numbers.
For info the template excel file as headers on the first row that are all text.

Useful info:
Excel Connection String is:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=samplefile.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=YES";

Let me know if further info required, Thanks in advance.
0
Hi all.

We're trying to figure out a way to download emails from an Office 365 email address to a table in our SQL Server 2008 server. Is there a way to do this (ie. SSIS package or other).

We would bring in the from, to, cc, email body

Thank you in advance!
0
HTML5 and CSS3 Fundamentals
LVL 12
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Where to begin.
I need to Move some data from Teradata to MS SQL ussing SSIS.

Here is the part I am stuck. I order to create the query in Teradata I need to import a file with 20,000 IDs into a Volatile temp table in TeraData then use that table in a join.

CREATE MULTISET VOLATILE TABLE SomeTempTable(
IDs
)
PRIMARY INDEX(IDs)
ON COMMIT PRESERVE ROWS;

--After i create the temp table I need to do this
INSERT INTO SomeTempTable(?)

Open in new window



In SSIS how do i account for the import here because using SQL Assistant i get a explorer window to point to file with IDs.

I am thinking this would have to be done in a script Task. Even using a script task i am not sure how to point to file?

I would like to know how to get this done. If there is another way not involving script task I am all "EARS" as well.

Thank you in advance!!!
0
I tried to install Visual Studio 2017 Professional but it failed due to lack of disk space.

So when I opened my Visual Studio 2008 SSIS project, I get this error:

Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)) (System.Windows.Forms)
0
In Visual Studio, Integration Services, I have a SQL source and an Excel destination.  Is it possible to create an initial step that deletes the contents of the Excel file, except for the column headings.
0
Just trying to find out more information about SQL SSIS which I know nothing about.  Can SQL SSIS be used ingests a Multi-Worksheet EXCEL file and separate each worksheet into a separate output file like text or csv?  We are think about using SQL SSIS as a preprocessor for an application that does not support multi-sheet EXCEL files.  I am aware we can use SQL SSIS to do data transformations but now i want to know if this can file re-engineering tasks like this.  Also, SQL SSIS be configured to generate an XML file?
0
Is it possible to create an SSIS package in Visual Studio with the Server Name and Data Source in the Source object as a parameter, so that you can provide the SQL server name at runtime?
0
Hi,

I have a DTS job that uploads some CSV data into a SQL table.

I wanted to schedual the DTS using an SQL job.

I have setup the job in SQL but when it is executed through the SQL job it fails (see error message below).

If I run the DTS directly it works ok.

I think it is something to do with the user permissions but I don't know why it fails as SQL user I have setup (web_admin) has SA rights.

Any ideas?

DTS fail
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.