I've just started using SQL Server 2005 Integration Services and
I've come up against a situation where I need to name output files
dynamically (i.e. based on a timestmap). Looking through this
newsgroup, and other web resources it looks like my only option is to use a Script Task/ActiveX Script Task to rename the file after it has
been created with a generic file name.
I was wondering if there was a different approach or if there was a way to pass in a variable to the file connection manager that I could append to the file name at run-time.
Is there a way to dynamically rename a report? For example if I schedule a report to run every day and I want the report names to be like Report_Dec01_2006, Report_Dec02_2006, etc. This way the users can see which days data is in each of the report.
Every day an application creates new tables and dumps static info into them.Â
I would like to create a package to dynamically export those database tables to raw files for long term archive, one file per table. Here is what I have so far and the issue I am having.
1)Â Â Â Â Â Get a list of un-archived tables. 2)Â Â Â Â Â Foreach table do the following.
a. Export the table into raw file. b. Zip the raw file. c. Update archive tracking table.
As long as the metadata for each table is the same this package seems to work fine. However, I have many tables with different metadata. How can I dynamically get the package update the metadata column collection when it hits a new table? When it hits a table with different metadata I am getting warnings like this:
The column "some_column" needs to be added to the external metadata column collection.
The "external metadata column "someother_column" (103)" needs to be removed from the external metadata column collection.
Then I get this error: Error: 0xC004706B at dump the table into a raw file, DTS.Pipeline: "component "OLE DB Source" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA"
hi , I am trying to copy files dynamically from a remot folder by using File System task , the files are going to be generated daily with f1yyyy-mm-dd.txt I created 3 file connections : - Existing remote folder (RemotFolder) and set that to var v_remotefolder - Existing destination folder (DestFolder) - existing file (filename) and set the expression to var (string ) =v_fname in the File System task I set the the Destination connection = DestFolder Operation =Copy file Source = filename expression >>Property (source) = @[User::v_remotefolder] + @[User::v_fname] I know I need the date too ,but I 'm getting error on this step. Can you please tell me if you know how to resolve this without using For each loop since I don't have control on the remote folder and I don't want to copy unneeded files (the files are 10 GB daily) and get delete it once every month. -I know this not uncommon task but I can't find any example. Thanks
We are using MSBuild with a custom activity to build and deploy ISPACs from SSDT BI 2012. We are building the ISPAC by calling the invoke process activity which inturn performs a build of the solution using devenv.exe.
All of this is working fine until we have solutions/ projects (dtproj) with complex names. We have a solution named Company.Project.SSIS.Package.sln when we do a build it creates an ISPAC as expected however it is named incorrectly - Company.Project.ispac.Â
When we subsequently deploy the ISPAC, it creates a project in the catalog called Company.Project.SSIS again this is wrong. This also happend if i perform a manual build in VS2012.Â
The issue is, the automated deployment of the ISPAC fails as it is looking for a file with a different name. Is there a way of changing these settings?
I need to get a list of customer ids and then use them as a parameter to select from a transaction table and then create a file for each customer. I have used an execute SQL task to get the list of customers and have put the result set into a variable.
How do go through the recordset to create a file for each customer?
I am pretty new to SQL Server 2005 and SSIS. I am trying to develop a package that will dynamically load files into SS2005 based on the contents of a configuration table. The configuration table (see below for example) contains the path to the file, a flag indicating whether or not to process the flag, the type of file (specifies the nature of data -- financial, order, etc.) and some parameters specific to each file.
FileName ProcessFlag Type ExcelTab Param1
C:File1.xls TRUE 1 Sheet1$
C:File2.xls TRUE 1 Sheet1$
C:File3.xls TRUE 1 Sheet1$
C:File4.xls TRUE 2 Sheet1$
C:File5.xls FALSE 2 Sheet1$
C:File6.csv TRUE 3
C:File7.txt TRUE 4
Right now I basically have a seperate sequence for each of the file types. The task in each sequence are virtually identical with the exception of the the data flow source in the data flow task (since the source file could be .xls, .txt, .csv). The first sequence ran fine in isolation, but when I linked a second sequence I started getting a Package Validation Error: DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER I use the same variables (FileName, ExcelTab) for each of the sequences, so I am not sure if that causes te error. I also tried changing the ValidateExternalMetadata setting to false, since the connection variable wont be applicable until that sequence is being processed. I am not sure where to go here ... should I rearchitect how the package all together?
Is there a better/most efficient way to to architect this package to handle the different file types (develop a package for each type which is called by a master package, create one package with a different sequence container for each type, etc).
I am pretty new to SQL Server 2005 and SSIS. I am trying to dynamically load files into SQL Server 2005 using files/paths contained in tables. I have a key table in my SSIS package that defines which files should be processed, some default values that will be associated with each file (ie CompanyKey and PeriodKey) and the file path (see below for example):
CompanyKey
PeriodKey
ProcessFlag
FileName
2
61
FALSE
2005TB200501.xls
2
62
FALSE
2005TB200502.xls
2
63
FALSE
2005TB200503.xls
2
64
TRUE
2005TB200504.xls
2
65
TRUE
2005TB200505.xls
2
66
TRUE
2005TB200506.xls
2
67
TRUE
2005TB200507.xls
2
68
TRUE
2005TB200508.xls
2
69
TRUE
2005TB200509.xls
2
70
TRUE
2005TB200510.xls
2
71
TRUE
2005TB200511.xls
2
72
TRUE
2005TB200512.xls
The package I am developing is supposed to loop through this table checking for files with ProcessFlag = TRUE. For those files, it will load all of the records/columns (prefaced with the company and period keys) into a common table.
Do I have to manually create data sources for each file or is there a way to dynamically define the connection and process the connection?
Any assitance you could provide would be greatly appreciated!
There is one manual process done by my friend. The process is " In query analyser, she runs a sql query and the resultset of Sql query, she copies and pastes into a excel workbook".
I wanted to write a SSIS package to automate it and providing a UI, in which she can give connection details and paste the SQL query and the package will in turn generate excel workbook in the target path". I thought of trying ExecuteSQL task, but the output resultset of ExecuteSQL task cannot be mapped dynamically to excel source(correct me if I am wrong). If I use Dataflow task, then also whether dynamic mapping of SQL output to Excel destination input is possible?
Can you please tell me the best approach for achieving the above functionality?
I have developed an SSIS package which extracts and creates 5 flat files and finally using Process Extraction task zip the folder. On my Dev environment everything is working fine but when I am moving to SIT and UAT, not able to set up jobs dynamically by importing XMLConfig file.I created variables and assigned values but still it doesnt take.Below are varaibles I created for flat file destination, Arguments and Working Directory (for zipping)On UAT when I go to SQLAgentJobs to set, import .dtsx file, XML config file....the new values doesnt appear. why ?DataSource is taking always dev location....why ? How can I set it up to take dynamic values what I mentioned in config file ?
Okay, here's my thing: I have a file that looks (sort of) like this:
A1a A2b A3c A4d A1e A2f A3g A4h A5i A1j A1k . . .
What I want to do is split these apart. A1 should be the first record in every new file. The first file would have four records, the second one would have five, the third one would have one, etc. How do I specify more than one output? Is there a way to do, e.g. "Output001.txt", "Output002.txt", etc.?
Brief overview...Running SQL Server 2003 Server Enterprise 64 bit - All Service Packs and patches current SQL Server 2005 Enterprise Edition 64 bit Build Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
I cannot import any SSIS packages nor crete any new folders under stored packages. I hve googled the news groups and looked at BOL to no avail. HELP!!!!
Hi;Before I start let me say I know this is a silly way to go aboutthings, but it is one of those "my company made me do it" things.My company is in the process of ( finishing ) migrating from foxpro tosqlserver.They have a foxpro program that does a lot of various updates. Iwould like to write a tsql script to replace it.The trick is that the tsql script would have to have some proceduralprogramming artifacts that I don't think it has.I/my company would like the script to generate output messages thatthe user can see while the script is running. Everytime I have ran ascript in query analyzer I usually don't see the output ("print"statemetns, etc ) until all of the work isdone. Is there a way to run thing so I can see the output statementsas the work is being done?Can print statements work from within a tsql loop?The other thing is that I/my company wants the tsql script to writeoutput messages to a log file while it is operating ( for errorchecking and other logging).Is this possible with tsql?Thanks in advance for the infoSteve
In SQL2005 (SP2) I have a standard user (User1) who owns some jobs. He has been granted the "SQLAgentUserRole" in the MSDB database so he can see his jobs and run them. He has all the normal permissions (Select, Update, Insert, Execute, Delete, View References) to the schemas in the database he works in.
Here is my problem. When a developer who is logged in as User1, tires to edit a job step that has an output file (which was initially moved from another server by a DBA with SA rights) he gets the following error message.
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ Only a sysadmin can specify '@output_file_name' parameter for a jobstep. (Microsoft SQL Server, Error: 14582) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3156&EvtSrc=MSSQLServer&EvtID=14582&LinkId=20476
I have tired all kinds of permissions (like granting the user SQLAgentOperatorRole) but it is still a no go. If I (as the DBA) remove the job step's output file, then he can edit it. He can also edit other job steps in the job, as long as they do not have output files associated with them.
How can I let the job owner use output files with their job steps and not make them SA. It just does not make sense.
We are trying to schedule a SSIS package as a job in the SQL Agent. However, we need to schedule the job dynamically. There is no fixed date (or period) when this job runs.
Is it possible to dynamically schedule the job? The next execution date can be stored in a database table or a config file, etc.
If not, then perhaps we can include a task at the top of the Control Flow, which, will check if the package needs to execute "today". For this the job will have to be scheduled on a daily basis.
i am transfering the table from one database to csv file format..i did it.. again i want to shift that csv files to another databse as tables. how to do this task.. pls help me.. its very urgent..out TL had given me the dead line.. send reply soon....
I have an ssis package that contains a DateTime variable named RefDate its value saved in the package is 8/31/2006. I setted up a Package configuration for read the value of RefDate from the parent package.I inserted a script task with message box to verify that the package configuration works. When I launch the parent package the message box shows the value of RefDate setted in the parent package. The problem is that when I use RefDate to dynamically set the connection string of the log connection of the SSIS log provider for XML files for the child package, the connection string contains the child value of RefDate.
Hi, I am new to SSIS and i have to develop a ssis package which will run in a production machine through VB.Net(2003) exe.I am facing a problem while setting connection string of SSIS package dynamically.Can anybody help me on this?
I'm using isqlw to generate delimited text files from scripts. There are several SET options available so that you can restrict the output to just the data in the script, for example SET NOCOUNT ON.
I can't find a similar SET option to suppress the column headings, although there is a tickbox that allows you to do this in Query Analyzer (Query -> Current Connection Options -> Advanced -> Print Headers)
DTS packages appear to support this, yet I can't find the setting from a dts file.
I have one share folder ,every month end-user will copy & paste excel file into particular share folder. Ok . Now i have to create new SSIS package as schedule should run every month to find the file and then load automatically into Sql server tables and then move those excel file to another share folder if file successfully loaded only. The excel file name will be changing every month. but the format wont change. If any body knows this process or steps. Please share with me .
Hi everybody, i'm a newbie to SSIS and I'm having a problem dynamically creating a new excel spreadsheet in SSIS. What I need to do is be able to dynamically create a brand new Excel spreadsheet after a data flow task completes.
I currently have a list of User IDs (in a flat file) and I need to connect to a database I have read-only access to, so that I can retrieve additional data about these users.
I imagined a package that ran a query something like:
SELECT * FROM table WHERE UserID IN (<dynamically populated from flat file>).
Can somebody give me some advice as to how I can achieve this (either the way I suggested or another way).
I would like to modify "Files" attribute of the Foreach Loop of type File Enumerator. This attribute is used to set the mask (for example *.txt) to specify which files to include in the selection. I need to be able to change this mask dynamically depending on package global variable. Is this possible?
In my SSIS package I have a loop container that I am running the same code against 4 servers. I have the package export the SQL data to an Excel spreadsheet that has multiple tabs.
Is there a way I can change the tab on the fly or do I need to create a Connection for the same spreadsheet 4 times Each Connection pointing to a different tab?
I tried to set up a expression for the Excel Connection Manager to use the InitialCatalog for the tab and change it based on the script in the loop however this causes the following error:
An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Cannot create an OLE DB accessor. Verify that the column metadata is valid.
I seem to be missing something with SQL 2005 SSIS and I am wondering how I would accomplish my taks. With SQL 2000 and DTS we designed our DTS packages so that we could load one file or database from start to finish. We then have controlling programs written in VB that will monitor directories, when a file shows up in a directory it loads the package, changes the necessary properties and executes it. These programs handle all of our error notifications and dealing with the files afterwards, making sure files are complete in the directory before the DTS runs, etc. With .NET and SQL 2005 I have learned that I can change the properties by using variables and expressions, which I think would handle all of our situation but I can not run them remotely. The documentation says that I could setup a scheduled job and then call sp_start_job from my program. That seems like it would require a lot of scheduled jobs and then I still need to get the parameters into the packages. The other option is to use a web service, that would require IIS on my SQL box which everywhere says I don't want. Am I using SSIS for something I am not supposed to be or how do I accomplish this in 2005? I have noticed that you can not even run packages through the SQL Server Management Studio. We have a number of packages that are built that are run on demand when needed. In SQL 2000 I could just click and execute the package. In 2005 is my only option to setup some sort of fake job in the job scheduler?
Trying to figure out the best method of reading in a number of flat files, all with different number of columns and data types and outputting them to a database.
Here's the problem: They are EBCDIC encoded and some of the columns are packed decimal. I've set up one package that takes the flat file, unpacks the decimal (Using UnpackDecimal component) and then sending the rest through a second component to go from EBCDIC -> ASCII.
What I need is a way to do this for every flat file based on the schema for that flat file. One current solution is to write a script/app to create the .dtsx XML file and then execute that for each flat file. It appears like this may be possible, but I haven't gotten far enough to know for sure. So my questions are this:
1) Is there an easier way to do this (ie somehow feed the schema to the package and use it to dynamically set up the column makers and determine which columns get fed to the unpack decimal component.
2) If there isn't a better way, will dynamically creating the .dtsx XML file based on the necessary input/output columns for each flat file work? If so, what is a good source of information on this (information about how the .dtsx XML file is set up, what needs to be changed/what doesn't, etc).
I have an issue where I'm trying to export data from Sql Server tables (or from a result set in a SP or view) into Excel Spreadsheets. Normally I would use a simple data flow to do this. However, I need to do this on-the-fly because the schema of the Sql data is not static. The table could be a different one or the result set would have column schema that is not always the same.
The constant in all of this is that the spreadsheet columns and the table (or result set) column schema is identical. It's just that the column count and column names are not defined at design time, but would need to be defined at runtime.
Going from Excel to Sql Server is simple as I used a Script Task and the SQLBulkCopy class to dynamically transfer the data. However, BOL says that it's only one way (Data to Sql Server). Basically I need the to go the opposite direction now.
I have all of the information (SQL Table server, database, schema, and name and the Excel file path and name) already set up in variables and running through a ForEach container and I can dynamically change the variable information. I just need to figure out how to dynamically map the columns, create the spreadsheet file, and load the data into the spreadsheet. I'm sure this has been tossed around before. If someone could point me in the right direction I would most grateful.
