How to add txt to csv file 1

[Cpreston]

Hi

I am fairly new to SSIS and am currently exporting data from our SQL server into a csv file format. This I have working to a certain extenet but have a couple fo questions maybe you guys could advise on.

1. The csv file requires a couple of fields populating with data that does not exist in the database. Foer example one filed needs to just have the letter D in it, nothing else. Is there a way in SSIS I can make it do this. I found I could add a filed into the export parameters but nowhere I can make a default entry.
2. How do we automate the file to run. I have googled it but cannot find a simple answer. So I want it to run every 15 minutes and overwrite the old file.

Hope you can help and please remember I am fairly new to SSIS so any replies please make idiot proof

 

[micang]

Hi,

1.) Have you used a "Derived Column" item in your Data Flow? You can use this to add columns and populate them with what data you like.
2.) You can use "SQL Agent" to schedule your package.

Michael

 

[Cpreston]

Hi

I noticed the Derived column and took a look but could not figure out how to use it so it added in a column to the flat file destinations with just the letter D filling the columns.
I googled it but cannot find a simple example to follow, all quite in depth for me.

I have also noticed the SQL agent to sue and actual found a good guide so once I have the file working I will mover on to the actual agent side of things.

Do you know of any idiot guides to derived columns. many thanks

 

[Cpreston]

Hi

Ok managed to get it working with the derived column. Now for the scheduled task side.

 

[micang]

Glad you got the Derived column working.

Hopefully this will help with the scheduling:

https://www.mssqltips.com/sqlservertutorial/220/scheduling-ssis-packages-with-sql-server-agent/

Michael

 

[Cpreston]

Hi

I have tried all sorts to get it working but keep getting errors. I have ran the package form the file location and it runs ok no problems. Cant work out why it will not run through the Job. Any ideas please. I put the error message in form the History log. Thanks


Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility Version 11.0.5583.0 for 32-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 15:27:22 Error: 2016-03-10 15:27:22.39 Code: 0xC0011007 Source: {BE79642E-D827-4EDD-8FE0-4C9BF4C093EC} 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: 2016-03-10 15:27:22.39 Code: 0xC0011002 Source: {BE79642E-D827-4EDD-8FE0-4C9BF4C093EC} Description: Failed to open package file "C:\Users\cpreston\Documents\Visual Studio 2012\Projects\ExportCSV\ExportCSV\Package.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. End Error Could not load package "C:\Users\cpreston\Documents\Visual Studio 2012\Projects\ExportCSV\ExportCSV\Package.dtsx" because of error 0xC0011002. Description: Failed to open package file "C:\Users\cpreston\Documents\Visual Studio 2012\Projects\ExportCSV\ExportCSV\Package.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. Source: {BE79642E-D827-4EDD-8FE0-4C9BF4C093EC} Started: 15:27:22 Finished: 15:27:22 Elapsed: 0.031 seconds. The package could not be found. The step failed.

 

[micang]

Hi,

It could be a permission issue - talk to your DBA or IT department.

Thanks
Michael

 

[Cpreston]

Hi

Definitely not a permission (well fairly certain anyway) issue I have the server loaded locally and also tried a shared network path to run the file from. Both packages execute from the running it directly from the package, so scratching my head on this one.

 

[Cpreston]

Hi

Managed to get it working, it was a folder permissions, solved by moving the package folder to another location on the C:\ drive.

All working now

Thanks for the help

 

[micang]

Thank you for the feedback and star, am glad you got it working, you're very welcome.

Michael