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 stg_table

sin    lcode
123    01
123    02
123   03

join it with d_code
d_code  desc               d_code_id
-99         unknown         -99
01           usa                   2
02           ca                    3

when i join them using left outer

i get results like below

sin       lcode  d_code_id
123    01            2
123    02            3
123   03            null


i want null to be -99 i try to do in expression isnull(d_code_id) =-99 it is not liking it
0
Free Tool: Path Explorer
LVL 11
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.

Hi,
I have dimension table with identity row dim_id ,
I want to add exception rows in dimensions like
-99 invalid
-98 unknown

how can i do that using ssis .
0
Hi
I have a got a list of files in a folder text file with names like this:
baseFile_03_17_2017 .txt
baseFile_04_17_2017  .txt
baseFile_05_17_2017  .txt

Keep in mind>> there is no date column within each the text file.

 I want to a do a SSIS (SQL Server Integration Services)  loop where I loop through each file and insert it into a stage table which has an extra empty “Reportdate” column at the very end.

The SSIS job then gets the date from the file name and inserts it into the very last “ReportDate” column in that stage table


How do I do that?

Thanks
0
Hi,

I am currently building am data warehouse but I am finding that the reload of the fact table is taking longer than the business finds acceptable. I have been emptying out the fact table on every incremental load and repopulating it. So on each load, we start again from scratch. It worth noting that when a row needs to be changed in the source, a new row is added to the source. so type 2 SCD do not seem to be worth the effort.

Is there any way that we do not have to empty and reload the fact table?

Many thanks
0
Hi EE,

Back again with the lookup issue in SSIS,
SSIS lookup failing with the following errors
[ClientRefLkup [296]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "ClientRefLkup" failed because error code 0xC020901E occurred, and the error row disposition on "ClientRefLkup.Outputs[Lookup Match Output]" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "ClientRefLkup" (296) failed with error code 0xC0209029 while processing input "Lookup Input" (310). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

[Booking Source [2]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on Booking Source returned error code 0xC02020C4.  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 

Open in new window

0
Hi EE,

I am trying to use SSIS lookup but getting the error on second lookup. I have tested with one lookup and it works fine, but as soon as i add up the second lookup it fails and throws errors.

SSIS error
The four error i get are below and i couldn't find any solution for this.
[SupplierLkup [401]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "SupplierLkup" failed because error code 0xC020901E occurred, and the error row disposition on "SupplierLkup.Outputs[Lookup Match Output]" 
specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "SupplierLkup" (401) failed with error code 0xC0209029 while processing input "Lookup Input" (415). 
The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  
There may be error messages posted before this with more information about the failure.

[Booking Source [2]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.


[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on Booking Source returned error code 0xC02020C4.  The component returned a failure 

Open in new window

0
Hi,

I have a very basic SSIS package which copies records from a SQL database table to an Oracle database table.  Although it executes correctly without error, there is a persistent 'WARNING' icon on the lookup component.  The warning is:

[Lookup [2]] Warning: Cannot retrieve the column code page info from the OLE DB provider.  If the component supports the "DefaultCodePage" property, the code page from that property will be used.  Change the value of the property if the current string code page values are incorrect.  If the component does not support the property, the code page from the component's locale ID will be used.

I have resolved this same warning on the OLE DB source and destination components by setting the property "AlwaysUseDefaultCodePage" to TRUE but the LOOKUP component does not seem to have this property.

My question is:
Is there something I can do to resolve this warning on the LOOKUP component in the data flow or is it ok to ignore it as the package executes successfully?

Thank you for your help! (I am new to SSIS...)  I am developing the package using Visual Studio 2010 Shell

This is a screenshot of the data flow so you can see the warning is on the lookup component:
SSIS Lookup Component Warning screenshot
0
hi,

I am using an SSIS package to upload some CSV data to my SQL table.

Here is the error:
SSIS Error

Attached is the table structure (I have numbered the columns so you can see which column 97 is).  See file "SQL-Table-Design.ODS".

Attached is the CSV file that contains the data that is failing: See file "CSV_data.csv".



The datatype of EVERY single field in the table is set to VARCHAR(MAX)  - I understand this is bad database design but I just wanted a 'catch all' structure.
Any help or advice is appreciated.

Many thanks

Stuart
SQL-Table-Design.ods
CSV_data.csv
0
Hi All,

   I'm new to SSIS.
    I have an urgent requirement to do the following:
a) I have a table TBL_FILENAME, which has a coulmn called "CName". This name changes every day.
b) I have another SQL query, which produces an output containing 10 fields.
c)  My requirement is to export the output of the SQL query mentioned in step b)
     into a pipe delimited file, where the name of the file should be "CName" mentioned in step a)
