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 have to create a csv file nightly that captures various data for our patients based on the visit type. Because the queries do not easily combine into one, I have 4 basic queries. I need to run the first one and use that unique patient identifier for subsequent queries but also save the output of each of those to a file or multiple files organized by patient.
Once it finishes the first patient it needs to loop through the remainder of patients that had that visit type for that day and go through all 4 queries as well.
If there were a logical way to combine these queries that would be great but I'm not really sure there is.  
I'm attaching a spreadsheet with the tables involved and the queries.
I'm not sure this can be done so any guidance in getting it to a point where it can, even if multiple steps or SSIS packages etc. is fine. Thanks
Queries_tables_rev2.xlsx
0
Free Tool: Path Explorer
LVL 10
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.

I need an output file(s) that selects from a single Intake table/template in our EHR from a single most recent visit that equals @CurrentDate. However this single row return also needs to include all the meds, allergies and problems they have. Can this be done as a single output file? Once I have this I need to utilize it in an SSIS package to loop through all patients that meet the criteria.


I can include detail of the tables but for now just not sure how to even go about this. A UNION, or nested select statements? Not sure.  This can be done as separate files as well that breaks out meds, allergies etc. so that each one saves to a csv file with the unique patient identifier tying them together. Maybe a temp table could work as well.

Appreciate any help.
0
Expert Advice (Kent Olsen)
me to use for adding Frieght to FactInvoiceDetails table

CREATE TABLE DimItemType (
  id   integer IDENTITY,
  TypeName varchar(30)
);

And populate it with the possible entry types:

INSERT INTO DimItemType (TypeName) SELECT 'DATA'
INSERT INTO DimItemType (TypeName) SELECT 'FREIGHT'
INSERT INTO DimItemType (TypeName) SELECT 'SALES TAX'
etc...

Add the column ItemTypeID (integer) to the FactInvoiceLine table.
When an item is stored into FactInvoiceLine, set the ItemTypeID value according to the data type.

My questions is :

how to combine FactInvoiceDetails Table and DimItemType and i have created view to transfer to FactInvoiceheader Table through
SSIS. (OLEDB source)

01. Following View is correct? or any issue
02.Then how to add the ProductID for the FactInvoiceDetail Table, becuase when i'm adding extraline for
Frieght and Tax
CREATE VIEW DW_FactInvoiceDetails
AS

SELECT invhdr.invNumber,0 AS InvLine,0 AS Qty,invhdr.FREIGHT_AMOUNT AS price,2 AS ItemTypeID --FRIEGhT is 2
FROM Invoiceheader invhdr
WHERE FREIGHT_AMOUNT <>0 --May be - also possible

UNION ALL

SELECT invLine.invNumber,invLine.InvLine,invLine.Qty,invLine.price,1 AS ItemTypeID --DATA is 1
FROM InvoiceLine invLine


UNION ALL

SELECT invLine.invNumber,NULL AS InvLine,0,invLine.Sales_Tax AS price,3 AS ItemTypeID --SALES TAX is 3
FROM Invoiceheader invLine
WHERE invLine.Sales_Tax NOT IN(0)

GO

Open in new window

Any comments greatly appriciated.

Many thanks
0
Hi,

I having little bit experience on SSIS,SSRS & SSAS. I want to get advanced knowledge on this.

Please provide some realtime scenarios with solutions?
0
Hi,

In a excel source, i have a field with values like 12,232,432,0342,0234 etc.

While loading the data to SQL table, the data is like 12,232,432,null,null.

I used IMEX=1 property also, but no use.

Thank you.

Saanvi S
0
If a patient has a certain type of visit I need to extract all their current data and send it to a file for all patients who have that visit type. I will pull mostly from one table that has their intake info, however I also have to pull all medications, problems and allergies. That makes it something where it would be difficult to just do joins on multiple tables. There could be 20 meds and 5 allergies etc.
What is the best method of doing this. I'm not a SQL expert. Maybe a stored procedure that uses multiple temp tables but that might be beyond my skill level.
I have created SSIS packages that pull data and dump to a file but in this case it gets much tougher because I have to run the job or procedure and loop it until there are no more patients that day that had this visit type and pull have it pull all their data and dump each to a file. I'd like to pull each file and name it with a medical record number and drop it into a folder that houses them all so that it could be pulled via sftp. Thoughts?
0
Hi,
I'm trying load fact table compare with DimSalesperson . Fact data coming for Different Database and server.The DimTables coming from Different Database and Server?
I wrote query to match but just wondering how create connection with 2 server and 2 database
Example

SELECT s.Person, c.SKSalesPerson
FROM   [DWServer].[Database1].dbo.dimSalesPerson c
      [DBServer].[Database1].dbo. FactSales s
WHERE  c.SalesPerson_id = s.SalesPerson_id

This servers and 2 phisical locations.(May be Virual servers)
Without using Link servers
0
I'm not able to run Job thru SQL server agent, I have given full permission but still no success.

error states, "Access denied & invalid xlm file"
0
http://blog.extreme-advice.com/ Backup Linked Server script in SQL Server by SSIS

Should I be receiving this error and what is the work around.  Thanks much!

I copied the code from the blog into the sql command.  When previewing I get the following error:

Error at data flow task ssis error code dts_e_oledberror.  An ole db error has occurred.  Error code 0x80004005. An ole db record is available.  The metadata could not be determined becuase the statement 'Exec (@SQL) contains dynamic SQL.  Consider using WITH RESULTS SETS clause to explicitly describe the results set.  

