SSIS

1K

Solutions

21

Articles & Videos

1K

Contributors

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

If we have delimiters  as "." then     why "Will D. Smith" and "Will D Smith" returns similarity  as 0.98 instead of 1?
0
Free Tool: ZipGrep
LVL 8
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

It would appear that SSIS is now called SSDT. Is that correct?

I need to learn as much simple functionality as I can of SSIS by Friday for an interview opportunity. The largest one I have ever had.
Then I need to brush up on my SQL Server Query abilities. Which are old and rusty. Any thoughts on how I can get caught up quickly?
I understand the concepts of both products and have used SQL Server in the past just need any input so that I can snag a job. Please advise. Thank you.
0
Hello,

I have a text file that is well over 1GB in size (1+ million rows).  I am trying to read the file, have it fix something potentially with each line and then write out each line, including fixes to another file.

I am running into a "out of memory exception as my current process reads everything into memory.  Is there any way to  invoke a Batch Size with either StreamWriter and StreamReader?

I am happy to include my current working code if it is easier to customize that.

Thank you...
0
I imported an SSIS package with Import Project Wizard (SSDT)  from SSISDB.   I made changes to the package  via  SSDT.   How do I save the changes  back into  SSISDB via SSDT tool?   I do not get the option to update the original package (SSISDB)  with the changes.
0
Hi All,

I have a column names as listed below, Data types are Varchar

       cdn#                       CC#                           CCType

      B5793846      ****-****-****-9043        Visa


For the above i Need to add zeros and display in  the below format,

cdn#                                     CC#                                     CCType

000000000000B5793846      0000000000009043        CC(Constant)


Currently I am working on the below Query, Please can any one help me i tried with Format function. Please kindly review My Query and let me know any modification to be done.

