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

Hi there,

thank you for reading this ,  i have a light SSIS package . It runs without any errors in the visual studio 2012 in  dev environment.  but once it gets deployed to test environment   using proxy account with full permission access ran the package in SSMS 2012 . it shows error below .  Does anyone have any ideas ?  Thank you .

DataFlowTaskError: there were errors during  validations
DataFlowTaskError: Failed validation and return status "VS_ISBROKEN"
DataFlowTaskError:  one or more component failed validation
DataFlowTaskError: Microsoft SQL server Client 11.0  hresult:0x80004005
Description :Syntax error, permission violation , or other non specific error
0
Announcing the Most Valuable Experts of 2016
LVL 6
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

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?
0
Hi,

Can anyone help me regarding scripts for monitoring SSIS and SSRS (SQL Server 2012/2014/2016).

-- Blocking issues
-- SSIS and SSRS Services status
-- SSIS Packages Performance
-- SSRS reports Performance
-  CPU/Memory/Disk IO utilization.

Thanks,
Chandra
0
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?
0
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.
0
Hi All,

Could you please advise how to achieve below requirement (using SSIS or any other methods)

Requirement:
I have T-SQL Server query and Oracle SP (SP is located on Oracle DB).

Now I want to update records on Oracle DB table using Oracle SP. Before updating records on Oracle, 1st SP validates data of MS SQL query output (if record is exist in SQL server and Oracle table (or if matches on both), then update oracle DB table else ignore...this is logic of SP)

Can you please tell me how to design SSIS package for above requirement in SQL 2014.
0
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:
MSSQL


Current ETL tools:
Microsoft SQL Server Integration Services

Front end
.Net/Salesforce
0
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.
Sample.txt
Error.JPG
0
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
0
I am not able to see the MSDB Folder in the SSIS Stored Packages. We are using SQL 2014 64-bit. In the Database Folder Databases\System Databases\MSDB\Tables\System Tables, I see the table dbo.sysssispackages but no table contents. In our SQL 2008 32-bit I can view the contents and all the MSDB tables are available to view and the Stored Packages MSDB is visible . In SQL 2014 64-bit, that is not available.

Do I have a corrupted MSDB in 2014? How is the available and yet no editing or adding data to the table? How do I find out if the MSDB needs to be repaired?
0
On Demand Webinar: Networking for the Cloud Era
LVL 9
On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

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.
6-23-2017-4-38-45-PM.png
0
I have a SQL Server 2012 DTS package that receives data from AS400. Before these three days, everything was going well.

But suddenly when I tried to run these error messages appeared:
1. SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on AS400 returned error code 0xC02090F5.  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.
2. The AS400 was unable to process the data. CWBCO1054 - A user-specified time-out occurred while sending or receiving data.

Sometimes when I run it manually, it ran perfectly, but sometimes the error messages appeared. I need to run it several times till the error didn't show up. Different when I ran through batch file (task scheduler/sql job agent), I always got fail.

I already checked the connection by test the credentials, ping and telnet to the server. It's okay.

Any idea why this error is coming up? I appreciate any assistance from everyone :)

Thanks a lot
0
Is there any way we can compare Name vs (ShortName or LongName)?
0
Hi,
Is there any free trail sql server free download with ssis for personal use.( need ssis too)
Thanks,
0
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 ???
0
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.
0
Can somebody tell me the best way to compress files hoin SSSIS as part of the package flow?

is there a task?
0
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
Raw-Input-File.XLS
Output-File.XLS
0
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.
Error

Instead, I installed the package manually. While everything looked ok the project will not build complaining that NuGet pacakges are misssing on this computer.
0
Free Tool: Subnet Calculator
LVL 9
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

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\ECMC.TUR.PayrollExtract-Transactions.504001631112121.zip"" --symmetric-passphrase ""Example1"" --overwrite remove --output ""G:\MYHR\Payroll\PGP\DECRYPTED""";
                p.StartInfo.Arguments = " " + args;
                MessageBox.Show(p.StartInfo.Arguments);
                p.Start();
                p.WaitForExit();
                Dts.TaskResult = (int)ScriptResults.Success;

Open in new window

0
SSDT   Visual Studio 2010 shell.   I am not able to save a copy of an SSIS package to  SQl server--2012 database.  I get save a copy option alright but only to a file system.  I am trying to save to a SQL server.
0
i have two oledb destinations after this im loading error data to text file and iam using union all for combining data im getting below error
[SSIS.Pipeline] Error: SSIS Error Code

DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on

component "Union All" (990) failed with error code

0x80004005 while processing input "Union All Input 3"

(1198). The identified component returned an error from the

ProcessInput method. The error is specific to the component,

but the error is fatal and will cause the Data Flow task to

stop running.  There may be error messages posted before

this with more information about the failure.
0
Good Evening All - I am still learning SSRS and wanted to know how I can setup an page so the users can see all the reports they can request?  In the past I would create a form in Access with check boxes for my different reports and have some text boxes, combo boxes, and list boxes for variables to pass along to my report,  I would like to have something like that in SSRS.  Can i do this?  Or do I just create a web page that has all of my reports and a link to the report and then the users just file out the variables at the top of the page.  I would like to customize it more than this.  I do not like that I cannot format or show the control like I would like the to appear.  Seems like a step back.

TIA,
Rodger
0
Hi,

I'm using an OLE DB Source Editor to extract data from a sql server table  into another sql server table (not on same server).  On one particular tbl the connection takes a long time (validation).  When I run it in a job it errors out with error 'connection lost' or query time out.  It just happens on one table and there's only 3491 rows.
0
Hello -

I am trying to use SSIS to pull SharePoint lists into SQL to be used elsewhere.  It is SharePoint 2003, but so far the same ideas that work for later versions also work for this version.

I am able to pull down normal SharePoint lists through SSIS.  However, I can't figure out how to pull the hidden User Information List from SharePoint and I need it.

Does anyone know how to do this?

Thank you.

Tammy
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.