Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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

Hi,

I have a file with 2 sheets in it. It has the header information in it. I tried foreach loop container to load the data
Error i got is:
[SSIS.Pipeline] Error: "Excel Source" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

I tried removing the header row manually from the sheets and ran foreach loop container and it worked perfectly fine.

But in my requirement i will be getting the header row in each sheet.
How do i do in this case.

Attaching my sample file

Your help is much appreciated.
Thank you,
swathi
2017-MULTIPLESHEETS-TESTING.xlsx
0
On Demand Webinar: Networking for the Cloud Era
LVL 10
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.

Hi EE,

I get the following error see attaching when trying to open the MSDB folder on a SSIS instance.

Tried to fix using the following post but no luck.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/59fdf431-9cfc-4ecd-9001-71f65e50e27b/ssis-installed-on-64-bit-sql-clustered-node?forum=sqlintegrationservices

Attached is the current  MsDtsSrvr.ini.xml file I am using.

Any assistance is appreciated.

Thank you.
Error.PNG
Xmlfile.PNG
0
I have 2 different types of XML files called Install and Uninstall.

Install I have the XML file, generated the XSD and then ran the file through and it imports the data fine.

Now the uninstall one is very similar to the install, mostly the same columns 1-2 different, I have created it in exactly the same way, generate the XSD and then run my job but no data is transferred from the flat file source, there are no error messages and no information.

I am at a bit of a loss here as to why this could be happening. Does anyone have any ideas what could be causing this?

Looking at both XSD and XML files I cant see any structure problems.
0
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service does not exist as an installed service'”.
0
Hi.  Do you know how I can translate the .vbs to vb.net for SSIS 2008?   There’s a lot of logic in the .vbs files so I am concerned about not translating it right.   Please provide specific examples of a .vbs and the end result SSIS .dtsx package so I can follow, not just guidelines.

I am using:
-  Visual Studio 2008
- SQL SERVER 2008R2
0
Hi,

I am currently working on a data warehouse project. I have used CDC to detect changes in all required table and this data is then loaded into the staging area. I am using SSIS to load the Dimension table into the live ware house but have hit an issue with audit numbers.  The Business keys in all of the dimension tables are a combination of an audit number and one or more codes. For example the Dim_Reporting table has a business key of an audit number and a reporting code. tHe most recent entry would have the highest audit number. How would I load the fact table with the surrogate key for most recent entry?   In the source tables, when an amendment is made, a new entry is created and given the next audit number.

Any assistance would be appreciated.

Many thanks
0
I am a newbie to SSIS.

I have a Foreach loop that is feeding a data flow task that processes flat files from a folder.  I have a failure path that does a file system task and moves the failed file to a \ErrorFolder.  I want it to process the next file in the loop after it moves the file out, but the loop stops processing.  How can I make it process the next file just like it did on success?

Thank you
0
I set breakpoints all throughout my script task but SSIS is not stopping at them.

I have already changed my  SSIS Project's "Run64BitRuntime" propery's value from its default True to False.

Any other suggestions?
0
I have been tasked with moving some SQL Server reporting cubes from Server A in Datacenter A to Server B in Datacenter B and want to make sure I'm not missing anything (this is way outside my usual ball park of operations - I'm mostly an Oracle guy).

So far I have identified:

* There is a pair data warehouse databases that need moving - Simple DB backup and Restore for initial setup should work
* There are 4 SSAS Cubes.  I believe this is a backup of each cube and restore on new server
* There are several SSIS packages that need to be exported individually and re-imported into the new server
* There are several SQL Server Agent jobs that refresh these cubes that will need to be copied to the new server

Are there any other steps I need to be aware of or pieces that might need to be moved that I haven't listed above.

Thanks
0
New to SSIS

I have a folder of flat files that need to be processed.  I learned Foreach loop container yesterday.  I need to pass those files from that location into my data flow task.  I have my data flow working now with a single flat file connection.  The first component is a data conversion.

Can someone point me to a tutorial web page or youtube video that demonstrates this?
0
Enroll in September's Course of the Month
LVL 10
Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