select right('D00000000000' + REPLACE(CONVERT(VARCHAR,amount),'.',''),11) AS AMOUNT,
  [Account #]as cdn#,
  [CC ACCT]as CC#,
  [CardType] as CCType 
  from [PayPal_staging].[dbo].[VendorFiles] as a
   join [CUBS].[dbo].[Client] as d
  on d.PK_Client=CONVERT(varchar(250),a.client)
  where  PK_Client in  ('SWMC203','SWMC600','SQMC203','SQMC600','SBMC203','SBMC600',
    'PROV203','PROV600','SWRC203','SWRC600','SMCC203','SMCC600',
    'SWIS203','SWIS600')
Union all
  Select [Amount],[CDN #] as cdn#,[Card_No] as CC#,[Card_Type] as CCType 
  from [PayPal_staging].[dbo].[VirtualFiles] as b
   join [CUBS].[dbo].[Client] as d
  on d.PK_Client=CONVERT(varchar(250),b.[Client_Id])
  where  PK_Client in  ('SWMC203','SWMC600','SQMC203','SQMC600','SBMC203','SBMC600',
    'PROV203','PROV600','SWRC203','SWRC600','SMCC203','SMCC600',
    'SWIS203','SWIS600')
  union all
  select [SV10_Amt] as Amount,[Cdr_Id] as 

Open in new window

0
I am trying to parse a cleansed file for rows (that exist) that do not have 18 delimiters (a pipe).  When it finds a row that does not have 18 pipes, I need to to write or append the line/row to the error file.

Currently, I cannot get the currentLine to write to a file as I get a "cannot convert from 'string' to 'System.Collections.Generic.IEnumerable<string>" error.

Can someone please help?

        string fixedpath = Variables.xFixedFolder + Variables.xCurrentFixedFile; // Ex: C:\Folder\Fixed\FixedFile.txt
        string errorpath = Variables.xErrorPath + Variables.xErrorFile;  // Ex: C:\Folder\Error\FixedFile.txt
        //string delim = ("|");

        ComponentMetaData.FireInformation(10, "File Operation", "Checking for erroneous rows, write to error file: " + errorpath, "", 0, fireAgain);

        // Reads all lines in the Fixed file
        string[] text = File.ReadAllLines(fixedpath);

        // Parses throught the file, line by line
        foreach (string line in text)
        {
            string currentLine = line;
            int totalNumberOfPipes = currentLine.Split('|').Length - 1;

            // If the line contains more or less than 18 pipes, it's a bad row
            if (totalNumberOfPipes != 18)
            {
                // Write error rows to error file
                ComponentMetaData.FireInformation(10, "File Write", "Error rows found, writing to file: " + currentLine, "", 0, fireAgain);
                

Open in new window

0
Hi All,
I need to remove the decimal point and add leading zeros in the derived column, Can anybody Help me what expression to be used.

Example :no decimal (i.e. $50.00 would be represented 00000005000)
0
i have this error
- Executing (Error)
Messages
Error 0xc02020c5: Data Flow Task 1: Data conversion failed while converting column "COL_11" (50) to column "COL_11" (115).  The conversion returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
 (SQL Server Import and Export Wizard)
 
Error 0xc020902a: Data Flow Task 1: The "Data Conversion 0 - 0.Outputs[Data Conversion Output].Columns[COL_11]" failed because truncation occurred, and the truncation row disposition on "Data Conversion 0 - 0.Outputs[Data Conversion Output].Columns[COL_11]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
 (SQL Server Import and Export Wizard)
 
Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Data Conversion 0 - 0" (107) failed with error code 0xC020902A while processing input "Data Conversion Input" (108). 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.
 (SQL Server Import and Export Wizard)
 
Error 0xc02020c4: Data Flow Task 1: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
 (SQL Server Import and Export Wizard)
 
0
I have a xml file I have been trying to import data from to sql server using ssis packages. After some research I learnt that there are namespaces that I have to get rid of so, I put an xml task to clean the xml file. I have given both the files below.

After that I generate an xsd file which gives an error:
"Pipeline component has returned HRESULT error code 0xC02092A1 from a method call
Error at data flow Task [XML Source[1]]: The XML Source Adapter does not support mixed content model on Complex Types."

Thank You for the help.
0
Hi Expert,

I want to inventory all of my SQL Server Instances along with Server Name, DB_Name, DB_Owner, Create_Date, Version, Edition, Recovery_model as much i can retrieve.

Please Help!
0
On Demand Webinar - Networking for the Cloud Era
LVL 8
On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

0
I have the following code in a Batch file I am trying to call
dtexec /f C:\EW\SSIS\BlueGreen\Package.dtsx

I am getting the message that I need to install the Standard Edition of Integration services or higher

Which I DO have installed

Screen Print
0
Hi All

As i am new to SSIS can any one help me with the below task, As i need to transform the data from three tables to .txt with Header,detail and trailer format.

Please help me with the link and step by step procedure to start with the project....

File Type:       Fixed width file .txt
Frequency:      Daily, if no transactions are taken the file should contain the Header & Trailer Record indicating no trans count
      H – Header Row (this remains the same daily).
      D – Detail/Data Row
      T – Trailer Record

File Name:            SMCBDPMT_PASC_1_MMDDYY.txt

PayPal Manager:            acsswepasc

Client #s:      SWMC203; SWMC600; SQMC203; SQMC600; SBMC203; SBMC600; PROV203; PROV600; SWRC203; SWRC600; SMCC203; SMCC600; SWIS203; SWIS600
Header:
H21707263227615
Detail:
D00000006000000000000000667567770000000000009043CC
•      D – data type
•      Next 11 – dollar amount with no decimal (i.e. $50.00 would be represented 00000005000)
•      Next 20 – HAR (client reference#/CDN)
•      Next 16 – Credit Card # (last4)
•      Next 2 – Payment type (in this case it will always be CC)

Trailer Record:

T00000032248680000000159

•      T – Trailer Record
•      Next 13 – Sum of dollar amount with no decimal ( i.e. $3500.00 would be represented as 0000000350000)
•      Next 10 – Count of transactions/line items
0
I am using the import /Export to export selected tables from a database.   I am looking for  a way to import primary keys and all other constraints on the table.  I tried the generate script options but the tables  have millions of records so the scripting is not working very well.

Any inputs appreciated
0
Is there a way to run a SSIS Package directly in a Windows Task Scheduler

OTHER THAN...

A batch file of some kind that uses the dtexec command?

dtexec /f C:\EW\SSIS\BlueGreen\Package.dtsx
0
I am getting the following error message on executing a DTS Package through a batch file

Source: Execute Process Task    Description: To run a SSIS package outside of SQL Server Data Tools you must install Standard Edition of Integration Services or higher.

This is the code in my Batch FIle
dtexec /f C:\EW\SSIS\BlueGreen\Package.dtsx



I have this VS BUsiness Tools Version installed which I build the Package in
SP
0
Hi Expert,

This is my following query but there are 3 condition ebs, non-ebs and open

select
case when i.is_ebs = 1 then 'EBS'
        when i.is_ebs <> 1 then 'Non-EBS'
        when i.instancename IS NULL then 'OPEN'
        when i.is_ebs is null  then 'Non-EBS'
            end as is_ebs
    ,o.hostname
      ,o.appname
,      o.processorcount
,      o.memorysize
      ,i.instancename


from oraclehosts o
left join oracleinstances i on o.hostname = i.hostname
left join hostwarranty w on w.hostname = o.hostname
Collate SQL_Latin1_General_CP1_CI_AS
left join puppetdb_certname_facts p
on p.certname = o.hostname where ipaddress not in ('xx.jj/cc', 'cc.uu/dd') and
p.fact = 'oracle_license' and p.fact = 'oracle_license' AND  p.value = 'true'


group by
i.instanceName
   , o.hostname
,      o.processorcount
,      o.memorysize
 , i.is_ebs
 ,o.appname

Query Output:--

is_ebs      Hostname appname      Processorcount memory      instancename

OPEN                     xyz      ddd            4                            15.42 GB      NULL
OPEN                    xxx     ccc            48                           125.64 GB      NULL
EBS                           yzx      eee            10                           251.57 GB      DEV            
EBS                          crs      rrr            10                          251.86 GB      xyx      





NB. I want create three section report and each section want to add processor count



is_ebs      Hostname appname      Processorcount memory      instancename

OPEN      xyz                         ddd            4                    15.42 GB      NULL
OPEN      xxx                         ccc            48                    125.64…
0
hi,

anyone running SQL 2012 with SSIS on top of VMWare 5.1 VM?

any problem of it ? 100GB disk should be enough for it...

is SSIS failover in AOG in VMWare 5.1 possible ?
0
Hello. I have two SQL Server Agent Jobs that I created practically at the same time. Both jobs access views (unique views from the same source server) and send data to the same destination.

Executed as individual packages, they both run fine. Scheduled as a SQL Agent Job, one runs fine and the other results in an error. Both jobs are using the same account to access and run the packages, so i'm not sure what's going on.

Below is the error. Please let me know if you need more information.


The job failed.  The Job was invoked by User DOMAIN\webadmin

Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted.  End Error  Error: 2017-05-15 11:00:08.45     Code: 0xC0011002     Source: {2865C875-09B5-43E3-BBA4-137B5EDDD893}

Description: Failed to open package file "C:\Users\webadmin\Documents\DataPackage1.dtsx" due to error 0x80070005 "Access is denied."

This occurs when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of specifying an incorrect file name when calling LoadPackage or the specified XML file has an incorrect format.

Any help would be much appreciated.
0
On Demand Webinar: Networking for the Cloud Era
LVL 8
On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

A new database was set up, to which I have access.  Only 2 tables and neither has data.

I can login from SQL Server Management Studio.

However, I get the following error when I test a connection from SSIS:

     "Test connection failed because of an error in initializing provider. Logon failed for login (blah) due to trigger execution"

I've done the Google searches, but can't find anything specific.
0
I use SSMS often and have used SSRS as well.

What does SSIS do? What are the biggest features of SSIS?
0
I am currently working on a solution to load a data warehouse from a SQL Server 2005 source. From my research it seems the best way to do an incremental load is to use rowversion. The tables I need to use all have timestamp fields. This is great but I am confused as to the best way to handle these in SSIS, particularly in converting them. I am currently using min_active_rowversion() to los into a variable and compare it with the previous value stored in a utility table.

The code used to get the current active version is;

'select ? = CONVERT(varchar(50),min_active_rowversion(),1)'

I then use the following to get the last rowversion used for the last extract.

Select ? = isnull(VersionStamp,'0x0000000000000001')
from DWPROC.ETLRowversions
where SchemaName = 'dbo'
and TableName = 'Products'

Both results are mapped to String variables, 'MaxR' and 'Latest' respectively.

To determine what has changed the following is used:

"select * from dbo.Products where Timestamp > " +  
@[User::Latest] + " and Timestamp <= " +  @[User::MaxR]

When I run this, I don't get any results and there should be. Stepping through it, I'm getting an empty string in the variables. I think my proble is data conversion but I'm not sure how to implement this. I think I should be using dbo.fn_varbintohexstr() but I am not sure how to use this or where.
Any guidance would be appreciated.
Thanks
0
Hi Experts,
                    I  am facing an issue in sql server agent job for a project that I created.  Details are given below. Thanks in advance

Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility  Version 11.0.6020.0 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  12:02:41 PM  Error: 2017-05-09 12:02:42.86     Code: 0xC020200E     Source: Data Flow Task Flat File Source [374]     Description: Cannot open the datafile "\\Sono\MART\Demand05082017.csv".  End Error  Error: 2017-05-09 12:02:42.86     Code: 0xC004701A     Source: Data Flow Task SSIS.Pipeline     Description: Flat File Source failed the pre-execute phase and returned error code 0xC020200E.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  12:02:41 PM  Finished: 12:02:42 PM  Elapsed:  0.922 seconds.  The package execution failed.  The step failed.
0
Hello all,

What is the best way where I can hit a Rest Service URL I define where I would dynamically through a for container somehow pass an ID as the last part of the URL http://myserver.app/id/40 and as I loop through if I don't return a response for one of the calls I need to set a variable as error.

Thanks all
0
If I select use Windows Authentication how to I make sure it is not using my Windows account? We have a Windows account that has some sql rights and file rights on the network. Since I'm using SSIS to run a view and then write to a file it would seem I need a windows account that we can use that has just enough rights to do both. Using SQL authentication doesn't seem logical in this scenario. Problem is we have an account to use but that only seems to be available to set up to schedule the job to run from SSMS, but that is not the same account that you would think is being used to create the connection to the database within the SSIS package.
0

SSIS

1K

Solutions

21

Articles & Videos

1K

Contributors

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.