Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

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

I want to execute a simple "truncate table" command, but can't find the "execute SQL" task in this new version. Please help.
0
Free Tool: Port Scanner
LVL 10
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Hi,

I want to see the chart for the specific SQL report. But it is not displaying. Here i have attached the screen shot.

Thanks

Zahid
RPT_Design.PNG
Report.PNG
0
When i upload reports with subreports do those have to be uploaded to or are they embedded into the code of the main report?
0
I hope to spend some time digging into SSIS and wonder what are the most useful aspects of SSIS?

Thanks
0
Migrated web application from one sever to another. couple of folders in web app have rdlc files.
When trying to browse on the browser <ip-address>/Reports/default.aspx , it prompts with windows username and password.

Then it displays error on browser saying

Description: HTTP 404. The resource you are looking for (or one of its dependencies) could have been removed, had its name changed, or is temporarily unavailable.  Please review the following URL and make sure that it is spelled correctly.

I can see on the web application drive the reports folder and default.aspx exists along with rdlc files. What am I missing here?
0
How can we extract data from linked tables in an access DB using SSIS?
0
I'm running SQL Server 14 (x64) on a Windows 7 Pro (x64) computer.

I've just spent the last two days resolving issues which prevented me from uploading a CSV file into SQL Server and finally got all of the server settings and syntax correct, only to find that because I'm using the Microsoft.Ace.OLEDB.16.0 driver it will only upload 255 columns.  Unfortunately, I'm getting these csv files, many of which contain over 500 columns from a client and need to be able to read these into a SQL Server database.

So, what I need to figure out is:
1.  Can I create an SSIS package which will upload a CSV file with over 255 columns into SQL Server.
2.  Can I create a script that will allow me to pass the name of the file (varies each time) to SSIS, would then execute the SSIS package, and then return the list of column names to my calling application?
0
I have created a package that uses CDC. We are running SQL Server 64 bit Standard Edition. We have applied SP 1 to release the functionality of CDC for  the Standard edition.

Debugging in BIDS works fine, but when running on the server via schedules task, we get the following error:
CDC Control Task cannot run on the installed version of Integration Services. It requires Enterprise Edition or higher.
Anyone know a way around this?

Any help would be appreciated

Thanks
0
hi im daily importing data from file to table ,new data is inserting and old data is updating. now i want few fields from that table , in this status will change daily so in new table i want previous and current status in same row how to achieve this using ssis
thanks in advance
0
Hi,

I have created an SSIS package that has multiple sequence containers. The whole package works as expected in BIDS. When it runs on the deployment server, when the last sequence container runs I get the 'Cannot acquire a managed connection from the run-time connection manager. I have tried setting the delayvalidation to true but it still fails. I have also removed and recreated the connection manager but still get the error.

Anyone any ideas?

Many thanks
0
Ask an Anonymous Question!
LVL 10
Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

We are implementing a new system that will be interfaced with several of our other internal systems.  These interfaces are being built in house using SSIS.  As this new system is hosted by a third party, the outputted files from the SSIS job are then SFTP’d to the third party for input.

My concern is around the security of this SFTP process, but FTP is something I’m not particularly hot on at the moment.  From the research I have done so far, SFTP is not natively supported by SSIS?

The information I have found from our in-house guys is that the interface file is outputted to one of our network shares, where an SSIS job then SFTP’s it to the third party.  This is fully automated I am told, which I presume means the SFTP username and password are then stored in the SSIS job so that a user doesn’t have to enter the details each time (every night).

What security best practices should we be implementing here, especially around this account name and password being stored in the SSIS Job, is it a concern and how can we control it etc.?
0
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
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
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
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
New feature and membership benefit!
LVL 10
New feature and membership benefit!

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

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

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.