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 one requirement like , I have to copy the data from source server tables to destination server tables which are having same columns for all the tables. Server A is having X,Y,Z tables and Server B is Having same X,Y,Z tables… the data is not direct copy means to retrieve the A table’s data we have to do the some join operations with other tables like B,C..etc and the same for B,C tables data also. So i wrote one Stored procedure to pull the details from these tables (A,B,C) so the sp outcome is having 3 result sets i.e A,B,C. So to do this I took Script Component and calling this stored procedure and also i created output columns for all these tables and in the script i wrote the code to bind the sp data to these output buffers. So finally my script component is returning A,B,C tables result sets , now how do i bind these result sets to destination server A,B,C respective tables. Please help mee sirrrrrrrrrrrrrrrrrrrr
New benefit for Premium Members - Upgrade now!
New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

I am trying to read  microsoft excel 2016 sheet from ssis excel source not working . I have two tabls in there i can't even pick it up .

Same file works in 2013.please advise
In internet explorer i am able to access a https site and download a file. i have to use an username and password to access the site first.
However, when i enter the URL and credentials in SSIS using the HTTP connection managerand press the test connection button,  i get the message:

The remote server returned an error: (401) Unauthorized.

Any ideas why this is happening. The username and password are correct.

Can i use an https site in a http connection?

Any help appreciated.

I can not send mail through the SMTP connection manager (send mail task).

attched file

Previously I was able to make use of an sysadmin account on our client's server to create jobs and execute packages. The client has now changed the rights of this account so it's not a sysadmin anymore and now the jobs are failing. They don't want to give me another sysadmin account. I now need to know how do I go forward in giving this new account permissions to run Sql Server Agent jobs and to execute packages. The account they have created for me is not a windows login but only a sql login.

Hi we have job that copies file to a destination, So is there a way that I can be notified after all the files are downloaded completely ? Is there a feature in SQL, SSIS or Batch script that can identify and send a message or raise a flag once all the files are downloaded.
Hello Experts

I have developed a package that works very well in development. I do 2 things
1. I publish it to the SSIS server
2. I call you through a job

In the execution of the job falls for a subject of permissions
This is the error
Error: 2017-08-12 13:53:28.56
   Code: 0xC0016016
   Source: DWH 
   Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Clave no v lida para utilizar en el estado especificado.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
End Error
I'm looking for recommendations for the best training options for SSIS. I know there is a Microsoft class, but I'm hoping to find either one-on-one training (I would pay) or an official course (my firm would pay) that allows me to focus on a real world project. I need to quickly learn how to use SSIS to transmit via SFTP several files (wait for each to completely copy over) and then transmit an acknowledgement (.ack) file - and update a local database table with status and any errors for each file processed.

I'll need the same thing for pulling files from the SFTP site and saving them locally.
Hi ,

I am trying to run command script of SSIS Package on powershell but it is getting me error , although ps1 file is present in that folder
I have a requirement that I need to load an Excel file with multiple tabs into the SQL Server table.

The Excel file contains multiple tabs with each month of data in it. I should treat the current month tab like a daily month file and import the full tab each day as a truncate and reload.

Truncate and reload raw tables for current tab and last month tab.

How do we do dynamic tab variables for identifying ingestion in SSIS?

FYI.. TABS in the Excel file are named Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec.

These tabs are named at random - not in a sequence..

Can anyone please guide me?

FYI..I wanted to load just 2 months data at the same time not all months. For Instance,
This is August so i would like to load July and august data every day. I truncate the table and reload every day.
When i am in September then i should load Sep and Aug data.

Appreciate your response.
Here is the example how tabs are arranged.
Jan, Feb, Mar, Apr, May, Jun, Aug,Jul,  Sep, Oct, Nov, Dec.
Free Tool: Path Explorer
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Hi All,

I'm having an issue with SSIS 2012 Look up transformation . I'm querying 4.5 million records from source system. Then I will need to compare them with a destination table  (another 4.5 million records) to perform insert for new records and update for existing records.

while running this ssis package my c drive getting full.

i am using lookup full cache mode.

kindly help me to resolve this issue.

Thanks in advance.

Warm Regards

Ganesh Yerme
Hello All,

We have SQL 2014 with Ent edition and our DB size is 340 GB. Can you please review and suggest below SQL memory settings are correct or do I need to change min or max memory settings.

Physical memory : 256GB
Min memory: 64000 MB
Max memory: 216000

We have dedicated DB (VM machine) and on same DB we are running SSIS, SSRS.

In DB server-->task manager-->we always see sql server management studio.exe, the memory utilization is 98-99%. Is there an way to reduce this value by memory settings?

Thanks in advance.
I need to fetch a description based on ,maybe, two lookup in SSIS.

So I have first to check in the sub-category table with look-up to fetch the category name based on key,

else check at the category table with a lookup . I used a 2nd lookup in the no match output, but that's wrong because I get duplicates [if there is a join in both match outputs]. Any advice?
Can anyone give me step-by-step instructions on how to just get this task to successfully execute my stored procedure.  The error messages are getting worse
  • "Value does not fall within the expected range.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly"
  • "The query failed to parse. Attempted to read or write protected memory. This is often an indication that other memory is corrupt."
  • "The EXEC SQL construct or statement is not supported."

