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,
I am trying to load info from one table directly into a new table (replicated in the same way), so I can keep an archive , since the org table clears out every 30 days.
I setup a simple ETL flow, but it just hung for hours.
So I wrote an insert statement inT-sql and this took some time, but after hours did run.
so I rewrote another insert statement using top 150k entries per load and this seems to run faster.
so I thought I would use a query insert on SSIS and tried this, but the same query still hangs for hours rather than running in seconds as it did on management studios.

I'm not sure what to look for her, can anyone please advise?
thank you,
Elaine.
0
On Demand Webinar: Networking for the Cloud Era
LVL 9
On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

We are importing (Bulk Insert) of around half million records to the staging table and then to the production table.

More often column data type or size validation fails the bulk insert process from Staging table to the Production table.

I will have to redesign the process by SSIS packages by inserting all the good records from Staging to the Production and load the bad / error out data to the staging table. Those bad or error out data with proper validation message.

Validation message is based on first error out column. If the all the columns in that row are erroring, we can consider with the first column error and reload the data after fixing that data.

Example Error Message: Column PurchaseDate has invalid data

How do I achieve this complete workflow SSIS package. What are the controls, I need to use to start with?

Please :)
0
We are importing (Bulk Insert) of around half million records to the staging table and then to the production table.

More often column data type or size validation fails the bulk insert process from Staging table to the Production table.

I will have to redesign the process by SSIS packages by inserting all the good records from Staging to the Production and load the bad / error out data to the staging table. Those bad or error out data with proper validation message.

Validation message is based on first error out column. If the all the columns in that row are erroring, we can consider with the first column error and reload the data after fixing that data.

Example Error Message: Column PurchaseDate has invalid data

How do I achieve this complete workflow SSIS package. What are the controls, I need to use to start with?

Please :)
0
 
LVL 11

Administrative Comment

by:Andrew Leniart
Hi chokka,

What you have done is made a "Post" here. To get help from the experts, you need to "Ask a Question" so that more experts are able to see that you need help. Click the Big blue button near the top of your screen.

Ask a Question

The following link also explains more about asking for help at Experts Exchange..
http://support.experts-exchange.com/customer/portal/articles/336330

Hope that's helpful.

Regards,
Andrew
EE Topic Advisor
0
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.
0
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
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
Hello, In a google search, I come up the knowledge that the MS SSDT, which is the SQL Server Data Tools.  So I downloaded SSDT.
I installed SSDT trying to get to the SSIS, which is the SQL Server Integration Services.
I can't find it.  Where is it?
I even have the SSMS and it is not there.
thanks
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
Free Tool: ZipGrep
LVL 9
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

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
Hi EE,

I have got a source table with a column of data type Nvarchar(max) and in my lookup dimension i have got a column with the same data type as Nvarchar(max). When i try to map those columns i get the following error message.
Cannot map the lookup column, 'AdditionalInstructions', because the column data type is a binary large object block (BLOB).

Open in new window


Can you please help.

regards,
0
hi experts

i am reading about Upgrading Data Quality Services:
DQS schema must be upgraded as a separate step

what's the mean DQS schema
0
Hi EE,

I have got a scheduled job which populated the data warehouse. There are a wrapper packages in the job and wrapper package has sub-packages.  until 28/06 everything has been fine but it started failing. when i look into the errors its very confusing.
Sometimes it fails on first sub package but other times it fails on another sub package. i have tried running it from Visual Studio as well and same thing is happening that is it can fail on any sub package but when i run that package again it might succeed or might not. It might succeed on 3rd fourth attempt and keeps giving me different errors on different packages.
The server is SQL Server 2014 SP2. The server is windows serrver 2012 R2 and i have applied all the updates too.

The errors i get on different times are below

