Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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 a request of how to write a Execute SQL task in SSIS.   More of a fail safe for when a vendor's file does not make it to our system.    This is causing the below issue.

I have a SQL job that pulls using a WINSCP Windows task script to retrieve the latest CSV file from a SFTP site.   Then the SQL job Step 2 sends the file to the SSIS package for loading into SQL.   The problem occurs when the SFTP site does not receive a new file.  
Step 1 does not fail because it successfully retrieved the latest file, however, Step 2 using the package fails because the file that was pulled already exists from the previous pull.   I need a way to check in the SSIS package to see if the dynamic named CSV file exists before processing.    I am very green to writing variables and I have never used the Script task command.  Is there a way to do this check before my package kicks off the For each file task by using the Execute SQL task  ?    

As a side note, I do not have permissions to delete older files off of the SFTP site.    And, even though the SQL Job Step 2 says it failed, it still executes the CSV file so now I have duplicate records for the same day.

Please advise exact steps as I am a beginner.

0
 

Author Comment

by:JOELL MERRITT
Oh ok !   Sorry!  :)
0
 
LVL 26

Expert Comment

by:Brian B
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 ,how to stop or disable transaction log file (.ldf file) using SQL query or ssis
0
 
LVL 24

Expert Comment

by:yo_bee
I am not an expert in SQL, but I do not think that this is possible. Transaction logs are a key part of the database world.
0
 
LVL 26

Expert Comment

by:Brian B
You have entered this as a post, but it looks like it is a question. To get the best response from the Experts, you should probably re-submit this as a question using the "ask a question" button at the top of the screen, or this link: https://www.experts-exchange.com/askQuestion.jsp

Please see here for further details: http://support.experts-exchange.com/customer/portal/articles/756544-how-to-succeed-at-ee-as-an-asker
0
We are importing (Bulk Insert) of around half million records to the staging table and then to the production table.

More often column data type or size validation fails the bulk insert process from Staging table to the Production table.

I will have to redesign the process by SSIS packages by inserting all the good records from Staging to the Production and load the bad / error out data to the staging table. Those bad or error out data with proper validation message.

Validation message is based on first error out column. If the all the columns in that row are erroring, we can consider with the first column error and reload the data after fixing that data.

Example Error Message: Column PurchaseDate has invalid data

How do I achieve this complete workflow SSIS package. What are the controls, I need to use to start with?

Please :)
0
 
LVL 12

Administrative Comment

by:Andrew Leniart
Hi chokka,

What you have done is made a "Post" here. To get help from the experts, you need to "Ask a Question" so that more experts are able to see that you need help. Click the Big blue button near the top of your screen.

Ask a Question

The following link also explains more about asking for help at Experts Exchange..
http://support.experts-exchange.com/customer/portal/articles/336330

Hope that's helpful.

Regards,
Andrew
EE Topic Advisor
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.