The code copied into the sql command
SET FMTONLY OFF
Declare @SQL Varchar(Max)
Set @SQL =
'Declare @LinkedServer Table ( LinkedServer Varchar(Max) )
Declare @status smallint,
@server sysname,
@srvid smallint,
@srvproduct nvarchar(128),
@allsetopt int,
@provider nvarchar(128),
@datasrc nvarchar(4000),
@location nvarchar(4000),
@provstr nvarchar(4000),
@catalog sysname,
@netname varchar(30),
@srvoption varchar(30),
@loclogin varchar(30),
@rmtlogin varchar(30),
@selfstatus smallint,
@rmtpass sysname,
@passwordtext nvarchar(128),
@i int,
@lsb tinyint,
@msb tinyint,
@tmp varbinary(256)

select @allsetopt=number from master.dbo.spt_values
where type = ''A'' and name = ''ALL SETTABLE OPTIONS''

declare d cursor for
SELECT srvid,srvstatus, srvname, srvproduct, providername, datasource, location, providerstring, catalog, srvnetname
from master..sysservers where srvid > 0 …
0
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
6
[Webinar] Lessons on Recovering from Petya
LVL 10
[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

How to get 64 bit SDT tool for visual studio. I'm working on SSIS but it is causing a lot of compatibility issues.
0
I want to execute a simple "truncate table" command, but can't find the "execute SQL" task in this new version. Please help.
0
I'm using an SSIS job to import data from a flat-file (.txt) to SQL Server

The problem I'm having is that the author of the source file sometimes saves it as Unicode and sometimes as 'non-unicode' so the import either works or 'seems' to work but does not actually import any rows.

Is there a way in SSIS to test what format the flat file is (unicode or non-unicode) before attempting to import, based on that I can branch to alternative import steps.
0
I have 3 lakh rows in my excel but SSIS imports only 65000 records. When I try to export Text file which also have 3 lakh rows, it throws the error "count exceeds".
0
I thought this would be easy and I am able to do this with ease in TSQL, but I am struggling in SSIS. I have a flat file (.txt) that has a column named [DATE], this column puts the start_date and end_date together like this: "03/01/2017 - 04/01/2017". I am able to perform the conversion I need with a simple CAST in TSQL like this:
declare @test varchar (50) ='03/02/2017 - 04/01/2017'

select cast(left(@test,10) as smalldatetime) as Date_Start

select cast(right(@test,10) as smalldatetime) as Date_End

Open in new window


I have tried using the [derived column] and [data conversion] in SSIS with no luck. I keep getting this error when trying to insert into the OLE DB destination: "The value could not be converted because of a potential loss of data".

Please help experts....
0
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
SQL SERVER : Microsoft SQL Server 2014 (SP2-CU3) (KB3204388) - 12.0.5538.0 (X64)
OS : Windows Server 2008 R2 Datacenter SP1 (X64)

Hi!

We have in acceptance a SSIS catalog where we found recently that we can’t deploy new projects, last successfully deployed project was 12-Sep-2017.
When we deploy a new project to the server only one symmetric key and one certificate is created for the project and when executing any package from this project we instantly get a result of ‘Unexpected Termination’. We don’t get any error message on the execution report from the catalog. The package is never executed.
If we deploy the same project on top of a previous existing project that had 2 symmetric keys and 2 certificates, the packages run successfully.
We detached the ssisdb and created a brand new catalog from scratch and we re-deployed all our projects (17 total) and only 2 of them created 2 keys and 2 certificates, the other deployments only created 1 object of each and gave the same ‘Unexpected Termination ‘ error.
Finally we undo the brand new catalog by re-attaching the previous catalog.  
The operating system error is :
The SSIS Execution Process could not write to the IS catalog: 
<server name>:SSISDB Error details: Cannot find the symmetric key 'MS_Enckey_Proj_Param_10', because it does not exist or you do not have permission.;
 at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
 at 

Open in new window

0
Hello ,

I have a excel source that has values , specifically having a year and month value in 2 columns. I want to read from that spreadsheet and retireve the year and month value and use them as a parameter to be able to delete records from a SQL table using SSIS to do all of this?

Thanks
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
Hire Technology Freelancers with Gigs
LVL 10
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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

We have created one VBS file to convert the Excel file to CSV format and the given VBS file is being executed through Batch file.The batch file executes with the heop of SSIS Execute Process Task. Everything works fine through SSDT/BIDS but the package is getting hanged continuously if we try to run through SQL Job.

These are the code

 Batch File :
wscript S:\Batch\vbsExceltoCSV.vbs S:\Batch\Client_200817.xlsx S:\Batch\Client_200817.csv

VBS:
Set objArgs = WScript.Arguments
InputName = objArgs(0)
OutputName = objArgs(1)
Set objExcel = CreateObject("Excel.application")
objExcel.application.visible=false
objExcel.application.displayalerts=false
set objExcelBook = objExcel.Workbooks.Open(InputName)
objExcelBook.SaveAs OutputName, -4158
objExcel.Application.Quit
objExcel.Quit

Even when I try to execute the batch file from SQL Job (CmdExec) , facing same issue.

I have tried with both Service and proxy account but no luck.

Could you please help me to identify what could be the issue.

Regards,
Gopi K
0
Trying to use crexport on a crystal report within SSIS and receiving an error, "Error in File, Failed to retrieve data from the database"
I have other reports working fine from the same SSIS job
It doesn't work from the command line, either, so I am assuming it's something in the report
It DOES work from crystal reports, and doesn't have a stored procedure, only 3 tables

Any ideas on what I can check?
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

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.