[Update [2]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "LegacyDW Staging" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Open in new window


[WebBooking Staging [357]] 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: "Login timeout expired".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Unable to complete login process due to delay in prelogin response".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "TCP Provider: Timeout error [258]. ".

Open in new window


Test connection failed because of an error in initializing provider. Login timeout expired
Unable to complete login process due to delay in prelogin response
TCP Provider: Timeout error [258]. 

Open in new window


Truncate table: Error: Failed to acquire connection "connectionname" Connection may not be configured correctly or you may not have the right permissions on this connection

Open in new window


Unable to locate reference column "column name"

Open in new window


regards
0
Hi Experts,
I have one stored procedure to update the control table which contains two fields (Table_Name & Timestamp) to know the details of tables updated through SSIS . But now,I have a new requirement to add one more field (ie.Latest Data) which is a date field  to get the max(date ) from each table everyday.These are  for daily sheduled job.After the execution of each job,the stored procedure for update control table is called through "EXECUTE SQL TASK" in SSIS.So now,I want to pass the result of  an sql query (select max(date) from order)  as a parameter through execute sql task .Already a parameter is there to pass the table name to stored procedure.

Thanks in Advance
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
Want Experts Exchange at your fingertips?
LVL 9
Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

I have SSIS package and it runs every hour. This package import data from csv into database table. From march 2017 this package has been failing. What i see there is a SQL task step which is failing but before it was succeeded. Here is the below code:-- I put the four *** sign where it is failing.


declare @toparse nvarchar(max)
declare @strlength int
declare @separator_location int
declare @instance nchar(10)

declare @instance_url nvarchar(max)

declare @hostname nvarchar(50)
declare @strIn nvarchar(2000)
declare @separator nchar(2) = ' '
declare @inner_separator nchar(1) = '|'

DECLARE @item nvarchar(1000)  -- assume maximun item length 1000 char
DECLARE @len int, @sepPos1 int,  @sepPos2 int

-- truncate table staging_oracle_version
--declare @truncate varchar(200)
--set @truncate = 'truncate table staging_oracle_version'
--exec (@truncate)

-- get oracle instances to load
declare  oracleinstance_csr cursor for
select certname
      , value
from puppetdb_certname_facts
where fact like 'oracle_instance_url'
   and value IS NOT NULL
   and value !=''
  and value != '""'

open oracleinstance_csr

fetch next from oracleinstance_csr into @hostname, @strIn

--select @hostname, @strIn

while @@FETCH_STATUS = 0
begin

  SET @strIn = REPLACE(@strIn, char(10), @separator)  -- Replace all the LF (char(10)) from the string and replace with defined separator [ the code for CR is char(13)]
  Set @sepPos1 = 1
  set @sepPos2 = …
0
Dear All,

While updating he particular table i am getting this issue.
i am using sql server 2102 sp1
the update command is running through ssis package.

Update top (20000) [SQLMPDB].MPLokSewa_PSM_New_01.dbo.Trans_app_Log set INSERTFLG ='M' from [SQLMPDB].MPLokSewa_PSM_New_01.dbo.Trans_app_Log t, [SQLMPDB].MPLokSewa_PSM_New_01.dbo.Transfer_Reg_no R where t.Reg_no=R.Reg_no and (INSERTFLG <>'M' or INSERTFLG is NULL)

i have check with nolock also still i am getting same issue.

Please help suggest me.

Regards
Ganesh Yerme
0
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
Are there any list of requirements that must be in place for SSIS to show up in the program application list below. I would assume either a database must be present or is it a stand alone product in the list provide that I can point to a specific database to work on.

One of the members said Development licenses. I cannot afford one at the moment is there a trial or a way to get a free temporary license?
I think I just need as simple as I can get. I have weeks before I would start if I do get this opportunity. I think it will be time enough for me to catch up and get back into the swing of things. Reporting is what I do anyway. This is just a tool to clean up the data enhance performance and produce a result set to report off of.
SQL-Server-2016.jpg
0
Is there any way we can compare Name vs (ShortName or LongName)?
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.