At this point, I'd just like to get the stored procedure working.  All it's doing is having a parameter string value passed into it from a user defined variable in the package and then assigning a value to the SP output parameter which then gets assigned to a different package user variable.

My environment:  Windows 7 Professional, SQL Server/Visual Studio 2005

Any suggestions?
Hi, hoping somebody could give me a quick hand with this. Normally I would try figure it out on my own but I am on a very tight time scale and its not something I would usually deal with.

We have been given an XML file that has 2 namespaces, which I have read SSIS will not support.

I want to use an XML task to strip the first namespace. How would I go about doing this?

I have created an XML Task in SSIS, in the input I have put the link to my XML file, then in the output I just left it to "Save operation Result"

In the Second Operand I have put direct input and typed in exclude-result-prefixes="ms ns xsi"   However I am getting an error when I run the code saying it cant find any XML.

Any help / point in the right direction would be really appreciated.
I work in one of the largest health insurance company, in BI/Datawarehouse development. Currently we use Microsoft SQL Server Integration Services as ETL tool. Now we are plan to change or use the same etl. I participiate in an evalution process, where the options are to use Talend or change to Informatica 8.6 or Microsoft SQL Server Integration Services. Are there anyone out there that have had the oportunity to work with (or evaluated) two (or all) of these, and could give me some key points for consideration?

Our Databases:

Current ETL tools:
Microsoft SQL Server Integration Services

Front end
I am having a problem with an SSIS package that I am running with a scheduled task with SQL Agent.  If I run the package manually with SQL Server Information Services it works, but when I automate the task with SQL agent it fails. I am using SQL Server 2008.
I created a VM to replicate my server in production with the same SQL Server version and the task with SQL agent does not fail, but in the other server fails. The server in production is running other tasks with the agent that runs successfully, but this new task fails.
Attached is a sample of the data the SSIS pkg is converting to fill out 4 tables: Employees, OU, EmployeeGroup, and Groups.
I also attached the error that I am getting.
I can figure out why the pkg runs successfully if I run it manually, but fails when is automated to run with the SQL agent.

Any help would be appreciated.
hi I'm taking destination as a lookup reference I taken ID in reference table and nomatch ouput map to destination ,partial cache mode I'm using ,I'm getting duplicates to destination
I created the script component and when I tried to reopen it after couple of days I get the below error. See attachment. Is there any other setting that I am missing.  I also found that the ScriptMain.vb file is pointing to a temp directory. Any suggestion.
New feature and membership benefit!
New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Is there any way we can compare Name vs (ShortName or LongName)?
I'm trying to use a lookup transform in SSIS to lookup a column (EMAIL) if the SOURCE doesn't match the destination, I want to add the row, else skip.

Best way ???
I am facing the issue when loading .dat file to sql table and the .dat file format is like
"V","02E300058PU00E","","GEARBOX "," ","N","Y"," ",01500.00,01500.00,01500.00," "," "," ","","N","001"
"B","02E300058PU00P","","GEARBOX "," ","N","Y"," ",00750.00,00750.00,00750.00," "," "," ","","N","001"
"B","02E300058PX00E","02E300058PX00P","GEARBOX "," ","AKL","Y"," ",03150.00,04199.99,04199.99," "," "," ","02E300058PU00E","N","001"

i an using the only few selected column fields column 1,2,4,9,10,11 but getting error

[Data Conversion [39]] Error: Data conversion failed while converting column "Copy of Column11" (110) to column "Copy of Column11" (63).  The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

[Data Conversion [39]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "Data Conversion.Outputs[Data Conversion Output].Columns[Copy of Column11]" failed because error code 0xC020907F occurred, and the error row disposition on "Data Conversion.Outputs[Data Conversion Output].Columns[Copy of Column11]" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
I tried from flat file source and using data conversion but after that unable to load data in stagging table.
Hi All.

I have an Excel XLS file which we would like to import to a database using SSIS.

The format of the file is as per Attachment 1
I would like to convert it to as per Attachment 2

Any ideas on the best way to achieve this?
Thanks in Advance
I'm using Visual Studio 2015 and want to add the Google.Apis.Translate.v2 Nuget package to Script Task. I receive an invalid arg error when trying to launch the manager.

Instead, I installed the package manually. While everything looked ok the project will not build complaining that NuGet pacakges are misssing on this computer.
I am using C# in SSIS Script Task and PGP Command Line tool for decrypting symmetric encrypted pgp file.
But this is not working, the command window appears and then hides showing a message:

pgp:decrypt < 3001:input file not found>
:decrypt <3090:operation failed, had parameters>

What is wrong with the code?

System.Diagnostics.Process p = new System.Diagnostics.Process();
                p.StartInfo.WorkingDirectory = @"C:\Program Files\PGP Corporation\PGP Command Line";
                p.StartInfo.FileName = @"C:\Program Files\PGP Corporation\PGP Command Line\pgp";
                string args = @"/c pgp --decrypt ""G:\MYHR\Payroll\PGP\ENCRYPTED\"" --symmetric-passphrase ""Example1"" --overwrite remove --output ""G:\MYHR\Payroll\PGP\DECRYPTED""";
                p.StartInfo.Arguments = " " + args;
                Dts.TaskResult = (int)ScriptResults.Success;

Open in new window



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.