in the sql command text in my OLE DB Source  I have a multiple joins related to the database that my connection manager is set up for but I have one join where it points to another database like this

INNER JOIN [TEST SERVER].databasename.dbo.TABLE

The problem is that in the production server is named something different. How would I make it so that I can move the package to the production server without having to change code
0
ODBC setup successful; however, connecting from SSIS using ODBC trigger the following error message.


Error [42968][IBM][CLI Driver] SQL1598N. An attempt to connect to the database server failed because of a licensing problem. SQLSTATE=42968.
0
hi Experts,

I have updated the variables ( recipient emails ) in SSIS  package. When I run the package, the ssis can send the report to the updated recipient email correctly. But, the recipient can not get the report automatically from sql job. I thought the tab "set value" which should capture the recipient email (see in the attachment ) and there was nothing there.

 Where can I change the recipient email in sql job and have the recipient to receive the report automatically.

Thank you
Capture.PNG
0
i created a variable in ssis now i want to give it the value YYYYStaff_Files_MMDDYY.xlxs.  How would I do this? What task would I use?
0
is there an ssis task to move and rename a file in one step?  If not what task should be used?
0
Hi all,

I have a ssis project with 3 ssis packages, one is a parent package which calls the other 2 packages based on some condition. In the parent package I have a foreach loop container which will read multiple .csv files from a location and based on the file name one of the two child packages will be executed and the data is uploaded into the tables present in MS SQL Server 2008. Since multiple files are read, if any of the file generates an error in the the child packages, I have to log the details of error (like the filename, error message, row number etc) in a custom database table, delete all the records that got uploaded in the table and read the next file and the package should not stop for the files which are valid and doesn't generate any error when they are read.

Say if a file has 100 rows and there is a problem at row number 50, then we need to log the error details in a table, delete rows 1 to 49 which got uploaded in the database table and the package to start executing the next file.

How can I achieve this in SSIS?

Thank you
Nagesh Kumar
0
I need run the python py file into the ssis package using execute process task
0
HI,
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
0
how do I join two queries, join them HORIZONTALLY, i.e. extra columns, second columns query 2 to right of first query



--query 1 OUTPUTS
SELECT a.timeStampKey, t.timeStamp,MAX(CASE WHEN a.PIE2_O_ID = 1 THEN value END) AS 'K_DESIGN_SG_A_AVERAGE_A', MAX(CASE WHEN a.PIE2_O_ID = 2 THEN value END) AS 'K_DESIGN_SG_B_AVERAGE_B'FROM   tblOutputs as a INNER JOIN       tblTimeStamp as t ON a.timeStampKey = t.timeStampKey GROUP BY a.timeStampKey, t.timeStamp
--query 2 INPUTS
SELECT a.timeStampKey, t.timeStamp,MAX(CASE WHEN a.PIE2_I_ID = 10104 THEN value END) AS Flow, MAX(CASE WHEN a.PIE2_I_ID = 10006 THEN value END) AS Head FROM   tblInputs as a INNER JOIN       tblTimeStamp as t ON a.timeStampKey = t.timeStampKey GROUP BY a.timeStampKey, t.timeStamp
0
Free Tool: IP Lookup
LVL 10
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

Thanks
0
I have installed SQL Server 2016 with  all the features, but when I open the management studio I am unable to connect to SSIS, all other services are availble and the SSIS  service is running. I read an article which suggested using Dcomcnfg.exe to add users to the permission for integration services which i have done. still unable to connect though

SQL server connector - Browsing for servers
0
I can not send mail through the SMTP connection manager (send mail task). System.net.webexepction

attched file
www.png
0
Hi,

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.

Regards
Christian
0
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.
0
Hi ,

Please provide detailed procedure for In-Place Up-gradation from MS SQL Server 2012 to MS SQL Server 2014 include MSBI Components like SSIS Packages, Cubes/OLAP Databases and SSRS Reports.

Please consider this high priority.

Thanks,
Chandra
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.