d) Also, the pipe delimited file produced in step c) should have the "CName" before the headers inside the file.

Can someone kindly help me with a sample package to achieve the same?
Thanks much in advance.
0
I have a server running windows 2008 and we installed SSDT 2012. We created an SSIS package from another workstation with SSDT 2016.

When we saved the package as 2012 then opened it on the 2008 server, the data source to the SQL Server 2016 database does not work. We installed the SQL Client Tools 31.1 and we are able to create an ODBC connection to the 2016 SQL Server using the "ODBC Drive 13 for SQL Server", however SSDT won't see the new driver and cannot connect to the SQL 2016 database.

Help please experts....
0
New feature and membership benefit!
LVL 11
New feature and membership benefit!

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

We are moving from SQL Server 2008R2 to SQL Server 2016 which is on a new server my dilemma is that i have 69 maintenance plans that I need to move to the new server. I know I can do it with SSIS by exporting modifying DTX files connection string for the new serve, but with 69 plans that is extremely tedious. Does anyone know of a tool or scripting that will accomplish this? I want to keep the as  Maintenance Plans not just Jobs.
0
curently i am using project parms as
reportingserver: 1.1.0.1
reporting password
reportinglogin: data

now i have moved that ssis package to my localhost for testing and i want to change that project parms as this job
calls another pacakage too and pass that parametrs to that job so that is failing


how i can do that as i have all tables on my localhost

my localhost  looks like below:

server :test

login test/aa
0
I have a very simple data load process that I am running. I loop through text files in a folder on a file share and load the data into a database table. For this example let's say the file contains 3 columns: NAME | PHONE | AMOUNT. I would like to add a column to my DB table named FILE_INSERTED and have that populated with the name of the file.

Example: a file named MY_FILE_20171101.txt has 50 rows of data. I would like the column FILE_INSERTED for all 50 of those rows populated with MY_FILE_20171101.txt.

Currently the database table looks like this:
 NAME | PHONE | AMOUNT
jane|520-234-1234|50.25
john|520-452-9987|67.32
.....

I want it to look like this
 NAME | PHONE | AMOUNT|FILE_INSERTED
jane|520-234-1234|50.25|MY_FILE_20171101.txt
john|520-452-9987|67.32|MY_FILE_20171101.txt

How do I add this to my DATA FLOW TASK as a column to be inserted into the DB table?
0
Hi guys,

I have an ssis package that has a 'EXECUTE SQL TASK' that calls a stored procedure A that returns data.  

THIS WORKS When there's data from Stored Procedure A then the downstream task Foreach Loop Container runs a Script Task which sends out an email with the subject='JOB SUCCESSFUL!' and a dynamic content with data from the STORED PROCEDURE A.

PROBLEM:  

THIS DOES NOT WORK:  When there's NO data in stored procedure A then I don't want to run the downstream  task Foreach Loop Container. How do I do that (I'm using Visual Basic 2008 in the SCRIPT TASK)?

Please show me a step by step settings/configuration/code.

NOTE: I'm using SQL SERVER 2008R2.

Thank you.
0
Hi,

I have to create a multiline DOS batch file in SSIS that looks like this:

cd e:\client\data
copy generic_output.txt clientname.yyyymmdd (yyyymmdd is today's date)

I have created quite a few single line DOS copy statements that do the copy (using a Derived column in the Data Flow) but can't come up with an approach that will allow me to create a template file that contains the (hardcoded) first line and then have a Data Flow task that writes out the first line and then have the Derived column be the second line.

Can I write out both commands in the Derived Column and put in a CR-LF in between the cd command and the copy command? Or is there a better approach?

Thanks,

Dick
0
Hi there,

I have requirement to produce a csv file from SSIS. I have different data sources i think six of them with different common id fields. I suppose i need to bring them in separate queries and join them and then produce csv file. Can you please guide me how to start on this?

regards,
0
We have an SSIS package configured to run from Task Scheduler.  When you are logged into the server as the user which has rights to run the package, it will run manually and as scheduled.  When you logout it will run, but threw a Last Run Result of 0x1 and never execute the package.  The task is set to Run whether user is logged in or not.  User that runs the package is a local admin so it should be able to Log on as a Batch.  Not sure what I'm missing, but it seems like its some sort of permission issue.  Any guidance would be appreciated.
0
I have an excel destination and I want the file that get's created to me named:

OutputFileYYYYMMDD.xls where  YYYYMMDD is replace with the dates.

How do I do that?
0
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: SSL Checker
LVL 11
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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 having little bit experience on SSIS,SSRS & SSAS. I want to get advanced knowledge on this.

Please provide some realtime scenarios with solutions?
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
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

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.