Hi All,
I am working on a SSIS package which is using a Script task, now I have all the connection properties set up in the .NET script using connection strings, what do I need to do if I have to set this up using a config file or something else which is more secure (I dont want to leave the connection information in the script). Please Advice.
Thanks
A common issue that I run across with clients is they want only want to process a file if it's finished transmitting to the server. This SQL Server 2005 task reads the properties of a file and writes the values to a series of variables. For example, you can use this task to determine if the file is in use (still be uploaded or written to) and then conditionally run the Data Flow task to load the file if it's not being used. You can also use it to determine when the file was created in order to determine if it must be archived.
http://www.pragmaticworks.com/filepropertiestask.htm
Hi ,
What is the equivalent of DTS Dynamic Properties task into SSIS ?
How do I convert this task to SSIS ?
Thanks,
Vikas
I have a Dynamic propeties task in dts 2000 that process/executes a global variable.
The global variable basically executes a bat file.
How do i set this up in ssis. The migration failed to properly convert this task.
Please help.
Thank you.
Hello All Experts,
I have created one custom task with PropertyGrid Control and two button on it. I have everything under one class library project.
Problem I am facing is when i load task and clik on Edit I can not see those properties into that GUI and even functionlity of those two buttons (OK and Cancel) not working but I am able to see those properties in default property window.
If I create this GUI as a seperate window application then I am able to see those properties in GUI and buttons also working but in SSIS I am not able to load the task.
After reading on internet about SSIS they suggest to create everything under one project which I did.
Basically I am trying to populate connection managers like Source Connection and Destination Connection when I load this task and there are much more backend functionlity but at first step i m stuck and not able to see those properties in GUI.
Please help and give your input on it. I was following "Increment Task" example given by MSDN.
If you need more info let me know.
Thanks
I am trying to read in a flat file, transform the fields and store into a destination database.
In DTS, this works using Transform Data Task Properties. I define the columns and then have a VB script on the Transformations tab that changes any bad data.
Is there a way to do this in SSIS that I can define the column transformations and re-use my VB scripts?
Linda
If, in an SSIS package, you put an instance of an 'Execute SQL Task' task in the Control Flow, in the Properties window, you can see the properties of the task, for example CodePage.
If you double click on the task, the Execute SQL Task Editor appears, with several of the properties which are also in the Properties window, including CodePage.
If, in the Editor, you update the value of CodePage, then click OK, the value of CodePage in the Properties window is updated immediately.
I have written a custom SSIS task, which also has the same properties in the Properties window and in the Editor. The Editor also has an OK button. When OK is clicked, the values of the task properties are updated. An example property is FolderToArchive. If I open the Editor, change the value of FolderToArchive and click the OK button, the value of FolderToArchive in the Properties window is NOT immediately updated.
If, however, I select the FolderToArchive field in the Propertiesd window, it is then updated with the value I entered in the Editor.
How do I get my task to update the values in the Properties window, after changing a value in the Editor, when I click the OK button?
I would have thought I would need something like, in pseudo-code,
   Task.Parent.PropertiesWindow.Refresh
   where task is of type Microsoft.SqlServer.Dts.Runtime.Task and Task.Parent is of type Microsoft.SqlServer.Dts.Runtime.Package.
