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
Important Lessons on Recovering from Petya
LVL 10
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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
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
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'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
Keep up with what's happening at Experts Exchange!
LVL 10
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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
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
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
hi,
I am getting the following error when.trying to load an sln file into Microsoft visual studio 2012
one or more projects in the solution were not loaded correctly. please see the output window for details.

there is no information displayed in the output window.
any help greatly appreciated,
thanks
0
Hi

I have inherited an old database which uses a MS Access Frontend to Import data into a SQL Backend via SSIS package .
It appears the connections to SSIS in the following Access DB Vb script fails as it only complies with TLS1.0 and not TLS1.2 as desired when using web based access.
Code:
cnn.ConnectionString = "Provider=SQLOLEDB;Data Source=MySQLData;Initial Catalog= MyProject;User ID=MySSISImport; Password= XXXXX;"
    cnn.Open

To resolve this to be TLS1.2 compliant should I be looking for
1- A security patch for SSIS
2- Re-write connection string an alternative way

Thanks
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
Free Tool: Subnet Calculator
LVL 10
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.

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
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
I need run the python py file into the ssis package using execute process task
0
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 a requirement that I need to load an Excel file with multiple tabs into the SQL Server table.

The Excel file contains multiple tabs with each month of data in it. I should treat the current month tab like a daily month file and import the full tab each day as a truncate and reload.

Truncate and reload raw tables for current tab and last month tab.

How do we do dynamic tab variables for identifying ingestion in SSIS?

FYI.. TABS in the Excel file are named Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec.

These tabs are named at random - not in a sequence..

Can anyone please guide me?

FYI..I wanted to load just 2 months data at the same time not all months. For Instance,
This is August so i would like to load July and august data every day. I truncate the table and reload every day.
When i am in September then i should load Sep and Aug data.

Appreciate your response.
Here is the example how tabs are arranged.
Jan, Feb, Mar, Apr, May, Jun, Aug,Jul,  Sep, Oct, Nov, Dec.
0
Hi All,

I'm having an issue with SSIS 2012 Look up transformation . I'm querying 4.5 million records from source system. Then I will need to compare them with a destination table  (another 4.5 million records) to perform insert for new records and update for existing records.

while running this ssis package my c drive getting full.

i am using lookup full cache mode.

kindly help me to resolve this issue.



Thanks in advance.



Warm Regards

Ganesh Yerme
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.