Hello, I have a situation in which I need to use a foreach iterator that will perform a transformation on each excel file in a directory. The file names will change, but the structure will stay the same.
I was able to get this working by assigning the file path for each iteration to a variable, and then using that variable to set the excelfilepath in the Excel connection manager. However, for this to work I have to assign the variable to a default file.
Because of this, when I try to deploy the package I need to also add a configuration property for the variable, otherwise the first run will fail. The dummy file doesn't even really need to exist - I just have to put in a valid path, and then use any name that has an .xls extension. After that it runs fine regardless of what is in the directory.
This seems odd that I would need to do this - am I missing something? Apart from creating the Excel Connection Manager programatically (which I'm guessing might solve this), is there a way to avoid having to specify this dummy file?
Hi, Trying to dynamically set the connectionstring property of the excel source. This is what I enter instead of the hardcoded excel file paths: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::FileName] + ";Extended Properties=Excel 8.0;HDR=YES"
I get this error, every time I set the delay validation property of the dataflow tab to true. Cannot detach from one or more processes. The object invoked has disconnected from its clients Do you want to terminate them instead?
I was wondering if there is a way to 'Move File' with the File System Task inside of a For Each Loop container but to dynamically set the Destination path variable.
Currently, this is what I have: FileDestinationPath variable - set to C:TestFiles FileSourcePath variable - set to C:TestFiles FileNameAndLocation variable - set to blank
For Each Loop Container €“ Iterates through a folder C:TestFiles that has .txt files in it with dates in the file name. Ex: Test_09142006.txt. Sets the file path (fully qualified) to the Variable Mapping FileNameAndLocation.
Script Task (within For Each Loop, first step) €“ Sets the FileDestinationPath to the correct dated folder within C:TestFiles. For example, if the text files I want to move are for the 14th of September, it takes FileDestinationPath and appends the date folder to the end of it. The text files have a date in the file name (test_09142006.txt) and I am picking this apart (from FileNameAndLocation in the For Each Loop) to get the folder date. (dts.Variables(€œUser::FileDestinationPath€?).Value = dts.Variables(€œUser::FileDestinationPath€?).Value & €œ€? Month & €œ_€? & Day & €œ_€? & Year & €œ€?) which gives me €œC:TestFiles 9_14_2006€?.
File System Task (within For Each Loop, second step) €“ This is where the action is supposed to occur. I want it to take the FileDestinationPath and move the FileNameAndLocation file (from the For Loop) into this folder for each run.
Now as for my problem. I want this package to run everyday but it has to set the FileDestinationPath variable dynamically according to that day€™s date. Basically, how do I get this to work since I can€™t hard code the destination path variable from the start? I have the DestinationVariable on the File System Task set to the FileDestinationPath variable, after the script task builds it. However, using FileNameAndLocation as the SourceVariable on my File System Task tells me that the €œVariable €œFileNameAndLocation€? is used as a source or destination and is empty.€?
Let me know if I need to clarify further€¦...I may be missing something very simple. Any help would be greatly appreciated!
I am deploying programatically an Excel 2007 file to a SQL Server 2005 Reporting Server. The problem is that if a file with the same name already exists, that file isn't replaced. I would like the opposite to happen. I'm using the following code:
--Executable
set svr=http://w3sdwsqld1/reportserver set src_fld="\w3sdwsqld1\deploy\SAD\ECRANS\UPDATES_20061127_190000\Ecrans\AM\Associados\" set dest_fld="Associados" set script="\w3sdwsqld1\deploy\SADECRANS\UPDATES_20061127_190000\Ecrans\AM\Associados\PublishReports.rss" REM Sample: deploy.bat http://w3sdwsqld1/reportserver "\w3sdwsqld1\deploy\SAD\ECRANS\UPDATES_20061127_190000\Ecrans\AM\Associados\" "Associados" "\w3sdwsqld1\deploy\SADECRANS\UPDATES_20061127_190000\Ecrans\AM\Associados\PublishReports.rss" for /R %src_fld% %%f in (*.xlsx) do rs -i %script% -s %svr% -v ParentFolder=%dest_fld% -v reportP="%%~nf" -v path=%src_fld% PAUSE
--rss Code
' ' Script Variables ' ' Variables that are passed on the command line with the -v switch: ' ' (a) parentFolder - corresponds to the folder that the script creates and uses ' to contain your published reports
' (b) reportP - corresponds to the report to publish
Dim ROOT As String = "/SAD/Ecrans/Ecrans/AM"
Dim definition As [Byte]() = Nothing Dim warnings As Warning() = Nothing Dim parentPath As String = ROOT + "/"+ parentFolder Dim filePath As String = path Dim report As String = reportP
'Create the parent folder Try rs.CreateFolder(parentFolder, ROOT,Nothing) Console.WriteLine("Parent folder {0} created successfully", parentFolder) Catch e As Exception
Console.WriteLine(e.Message)
End Try
'Create shared data source 'CreateSampleDataSource("Solucao_Integrada", "OLEDB-MD", "Data Source=dwareas1;Initial Catalog=SAD_Solucao_Integrada")
'Publish the sample reports PublishReport(report)
End Sub
Public Sub CreateSampleDataSource(name As String, extension As String, connectionString As String) 'Define the data source definition. Dim definition As New DataSourceDefinition() definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated definition.ConnectString = connectionString definition.Enabled = True definition.EnabledSpecified = True definition.Extension = extension definition.ImpersonateUser = False definition.ImpersonateUserSpecified = True 'Use the default prompt string. definition.Prompt = Nothing definition.WindowsCredentials = False
Catch e As Exception Console.WriteLine(e.Message) End Try
End Sub
Public Sub PublishReport(ByVal reportName As String) Try Dim stream As FileStream = File.OpenRead(filePath + reportName + ".xlsx") Console.WriteLine(reportName)
definition = New [Byte](stream.Length) {} stream.Read(definition, 0, CInt(stream.Length)) stream.Close()
Catch e As IOException Console.WriteLine(e.Message) End Try
Catch e As Exception Console.WriteLine(e.Message) Console.WriteLine("Failed to publish report") End Try End Sub --------------------------------------------------------------------------------------------------------------------
I have a SSIS Package that exports data from Sql Server to an Excel file. I need help figuring out how to have the file name be "Report_02132007.xls". Basically I want to append the date to the file name. Any ideas?
I've seen a number of posts similar to this but i still cannot figure out what i need to do to get it working. So here goes with a couple of newbie questions.
Question 1: Once created how do i go about executing a SSIS package. I want to be able to call it from a C# application from which i pass in a couple of parameters?
Question 2: How do i go about setting the file path of my Excel source to a dynamic value passed at runtime. I want to be able to loop through a number of Excel files and do some processing on them. I've set up a variable (which i think i need to do) after that i get stuck however. Some other posts suggest configuration packages but i cannot get my head around how they work?
Any help on this matter would be gratefully recieved.
I am trying to create an ssis package with dynamic csv file as output. and out format contains query output.
sample file name:
Unique identifier + query output + systemdate();
The expression is looking like this.
@[User::FilePath] + @[User::FileName] + ".CSV"
-- user filepath is a variable from ssis package. File name is the output from SQL query. using script task i have assigned the values to @[User::FileName] .
When I debugged the script task the value getting properly but same variable am using for Flafile destination. but its not working.
I got your email address from your web cast. I really enjoyed the web cast and found it to be very informative.
Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question regarding the product. I have looked for the information on the web, but was not able to find relevant information.
We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets Are generated using reporting services. On examining the excel sheet, I found out that the name Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc. And even the number of columns are not static. It depends upon the range of date selected by the user.
I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic Number of columns.
Your help in this respect is highly appreciated!
Thanks,
Hi Anthony, I am glad the Web cast was helpful.
Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using.
I suggest you post a question on the SSIS forum on MSDN and you should get some good answers. http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1 http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
I am creating an SSIS package witha a Dataflow task, which reads from an Excel source and then uses script component to dumpt the data to multiple tables in Sql Server database
I need to some how make my Excel source dynamic, that is my excel template which i would be using to map the excel columns to script component's input columns would be dynamic..
In other words, I should be able to define the Excel Source, Column Mapping Information, Precedence constraint to the Script component dynamically
We have been having problems getting a linked server to an excel file working with an UNC path. If the UNC path is to the SQL server itself it will work but not if the UNC path is to another server.
The SQL server 2000 SP 4 processes are running under a domain id and we can logon interactively with that same login id and we can access the excel file via the same UNC path.
We have tried it with setting up a linked server and also linking to it 'on the fly':
SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="\AnotherServerUNCpathexcel.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...sheet1$ go
The message we are getting is the following: Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error. OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].
It appears from reading Microsoft's documentation and other topics that this should be possible. Any ideas on what we are missing?
Currently have a single hard coded file path to the SSRS config file which parses the file and provides the reporting services web service url. Â My question is how would i run this same query against 100s of servers that may or may not share the same file path as the one hard coded ?
Is there a way to query the registry to find the location of the config file of any server ? which could be on D, E, F, H, etc.Â
I know I can string together the address followed by "reports" and named instance if needed, but some instances may not have used the default virtual directory name (Reports).
Am I going about this the hard way ? Is there a location where the web service url exists in a table ? I could not locate anything in the Reporting service database. Basically need to inventory all of my reporting services url's.
I am running my package in sql server 2012, in which i am giving network path for flat file destination. And its working fine. But if i give m local path, its giving me  error " cannot open data file" ...
I have an issue with a DTS package. We create a zip file and then attach it to emails going out using DTS. The problem is that the attachment, when received, is named using the full path to the file, so it is quite long.
Has anyone seen this before? Is there a way out of this?
I am considering mapping a drive to the share holding the file to be named, but the fact is this will shorten the name but will still result in the path being included.
I am wondering if this is a bug, as I suspect this isn't the default behaviour.
I experienced a weird error while deploying my SSIS package. After running the manifest file, i noticed that one of the configuration file's path was not updated in the dtsx file. My solution has 8 packages and almost every package has 2 configuration files. Except 1 file every other config file's path is being updated. Has anybody experieced such a problem?
I have the Excel Connection Manager and Source to read the contents from an Excel file. For some reason couple of numeric fields from the Excel worksheet are brought over as nulls even though they have a value of 300 and 150. I am not sure why this is happening. I looked into the format of the fields and they are set to General in Excel, I tried setting them to numeric and that did not help.
All the other content from the excel file is coming thru except for the 2 numeric fields.
I tried to bring the contents from the excel source to a text file in csv format and for some reason the 2 numeric fields came out as blank.
Any inputs on getting this addressed will be much appreciated.
I am having issue in running a ssis package which connects to an excel file from shared location.It works fine on the machine of the person who has developed it as he has access to that shared drive.After deploying the ssis package to SSISDB and creating a proxy account with the developer's credential, and running the ssis package using the proxy under SQL Agent Jobs, it is failing with error :
Load XXXXXXXXXX :Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "XXXXXXXXXX.xlsx"
failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
XXXXXXXXXX:Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available. Source: "Microsoft Access Database Engine" Hresult: 0x80004005 Description: "Failure creating file.".
Is there anyway to  send excel file from ssis using send mail task without saving the excel file locally. I need to automate the process which involves loading the excel file from the database and send it to some people.Â
I cannot get the log file path read from the configuration.
If the path in the Connection Manager is invalid, package throws an error "SSIS logging provider has failed to open the log" instead of reading it from the config.file. What am I doing wrong?
Here is the portion of the config file. Everthing else is read from the config file correctly.
How do I dynamically change the file path for the source file in DTS? Can i use global variable?. if so , How do i use it in the connection task. Appreciate your help.
Hi,A quick question, is it possible to deploy a website on a shared hosting environment which attaches to a MDF database file stored in the App_Data folder?
Suppose we are two developer.Who have SSRS install at there own system. Our database server is intalled on our main db server.
1,First thing does it necessay to create a shared datasource. I have a web application in which I want's to add a folder say My reports in which i will put my reports {that is .rdl file} Then I want's to hit a url with render format as excel this will bring down my report in excel format.
2,How to configure a connection I want's to pass the connection string in the rdl via some xml file let say myconnectionn xml file which look like <connection-sources> <source name="Mycon1" default="true" > Data Source=abcyukon;User ID=sa;Password=as;Initial Catalog=MyDbName1 </source> <source name="Mycon2" skip="true" > Data Source=asdsadabcyukon;User ID=sa;Password=as;Initial Catalog=MyDbName2 </source> </connection-sources>
connection string would be where attribute default of source should be true.
I'm trying to write a SP thataccept in input a parameter with the nameof a file (with complete path)but I noticed some problems....It's right this way? Thanks!set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[BI]@FileToImport nvarchar(100)ASBEGINSET NOCOUNT ON;DECLARE @SQL nvarchar(200)SET @SQL = "BULK INSERT tmptable FROM '"+@FileToImport+"'"EXEC (@SQL)END
Hey there,I used the FOR XML PATH feature but the XML result is in only one line,it does not conserve the XML structure (treeview). When I open the XMLfile with IE, it s working very (I have the XML hierarchy) but when Iuse Visual Studio or other tool to edit it, I ve just one line!For example:<children group=""><child firstname="" lastname=""></child>=""><childfirstname=""lastname=""></child>..............................</children>............And I d like to have<children group=""><child firstname="" lastname=""></child>=""><child firstname="" lastname=""></child>..............................</children>..............To give you an example of the query:SELECTChild.group AS '@group',(SELECT firstnameAS '@firstname',lastnameAS '@lastname'FROM Collecte_Data_Extract cdeFOR XML PATH('child'), TYPE)FROM Feed AS ChildFOR XML PATH('children'), TYPEAny idea?Many thanks in advance
I have a sql compact table that stores a series of directory paths like so: C:F1BackupadministratorCDatakit.zip__--1-07272007124351.F1O C:F1BackupadministratorCDataDeploySMD_FSWsetup.exe__--1-11292007122443.F1O C:F1BackupadministratorCDataDeploySMD_FSWSMD_FSW.application__--1-11292007122443.F1O
Is there a way in sql that I can get just the directory name so the results would look like this: C:F1BackupadministratorCData C:F1BackupadministratorCDataDeploy C:F1BackupadministratorCDataDeploy
Also, is there a way to just get the file names so the results would look like this: kit.zip__--1-07272007124351.F1O setup.exe__--1-11292007122443.F1O SMD_FSW.application__--1-11292007122443.F1O
I've seen some examples with Sql server but they use the reverse function which is not available in sql compact.
Hello! Please help me. I want to know, what i do wrong or may be samewhere documentation is describe this situation:
sql server on servername filename.txt is situated on servername c:filname.txt and this query is runing on this server Version of Msjet40.dll on servername is 4.0.9025.0 SELECT ( [Host] ) AS [Host] e WHERE EXISTS(SELECT PVListFile.F1 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source="c:";User ID="Admin";Password=;Extended Properties="text;HDR=No"')...filename#txt as PVListFile WHERE [Host] LIKE PVListFile.F1 COLLATE database_default ) AND NOT ( EXISTS(SELECT PVListFile.F1 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source="\servernamec$";User ID="Admin";Password=;Extended Properties="text;HDR=No"')...filename#txt as PVListFile WHERE [Host] LIKE PVListFile.F1 COLLATE database_default ) ) ORDER BY e.[Host], e.[Mac], e.[startGMT], e.[stopGMT]
i get this error message [OLE/DB provider returned message: The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data.] OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' ICommandText::Execute returned 0x80004005: ]. Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
Is there any way I can configure the Configuration file Path.
I have an understanding that wherever I store my package but my Configuration file(XML) must be store in the place specified in the package. In my dev environment I have C Drive as such the package is in DevServerNameC:SSIS.
However, there is no C drive allocated in Prod envirnment as such package is going to rest in ProdServerName/SSIS
Now if I place my package and execute here and it will look for configuration files(XML) which are on C file path. Then I guess my package fails.
I am just wondering how can I make my package configure file path dynamic?
Trying to upload excel in server where excel is not installed. BIDs was there in the server, when i am trying to craete Excel source I am not able.what the workround for this.. How to upload excel without excel installed on the server.
I am using a Excel Source to get the data from an excel file to sql server 2005 table. A couple columns are coming in a double precision float, but some values have characters in them, but those values are coming out as null, even though I changed the datatype from float to unicode string. Any inputs on resolving this will be much appreciated.
