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

Looking for best ways to report data from SQL Server Databases to users.   We have tried SSIS packages that outputs data to excel files etc.  
 Report Server-- tedious with so many fields
0
Exploring SharePoint 2016
LVL 12
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.

Hi,

I have created an SSIS package with 3 input parameters. 2 parameters are mandatory (Required = True) and 1 parameter is NOT mandatory (Required = False).

I have deployed the package to SSISDB and am running it as a SQL Server Agent job. My question is, how do I set the non-mandatory parameter to blank\Null\Empty?

If I set it to nothing I get the following error message when I click OK.
 
SSIS.png
0
How to convert a string to money in the Data Conversion Transformation process in SSIS?

My Source file is an Excel File. I added the Data Conversion Transformation to convert the Price column to the Data Type "currency[DT_CY]".

Source value : $50,240.30
Expected result : 50240.30
SQL Destination Table has the column Price with the Data Type : Money

However, conversion fails with the following error :

The "Data Conversion.Outputs[Data Conversion Output].Columns[Price]" failed because error code 0xC020907F occurred, and the error row disposition on "Data Conversion.Out

Open in new window

0
I am currently using the following in an SSIS package :
"SELECT *  FROM dbo.TestCategoryTestType WHERE Versionstamp >  " +   @[User::LSERV] + " and Versionstamp <=   "  + @[User::MaxRV]

Open in new window


When I click on the Evaluate Expression button, the above evaluates to:
SELECT *  FROM dbo.TestCategoryTestType WHERE Versionstamp >  0x0000000000000001 and Versionstamp <=   0x0000000000000001

Open in new window



This is what I expect to happen but when I run the package, it fails returning 'Incorrect syntax near '=''

Can anyone see what I'm doing wrong here. Using Visual Studio 2015 to develop.

Many thanks
0
I am running SQL Server 2017 Developer Edition with CU8 installed with SSMS 17.9.1.

I am running a script that goes through all the backup files on a network share and collects the header data for each file using the "RESTORE HEADERONLY" command.

I have run into some files that might be corrupt, and they take over an hour to return an error, whereas a successful run of the command is completed in 1-2 seconds.

Is there a way I can limit the execution time of the "RESTORE HEADERONLY..." command so that it runs for a maximum of 60 seconds?

I am reading files from network shares on other servers.

I have already tried limiting execution time to 60 seconds with this:

use master
go
exec sp_configure 'remote query timeout', 60;
go
reconfigure;
go

Open in new window


But the RESTORE HEADERONLY command still kept running for a long time on the suspected corrupt file.

Any help would be greatly appreciated!
0
Hello

I have run into an issue I can not figure out. I am running a 2014 sql express server that is connected to and importing data from a 2008 R2 sql server.  Each server is Hosted within the same Virtual Machine host using the same data storage but on individual VMs. When trying an import the from the express machine the script runs well but hangs because of some type of temp disk space utilization that that fills the drive with over 40GB. On reboot the disk space returns to normal. The script is pulling only about 6000 records from the other data base and when complete the dtabase file is less than 50MB.

We copied the data from the remote server and placed it on the 2014 express server changed the script to look remotely and the import happened as it should.

My questions are why and how does the fail happen?  Why would a large temp file of some type be written when importing between VMs. How do I over come this?  

I will provide any other information you may need and thank you for your help.
0
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
What do i need to do to download a free version of SSIS?
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
C++ 11 Fundamentals
LVL 12
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

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
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
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
Hi , I would like to load data from 3 different SQL Command (3 different queries) in 3 different excel files in one folder with each excel file with Date time feature in One package .
I tried this solution however getting issue as It wont open mapping window for 2nd Excel destination. However 1 excel file is loaded successfully., Your help is appreciated

Error is:

Excel destination: Opening a rowset failed. Checked that objects exists in Database.
0
How to resolve culture problem of decimal and thousand separator  of number of excel using c#
1
Hi,

I'm using SSIS 2008.

I have 2 ssis packages.
QUESTION:
1. can packageA call packageB ?   How (please show example code)?
2. Will packageA task that executes packageB wait until packageB is completed before packageA goes on to process a subsequent task?

Thanks.
0
Help with C# code to delete first row of all Worksheets in an Excel Workbook using SSIS script task.
There are about 17 worksheets, the script needs to loop through and delete the first row.

 Loop-through-Excel-sheets-and-delete.sql
test.xlsx
0
Hello,

I am getting below error while connecting Oracle DB from Visual Studio 2015 - SSIS. Can you help me to fix below error?
MS SQL Version: MS SQL 2016 Ent Edition
Oracle client version: Oracle 11g 64 bit (Oracle Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows (x64) )
ODAC version: ODAC 11.2 Release 5 and Oracle Developer Tools for Visual Studio (11.2.0.3.20) (32 bit)

I have also tried with 64-bit ODAC 12.2c Release 1 (12.2.0.1.1) for Windows x64, but still same error. There are No multiple 32-bit client libraries (SQAORA32.dll) on the same machine.

I am able ro connect oracle DB using SQL plus utility but geeting error in SSIS.
0
Become a Certified Penetration Testing Engineer
LVL 12
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Hi,

I'm using SQL SERVER 2008R2 and SSIS 2008.  How do I export a SQL SERVER view to Excel .xls format?  I've done it a long time ago.

In the DTS task, I've set the fields to DATA TYPE=UNICODE STRING [DT_WSTR].

Problem I'm running into is the next task  EXCEL DESTINATION: when I click MAPPINGS, the right side of the screen just says F1  ssis_export_to_excel.JPG
0
I have created SSIS package in VS 2012 and SQL server 2012, it is working properly V.S. on the local machine
but  I am facing One issue when I am executing my package using a batch file on Server  
I got one error as
Error: 2018-08-30 07:37:43.57
Code: 0x00000001
Source: Script Task
Description: Exception has been thrown by the target of an invocation.
End Error

Can you please  provide a solution how to fix this issue
0
How to access secrets stored in Azure Key Vault in SSIS? Can we access it using script task or is there another way to achieve this? I tried a C# code in SSIS Script Task functionality to access Key Vault, for that, I installed some NuGet packages in my solution. But when I save and exit script task and open it again, all the NuGet packages I installed have vanished from the solution, so one more question arises here, does SSIS Script Task supports NuGet functionality?
0
We have a database on-Prem which we want it to sync with Azure DB. So all the changes that we do on-Prem should appear on Azure DB next day. To accomplish that I tried data Sync option and that option only syncs Tables and syncs data creating lot of data sync objects on our on-Prem DB. I tried to take a backup copy of DB and put it on Blob Store and from there tried to restore to Azure DB but we are using Azure SQL Server DB which does not support restored DB option from backup file unless its a SQL DB on a managed instance.

So what other options do I have? Copy data in Data factory only copies data are there any other Azure tools that I can use to sync both the DB's
0
I  am looking for the best way to use SSIS to export contact data from SQL Server 2016 to an Outlook 2016 contact folder.  The contact data come from a view with over 5000 records.
0
i'm looking for a way to web scrape a simple web page by taking a csv list as my parameter and retrieving the html table on the next page through SSIS
The webpage is http://p2web.energy.dla.mil/pls/p2wp/dfsc_pkg.df_contract

so in the contract textbox i would put contract number item in my csv and retrieve the following html element

table
not wanting to use Zappysys. wondering if there are other tools out there that are free.
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.