Hi - Im migrating packages in dts 2000 to SSIS 2005 using package migrator wizard. It loads a CSV to a table in DB. I need to change the server and file location paths after migration. While changing this, im encountering error - "the acquireconnection method call to connectionmanager failed
View 1 Replies View RelatedHI,
I need to open a File through File connection manager and want to assign these file properties to SSIS precreated varibale or Newly created varibale. I want to show file properties in Propertygrid. Properties grid will conatin File Propeties Column and SSIS varibale Combobox column. The combo box will contain New variable field. When user select New Variable field, then a new SSIS varibale window will open and we can able create New variable and that Newly created variable should add to that property comboBox.
For Instance if we create a new varibale Name "Creationdate" by clicking on New Varible in ComboBox, then that CreationDate variable should add to Property ComboBox in PropertyGrid. After adding when we select that variable Name "Creationdate" then that selected file Creation date should assign to SSIS varibale "Creationdate" field.
Any Comments or sample will help me.
Nitin
How to use variables in Connection Manager's properties? I see some replies through Configuration Package. But what if, it is still in development stage? I mean, can I use the Variable tab and create some variables like
User::DBUserNameSource, User::DBPasswordSource,
User::DBuserNameDestination, User::DBPasswordDestination,
Then put them in Password and UserName property of Connection Manager? If this is possible, how and how can I set the values of those variables I mentioned when I am going to deploy the package in the Production?
Hi JayH (or anyone). Another week...a new set of problems. I obviously need to learn .net syntax, but because of project deadlines in converting from DTS to SSIS it is hard for me to stop and do that. So, if someone could help me some easy syntax, I would really appreciate it.
In DTS, there was a VBScript that copied a set of flat files from one directory to an archive directory after modifying the file name. In SSIS, the directory and archive directory will be specified in the config file. So, I need a .net script that retrieves a file, renames it and copies it to a different directory.
Linda
Here is the old VBScript Code:
Public Sub Main()
Option Explicit
Function Main()
Dim MovementDataDir
Dim MovementArchiveDataDir
Dim MovementDataFile
Dim MovementArchiveDataFile
Dim FileNameRoot
Dim FileNameExtension, DecimalLocation
Dim CurMonth, CurDay
Dim FileApplicationDate
Dim fso ' File System Object
Dim folder
Dim FileCollection
Dim MovementFile
'======================================================================
'Create text strings of today's date to be appended to the archived file.
FileApplicationDate = Now
CurMonth = Month(FileApplicationDate)
CurDay = Day(FileApplicationDate)
If Len(CurMonth) = 1 Then
CurMonth = "0" & CurMonth
End If
If Len(CurDay) = 1 Then
CurDay = "0" & CurDay
End If
FileApplicationDate = CurMonth & CurDay & Year(FileApplicationDate)
'=====================================================================
' Set the movement data directory from the global variable.
MovementDataDir = DTSGlobalVariables("gsMovementDataDir").Value
MovementArchiveDataDir = DTSGlobalVariables("gsMovementDataArchiveDir").Value
fso = CreateObject("Scripting.FileSystemObject")
folder = fso.GetFolder(MovementDataDir)
FileCollection = folder.Files
' Loop through all files in the data directory.
For Each MovementFile In FileCollection
' Get the full path name of the current data file.
MovementDataFile = MovementDataDir & "" & MovementFile.Name
' Get the full path name of the archive data file.
MovementArchiveDataFile = MovementArchiveDataDir & "" & MovementFile.Name
DecimalLocation = InStr(1, MovementArchiveDataFile, ".")
FileNameExtension = Mid(MovementArchiveDataFile, DecimalLocation, Len(MovementArchiveDataFile) - DecimalLocation + 1)
FileNameRoot = Mid(MovementArchiveDataFile, 1, DecimalLocation - 1)
MovementArchiveDataFile = FileNameRoot & "_" & FileApplicationDate & FileNameExtension
If (fso.FileExists(MovementDataFile)) Then
fso.CopyFile(MovementDataFile, MovementArchiveDataFile)
' If the archive file was coppied, then delete the old copy.
If (fso.FileExists(MovementArchiveDataFile)) Then
fso.DeleteFile(MovementDataFile)
End If
End If
Next
fso = Nothing
folder = Nothing
FileCollection = Nothing
Main = DTSTaskExecResult_Success
End Function
I have an ODBC connection string that is working fine with the following properties:
Database="XXXXXXX",Network="YYYYYY"; strangely no server is specified in the string, but it is specified in the ODBC Connection file.
I am trying to do a new server registration in SSMS for this database.However, I don't understand where the network spec is placed.
Under Registered server name I've tried:
YYYYYYXXXXX
When I browse the server for the database instance list, I receive "network path was not found".
I even tried:"XXXXXXX",Network="YYYYYY" for the registered server name.Same error message.
What am I doing wrong ?
Hi,
under the script that I use for upload file using SSIS asp.net task.
My problem....
If I comment this line
'ftp.SendFiles(files, "", True, False) ' the True makes it overwrite existing
upload made successfull without failure instead If I leave uncomment I got an error but files upload anyway correct.
Someone can give me some explains about this behavior?
TNKS Alen, Italy
------------------
Public Sub Main()
'Create the connection to the ftp server
Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
'create the FTP object that sends the files and pass it the connection created above.
Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))
Try
'Set the properties like username & password
cm.Properties("ServerName").SetValue(cm, "xxxxxx")
cm.Properties("ServerUserName").SetValue(cm, "xxxxx")
cm.Properties("ServerPassword").SetValue(cm, "xxxxx")
cm.Properties("ServerPort").SetValue(cm, "21")
cm.Properties("Timeout").SetValue(cm, "0") 'The 0 setting will make it not timeout
cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb
cm.Properties("Retries").SetValue(cm, "1")
'Connects to the ftp server
ftp.Connect()
'****************************************
' Potrebbe servire in futuro la tengo
'****************************************
''Get file listing
'Dim fileNames() As String
'Dim folderNames() As String
'ftp.GetListing(folderNames, fileNames)
''ftp the files
'ftp.DeleteFiles(fileNames)
'Build a array of all the file names that is going to be FTP'ed (in this case only one file)
Dim files(1) As String
files(0) = "\manny-slaveappWorkFACT-FINDERa.txt"
files(1) = "\manny-slaveappWorkFACT-FINDER.txt"
'ftp the file
'I found it by mistake by creating both the FTP connection and task in the SSIS package and it defaulted the remote path setting in the FTP task.
'ftp.SendFiles(files, "", True, False) ' the True makes it overwrite existing file and False is saying that it is not transferring ASCII
ftp.Close()
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
ftp.Close()
Dts.TaskResult = Dts.Results.Failure
Finally
ftp.Close()
End Try
End Sub
Hi All,
I am using a SSIS package to import the .xml data file from FTP location and process them into SQL Server DB by using Stored Procedures. I am using FTP Task to retrieve the file from FTP location but it only works when ever internet is available otherwise it fails, I placed this package as a SQL Server Agent Job. Can anybody tell me the way to check the CONNECTION before FTP Task start its process to avoid the errors???
Please see the error below which I get from SQL Server Agent:
"Description: Failed to decrypt protected XML node "DTSroperty" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2007-12-19 04:00:03.66 Code: 0xC001602A Source: AA_Trans Connection manager "FTP Connection Manager" Description: An error occurred in the requested FTP operation. Detailed error description: The password was not allowed . End Error Error: 2007-12-19 04:00:03.67 Code: 0xC002918F Source: FTP AA Transactions Receive FTP Task Description: Unable to connect to FTP server using "FTP Connection Manag... The package execution fails"
Thanks in advance.
Zeeshan.
When I try this code in an SSIS "Script Task":
Dim dbConnectionManager As ConnectionManager = Dts.Connections(0)
Dim dbConnectionRaw As Object = dbConnectionManager.AcquireConnection(Nothing)
Dim dbConnection As OdbcConnection = CType(dbConnectionRaw, OdbcConnection)
I get this error:
Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.Odbc.OdbcConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.
I'm just trying to get a basic database connection from the DTS package in my
script task. Is there a better way to do this? Preferably, I would use ADO.NET
rather than the old COM stuff. However, if SSIS still requires the use of COM,
that's fine as well, as long as I can fix the above code.
Thanks in advance!
I have a simple SSIS package with three "Execute SQL Tasks". I am using ADO.Net Connection to execute SPs on a DB server.
When I execute this package It works fine. So far so good.
Now, I need to implement transation on this package. And problem starts now onwards. When I try to execute package after setting TransationOption = Required for the Sequence container which contains all the tasks, I get following error.
[Execute SQL Task] Error: Failed to acquire connection "NYCDB0008.Export". Connection may not be configured correctly or you may not have the right permissions on this connection.
"NYCDB0008.Export" is the name of the ADO.Net connection. I have been hunting for any solution but all in vain. I have tried changing all DTC settings on the dev as well as Database server.
Please respond if anyone has any solution.
Thanks!
Anand
Hi,
I recently came across a DTS made by an experienced DBA and was impressed by the use of Dynamic Properties DTS Task used. As I understood the DTS was generic and if I'm not mistaken, can be easily transferred to another server/machine on an AS IS basis and without having to change any of the properties (server name, login, password etc.) for the source or destination server.
This seems to be a really neat feature.
I tried to put this to use but am having problems regarding how to proceed... Unfortunately I have not been able to find any article either which addresses this particular request and takes a novice step by step so that this feature can be used.
Will appreciate any help.
Thanks
Hi,
DTS now has a dynamic properties task in sql 2000 which at a first glance is pretty cool. However i do not know if it would sort out a particular issue I have.
I need to have a generic DTS package that pulls data from different locations based on the project selected by a user. The source is a btrieve database
The name of the tables change with the project, so for example the F24 project would have its source table named F24TACT and the IFO source would have its source table named IFOTACT.
I need to be able to dynamically retrieve the table name from a local SQL table and assign this name to the data pump for the data extraction..
I don' t know if this is achievable in DTS. I can retrieve the value into a look up variable but how can i set it at runtime ?
thanks
Tony
Hey all,
I have a stored procedure, which need one variable as parameter. I am trying to call this stored procedure from my DTS Task and my parameter is defined as the Global Variable in DTS. here is the SP call within my DTS Task
declare @id int
select @id = DTSGlobalVariables('ClientId' ).value
exec sp_Update_DayPart @ClientId= @id
it gives me an error that DTSGlobalVariables function not defined. In this case how can i pass the value of Client Id which is my global variable to my SP.
Thanks in Advance
Hello,I am building a packge where an FTP task needs to pull down a singlefileevery day from a specific location. The location will only have theone file.The file name will be different every day. A sample of one of the filenameslooks like this:CDNSC.CDNSC.SC00015.04012007The file names will be different every day, as the last eight digitsrepresent the date of the data in the file. The source files will belocatedin a subdirectory called 'outgoing'.My first approach to this has been to use a Dyamic Properties Task tosetthe SourceFileName of the FTP Task. I tried using a sting GlobalVariableexpressed as *.*.*.* thinking a wildcard would work, but it didn't.What approaches can I take so that the Source Filename in the FTP taskwilldymanically update each day to get the one file?Thank you for your help!cdun2
View 1 Replies View RelatedHi everyone,
I€™d like to read the SqlStatement property for a Sql Task from a Script Task (previously Sql Task is executed) in execution.
Keeping on mind that such SqlStatement receives an input parameter.
Is it possible? I think so but how do I such thing?
Let me know if you need further details.
Thanks a lot for your time and thoughts,
HI,
How can I programmatically change the properties of Send Mail task using Script Task. I want to change the From, To and Attachment parameters of the Send Mail task.
If the Script Task can't do it, Is there any alternative to do?
Thanks
I have a component who's OnPreExecute event handler contains a Script Task. I would like to use this Script Task to access the component's properties. Can anyone provide script examples for doing this if this is possible?
View 6 Replies View RelatedI added a connection (ADO.NET) object by name testCon in the connection manager - I wanted to programmatically supply the connection string. So I used the "Expressions" property of the connection object and set the connectionstring to one DTS variable. The idea is to supply the connection string value to the variable - so that the connection object uses my connection string.
Then I added a "Backup Database Task" to my package with the name BkpTask. Now whenever I try to set the connection property of BkpTask to the testCon connection object, by typing testCon, it automatically gets cleared. I am not able to set the connection value.
Then after spending several hours I found that this is because I have customized the connection string in testCon. If I don't customize the connection string, I am able to enter the "testCon" value in the connection property of the BkpTask.
Is this an intrinsic issue?
I would like to store the file properties like (title, created by , created time, modified time) and load in to a audit table along with file name. I know how to design this process inside a foreach lookup with script task and execute sql task.
I'm trying to achieve this by editing my existing code which captures the most recent file based on its file properties.Â
public void Main()
{
// TODO: Add your code here
var directory = new DirectoryInfo(Dts.Variables["User::Csv_Source"].Value.ToString());
FileInfo[] files = directory.GetFiles("*.csv");
DateTime lastModified = DateTime.MinValue;
[Code] ....
I have SSIS packages that send success/failure email upon completion, and I'd like to add a note that identifies the server and database used. I can certainly add variables to the packages and use them when constructing the email, but I'd prefer to get the information directly from the OLE DB connection itself. Is there a way to access the connection string from within a control-flow VB script task? Furthermore, can I get the data source and initial catalog from that connection string, or do I need to parse it myself?
Thanks!
Phil
Howdy folks,
I'm trying to get a better idea of how I should set some of the SSCE connection properties. I will be deploying the application on a WinCE5 board with a 4G flash storage card and 512MB RAM. The application must run 24/7 with a medium amount of traffic. At any given point I expect up to 3 connections to the same database. I'd really like to keep performance high without risking any database corruption issues. As such, I need to make sure my connection properties are optimized.
I read from Joao's article that setting DBPROP_SSCE_MAXBUFFERSIZE = 1024 gave a considerable performance increase, while anything higher gave diminishing returns. Is this for a certain amount of RAM, or is it uncorrelated to RAM size?
What's the best practice for specifying DBPROP_SSCE_TEMPFILE_DIRECTORY and DBPROP_SSCE_TEMPFILE_MAX_SIZE? Is it possible in WinCE5 to directly put it in RAM? How big do these temp files get?
I've read of some bad experiences with autoshrink. Are there any reasons not to set DBPROP_SSCE_AUTO_SHRINK_THRESHOLD to 100 if the database will be compacted regularly?
Any other advice for the more obscure settings such as DBPROP_SSCE_FLUSH_INTERVAL or all the lock settings?
Thanks a bunch!
I'm programmatically loading a package that was created with VS 2005. The last task in the package is a Send Mail Task. It has two properties, FileAttachments and ToLine, which are set to expressions whose values come directly from package variables. The package runs in debug and non-debug (under VS) correctly.
View 3 Replies View RelatedI implemented a custom source adaptor. I want to be able to associate custom properties with each of the output columns. I want them to be passed downsteam. The idea is to be able to retrieve these information in a downstream custom transformations of ours and process the various columns accordingly. How do I go about doing this?I noticed that the IDTSCustomProperty90 seems to have a local scope only.
View 1 Replies View RelatedI want to transfer data from one server to another by using SSIS. i want the connection string to be dynamic and also according to the some other variable, the transforming data is changing.
Could you provide me the solution thet how i am able to change my connetction string dynamically and the other variable too.
i am using VS 2003 as front end and SQL server 2005 as a backhand.
Due to VS.NET 2003 i am able to create DTS packages but i have to migrate it and then anly i am able to use it in JOB in SQL server agent of SQL server 2005.
is that any code or any stored procedure from which i am able to migrate DTS packages to SSIS packages.
Thank you
I designed a DTS package with eleven different connections. When I try to see the proberties of a connection the enterprise manager immediatly closes without giving me a warning. This happens only with two connections (in my example M1 and M2). This mistake has no influence to the execution of the package. In my opinion it is a fault of the DTS package desinger but may be that I made something wrong.
Is there anybody who knows this mistake? Thanks for your help.
I used to do this in DTS but wondered how to do it in SSIS. I have a For Each ADO recordset loop that reads these four columns into variables:
DataSource Name, Name(for a WHERE clause), user and password.
For each "Building" there is a new server to connect to, with identical tables. I have 10 tables in the loop to pump from one database to a SQL 2005 database (i.e. 10 dataflows). I want to set the source connection information with the variables. My questions:
1. How do I set the connection information for the source connections? Do I just set the properties for the connection manager and then it sets for all the sources? What property do I set--do I need a script task?
2. How do I use the values from the "Name" column in a WHERE clause in each data flow?
Thanks!
Kayda
Hi,
I saw some thing called custom properties for the "Derived transformation" in the msdn site. I tried to use them in a simple package, but I am getting an error as "can't write to derivedoutputcolumnname.friendlyexpression". Friendly expression is one of the custom properties available for the derived transformation output columns.
The steps I followed to get to this error are as follows:
1) Get data from a table using OLEDB Source. Suppose I am getting firstName, LastName etc.
2) Derived column input is values from the above OLEDB Source.
3) I have added a new column called "Concatenated name" which is concatenated value of first and last names.
4) Then in the properties editor of this data flow task in expressions option I clicked on ellipse available. I got an editor for property expression, which contained two columns called "Property" and "Expression". Property column contains dropdown with friendly expressions propety for the derived columns and expression column is a text box, where in we can enter expression to be evaluated for the corresponding friendly expression property.
5) Now when I click on OK and try to debug it gives an error as "Can't write to concatenatedname.friendlyexpresiion".
If anybody has already faced this problem and solved it please let me know, because I am struck here a long time.
Thanks&Regards,
Sreekanth Ammisetty